Re: RAC Cluster Waits

silverback@xxxxxxxxxxxxxx wrote:
Hi,

I have a 10.2 RAC with 3 nodes. It is an OLTP system.

We are seeing what may be "excessive" cluster waits.

I need to know how to judge what is ACTUALLY excessive, and how to
tune the system. Please direct me to a detailed or complete refrence
describing the parts of "Cluster" and how to tune them. I see many
lists of wait events on the web, but no description nor how to tune.

Why do I think it may be excessive?

This was a CPU intensive system in the 9i, non-RAC environment, I
expected the CPU to be a sizable percentage of waits/activity.

Our system runs fine except during our bi-weekly crunch. The waits are
heavily CPU biased, but the total waits are low. Cluster is an almost
invisible line on the GRID graph. Our ASH reports look fine.

During the crunch time, the users report long delays for report
requests and row updates.
Ash reports the Top DB Objects as follows:
Index - "gc current block 2-way" activity 16.3% event 16%
Index - "db file sequential read" activity 5.22% event 5%
Table - "db file sequential read" activity 2.58% event 2.5%
Table - "db file sequential read" activity 1.3% event .77%
Index - "db file sequential read" activity 1.2% event .98%

When I monitor the system via GRID, I see that CPU is about 60% of the
activity. Cluster is about 25%. We see "gc current block 2-way" a good
bit.

One of our Top SQL Statements is a select using 37% of activity.
It has event percents = 20% "CPU and Wait for CPU" and 16% "gc current
block 2-way".

Thanks in advance

-- Current block transfer statistics
col "AVG RECVD TIME (ms)" format 9999999.9
col inst_id format 9999
prompt GCS CURRENT BLOCKS

SELECT b1.inst_id, b2.value RECEIVED, b1.value "RECEIVE TIME", ((b1.value/b2.value)*10) "AVG RECEIVE TIME (ms)"
FROM gv$sysstat b1, gv$sysstat b2
WHERE b1.name = 'global cache current block receive time'
AND b2.name = 'global cache current blocks received'
AND b1.inst_id = b2.inst_id;

-- measure block contention using block transfer time
col "AVG RECVD TIME (ms)" format 9999999.9
col inst_id format 9999

SELECT b1.inst_id, b2.value RECEIVED, b1.value "RECEIVE TIME", ((b1.value/b2.value)*10) "AVG RECEIVE TIME (ms)"
FROM gv$sysstat b1, gv$sysstat b2
WHERE b1.name = 'global cache cr block receive time'
AND b2.name = 'global cache cr blocks received'
AND b1.inst_id = b2.inst_id;

Likely you are experiencing the spotlight effect caused by a poorly
designed schema causing a lot of block sharing between nodes. Lets
see.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Windows was unable to complete the format
    ... Originally I had a cluster with 2 poweredge dell servers running server ... The cluster is connnected to storage array EMC AEX-4 box. ... something went wrong in the process and now I can't format the volumes on ...
    (microsoft.public.dotnet.general)
  • Re: 2 large hard drive questions
    ... However, on the cluster issue, I was wondering what effect cluster size would have on these big ... I did install my OS on its own partition, about 40GB and then formated the rest of the ... smaller drives but I was thinking that in the same way the manufacturers use of 1000 instead of ... It will format it automatically. ...
    (microsoft.public.windowsxp.hardware)
  • GPF - how do you map two different devices names to the same nsd device name from two different
    ... Write now I am trying to setup GPFS w/o any underlying clustering s/w ... The media is not formatted or the format is not correct. ... 6027-1371 Propagating the cluster configuration data to all ...
    (comp.unix.aix)
  • Re: Problem Copying Files to a ZIP Drive
    ... The "size on disk" may be much more than that, depending on how the Zip ... ("cluster"), and the size of a cluster depends on the format. ...
    (microsoft.public.windowsxp.basics)
  • RAC Cluster Waits
    ... We are seeing what may be "excessive" cluster waits. ... describing the parts of "Cluster" and how to tune them. ... This was a CPU intensive system in the 9i, non-RAC environment, I ... Our ASH reports look fine. ...
    (comp.databases.oracle.server)