RE: 10.2.0.4 Patch + CPU 24
- From: "Vishal Gupta" <vishal@xxxxxxxxxxxxxxx>
- To: <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Nov 2009 22:51:00 -0000
Chris,
Cache buffer chains wait event usually occurs, when someone is getting
lot of data from disk into SGA. May be while you were running your
query, some other rogue process was doing lot of physical read. And on
your second run, other process might have finished.
Regards
Vishal
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Taylor, Chris David
Sent: 23 November 2009 21:02
To: 'Allen, Brandon'; 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: 10.2.0.4 Patch + CPU 24
The query did not use bind variables.
I verified that by forcing a different execution path (bypassing the
function based indexes) caused the query to return to its pre-patch
timing. However, I did not verify that the indexes in question were
being used by the query pre-patch. Though, I would have to assume they
were considering nothing else changed as far as optimization parameter
or statistics. But that is an assumption.
The reason I started digging into latches was because when I originally
started looking at the query I was getting a wait event on LATCH: Cache
Buffer Chains while consistent gets were spiraling upward.
Then subsequent tests of the query didn't seem to give me the same wait
event. Sometimes the wait event would be "SQL*Net message to Client",
other times it was "db file sequential read" - In all cases the
consistent gets I/O was spiraling up, but with different wait events
listed as the "waiting event".
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxx
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential
and may also be privileged. If you are not the named recipient, please
notify the sender immediately and delete the contents of this message
without disclosing the contents to anyone, using them for any purpose,
or storing or copying the information on any medium.
From: Allen, Brandon [mailto:Brandon.Allen@xxxxxxxxxxx]
Sent: Monday, November 23, 2009 2:53 PM
To: Taylor, Chris David; 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: 10.2.0.4 Patch + CPU 24
I don't see how you made the jump from a poorly performing query to
looking for a bug with latches. It sounds like you're just getting a
different execution plan either due to a change in optimizer behavior,
or maybe just from the usual problem of peeking at a different set of
bind variables? Have you done a comparison of the explain plans for the
query before and after the patch? You should have the old explain plans
still available in AWR, or in statspack if you were running those
snapshots instead of AWR at level 6 or greater.
I'm not clear on why you dropped the indexes either - are you sure those
aren't being used by any other queries? It's possible that by dropping
those indexes, you just caused the query to be reloaded with a new set
of bind variables that caused the optimizer to select a better plan -
that is assuming that the query uses bind variables - can you confirm?
________________________________
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do
not consent to Internet email for messages of this kind. Opinions,
conclusions and other information in this message that do not relate to
the official business of this company shall be understood as neither
given nor endorsed by it.
Other related posts:
