Understanding Optimizer Statistic’s
We were discussing hell lot on oracle statistics in our
recent meetings. I tried to dig deeper
in understanding the relation between the optimizer and the database statistics.
In this post a three part series I would like to discuss what I understood on this fragile
relationship and I would welcome any suggestion or correction to my
understanding of the subject.
Statistics:
Statistics are the metadata of the objects in the database. Statistics
are used by optimizer to determine or choose the best execution plan for each
sql statement . The choice of execution plan made by the optimizer is as good
as the statistics. For the better execution plan optimizer needs better
statistics of the table.
An execution plan is set of instruction to the oracle engine
on how to fetch the data from the database.
Why Statistics:
The optimizer statistics are created for query optimization. The optimizer uses statistics on tables and on
the indexes of the table. Optimizer
requires the following statistics for construction of optimized execution plan:
Table
statistics
o Number of rows
o Number of blocks
o Average row
length
Column
statistics
o Number of
distinct values (NDV) in column
o Number of nulls
in column
o Data
distribution (histogram)
Index
statistics
o Number of leaf
blocks
o Levels
o Clustering
factor
System
statistics
o I/O performance
and utilization
o CPU performance
and utilization
Optimizer:
Optimizer is an algorithm used to construct the execution
plan for the SQL statements. The goal of the optimization is throughput i.e.
using the least amount of resources necessary to process the data accessed by the SQL
statement.
Opitimizers are of two types rule based (RBO) and cost based
(CBO). RBO is an old technique of query optimization whose algoraithm is based
on set of rules. RBO becomes obselete from oracle 10g.
To execute a certain query oracle engine has several paths. CBO
chooses the very best path which uses the least amount of resources (CPU,
Memory) for the query execution and construct the plan to execute it.
Note: If we found the execution plan selected by the optimizer is not optimized
we can change the plan by providing hints to the optimizer.
Methods of Gathering Database Statistics:
There are 3 different methods of gathering stats
o
ANALYZE statements
o
DBMS_UTILITY package
o
DBMS_STATS package
The old fashioned Analyze statement and
DBMS_UTILITY methods are deprecated/obselete from oracle 10g and gathering CBO stats
using these methods are somewhat dangerous for SQL performance.
The DBMS_STATS package is more suitable and accurate for
gathering CBO statistics.The recommended approach to gathering statistics is to
allow oracle to automatically gather the statistics. Oracle gather statistics
on all database objects automatically and maintains those statistics in a
regularly-scheduled maintenance job. Automated statistics collection significantly
reduces the chances of getting poor execution plans because of missing or stale
statistics.
Gather_stats_job gathers statistics automatically on all
objects in the database which have missing or stale statistics. This job is
created at the time DB creation and managed by the scheduler.
Query to check the automatic stats gathering job is
scheduled.
select * from dba_scheduler_jobs
where job_name = 'GATHER_STATS_JOB';
Example of gathering stats manually for all objects in the
schema and for a table respectively.
EXEC dbms_stats.gather_schema_stats (‘HR’, cascade=>TRUE);
EXEC dbms_stats.gather_table_stats (‘HR’, ’EMPLOYEES’, cascade=>TRUE);
The cascade command collects the stats on the index on the
objects.
Statistics are stored in data dictionary. To view statistics in
data dictionary query the views below:
dba_tables
dba_object_tables
dba_tab_statistics
dba_tab_col_statistics
dba_tab_histograms
dba_indexes
dba_ind_statistics
dba_clusters
dba_tab_partitions
dba_tab_subpartitions
dba_ind_partitions
dba_ind_subpartitions
dba_part_col_statistics
dba_part_histograms
dba_subpart_col_statistics
dba_subpart_histograms
No comments:
Post a Comment