Oracle Database Statistics Gathering

On May 26, 2013, in Oracle SQL Tuning, by Stelios Charalambides

It always surprises me that statistics doesn’t have its own manual in the Oracle documentation set. In the current version of the Oracle Database Performance Tuning Guide, (which I’ve mentioned on more than one occasion should be mandatory reading)  Chapter 13 is called  ”Managing Optimizer Statistics”,  but let’s be honest,  how many people have actually read this section? Life as a DBA these days is far too busy and hectic to have time to read an Oracle Manual. A much better choice is to read a well targeted book on the subject.

For example, in the first paragraph of this section of the manual it describes the difference between

GATHER_DATABASE_STATS_JOB_PROC
and
GATHER_DATABASE_STATS

Do you know what the practical difference is between these two? My memory was jogged because I had a problem this week with a statistics gathering procedure that was running out of time in the maintenance window and was leaving some crucial tables without statistics and that lead to other performance problems. The solution was  either to run a one off procedure to gather what you want and do that manually or to adjust the percentage being collected and thus allow the automated job to complete in the time allocated, or possibly a combination of both. Too often I see automated statistics being collected with no other thought for the statistics gathering process. When to collect, how to collect, what to do when the statistics are not as good as you expected. There are a few paragraphs in Chapter 13 of the manual, but by no means enough to give you guidance. I’m often left thinking

“yes that’s what that procedure CAN do, but should I use it?”

It’s like being given all the pieces of a jigsaw and being told that it makes a really nice picture of something, but not what the something actually is. Yes, I like to know what the different parameters of all the dbms_stats procedures do, but which ones should I use and under what circumstances? There is no guidance on this.

It got me thinking there was a need for guidance in this area. There are over 80 procedures in the DBMS_STATS package. I’m sure that by now, everyone has stopped using ANALYZE (except for older systems and for verifying structures) but even a common procedure such as DBMS_STATS.GATHER_TABLE_STATS has some obscure options. For example the method_opt parameter is often used like this

method_opt=> for columns size 1

But there are many many other choices. And how about the granularity option which can have a value of ‘ALL’, ‘AUTO’, ‘GLOBAL’ etc? Given this complexity,  how many of you choose to set everything on AUTO and blame Oracle when it doesn’t work out?

I mention all this on Statistics because my next book is going to be on this very subject, and it would be really useful to know if anyone had any stories they’d like to share or suggestions on topics  in this area that need to be covered.

Here’s my list of  tentative chapter headings, if you’ve any ideas or suggestions for the book, just post a comment and I’ll do my best to reply.

  • What are statistics
  • Different kinds of statistics (Object, Dictionary, System)
  • What Automation does for you
  • How automation can go wrong
  • Granularity
  • Partitions
  • Setting preferences
  • When to gather less than everything
  • When to check results against SQLT
  • How to export and import statistics
  • Histograms
  • Pending Statistics
  • Complex statistics
  • Strategies for Statistics gathering
 

Leave a Reply



Buy My Book!

Archives

All entries, chronologically...

Visit our friends!

A few highly recommended friends...