Gather Optimizer statistics, 11g

In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.

Below are the steps in case if we want to delete & gather schema stats on adhoc basis.

Gathering statistics for all objects in a schema

1. Ensure that the automatic statistics gathering job is scheduled, using the following SQL.

SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

Output should be 'TRUE'.

Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.

2. Delete any existing statistics, using the following command from SQLPLUS.

EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

3. Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:

EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');

4. Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Gather Schema statistics

exec dbms_stats.gather_schema_stats(

       ownname => 'SCOTT',

       cascade => TRUE,

       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

Gathering statistics an individual table

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'

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:

• ESTIMATE_PERCENT: defaults:
•  9i : 100%
• 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
• 11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)

• METHOD_OPT: defaults:
• 9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
• 10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan. 

Export and Import schema statistics

Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.

Create the table to store the statistics- 

EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’);

Export schema stats – will be stored in the ‘STATS_TABLE’

EXEC DBMS_STATS.export_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’);

If required import these statistics back to TEST schema.

EXEC DBMS_STATS.import_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’);

Finally drop the table created to backup the schema stats

EXEC DBMS_STATS.drop_stat_table(‘TEST’,’STATS_TABLE’);