I have been looking after Peoplesoft systems for several years and I know those cascading NLs. They can drive up logical reads and cpu usage to astronomical heights. Do you by any chance have optimizer_index_cost_adj or optimize_index_caching changed from their defaults? Care to send me the sql and a 10053 trace of the explain? A warning though. I will be out camping the next three days, so the earliest I will be able to look at it is Monday (july 28th).
At 01:54 PM 7/24/2003 -0800, you wrote:
Wolfgang,
There are 4 cpu's, and file# 65 block# 6041 is from the driving table of
the 5 table join (all NL joins). I will take a look at v$bh to see what
blocks from the other tables are in memory next time I run this. Aside from
this indirect approach, any other suggestions on confirming your plausible
hypothesis? Is there a way to breakdown the workload of individual cpu's?
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).