Oracle Application Tuning

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Using the New Optimizer Statistics

� The default value for the OPTIMIZER_MODE initialization parameter is   ALL_ROWS.
� Automatic Statistics Collection
� Changes in the DBMS_STATS Package
� Dynamic Sampling
Oracle determines at compile time whether a query would benefit from dynamic   sampling.

Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization   parameter, a certain number of blocks are read by the dynamic   sampling query to   estimate statistics.

OPTIMIZER_DYNAMIC_SAMPLING takes values from zero (OFF) to 10 (default is 2).

� Table Monitoring
If you use either the GATHER AUTO or STALE settings when you use the DBMS_STATS package, you don�t need to explicitly enable table monitoring in OracleDatabase 10g; the MONITORING and NO MONITORING keywords are deprecated.

Oracle uses the DBA_TAB_MODIFICATIONS view to determine which objects have stale statistics.

Setting the STATISTICS_LEVEL to BASIC turns off the default table monitoring feature.

� Collection for Dictionary Objects
You can gather fixed object statistics by using the GATHER_DATABASE_STATS procedure and setting the GATHER_FIXED argument to TRUE (the default is FALSE).

You can also use the new procedure:

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL')

You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to analyze any dictionary objects or fixed objects.

To collect statistics for the real dictionary tables:

o Use the DBMS_STATS.GATHER_DATABASE_STATS procedure, by setting the GATHER_SYS argument to TRUE. Alternatively, you can use the GATHER_SCHEMA_STATS ('SYS') option.
o Use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure.