Oracle: Concurrent Database/Table Statistics Gathering

Product: Oracle RDBMS
Version: 12.1.0.x onward

For more than 10 years, Oracle has not able to allow multiple threads of table/index statistics gathering.  I am limited to use DEGREE which I don't prefer, or auto degree.  This is certainly my personal preference as I like to have more control.  Oracle 11.2.0.2 introduced concurrent statistics gathering parameter and it is saving me time, as well as providing me greater flexibility.  I can use both auto degree, and auto concurrent statistics gathering which reduced the 1.5 hr serial task to < 5 min.

Following is the procedure to leverage this feature:

1. Enable concurrent statistics gathering preference:
1. exec dbms_stats.set_Global_Prefs ('Concurrent', 'Automatic')
2. I dislike using DEGREE > 1, except for large table.  So for all the small tables which is < 300MB (~ 1 million rec), I will force it to 1, but for large table, I will let Oracle decide by using DBMS_STATS.AUTO_DEGREE
1. Small table: exec dbms_stats.set_table_prefs(user, '', degree=>1)
2. Large table: exec dbms_stats.set_table_prefs(user, '', degree=>DBMS_STATS.AUTO_DEGREE)
3. Now I can start statics gathering job
1. exec dbms_stats.gather_database_stats
4. For volatile tables, I will delete its stats so that Oracle will rely on dynamic sampling to gather stats.  I will further lock it to prevent other DBA accidentally gather stats on these tables

Other dependencies:

1. Disable this parameter to prevent parallel jobs from being down graded to serial
1. alter system set parallel_adaptive_multi_user=false;
2. If table partition exists, then configure INCREMENTAL=true preference for each partition/subpartition (requires table_pref of granularity=global, estimate_percent=auto_sample_size)

Maintenance Plan

When used DEGREE, Oracle only use 100% CPU for 1 CPU core.  I recommend to create a maintenance plan which allows to leverage 75% - 95% CPU, depending on application impact and time of execution.  In many cases, the task will be perform at night time once a week, but on certain scenarios, I might have to perform adhoc tuning, so I need to create 2 maintenance plan, one with 95% CPU at night time, and another one with 75% CPU at day time

Creates new Resource Manager Plan that uses 95% CPU on 4 CPU

1. exec dbms_resource_manager.Clear_Pending_Area;
2. exec dbms_resource_manager.Create_Pending_Area;
3. exec dbms_resource_manager.Create_Plan('STATS95_RM_PLAN', 'Resource Manager Plan - DBStats 95%')
4. exec dbms_resource_manager.Create_Plan_Directive(plan=>'STATS95_RM_PLAN',
     Group_or_SubPlan => 'ora$autotask',
     Mgmt_p1 => 5,
     Max_Utilization_Limit => 95,
     parallel_degree_limit_p1 => 4)
5. exec dbms_resource_manager.Create_Plan_Directive(plan=>'STATS95_RM_PLAN',
     mgmt_p1 => 20, Group_or_Subplan => 'other_Groups')
6. exec dbms_resource_manager.Validate_Pending_Area
7. exec dbms_resource_manager.Submit_Pending_Area

You can check DBA_AUTOTASK_JOB_HISTORY to find out how long the statistics gathering job run after created above Resource Manager plan/directive.

select job_start_Time, job_Duration, job_Status from DBA_AUTOTASK_JOB_HISTORY

where client_name = 'auto optimizer stats collection' order by job_start_time desc;

Resource Manager plan can be switch/modify in real time, so if there is some schedule maintenance that overlapping with the stats gathering task, I can switch the plan with another one that used 75% CPU, or modify it further to 20%.  Last resort is to suspend it, and resume it next day