Re: Analysing SYS tables

,
>
> Does someone have any experience using the cost-based optimiser to access
 

> tables owned by SYS?
> Oracle Support advises not to analyse SYS tables in order to make sure
 the
> rule based optimiser will be used accessing these tables.
> Does this mean that Oracle has not been able to make the cost based
 optimiser
> good enough to guarantee performance?
>
>

The answer in a nutshell is DON'T ANALYZE SYS TABLES.

We found this out the hard way. Like yourself we assumed that analyzing the sys tables would give us a better execution plan, increasing performance. We currently have 128,000 objects in our database and the query perfomance against the dictionary is abissmal (even though the shared area is tuned to optimal - 99% hit rate).

Well, after analyzing a few of the sys tables namely, SEG$,UET$,FET$ and a few others, the performance of the queries became catastrophic!. A report that normally took +- 20 mins (extract next extent sizes) now took a full 5 hours!. It did not affect performance on the applications except where the application needed to drop tables. A table drop now took 8 mins as opposed to seconds.

We hand to reverse the analyze by using "remove statistics" option to get us back where we were. I am stiil less than happy with the standard performance of the dictionary queries given that they are cached and have excellent hit rates. I am currently investigating if indexes would not help as our database is large with many extents. The SEG$,UET$ tables are huge and looking in user_indexes there are no indexes over these tables, thus Oracle is relying on full table scans to access these tables. Don't know if I will succeed as they also say you should not build indexes over SYS tables.

What can I say ?????

If you get any enlightenment on this topic I would like to hear about it. Regards
Mike Heydon Received on Mon Dec 08 1997 - 00:00:00 CST