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: Wed, 06 Jun 2007 18:04:36 -0700
Message-ID: <1181178276.301462@bubbleator.drizzle.com>


silverback_at_photobooks.com wrote:

> On Jun 5, 9:12 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> silverb..._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"clusterwaits.
>>> 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 ofwaitevents 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.Clusteris 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.Clusteris 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 andWaitfor 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
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> It's vendor code, of course it is badly written.
> The selects show no rows, so I guess I need to wait for the blocking
> to start (two week cycle). I will report back when I know something.
> 
> If this is the problem, is there any tuning I can do other than
> changing the pctfree/pctused to spread the records?
> 
> Thanks again for the code.

Depends on what is causing the problem. Assuming it block sharing is it index blocks or data blocks? Is it inserts, updates, and deletes or selects?

If it is index blocks you could try converting Normal B*Tree indexes into Reverse B*Tree Indexes. Other suggestions I would make relate to primary key strategies but since you don't control the app you couldn't make those types of changes.

Hit the PSOUG links page: http://www.psoug.org/links.html and look at the "High Availability" links.

-- 
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 Wed Jun 06 2007 - 20:04:36 CDT

Original text of this message

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