Thanks for the question, Marc.
Asked: November 09, 2016 - 2:10 am UTC
Last updated: November 09, 2016 - 6:36 am UTC
Version: 10.2.0.5
Viewed 1000+ times
You Asked
Hi Tom,
I've been asked to review performance on one of our databases, and one of the issues I found was the statistics have been locked since 2011 and are considered stale.
My recommendation was to back them up (as a rollback point) and update them, however our customer has another consultant (who originally locked them), stating that updating them won't change a thing. I disagree. Is there a way to prove otherwise?
Marc
and Connor said...
Check out published vs pending stats.
http://docs.oracle.com/cd/E25178_01/server.1111/e16638/stats.htm#BEIEGBGI You collect stats in pending mode, so nothing "sees" them, and then run selected key queries from the application against the pending stats, using:
optimizer_use_pending_statistics = true
at session level, or by login trigger etc.
Then you can see what effect (positive or negative) you'll get.
Is this answer out of date? If it is, please let us know via a Comment