Gather Optimizer statistics, 11g

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');

d. 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);

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. 

Automated Maintenance Tasks in Oracle 11g

Oracle Database 11g comes with three predefined automatic maintenance tasks:

• The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
• The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
• Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database. as shown in the following example:

Oracle PL/SQL

1 2 3 4 5 6 7

SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  ENABLED   auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED

These tasks are enabled by default.

All automated maintenance tasks are scheduled to run during the Oracle Scheduler maintenance window. The Oracle Scheduler creates a job when the window (period) starts. After the automated task job completed, the Oracle Scheduler automatically drops that job.

You can view all the predefined maintenance windows by querying the DBA_AUTOTASK_SCHEDULE view:

Oracle PL/SQL

1 2 3 4 5 6 7 8 9 10

SELECT * FROM dba_autotask_schedule;   WINDOW_NAME          START_TIME    DURATION     -------------------- ------------- ----------- MONDAY_WINDOW        08/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   MONDAY_WINDOW        15/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   MONDAY_WINDOW        22/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   ...   SUNDAY_WINDOW        21/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0   SUNDAY_WINDOW        28/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0

You can disable any of the tasks by using the DBMS_AUTO_TASK_ADMIN package. If you want to disable all the automated tasks, issue the following command::

Oracle PL/SQL

1	EXEC dbms_auto_task_admin.disable;

If you want to enable all the automated tasks, issue the following command:

Oracle PL/SQL

1	EXEC dbms_auto_task_admin.enable;

You can also disable (or enable) a specific task. For example, you can issue the following command to disable “gather_stats_prog” task:

Oracle PL/SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

EXEC dbms_auto_task_admin.disable( 'auto optimizer stats collection', NULL, NULL );   SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  DISABLED auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED   EXEC dbms_auto_task_admin.enable( 'auto optimizer stats collection', NULL, NULL );   SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  ENABLED   auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED

You can also use the “Automated Maintenance Tasks” screen of Enterprise Manager to manage these automated tasks.