Oracle statistics

Submitted by madvip on Fri, 02/22/2008 - 11:54.

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
* Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired.

The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement:

    EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
    EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
    EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

    EXEC DBMS_UTILITY.analyze_database('COMPUTE');
    EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100);
    EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);

As described in a previous post, you can use parallel DDL to increase speed!

»