Oracle-L: Re: Extremely Slow Query

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Thu, 02 Jan 2003 15:14:31 -0800
Message-ID: <F001.00526438.20030102151431@fatcity.com>

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-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:14:31 CST