Oracle Statistic's - II

Understanding Optimizer Statistic’s

From the part I of the oracle statistics we saw how the statistics are aiding to generate better execution plan for the SQL performance. Review the first installment of the series here.

http://avaismohammed.blogspot.com/2012/07/oracle-statistics-i.html

Table statistics: Holds the information on number of rows in the table, the number of data blocks used for the table and the average row length in the table. The optimizer uses this information to estimate the number of rows the operation will produce.  The table statistics can be view from the dictionary view DBA_TAB_STATISTICS.

Column statistics: Holds the information on number of distinct values in a column as well as min and max value found in the column. The column statistics can be found in DBA_TAB_COL_STATISTICS. The optimizer uses this information to estimate the numbers of rows returned by this operation.

Index statistics: Holds the information on the number of distinct values in the index (keys), depth of the index (blevel), the number of leaf blocks in the index and the clustering factors. The optimizer uses this info to determine the cost of an index access.

Gathering Statistics: For objects that are changing constantly, statistics must be regularly gathered so that they accurately describe the database object. Oracle preferred method for gathering stats is using PL/SQL package DBMS_STATS. The frequently used procedure in the DBMS_STATS package is GTAHER_TABLE_STATS allows you to gather Table, Partition, Index and Column statistics.

The parameters of the procedure GTAHER_TABLE_STATS are:

Ownname	Schema of table to analyze.
Tabname	Name of table.
Partname	Name of partition.
estimate_percent	Determines the percentage of rows used to calculate the statistics. AUTO_SAMPLE_SIZE is the default parameter in all GATHER_*_STATS procedures.
method_opt	Controls the creation of histograms during statistics collection. The default value of FOR ALL COLUMNS SIZE AUTO, Oracle automatically determines   which columns require histograms and the number of buckets that will be used based on the column usage information (DBMS_STATS.REPORT_COL_USAGE) and the number of distinct values in the column.
degree	Controls the number of parallel server processes that will be used to gather the    statistics. By default, all tables in an Oracle database have this attribute set to 1, so it may be useful to set this parameter if statistics are being gathered on a large table to speed up statistics collection. By setting the parameter DEGREE to AUTO_DEGREE, Oracle will automatically determine the appropriate number of parallel server processes that should be used to gather statistics, based on the size of an object.
granularity	Dictates the levels at which statistics are gathered on a partitioned table.  Statistics are always gathered on the first level of partitioning regardless of the partitioning type used. Sub-partition statistics are gathered when the sub partitioning type is LIST or RANGE.  This parameter is ignored if the table is not partitioned.
cascade	Determines whether or not statistics are gathered for the indexes on a table. By default, AUTO_CASCADE, Oracle will only re-gather statistics for indexes whose table statistics are stale.  Negating the need to gather index statistics when the table statistics are gathered.
stattab	User statistics table identifier describing where to save the current statistics.
statid	Identifier (optional) to associate with these statistics within stattab.
statown	Schema containing stattab (if different than ownname).
no_invalidate	Does not invalidate the dependent cursors if set to TRUE.
force	Gather statistics of table even if it is locked.

The default values for the above parameters can be changed using packaged procedure DBMS_STATS.SET_TABLE_PREFS. If you are unsure of what preferences have been set, you can use the DBMS_STATS.GET_PREFS function to check. The function takes three arguments; the name of the parameter, the schema name, and the table name.

To change the default value for any of the parameters used by the DBMS_STATS subprograms, use the DBMS_STATS.SET_PARAM procedure.

BEGIN

DBMS_STATS.SET_PARAM('CASCADE','DBMS_STATS.AUTO_CASCADE');

END;

/

Automatic statistics gathering job: Oracle will automatically collect statistics for all database objects, which are missing statistics or have stale statistics by running an Oracle Scheduler job (GATHER_STATS_JOB) during a predefined maintenance window (10 pm to 6 am weekdays and all day at the weekends). We can adjust the predefined maintenance windows to a time suitable to your database environment using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure. For example, the following statement moves the WEEKNIGHT_WINDOW to midnight through to 8 a.m. every weekday morning:

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE ('WEEKNIGHT_WINDOW','repeat_interval',

                                                                                    'freq=daily;byday=MON, TUE, WED, THU,

                                                                                    FRI;byhour=0;byminute=0;bysecond=0');

We can verify the automatic statistics gathering job exists by viewing the DBA_SCHEDULER_JOBS view:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

Statistics on a table are considered stale when more than 10% of the rows are changed (total # of inserts, deletes, updates) in the table. Oracle monitors the DML activity for all objects and records it in the SGA. The monitoring information is periodically flushed to disk and is exposed in the dba_tab_modifications view.

SELECT TABLE_NAME, INSERTS, UPDATES, DELETES FROM DBA_TAB_MODIFICATIONS;

It is also possible to manually flush this data by calling the procedure dbms_stats.flush_monitoring_info. We can also enable or disable automatic stats gathering using the procedure.

BEGIN

DBMS_SCHEDULER.DISABLE ('GATHER_STATS_JOB');

END;

/

Managing Statistics:  When new statistics are collected on DB objects the original statistics are kept as backup in the dictionary table. We can view the history of statistics on the objects by querying DBA_TAB_STATS_HISTORY view.

SELECT OWNER, TABLE_NAME, STATS_UPDATE_TIME FROM DBA_TAB_STATS_HISTORY;

As the old stats are stored in the dictionary table gives us an opportunity to compare the statistics of any given timestamp. Thus if the new statistics are not helping or working satisfactorily we can retain/restore the old stats. To compare the current statistics with the old statistics in the history run the following SQL statement.

SELECT * FROM TABLE (DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY ('HR','EMPLOYEES’, SYSTIMESTAMP, TO_TIMESTAMP (SYSTIMESTAMP-3,'DD-MON-YY HH12:MI:SS PM'),5));

Oracle provides various methods of restoring the previous version statistics:

1)      Using DBMS_STAT.RESTORE_TABLE_STATS procedure.

2)      Using import export to transfer statistics from one system to others.

3)      Manually setting the statistics values using DBMS_STATS.SET_TABLE_STATS.

Gathering the statistics is very important and more important knowing when to gather statistics for the query performance.

As the post growing bigger than I anticipated we will continue our discussion on oracle statistics in next part of the series oracle statistics -III.

Oracle Statistic's - I

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

Here are the basic understanding of oracle statistics and how they are useful for query performance. In my next post I will elaborate more on the database and system statistics.