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.