Thanks everyone ...
Yes it is RAC (in fact I don't have non-rac databases anymore). Unfortunately this is not my query. TOAD runs this query when it starts-up and it seems like it hangs. As I am in process of deploying newest version of TOAD, I am pretty sure no one is going to like this. So That's why I asked this question looking for solution. This query runs fast in 9201 but in 9202 well .. that's this story.
Maybe I'll create an outline for this for everyone to use. Thanks Jonathan, Valdimir and Stefane and all. Yes this is a two node RAC, very small traffic (this is our DAYOLD) instance where support debug's critical problems. And for object counts ... here is the break-up.
1 select object_type, count(*) from dba_objects
2* group by object_type
SQL> /
OBJECT_TYPE COUNT(*)
------------------ ----------
CLUSTER 11
CONSUMER GROUP 4
CONTEXT 2
DATABASE LINK 77
DIRECTORY 2
EVALUATION CONTEXT 1
FUNCTION 264
INDEX 3847
INDEX PARTITION 24
INDEXTYPE 7
JAVA CLASS 9884
JAVA DATA 293
JAVA RESOURCE 193
JAVA SOURCE 16
LIBRARY 78
LOB 118
MATERIALIZED VIEW 1
OPERATOR 23
PACKAGE 820
PACKAGE BODY 769
PROCEDURE 306
QUEUE 8
RESOURCE PLAN 3
SEQUENCE 687
SYNONYM 438414
TABLE 3198
TABLE PARTITION 27
TRIGGER 429
TYPE 567
TYPE BODY 49
VIEW 3298
After taking Jonathan's advise ... here are the results ... much better than before.
select --+ leading(dba_types.type$)
*
from dba_types
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 1 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 30 4.75 15.12 9020 12465 0 431
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 32 4.77 15.15 9021 12467 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=12465 r=9020 w=0 time=15120561 us)
431 NESTED LOOPS (cr=12454 r=9020 w=0 time=15117437 us)
431 HASH JOIN (cr=11131 r=9018 w=0 time=15104996 us)
463494 TABLE ACCESS FULL OBJ#(18) (cr=5270 r=4611 w=0 time=4150680 us)
436 HASH JOIN OUTER (cr=5861 r=4407 w=0 time=10245897 us)
436 TABLE ACCESS FULL OBJ#(298) (cr=590 r=587 w=0 time=669055 us)
464557 TABLE ACCESS FULL OBJ#(18) (cr=5271 r=3820 w=0 time=9079489 us)
431 TABLE ACCESS CLUSTER OBJ#(22) (cr=1323 r=2 w=0 time=10389 us)
431 INDEX UNIQUE SCAN OBJ#(11) (cr=461 r=2 w=0 time=4140 us)(object id 11)
4 TABLE ACCESS CLUSTER OBJ#(22) (cr=11 r=0 w=0 time=1096 us)
4 INDEX UNIQUE SCAN OBJ#(11) (cr=6 r=0 w=0 time=258 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
global cache cr request 5654 1.22 7.58
db file sequential read 130 0.01 0.10
db file parallel read 613 0.03 0.66
db file scattered read 2033 0.10 3.44
latch free 1 0.00 0.00
cr request retry 2 0.00 0.00
SQL*Net message from client 30 45.09 46.04
==============================================================================
Now I am off to creating an outline because I can't change this query ... but I'll send an email message to the toad devl team.
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!
-----Original Message-----
From: Vladimir Begun [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Extremely Slow Query
RAC/OPS?
A guess: obj$ is a very popular table (and possibly
RAC-caches-wide-spread one ;) -- some its blocks
were not in the local cache. Does it take 2-3 mins
everytime you launch the query? Probably somebody
else is doing some manipulations with obj# actively
creates/alters/drops objects?
Check related RAC statistics regarding to global cache
and V$CACHE_TRANSFER. Probably it's not an obj$ but
the wait and FTS look suspicious.
To speed up the query you might want to use user_types
or write our own to avoid FTS.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
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 ...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
********************************************************************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.*********************************************************************2
