Wolfgang, Thanks for the offer. You're lucky. I got to go to New Jersey for the last three days. The trace I posted was my initial run with no attempt to tune. I had just never seen such a large discrepency between the wait elapsed time and the wall clock time in the trace file. The explanation posted by you and by Cary makes sense.
One way to get a breakdown by cpu on AIX is via sar. I've been trying for months, unsuccessfully, to get the SA to grant permissions to that command (by default in AIX it is only granted to root). Thanks for the offer to look at the 10053. Now that I've finally gotten the chance to work on some of the fun stuff, I'd like to give it a whirl myself. Is the offer still open if I run into trouble :) Henry -----Original Message----- Wolfgang Breitling Sent: Thursday, July 24, 2003 6:19 PM To: Multiple recipients of list ORACLE-L It's been about a decade that I have been working with AIX. There must be tools that give you a breakdown by cpu. Else you can do a ps -ef or ps aux and watch your process. If I'mm right you should see it consuming 99% cpu all the time. 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras 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).
