DB Wiki

Utilises three components

• Scheduler
• Advanced Queueing
• Resource Manager

• JOB_QUEUE_PROCESSES parameter must be set to at least 4
• Resource Manager must be enabled
• CONCURRENT preference of the DBMS_STATS.SET_GLOBAL_PREFS must be MANUAL, AUTOMATIC or ALL

Enable the Resource Manager by specifying a resource plan

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';

Set the JOB_QUEUE_PROCESSES parameter to a value at least twice the number of CPU cores

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 8;

Enable Concurrent Statistics by executing the SET_GLOBAL_PREFS procedure

BEGIN
  DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
END;
/

Check it is enabled by issuing the following query

SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;

After starting a manual or automatic statistics gathering job, you can monitor it

SELECT target, target_type, job_name, to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') time_started
FROM dba_optstat_operation_tasks
WHERE status = 'IN PROGRESS'
AND opid = (SELECT max(id) FROM dba_optstat_operations
            WHERE operation = 'gather_schema_stats');