Gathering Concurrent Statistics in Oracle Database -- Speeding up Statistics gather

 

We planned  to  share  load of  stats  gather across rac  instances and came across this feature of Concurrent Statistics .

Please note there are few  reported issue of high cpu usage

Dont forget to  fulfil requirements :

1) You also need to have job_queue_processes parameter different from 0:

SQL> show parameter job_queue_processes

2) You also need to deactivate parallel_adaptive_multi_user:

 ALTER SYSTEM SET parallel_adaptive_multi_user=FALSE;

3) You also need to have an active resource plan . The aim of this resource plan activation is to control the resources used by concurrent statistics jobs 

SQL> show parameter resource_manager_plan

Check for the stats global preference value using below query

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

Turn On CONCURRENT  statistics 

exec dbsm_stats.set_global_prefs('DEGREE', dbms_stats.auto_degree) ; 

BEGIN

DBMS_STATS.set_global_prefs (

pname => ‘CONCURRENT’,

pvalue => ‘ALL’);

END;

/

in 12cR1 the authorized values are : MANUAL, AUTOMATIC, ALL and OFF to control whether you wish concurrent statistics for manual commands, for automatic jobs for both or not.

Instead of ALL (which works for both manual and automatic stats collection), we can also set below values

MANUAL – only for manual stats collection

AUTOMATIC – only for automatic stats collection

OFF -- TO SWITCH OFF 

If you see that Global preferences value is already set to ALL, you need to grant below mentioned roles to the user which is performing gather stats.

These grants are not default, so users will face issues if they use concurrent statistics.

SQL> GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO testuser;

Be very careful with the concurrent statistics feature as when activated most of your users will not be able any more to gather statistics, even on their own objects:

 

ERROR AT line 1:

ORA-20000: Unable TO gather STATISTICS concurrently, insufficient PRIVILEGES

ORA-06512: AT "SYS.DBMS_STATS", line 24281

ORA-06512: AT "SYS.DBMS_STATS", line 24332

ORA-06512: AT line 1

Checking if concurrent jobs :

     col COMMENTS FOR a50

     SELECT job_name, state, comments

     FROM dba_scheduler_jobs

     WHERE job_class LIKE 'CONC%';

 

 

Checking Global Preference : 

SET LINESIZE 150

COLUMN autostats_target FORMAT A20

COLUMN cascade FORMAT A25

COLUMN degree FORMAT A10

COLUMN estimate_percent FORMAT A30

COLUMN method_opt FORMAT A25

COLUMN no_invalidate FORMAT A30

COLUMN granularity FORMAT A15

COLUMN publish FORMAT A10

COLUMN incremental FORMAT A15

COLUMN stale_percent FORMAT A15

SELECT DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET') AS autostats_target,

       DBMS_STATS.GET_PREFS('CASCADE') AS cascade,

       DBMS_STATS.GET_PREFS('DEGREE') AS degree,

       DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') AS estimate_percent,

       DBMS_STATS.GET_PREFS('METHOD_OPT') AS method_opt,

       DBMS_STATS.GET_PREFS('NO_INVALIDATE') AS no_invalidate,

       DBMS_STATS.GET_PREFS('GRANULARITY') AS granularity,

       DBMS_STATS.GET_PREFS('PUBLISH') AS publish,

       DBMS_STATS.GET_PREFS('INCREMENTAL') AS incremental,

       DBMS_STATS.GET_PREFS('STALE_PERCENT') AS stale_percent ,

       DBMS_STATS.get_prefs('CONCURRENT')  AS CONCURRENT  

FROM   dual;

col spare4 format a40 head 'VALUE'
	select sname,spare4
	from sys.optstat_hist_control$
	order by 1
	/

Checking Table level preference :

select * from dba_tab_stat_prefs where table_name = '&&TABLE';

 

References : 

Oracle Concurrent （CONCURREMT） Collect statistics ( file ID 1555451.1) 

https://docs.oracle.com/database/121/TGSQL/tgsql_stats.htm#TGSQL428