John,
Thanks for your input !
Unfortunately, this issue seems too weird ! (I know, I know, I shouldn't
be saying that as a DBA !!).
We have run that process three times today in prod in an effort to try and
isolate the issue. We have run it when only one other user was hitting the
prod database heavily using a select stmt on unrelated tables - but the
problem still persists. There were no concurrent requests running at the
time. The issue does not seem to be related to contention of resources.
I have tried to figure out what the hot blocks are (via x$bh) but have come
up empty-handed. I replied to Chris Spence's suggestions where I posted the
query & the explain plan - they look pretty simple.
I'm trying to figure out why this behavior is not evidenced in QA. Any help
would be appreciated !!
I will post a solution/cause when I crack this case !
Thanks !
Srini
John Kanagaraj <[EMAIL PROTECTED]>@fatcity.com on 06/06/2001 05:47:24
PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Srini,
I am sure you have lots of users in Prod compared to QA :)
Anyway, the point is that you are suffering from hot block contention which
reflects itself in latch waits on 'cache buffer chain'. I remember that
Steve Adams has a SQL at his site that identifies blocks that are 'hot' - I
have used a modified version of that to identify objects that are hot. If I
know that there are corresponding Conc. Manager jobs that are accessing
that
part of the Apps, then I know that this is due to contending reports.... I
have managed to reduce this by rebuilding stale indexes as heavy
inserts/updates/deletes also add to this. Short of re-scheduling jobs and
playing around with _DB_BLOCK_HASH_LATCHES, I don't see any solutions. Do
post us if you manage to reduce contention using the _ parameter as we have
a similar problem.
John Kanagaraj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
