RE: Extremely Slow Query

[Khedr, Waleed]

Title: Extremely Slow Query

Are you running RAC?

 

Waleed

-----Original Message-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 3:51 PM
To: Multiple recipients of list ORACLE-L
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!