----- Original Message -----
Sent: Thursday, January 02, 2003 12:51
PM
Subject: Extremely Slow Query
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!
