Gathering Statistics for the Oracle Optimizer

OracleWhen you execute a query in an Oracle database, Oracle has to decide how to retrieve that data. That’s where the query optimizer steps in. The query optimizer makes decisions like which indexes to use, weather to perform a full table scan etc. based on the tables, columns, calculations and joins in a query. Oracle can do this quite efficiently with the cost-based optimizer, but it is important that there be accurate statistics available for it to use.

A short history

Originally the query optimizer worked on a static set of around 20 rules. These rules would be applied regardless of the size and type of data in a table.

Oracle 7 introduced the cost-based optimizer which can make more intelligent optimization decisions. By analyzing pre-gathered statistics on database objects the cost-based optimizer estimates the “cost” of processing several possible execution plans. The cost-based optimizer then chooses the cheapest execution plan and the database executes the plan.

In Oracle Database 7 through 9i either the cost-based or rule-based optimizer could be used. The rule-based optimizer is no longer included in Oracle 10g.

How can I tell which optimizer mode I am using

If you are running Database 10g or later, you are using the cost-based optimizer.

If you are running Database 7 through 9i you should check the optimizer_mode parameter.

SQL> show parameter optimizer_mode

------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE

RULE means your database is using the old rule-based optimizer. The good news is you do not have to gather statistics on your data. The bad news is your queries probably aren’t running as well as they could.

CHOOSE was introduced as a stop-gap between rule- and cost-based optimizers. If there are no statistics available, the rule-based optimizer will be used; however if statistics are available the query optimizer will default to cost-based mode.

FIRST_ROWS or ALL_ROWS will force your database to use the cost-based optimizer regardless of statistics, so you’d better gather them.

How can I gather statistics for my index, table, schema, database, etc.?

The DBMS_STATS package is used to gather statistics for the cost based optimizer. Historically the ANALYZE command would perform similar operations

Here are a few popular examples. Of course you should always consult the documentation for your Oracle distribution before using a new command.

These can be run through SQL*Plus, but you will probably want to automate them for more active databases. There are more options that I have chosen to show here, but these should be a good start.


This will analyze statistics for your entire database. It is likely to take quite a while (hours) and generally should not be necessary, but if you want to analyze the whole database this will do it with one command.


This will analyze statistics for everything owned by the user ‘JEMMONS’. It is important to put the username in single quotes and all capitol letters. You can exclude the parameter and parentheses to analyze the current user’s statistics.


This will gather statistics for a specific table and all its indexes. This may be a good idea on tables which change drastically on a regular basis.


This will gather statistics on a specific index. If there is a need to drop and rebuild an index you could use this to re-analyze the index after rebuild.

The GATHER AUTO option can and should be added to the commands above after initial analysis. This will cause only objects with missing stats or more than 10% changed since last analysis (via insert, update or delete) to be analyzed. The resulting command should look something like the following:


How often should I gather statistics?

This is a question I cannot answer. I have schemas which do not change often that I may analyze once a month, others that I will gather new statistics on once a day. The GATHER AUTO option should be used to automatically gather missing and stale statistics rather than re-analyzing everything; however, sometimes there may be an advantage to re-analyzing an entire schema or database.

oracle, database, database administration, dba, database tuning

4 thoughts on “Gathering Statistics for the Oracle Optimizer”

  1. Insertion on a particular table are taking much time.very much time.We have index on table,statistics everything.Pls let me know the reason for this

  2. Siva, any number of things could be causing this. To even begin to troubleshoot it I would need a ton of information about your environment.

    If you submit your query info to metalink (you do have support, right?) they can often help. Addressing this type of problem is really beyond the scope of this site.

  3. Hi Silva,
    While there are very many variables that could be causing you load to perform slowly, there are a few things that you can easily check.

    First, are you dropping or disabling the indexes on the table you are loading into? If you are inserting a great deal of records, I recommend doing the following…

    Disable all constraints on the target table
    Alter all indexes to be usable
    Set the current session to skip unusable indexes
    Set the table to not log the inserts

    Then I would proceed with your insert, but again, if it is a great deal of data, you might want to insert to the /* append */ options.

    After than you set the session to NOT skip unusable indexes
    rebuild all of the indexes with no logging
    turn logging back on for the table
    re-enable all of the constraints on the table.

    if you need help with this, or any other datbase programming, feel free to contact me a

  4. This info is useful.
    I need a small clarification. We have a process in which we select millions of records from a table, do some sort of validation and insert the resultant data back into the same table. This process is taking very long time.
    We are wondering whether partitioning that table and gathering statistics would help us in reducing the execution time.
    Would someone be able to help me in this?

Leave a Reply

Your email address will not be published. Required fields are marked *