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 .
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 :(.