Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: RAC Cluster Waits

Re: RAC Cluster Waits

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 05 Jun 2007 18:12:47 -0700
Message-ID: <1181092366.958882@bubbleator.drizzle.com>


silverback_at_photobooks.com 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

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;

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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jun 05 2007 - 20:12:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US