Re: stats on SYS tables?

Steve Salvemini wrote:
>
> Following up info from Mark Powell, he's said that SYS tables should
> definetly not have stats.... whats the reason for this?
>
> I have found that the only way to get a reasonable performance in TOAD
> in the browser for 'Indexes' and 'Scripts' tabs, is to put stats on 3
> SYS tables, ie COL$, OBJ$, ICOL$ .... whats the problem with stats on
> all SYS tables
>
> also is there more doco on these issues in the Oracle manuals

The data dictionary (probably for historical reasons) is optimised for the RULE optimiser, thus having stats on the objects is not a good idea.

I haven't looked at what queries Toad is using, but some of the default views (dba_segments, dba_extents etc) aren't very efficient simply because they present so much information ... You'd be better off defining some of your own custom views and NOT having stats on dictionary objects.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse