How often should stats be collected?

If you ask Microsoft (or Oracle from version 10g), then just leave it to the database and the clever developers to find out when new stats should be gathered.

Right.

When you collect new stats you are almost guaranteed to change some execution plans in your system. Do you want that? No, you want the same execution plans as long as they have the response time your users want. They haven't asked you for a better (or worse) plan.

Here's a response I saw on a list the other day:

-------------------------------------------------------------
Oracle suggests the following best practices for collecting statistics. http://www.oracle.com/technology/books/pdfs/book_10g_chap9_ce1.pdf

· Collect statistics for normal data dictionary objects using the same interval that you would analyze objects in your schemas. In addition, you need to analyze the dictionary objects after a sufficient amount of DDL operations have occurred.

· Use the procedures gather_database_stats or gather_schema_stats with options set to GATHER AUTO. With this feature, only the objects that need to be re-analyzed are processed every time.

· For fixed objects, the initial collection of statistics is usually sufficient. A subsequent collection is not usually needed, unless workload characteristics have changed dramatically. In the next section, we will examine the changes introduced
-------------------------------------------------------------

And here's my reply:
---------------------------------------------------------------------------
I'd argue that you should be scared of socalled best practices on this and other issues.

How can people seriously write about best practices, when in reality we're talking practices that worked for a few sites that will never, ever resemble your site anyway?

Better to document more and more worst practices, as Cary Millsap suggested several years ago - that way you raise the bar instead of just placing it at a certain position.

By the way: Note the vagueness: "...the same interval" (what interval?), "...after a sufficient amount of DDL operations have occurred" (how much?). Can't be otherwise, but that doesn't make it more usable.

It's like the recommendations from Microsoft regarding SQL Server: Always run automatic stats gathering. Always set parallel to automatic. Always....

Two schools inside Oracle are interesting to study (and we're talking the real experts, not the document writers):

Benchmark guys: Collect 1% estimate after initial load, then never again.

Real World Performance group: Collect compute stats once, then never again.

But there's a better way, I think:

Analyze if it's neccessary!

It's only neccessary if the response time for a SQL statement becomes worse. So monitor the elapsed_time and the hash_plan_value (or plan_hash_value) in V$SQL to see if something goes South. If yes, one possible reason could be stats that are no longer representative for the data (more employees in department 42, much more data due to aquisition of a new company, whatever). THEN it's time to analyze.

Don't do it if you don't have to. Monitor the things that matter.

The reason you can't find any useful rules is that ... you can't find any useful rules.
------------------------------------------------------------------

Plan_hash_value and elapsed_time were added to V$SQL in 9i. Before that you'd need to do your own hashing after an "explain plan for..." command in SQL*Plus and store the result. Peter Gram from Miracle has done it for a 7.3 database.

I could talk for ages about this. How customers have run splendidly for years without analyzing (or precisely because of it), and how it's always ground-hoc day tuning when something changes: A system doesn't "become slow", an application doesn't "become slow". It's one or more SQL statements that change execution plan(s) for the worse. Since we never monitor those we do the same thing over and over again: Find the SQL statement with the largest response time, tune it, back to start.

But what if the SQL statement with the longest response time was NOT the one that changed? What if the SQL statement with the longest response time already had been optimised as best as could be done? That happens more often than you'd like to know.

So instead of zero'ing in on the SQL statement that changed execution plan (and therefor response time) for the worse, and return it to its former state or find out why it went bad, we end up tuning another statement that might not be easily tunable.

Hence my abstract for the Hotsos Symposium 2006: "No, Cary, we're STILL not tuning the right SQL statements" :-).