 |
|
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.
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
