| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Extremely Slow Query
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
------- ------ -------- ---------- ---------- ---------- ----------
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 TotalWaited
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
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Thursday, January 02, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L
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: Vladimir.Begun_at_oracle.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-LReceived on Fri Jan 03 2003 - 09:28:38 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.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).
- text/plain attachment: ESPN_Disclaimer.txt
 |
 |