Re: Extremely Slow Query

> "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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).