Oracle-L: Re: Extremely Slow Query

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 02 Jan 2003 15:44:15 -0800
Message-ID: <F001.00526454.20030102154415@fatcity.com>

Looks like you've hit the big problem with RAC - how many nodes do you have, how busy
are the nodes which are supposed to supply with with CR copies across the interconnect, and what's the latency and bandwidth of your interconnect ? (And how did you get that many objects into obj$ !!!)

If you check your CR gets and disc reads on the tablescan of obj$,

> 463566 TABLE ACCESS FULL OBJ#(18) (cr=5304 r=2548 w=0
time=373229866 >us)

you'll see 2,548 reads, and 5,304 CR gets - cross check against the waits for
> global cache cr request 2720

Note that 5,304 - 2548 = 2,756. That's close enough to make me think that your tablescan is largely responsible for global cache lock requests, and the interconnect is being flooded with cached blocks from other nodes.

Solution - in the short term I think I'd take Stefane's advice and write your own query based on the view - or possibly find a hint that you could turn into a global hint in your outer query - based on Stefane's comments you could try something like:

/*+ leading (dba_types.type$) */

(check for suitability, I haven't checked the definition of the dba_types view).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 02 January 2003 22:15

>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!
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).

Received on Thu Jan 02 2003 - 17:44:15 CST