RE: Need help tuning FTS

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 <john.kanagaraj_at_hds.com>@fatcity.com on 06/06/2001 05:47:24 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: john.kanagaraj_at_hds.com

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: ListGuru_at_fatcity.com (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: Srini.Chavali_at_Cummins.com

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: ListGuru_at_fatcity.com (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).