Using DBMS_STATS to Manage Statistics

DBMS_STATS package  provides critical functionality for the cost-based optimizer, including  the analyzing process extended to PL/SQL programs, allowing statistics to be modified, reverting back to previous statistics, and copying statistics from one schema (or database) to another.

By USING DBMS_STATS package you can

• You can collects statistics on tables and Indexes
• Modify Statistics on tables.
• Save statitstics at a certain point of time
• Revert back statistics to the Previously saved statistics.
• Copy Statistics from One Schema to another Schema.
• Import statistics from one database to another

To Collect Statistics on a table faster Use DBMS_STATS

DBMS_STATS offers two powerful ways of speeding up the analyze process. First, you can analyze tables (not indexes) in parallel. Second, you can analyze only tables and their associated indexes that have had more than 10% of their rows modified through INSERT, UPDATE, or DELETE operations.

To analyze all the tables in a schema in parallel, use a command such as the following:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'STRINIX', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);

This command estimates statistics for the schema strinix. The DEGREE value specifies the degree of parallelism to use. CASCADE=>TRUE causes the indexes for each table to be analyzed as well. DBMS_STATS has a GATHER STALE option that will only analyze tables that have had more than 10% of their rows changed. To use it, you first need to turn on monitoring for your selected tables.

For example:

ALTER TABLE STOCK_HIS MONITORING;

You can view the number of changes applied to a table by monitoring the table.And this info is available by the data dictionary view USER_TAB_MODIFICATIONS . You can see if monitoring is turned on for a particular table by selecting the MONITORING column from USER_TABLES.

SELECT MONITORING FROM USER_TABLES WHERE TABLE_NAME='{TABLE_NAME}'

With monitoring enabled, you can run the GATHER_SCHEMA_STATS package using the GATHER STALE option:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'STRINIX', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE, OPTIONS=>'GATHER STALE');

Because GATHER_STALE OPTION  is specified, tables IN the strinx schema  will only be analyzed if they have had 10% or more of their rows changed since the previous analyze.

Copying Statistics Using DBMS_STATS

DBMS_STATS package provides you the ability to copy statistics from one schema to another, or from one oracle database to another Oracle database, using the following procedure: 

Step1:
First save the statistics to a table. Create a table to store the statistics, DBMS_STATS.CREATE_STATS_TABLE can be used to create a new table in your own schema to store the statistics of tables and indexes.

EXECUTE DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=>'STRINIX', STATTAB=>'PAR_STAT_TAB');

Step 2. Populate the table with the statistics from the schema that you are copying from:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=>'STRINIX', STATTAB=>'PAR_STAT_TAB', STATID=> 'STAT_19DEC_2007');

Step 3. If you are copying statistics to a different database, such as from production to development, export and import that statistics table as required:

exp strinix/baba@prod file=stats tables=PAR_stat_tab
imp strinix/babat@dev file=stats tables=PAR_stat_tab

Step 4. Populate the statistics in the target schema’s dictionary. Below example show you that statistics are being loaded for the schema STRINIX_TEST from the table named PAT_STAT_TAB:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>'STRINIX_TEST', STATTAB=>'PAR_STAT_TAB', STATID=>'STAT_19DEC_2007', STATOWN=> 'STRINIX');

Manipulating Statistics Using DBMS_STATS

You can modify the individual statistic elements on a table. Perhaps this be mightbe useful where in if you want to replicate the table stats in development similar to that of production or replicate the table stats in production similar to that of development.

 You can achieve this by using DBMS_STATS.SET_TABLE_STATS to modify the statistics for a table in your development or for a test database to match those in your production database. The optimizer uses the number of rows, number of blocks, and number of distinct values for a column to determine whether an index or a full table scan should be used.

The following example assumes that your symb_history table is going to have 20,00,567 rows in 9,000 blocks:

EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>'STRINIX_TEST', TABNAME=>'SYMB_HISTORY', NUMROWS=> 2000567, NUMBLKS=> 9000);

Regardless of how many rows you really have in your test database for the table SYMB_HISTORY, the cost-based optimizer will now behave as if there were 20,00,567.

The optimizer also uses the number of distinct values for each column to decide on index usage. If the number of distinct values is less than 10% of the number of rows in the table, the optimizer will usually decide to perform a full table scan in preference to using an index on the table column. Change the percentage of distinct values for a column as follows:

EXECUTE SYS.DBMS_STATS.SET_COLUMN_STATS (OWNNAME=>'STRINIX_TEST', TABNAME=>'COMP_SCRIP_MAP',COLNAME=>'SYMBOL', DISTCNT=>400);

Reverting a Table to its Previous Statistics

Usually, re-analyzing a schema and specifying a high percentage of rows for the sample size will improve performance. Unfortunately, the occasional hiccup will occur when you re-analyze tables. Sometimes the new statistics produce much worse execution plans than before. You can avoid the risk of a major screw up by using the DBMS_STATS package to save a copy of your current statistics just in case you need to restore them later. This requires the following steps:

Step 1.
Export your schema statistics to your statistics table. If you don’t already have a statistics table, you can create it using the DBMS_STATS.CREATE_STATS_TABLE procedure. The export is performed as follows:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=>'STRINIX', STATTAB=> 'PAR_STAT_TABLE', STATID=>'STAT_12DEC_2007');

Step 2. Gather your new statistics:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'STRINIX', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);

Step 3. If there are problems with unsuitable execution paths being selected as a result of the new statistics, revert back to the previous statistics by loading the previous statistics from the statistics table:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>'HROA', STATTAB=>'HROA_STAT_TABLE', STATID=>'START_12DEC_2007');