Query on all_tables taking long time
- From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 May 2007 16:17:52 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
