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
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
