This discussion is archived
7 Replies Latest reply: May 2, 2011 4:56 AM by 698658 RSS

GATHER_DICTIONARY_STATS or  GATHER_FIXED_OBJECT_STATS  for slow dba_segment

698658 Newbie
Currently Being Moderated
Hi,
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 .
Regards
GregG

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points