> "Jamadagni, Rajendra" wrote: > > Does any know how to speed up following query? > > Select * > from dba_types > / > > It is taking about 2-3 minutes on my 9202 database. I see a lot of > Global Cache waits. The hammer shows following information ... > > ******************************************************************************** > > select * > from > dba_types > > call count cpu elapsed disk query > current rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > Parse 1 0.00 0.01 0 2 > 0 0 > Execute 1 0.00 0.00 0 0 > 0 0 > Fetch 30 4.14 375.13 2548 7945 > 0 431 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > total 32 4.14 375.15 2548 7947 > 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=7945 r=2548 w=0 time=375133913 us) > 431 NESTED LOOPS OUTER (cr=7936 r=2548 w=0 time=375131832 us) > 431 NESTED LOOPS (cr=7927 r=2548 w=0 time=375126319 us) > 431 NESTED LOOPS (cr=6604 r=2548 w=0 time=375115595 us) > 463566 TABLE ACCESS FULL OBJ#(18) (cr=5304 r=2548 w=0 > time=373229866 us) > 431 TABLE ACCESS BY INDEX ROWID OBJ#(298) (cr=1300 r=0 w=0 > time=739155 us) > 562 INDEX UNIQUE SCAN OBJ#(300) (cr=738 r=0 w=0 time=259416 > us)(object id 300) > 431 TABLE ACCESS CLUSTER OBJ#(22) (cr=1323 r=0 w=0 time=8971 > us) > 431 INDEX UNIQUE SCAN OBJ#(11) (cr=461 r=0 w=0 time=2237 > us)(object id 11) > 4 TABLE ACCESS BY INDEX ROWID OBJ#(18) (cr=9 r=0 w=0 > time=3532 us) > 4 INDEX RANGE SCAN OBJ#(38) (cr=5 r=0 w=0 time=2960 > us)(object id 38) > 4 TABLE ACCESS CLUSTER OBJ#(22) (cr=9 r=0 w=0 time=727 us) > 4 INDEX UNIQUE SCAN OBJ#(11) (cr=5 r=0 w=0 time=251 > 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 > SQL*Net message from client 30 1607.50 > 1608.47 > global cache cr request 2720 1.22 > 370.73 > db file sequential read 18 > 0.00 0.01 > db file scattered read 648 > 0.35 1.21 > cr request retry 63 > 0.00 0.00 > db file parallel read 10 > 0.00 0.01 > > ******************************************************************************** > > Raj
I suspect a full table scan of sys.obj$. Try select owner#, name,obj# from sys.obj$ where obj# = 18; It must be object # 18. I'd rather try to force a scan of sys.type$. If I were you, I would get inspiration from catadt.sql in $ORACLE_HOME/rdbms/admin and write my own view, complete with an ORDERED hint to make Oracle start with sys.type$. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
