Cost-Based Optimizer Tuning

Cost-Based Optimizer Tuning  : When using the Cost Based Optimizer (CBO), you can manually tune individual SQL statements, overriding the optimizer's decisions by including your own optimization hints within the SQL statement. By including your own optimization hints as "comments" within the SQL statement, you force the statement to follow your desired retrieval path, rather than the one calculated by the optimizer. In the following example, including /*+ RULE */ inside the SELECT statement instructs the optimizer to use the Rule Based Optimizer (RBO), rather than the Cost Based Optimizer:

# sql_jhint.sql
SELECT    /*+ RULE */  . . . .
  FROM    emp, dept
  WHERE   . . .
Note: Most likely, Oracle 10g is the end of the road for the RULE hint.

The optimizer hint(s) can be included only immediately after the initial SQL "action" verb and are ignored when included in INSERT statements.

SELECT  /*+ hint text */  . . . .
DELETE  /*+ hint text */  . . . .
UPDATE  /*+ hint text */  . . . .
Each hint is operational only within the statement block for which it appears.

A statement block is one of the following:

A simple SELECT, DELETE, or UPDATE statement
The parent portion of a complex statement
The subquery portion of a complex statement
Part of a compound query
Consider these examples:

    SELECT  /*+ RULE */  . . . .
    FROM    emp
    WHERE   emp_status = `PART-TIME'
    AND     EXISTS     ( SELECT  /*+ FIRST_ROWS */  `x'
                         FROM    emp_history
                         WHERE   emp_no = E.emp_no
                         AND     emp_status != `PART-TIME' )
    SELECT  /*+ RULE */  . . . .
    FROM    emp
    WHERE   emp_status = 'PART-TIME'
    UNION
    SELECT  /*+ ALL_ROWS */  . . . .
    FROM    emp_history
    WHERE   emp_status != 'PART-TIME'
Note: The RULE hint is a particularly helpful option if you have manually tuned some (but not all) of the  core application SQL statements using the Rule Based Optimizer and now wish to move the application to the Cost Based Optimizer. Be warned that Oracle 10g appears to be the end of the road for the RULE hint and the Rule Based Optimizer.

Optimizer Options Introduced in Oracle9i
The CBO now gives more meaningful cost estimates. Oracle has added new columns to the PLAN_TABLE table:

CPU_COST - The estimated CPU cost for the operation

IO_COST - The estimated IO cost for the operation

TEMP_SPACE - An estimate of temporary space that will be required by the operation
Further, the new features added to the CBO allow it to account for the effects of caching on the performance of nested-loop joins and index prefetching when costing out an execution plan.

Gathering System Statistics
Oracle9i has further enhanced the CBO so that it will consider the system's own performance statistics when costing plans. To take advantage of this new feature, first gather system statistics. Much like gathering object statistics, you must collect system statistics on a regular basis to derive the maximal benefit of this new feature. This is because CPU and IO footprints fluctuate over time.

As you gather system statistics, identify each statistic run with a specific statistic ID. This allows you to track system costs based on the query type footprint that the system is experiencing.

Note that SQL statements are not invalidated when system statistics are generated, thus only newly parsed SQL statements will benefit from the new statistics that are gathered. Oracle has added new procedures to gather system statistics. The DBMS_STATS procedures pertaining to system statistics are:

dbms_stats.gather_system_stats
dbms_stats.set_system_stats
dbms_stats.get_system_stats
dbms_stats.delete_system_stats
dbms_stats.export_system_stats
dbms_stats.import_system_stats
Gathering Dictionary Statistics
Prior to Oracle 9i, it was not a good idea to calculate statistics on Data Dictionary tables (those owned by SYS). Recursive SQL statements were not tuned with the CBO and statistics in mind. Calculating statistics on Data Dictionary tables was not supported. Oracle 9iR2 was the first version to support statistics on Data Dictionary tables. In order to calculate statistics on Data Dictionary tables, you must have been granted SYSDBA or the ANALYZE ANY DICTIONARY system privilege. The DBMS_STATS procedures pertaining to Dictionary statistics are:

dbms_stats.gather_dictionary_stats
dbms_stats.delete_dictionary_stats
dbms_stats.export_dictionary_stats
dbms_stats.import_dictionary_stats
dbms_stats.restore_dictionary_stats
Gathering Fixed Table Statistics
Oracle 10g was the first version to support calculating statistics on fixed objects, i.e. dynamic performance tables. Since Oracle 10g fully supports the Cost Based Optimizer, it makes sense that any object that is queried has statistics, including fixed tables. . In order to calculate statistics on fixed tables, you must have been granted SYSDBA or the ANALYZE ANY DICTIONARY system privilege. The DBMS_STATS procedures pertaining to fixed object statistics are:

dbms_stats.gather_fixed_object_stats
dbms_stats.delete_fixed_object_stats
dbms_stats.export_fixed_object_stats
dbms_stats.import_fixed_object_stats
dbms_stats.restore_fixed_object_stats