Thursday, December 27, 2007

What are Oracle Statistics?

Question on the Oracle forums, asked for a brief, non-document explanation of Oracle statistics. Here is my answer:
Oracle can do things in several different ways. For example, select might be done by table scan or by using indexes. It uses statistics - a variety of counts and averages and other numbers - to figure out the best way to do things.

It does the figuring automatically, using the Cost Based Optimizer. Your job is to make sure the numbers are good enough for that optimizer to work properly.

To get information about statistics (all links to 10gR2 docs):

General overview of statistics is found scattered in the Oracle Database Concepts manual. For 10g Release 2, Chapters 13 gives an overview, Chapter 15 gives details related to Table segments, Chapter 16 is about Index segments, Chapter 21 talks about automatic admin tasks.

The preferred tool for collecting statistics used to be the ANALYZE command. Over the past few releases, the DBMS_STATS package in the PL/SQL Packages and Types reference has taken over the statistics functions, and left the ANALYZE command with more mundane 'health check' work like analyzing chained rows and the like.

The definitve reference for using statistics is Chapter 14 "Managing Optimizer Statistics" of the Performance Tuning Guide manual.

As always, Dan Morgan has a great demo for DBMS_STATS at the Puget Sound Oracle User's Group web site in Morgan's library.

As well, Jonathan Lewis has some great hints and advice, although he still has not had a chance to classify that and it's getting stored under the Misc topics.

I have to admit some surprise that neither Howard Rogers nor Richard Foote has an in-depth discussion around DBMS_STATS, although both refer to statistics in their various articles and responses.

They may simply be leaving the details to Tom Kyte's discussion around the package.

No comments: