I'm on 4 node RAC 10.2.0.3 and we've notice that queries on
dba_segments, user_segment are performing really slow comparing to some period of time before .
Of course this is warehouse with thousends of tables and hundreds of thousends partitions but even that
I think we can get better performance .
As far as I know those views are based on objects like:
sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s
so looks like GATHER_DICTIONARY_STATS is the perfect choose .
But I'm really concern about even worst performance degradation after
refreshing those objects stats .
So is there any way I can recover from that issue, like restoring old saved stats .
That is possible for 'normal' tables .
But what about that owned by sys ?
Please share with me Your experience .
You right, but I've found that note in ML 'How to gather statistics on SYS objects and fixed_objects? [ID 457926.1]'
which states :
To gather the dictionary stats:-
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
It looks kind of redundant for me .
Any comments ?
Thank You, that looks like perfect workaround but I really need persistent solution which
is resonable response times for _segments queries .
Just little affraid that regathering dictionary_stats make it worst :(.