Sorry, I can't take credit for improving the query.
The improved time looks like an accident. The plan now has two scans of OBJ$, rather than one - it just that the two plans take 4 and 9 seconds - whereas the single scan in the first plan took 370 seconds. I guess that the first plan was a major anomaly due to recent changes to obj$, largely on the other node, aided (damaged) by heavy activity on the other node when you were running this query. Note how you've done twice the global cache cr lock requests in one fiftieth of the time. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____England______January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 03 January 2003 16:30 > > >After taking Jonathan's advise ... here are the results ... much better than >before. > >select --+ leading(dba_types.type$) > * > from dba_types > >call count cpu elapsed disk query current >rows >------- ------ -------- ---------- ---------- ---------- ---------- >---------- >Parse 1 0.02 0.02 1 2 0 >0 >Execute 1 0.00 0.00 0 0 0 >0 >Fetch 30 4.75 15.12 9020 12465 0 >431 >------- ------ -------- ---------- ---------- ---------- ---------- >---------- >total 32 4.77 15.15 9021 12467 0 >431 > >Misses in library cache during parse: 1 >Optimizer goal: CHOOSE >Parsing user id: SYS > >Rows Row Source Operation >------- --------------------------------------------------- > 431 NESTED LOOPS OUTER (cr=12465 r=9020 w=0 time=15120561 us) > 431 NESTED LOOPS (cr=12454 r=9020 w=0 time=15117437 us) > 431 HASH JOIN (cr=11131 r=9018 w=0 time=15104996 us) > 463494 TABLE ACCESS FULL OBJ#(18) (cr=5270 r=4611 w=0 time=4150680 us) > 436 HASH JOIN OUTER (cr=5861 r=4407 w=0 time=10245897 us) > 436 TABLE ACCESS FULL OBJ#(298) (cr=590 r=587 w=0 time=669055 us) > 464557 TABLE ACCESS FULL OBJ#(18) (cr=5271 r=3820 w=0 time=9079489 us) > 431 TABLE ACCESS CLUSTER OBJ#(22) (cr=1323 r=2 w=0 time=10389 us) > 431 INDEX UNIQUE SCAN OBJ#(11) (cr=461 r=2 w=0 time=4140 us)(object >id 11) > 4 TABLE ACCESS CLUSTER OBJ#(22) (cr=11 r=0 w=0 time=1096 us) > 4 INDEX UNIQUE SCAN OBJ#(11) (cr=6 r=0 w=0 time=258 us)(object id >11) > > >Elapsed times include waiting on following events: > Event waited on Times Max. Wait Total >Waited > ---------------------------------------- Waited ---------- >------------ > SQL*Net message to client 30 0.00 >0.00 > global cache cr request 5654 1.22 >7.58 > db file sequential read 130 0.01 >0.10 > db file parallel read 613 0.03 >0.66 > db file scattered read 2033 0.10 >3.44 > latch free 1 0.00 >0.00 > cr request retry 2 0.00 >0.00 > SQL*Net message from client 30 45.09 >46.04 >===================================================================== ======= >== > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
