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
