8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Concurrent Statistics Collection in Oracle Database 12c Release 1 (12.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.
Related articles.
- Cost-Based Optimizer (CBO) And Database Statistics
- Statistics Collection Enhancements in Oracle Database 12c Release 1 (12.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.
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.
For more information see:
- DBMS_STATS
- Cost-Based Optimizer (CBO) And Database Statistics
- Statistics Collection Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...