sys.cdef$ query performance issue in 10g

From: Manmohan Jalsingh <mjalsing_at_lycos.com>
Date: Wed, 22 Jun 2005 15:41:30 -0500
Message-Id: <20050622204130.A31FC3384B@ws7-3.us4.outblaze.com>

Hi,

I am having a issue in 10g ( 10.1.0.4 ) where the following sys query is running very slow and in fact is the slowest query in the database (total execution:13756 buffer:416,036,464 elapsed seconds:28694). It is called recursively during GRANTS and app code promotions which is making these operations run very slow.  

The sqlplan in 10g is shown below -

select c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user#

Plan Table

---

| Operation | Name |

---

| SELECT STATEMENT | |
| HASH JOIN | |
| TABLE ACCESS FULL |USER$ |
| HASH JOIN | |
| TABLE ACCESS FULL |CDEF$ |
| TABLE ACCESS FULL |CON$ |

---

The database has about 140k tables. In 9i, where sys schema was not analyzed, the same query was using NL and indexes. The 10g, the sys schema is analyzed. The column statistics for the "enabled" column in cdef$ table are -

num distinct:1
sample_size:1
num_bucket:3756

It looks like Oracle is not collecting the correct statistics for this column. There are 400+ distinct values in the column. The statistics on "sys" were gathered using following statment.

exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Has anyone noticed this issue in 10g. Can a SYS table be analyzed separately. Is it supported ?

Thanks
Manmohan

-- 
_______________________________________________
NEW! Lycos Dating Search. The only place to search multiple dating sites at once.
http://datingsearch.lycos.com

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

Received on Wed Jun 22 2005 - 16:46:55 CDT