Concurrent Statistics Collection in Oracle Database 12c Release 1

Concurrent statistics collection is simply the ability to gather statistics on multiple tables, or table partitions, at the same time. This is done using a combination of the job scheduler, advanced queuing and resource manager. Concurrent statistics collection can reduce the time it takes to gather statistics, provided the system can cope with the extra workload.

1. Enabling Concurrent Statistics Collection

From a user perspective, the concurrent statistics collection functionality is very simple. You set the CONCURRENT global preference to the required value using the DBMS_STATS package and Oracle determines if concurrency is appropriate and if so, the level of concurrency to use. The CONCURRENT preference is set to OFF by default, as shown below.

CONN sys@pdb1 AS SYSDBA

SELECT DBMS_STATS.get_prefs('CONCURRENT') FROM dual;

DBMS_STATS.GET_PREFS('CONCURRENT')
----------------------------------------------------------------------------------------------------
OFF

SQL>

This can be altered if required using the following command.

BEGIN
  DBMS_STATS.set_global_prefs (
    pname   => 'CONCURRENT',
    pvalue  => 'ALL');
END;
/

The allowable values for the CONCURRENT preference are shown below.

◈ MANUAL - Enabled for manual statistics collection only.
◈ AUTOMATIC - Enabled for automatic statistics collection only.
◈ ALL - Enabled for both manual and automatic statistics collection.
◈ OFF - Disabled.

2. Common Errors

As mentioned previously, concurrent statistics collection requires a combination of the job scheduler, advanced queuing and resource manager. The ability to interact with these features is not granted by default, so if you enable concurrent statistics collection, users may have difficulty gathering statistics themselves, even for objects they own. This issue is show below.

CONN test/test@pdb1

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 10000;

EXEC DBMS_STATS.delete_table_stats(USER, 'TAB1');

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

SQL>

The user must be granted the DBA role, or more sensibly the CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE privileges.

CONN sys@pdb1 AS SYSDBA
GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO test;

conn test/test@pdb1
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: Resource Manager is not
enabled.
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

SQL>

Notice the error message has changed. It is now telling us that resource manager is not enabled. Assigning a resource plan is a prerequisite for using this functionality.

CONN sys@pdb1 AS SYSDBA
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;

conn test/test@pdb1
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

PL/SQL procedure successfully completed.

SQL>

As we can see, with resource manager enabled and the required privileges present, we are able to gather statistics again.