0

ALL_TAB_STATISTICS has a column named STALE_STATS, but is there a way to determine the date when it became stale?

2
  • check LAST_ANALYZED column
    – lear1
    Nov 23, 2023 at 13:25
  • 1
    No, there is not. But why would you need that information?
    – Paul W
    Nov 24, 2023 at 15:13

1 Answer 1

3

If you look at the view definition, you'll see that staleness isn't a column value stored in a table anywhere, but a formula calculated at query time:

  when ((m.inserts + m.deletes + m.updates) >
        to_number(decode(p.valchar, null, (select spc from stale_pc),
                         p.valchar)
                 )/100 * t.rowcnt
       )
  then 'YES'
  else  'NO'

It gets the modification data from sys.mon_mods_v, which tracks the # of rows inserted, deleted and updated since the last statistics gathering event. If the # of rows modified is more than a certain percent ("stale percent") then the segment is considered stale. So to know when that happened would mean knowing the modification statistics from the past. While there is a timestamp column, it only reflects the last time the segment was modified, which doesn't tell you how much the modification counters were incremented at that time, so it's not really usable.

I don't believe this data is retained historically in Oracle. You would need to capture dba_tab_modifications at regular intervals and create your own historical data to achieve this. But probably there isn't a pressing reason to do so.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.