Skip to Main Content
  • Questions

  • What is the difference between full schema stats vs stale stats using table monitoring?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, r.

Asked: April 24, 2007 - 5:46 pm UTC

Last updated: April 25, 2007 - 3:57 pm UTC

Version: 9.2.5

Viewed 1000+ times

You Asked


We used to have a full Schema stats refresh and we moved to incremental approach few months back as the stats generation process is taking a long time. We thought that both approaches will produce the same query plans as Oracle monitor the changes for all objects and it refreshes the stats when the changes are around > 5 % (We used monitoring, gather stale states).

Apparently our assumptions are not correct as I saw at least 3 queries are changing the plan whenever we get new stats and changing its plan again in 2-3 weeks when stats were old (approximately these tables are getting stale once in every 4-5 weeks) We used same parameter for both methods (cascade => true).

Why can¿t oracle make same plan based on the available stats? Can you please let me know the difference for this change?


and Tom said...

the entire goal of gathering statistics is......

a) to get plans to change
b) to keep them the same

:)

we gather statistics in the hopes that some plans will change (else why bother? if they do not have the chance to change - why bother gathering at all if they would just stay the same????)

we gather statistics in the hopes that some other plans will stay the same... "where date > to_date( 'last sundays date here' )" Maybe today that is estimated to return 1 row because the last time we gathered statistics (a week ago), no data would have matched that. However, after gathering - it figures out that 10,000 rows would now match that. Data values that change over time - the high point moves up - need gathering in order to keep the plans the same.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

A reader, April 25, 2007 - 2:37 pm UTC

You are correct, here are my follow-up questions.

1. Why oracle provided Table monitoring option when it is not good at date > to_date(my date) queries? (Can¿t it be find out the last analyzed date and go back to a previous date and guess the possible number of rows?)
2. Where should we use this Table monitoring / gather stale stats option?
3. I am not collecting any histogram data, how oracle knows that it needs to read 10000 or some number of rows for a given date range? (I mean where is this data get stored in the dictionary)

Tom Kyte
April 25, 2007 - 3:57 pm UTC

1) because it solves many peoples issues.

2) in most cases

3) it knows low value, high value and number of distinct values - you need to adjust the high value over time for things that "grow". If that 10% modification does not trigger it soon enough, you might have to manually gather statistics.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.