Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: RAC Cluster Waits
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. Received on Wed Jun 06 2007 - 14:55:30 CDT