Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library