Oracle Statistics

Statistics are integral part of our life. whether it be movies,games or anything you can think of… everything in this world have got a rating, which helps us to

conclude it as GOOD or BAD, even without seeing/having it. Same is the case with Oracle !! Oracle Cost based Optimizer popularly known as CBO, uses the statistics

collected to conclude the best plan prior to actually executing it.

Provided below is an excerpt created referring the metalink notes to throw some light into recommended way of Stats Gathering & restoring.

Gathering Oracle Stats: Ref- 388474.1, 605439.1,749227.1
************************************************************

The Below stats gathering commands stands valid for majority of databases,The recommendations aim for statistics accuracy. But sometimes you might need to tweak this

according to your needs. For very large systems, the gathering of statistics can be a very time-consuming and resource intensive activity.In this environment sample

sizes need to be carefully controlled to ensure that gathering completes within acceptable timescales and resource constraints.

Oracle 9i:
+++++++++++

At Table Level:

exec dbms_stats.gather_table_stats(ownname => NULL,tabname => ‘Table_name’,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,cascade => TRUE,method_opt => ‘FOR ALLCOLUMNS SIZE AUTO’ );

At Schema Level:

exec dbms_stats.gather_schema_stats(ownname => NULL,cascade => TRUE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );

At DB Level:

exec dbms_stats.gather_database_stats(cascade => TRUE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );

Oracle 10g:
+++++++++++

Quick Recreate Recommendation

exec dbms_stats.delete_table_stats(ownname=>’user_name’,tabname=>’table_name’,cascade_indexes=>true);
exec dbms_stats.gather_table_stats(ownname=>’user_name’,tabname=>’table_name’,estimate_percent => 100,cascade=>true,method_opt=>’for all columns size skewonly’);

At Table Level:

exec dbms_stats.gather_table_stats(ownname => ‘ Schema_name’, tabname => ‘Table_name’, estimate_percent => 100,cascade => TRUE,method_opt => ‘FOR ALL COLUMNS SIZE 1’ );

At Schema Level:

exec dbms_stats.gather_schema_stats( ownname => ‘ Schema_name ‘, cascade => TRUE, method_opt => ‘FOR ALL COLUMNS SIZE 1’ );

At DB Level:

exec dbms_stats.gather_database_stats(cascade => TRUE,method_opt => ‘FOR ALL COLUMNS SIZE 1’ );

Oracle 11g:
+++++++++++

Quick Recreate Recommendation

exec dbms_stats.delete_table_stats(ownname=>’user_name’,tabname=>’table_name’,cascade_indexes=>true);
exec dbms_stats.gather_table_stats(ownname=>’user_name’,tabname=>’table_name’,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>true, method_opt=>’for all columns size AUTO’);

At Table Level:

exec dbms_stats.gather_table_stats(ownname => ‘Schema_name ‘,tabname => ‘Table_name’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,cascade => TRUE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );

NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ with
method_opt => ‘FOR ALL COLUMNS SIZE 1’

At Schema Level:

exec dbms_stats.gather_schema_stats(ownname => ‘Schema_name’,cascade => TRUE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );

At DB Level:

exec dbms_stats.gather_database_stats(cascade => TRUE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );

Dictionary Stats/ Fixed Object Stats: Ref- 457926.1
******************************************************

Dictionary objects and dbms_stats

Beginning with Oracle Database 10g, Oracle recommends you compute statistics for dictionary tables also. This is a new suggestion. It is recommended in part to enhance the performance of database queries.

There are broadly two types of objects in the data dictionary tables. They are: Fixed (in-memory) and Real (normal) Dictionary Tables.

You can use the following Oracle supplied packages:

DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_DATABASE_STATS
DBMS_STATS.GATHER_DICTIONARY_STATS

Note that the last package procedure gather_dictionary_stats is newly introduced in Oracle Database 10g and is specially designed for collecting statistics for dictionary objects. You need the new system privilege, ANALYZE ANY DICTIONARY, for this purpose. This privilege is required to be able to analyze the dictionary objects and fixed objects, unless you are the SYS user, or a user with SYSDBA privilege.

If your database encounters a lot of changes (DMLs) for SYS schema objects, then it is recommended to collect SYS schema statistics. The collection of statistics on

SYS Schema objects will optimize the performance of internal recursive queries and application queries on SYS schema objects. We can use any one of the following

commands for it.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’);
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

As seen in the above examples, the gather_schema_stats procedure accepts the sys argument to perform statistics collections. In case of the gather_database_stats procedure, there is a new argument gather_sys whose default value is FALSE. When needed, you can set it to TRUE and execute the procedure.

There is another procedure, delete_dictionary_stats, which allows you to remove data dictionary stats.

When you execute the gather_dictionary_stats procedure, it gathers statistics from the SYS and SYSTEM 

schemas, as well as any other schemas that are related, such as OUTLN or DBSNMP schemas.

Gather_fixed_objects_stats also gathers statistics for dynamic tables, e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally be recommended if poor performance is encountered while querying dynamic views ,e.g. V$ views.

Since fixed objects record current database activity, statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity .

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Restoring Statistics: Ref – 452011.1

*****************************************************

By Default oracle retains 31 days statistics.The below query will return the date after which the stats can be restored from. It depends on the retention set @ the DB level.

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

If you have set a non -default value for retention, It can be obtained by issuing

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
– will return the number of days stats are currently retained for.

We can alter the default retention of stats backup as below

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)
– where xx is the number of days to retain them

statistics history for a given table can be obtained as below

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history

We can restore the statistics, after deciding the date based on earlier steps

execute DBMS_STATS.RESTORE_TABLE_STATS (‘owner’,’table’,date);
execute DBMS_STATS.RESTORE_DATABASE_STATS(date);
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date);
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date);
execute DBMS_STATS.RESTORE_SCHEMA_STATS(‘owner’,date);
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date);

Eg:

execute dbms_stats.restore_table_stats (‘SCOTT’,’EMP’,’25-JUL-07 12.01.20.766591 PM +02:00′);