sys.cdef$ query performance issue in 10g
- From: "Manmohan Jalsingh" <mjalsing@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 22 Jun 2005 15:41:30 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » sys.cdef$ query performance issue in 10g
