Path: news.f.de.plusline.net!news-fra1.dfn.de!news.tele.dk!feed118.news.tele.dk!postnews.google.com!j4g2000prf.googlegroups.com!not-for-mail
From:  silverback@photobooks.com
Newsgroups: comp.databases.oracle.server
Subject: Re: RAC Cluster Waits
Date: Wed, 06 Jun 2007 12:55:30 -0700
Organization: http://groups.google.com
Lines: 85
Message-ID: <1181159730.300722.30080@j4g2000prf.googlegroups.com>
References: <1181085244.167045.156370@h2g2000hsg.googlegroups.com>
   <1181092366.958882@bubbleator.drizzle.com>
NNTP-Posting-Host: 170.140.201.191
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1181159731 23645 127.0.0.1 (6 Jun 2007 19:55:31 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 6 Jun 2007 19:55:31 +0000 (UTC)
In-Reply-To: <1181092366.958882@bubbleator.drizzle.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: j4g2000prf.googlegroups.com; posting-host=170.140.201.191;
   posting-account=If5v1w0AAAB7I5IqHoXNc6GUm8xKG0Fi
Xref: news.f.de.plusline.net comp.databases.oracle.server:199043

On Jun 5, 9:12 pm, DA Morgan <damor...@psoug.org> wrote:
> silverb...@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...@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.