Title: RE: cache buffer chains contention
Would it seem likely for Oracle to be
doing ANYTHING for a full 30 seconds
without hitting another wait?
Matt Adams - GE Appliances - [EMAIL PROTECTED]
Reason is 6/7ths of treason. - The Xtals
-Original Message-
From: Jonathan Lewis
The Xtals
-Original Message-
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 10, 2002 6:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: cache buffer chains contention
seconds_in_wait increments every three seconds,
so if State is anything other than WAITING
It's certainly possible for Oracle to be pushed
into very heavy CPU usage, particularly for
PX slaves, but even in serial queries.
Two common 'causes' are queries with
correlated sub-queries against small tables;
and queries which have been over-indexed
and hinted to avoid table-scans.
seconds_in_wait increments every three seconds,
so if State is anything other than WAITING the
column tells you how much time has passed
since the last wait completed. (Contrary to the
urban legend that says the value is meaningless).
There are various anomalies and oddities about
wait
Have a look of the hot blocks. The most probable reason for cache buffer
chains are SQLs with bad performance. Don't discard contention on higher
level latches as redo allocation.
So to look for those hot blocks, use one of the S.Adams scripts:
hot_hash_blocks.sql ( www.ixora.com.au) and these
