7 Replies Latest reply: May 2, 2011 6:56 AM by 698658 RSS

    GATHER_DICTIONARY_STATS or  GATHER_FIXED_OBJECT_STATS  for slow dba_segment

    698658
      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