Re: Query on all_tables taking long time

Harvinder

The predicate doesn't allow indexed access. And remember ALL_TABLES is a 
(complex) view. The name of the owner is stored in USER$, the name of the 
object in OBJ$. So the CBO (in effect) builds the entire ALL_TABLES view and 
trawls through it matching the predicate.


Rewrite the predicate as:
 
WHERE OWNER = :b1
AND   TABLE_NAME = :b2

Now you're giving the CBO the chance to decide whether to drive off OWNER or 
off TABLE_NAME

HTH

Regards Nigel
 
----- Original Message ----
From: Harvinder Singh <Harvinder.Singh@xxxxxxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, May 9, 2007 9:17:52 PM
Subject: Query on all_tables taking long time


Hi,

We have a function that checks if the table exists in current schema if
specified without the schema name or all_tables if we specify the table
name as object_name.table_name and it looks like query against
all_tables is running very slow. Following is the output from the tkprof
and it is not clear why optimizer is using 18 sec for cpu and doing 134k
reads. This is 10.2.0.1 on RHAT ENT 4


SELECT COUNT(1)
FROM
ALL_TABLES WHERE OWNER || '.' || TABLE_NAME = UPPER(:B1 )

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse      158      0.02       0.02          0          0          0
0
Execute    158      0.04       0.03          0          0          0
0
Fetch      158     17.99      17.88          0     134540          0
158
------- ------  -------- ---------- ---------- ---------- ----------
----------
total      474     18.06      17.93          0     134540          0
158

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 172     (recursive depth: 1)

Thanks
--Harvinder

--
http://www.freelists.org/webpage/oracle-l

Other related posts: