Table of contents
Preamble
To be honest I discovered this concurrent statistics feature in 12cR1 and then realized that it was available since 11.2.0.2 patchset. The high level definition is an improvement of statistics gathering with Job Scheduler / Advanced Queuing on multiple objects in parallel (multiple partitions of same table or multiple objects in same schema).
Improved in 12cR1 to club multiple small objects in one parallel job, it can also be used in automatic nightly statistics gathering job.
Testing has been done on Oracle Enterprise edition 11gR2 (11.2.0.4) running on a HPUX Itanium box: 11iv3 (11.31), 8 cores at 1.6 GHz and 32 GB of RAM. I was almost alone on the server…
The schema I have used (BCKGDBA) has 182 partitioned and non-partitioned tables with associated indexes for a total of 43 GB.
Traditional statistics gathering
My aim is to compare a traditional command to gather schema statistics versus a concurrent one.
If you issue below command:
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('bckgdba'); PL/SQL PROCEDURE successfully completed. Elapsed: 01:06:30.51 |
You can notice that you server is not highly overloaded due to sequential nature of your command:
System: server1 Wed Jun 24 16:29:36 2015 Load averages: 0.15, 0.13, 0.11 380 processes: 313 sleeping, 67 running Cpu states: CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS 0 0.18 57.4% 0.0% 4.0% 38.6% 0.0% 0.0% 0.0% 0.0% 2 0.08 7.9% 0.0% 9.9% 82.2% 0.0% 0.0% 0.0% 0.0% 4 0.09 0.0% 81.0% 3.0% 16.0% 0.0% 0.0% 0.0% 0.0% 6 0.19 45.0% 0.0% 4.0% 51.0% 0.0% 0.0% 0.0% 0.0% 8 0.26 6.0% 0.0% 11.0% 83.0% 0.0% 0.0% 0.0% 0.0% 10 0.03 3.0% 0.0% 12.0% 85.0% 0.0% 0.0% 0.0% 0.0% 12 0.27 0.0% 11.0% 4.0% 85.0% 0.0% 0.0% 0.0% 0.0% 14 0.10 4.0% 0.0% 6.0% 90.0% 0.0% 0.0% 0.0% 0.0% --- ---- ----- ----- ----- ----- ----- ----- ----- ----- avg 0.15 15.2% 11.1% 6.1% 67.7% 0.0% 0.0% 0.0% 0.0% System Page Size: 4Kbytes Memory: 11669972K (4717940K) real, 19870096K (7858640K) virtual, 5182180K free Page# 1/10 CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND 6 ? 22089 oradwhte 241 20 4779M 93600K run 23:27 97.86 97.69 oracledwhte 4 ? 28261 edwhrun 249 24 5733M 8812K run 0:12 79.78 42.10 oracleedwhubte 14 ? 12185 oradwhte 154 20 4702M 8524K sleep 0:03 1.21 1.21 oracledwhte |
An easy way to improve the schema statistics gathering is to use parallelism (double number of core for example):
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'bckgdba', degree=>16); PL/SQL PROCEDURE successfully completed. Elapsed: 00:28:41.77 |
It improves a lot the execution time and you are using much more resources:
System: server1 Wed Jun 24 16:30:42 2015 Load averages: 0.26, 0.17, 0.12 387 processes: 323 sleeping, 64 running Cpu states: CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS 0 0.26 3.6% 53.1% 0.4% 43.0% 0.0% 0.0% 0.0% 0.0% 2 0.33 7.3% 9.3% 1.2% 82.2% 0.0% 0.0% 0.0% 0.0% 4 0.28 44.6% 2.0% 1.8% 51.7% 0.0% 0.0% 0.0% 0.0% 6 0.38 7.9% 1.0% 2.4% 88.8% 0.0% 0.0% 0.0% 0.0% 8 0.26 31.6% 0.2% 3.0% 65.2% 0.0% 0.0% 0.0% 0.0% 10 0.19 15.6% 11.6% 1.8% 71.0% 0.0% 0.0% 0.0% 0.0% 12 0.21 5.7% 0.4% 1.6% 92.3% 0.0% 0.0% 0.0% 0.0% 14 0.15 5.5% 0.0% 2.0% 92.5% 0.0% 0.0% 0.0% 0.0% --- ---- ----- ----- ----- ----- ----- ----- ----- ----- avg 0.26 15.2% 9.7% 1.8% 73.4% 0.0% 0.0% 0.0% 0.0% System Page Size: 4Kbytes Memory: 11926460K (6044316K) real, 20082044K (10097280K) virtual, 4921592K free Page# 1/10 CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND 4 ? 22089 oradwhte 154 20 4746M 61664K sleep 44:24 33.23 33.17 oracledwhte 10 ? 12539 oradwhte 211 20 4729M 47100K run 9:51 16.98 16.95 ora_p001_dwhte 6 ? 12545 oradwhte 213 20 4729M 46960K run 10:10 16.87 16.84 ora_p004_dwhte 2 ? 2835 oradwhte 148 20 4729M 45164K sleep 9:12 16.44 16.41 ora_p013_dwhte 0 ? 12541 oradwhte 148 20 4729M 47104K sleep 9:57 16.41 16.38 ora_p002_dwhte 4 ? 12553 oradwhte 148 20 4729M 46960K sleep 9:50 16.41 16.38 ora_p008_dwhte 14 ? 12555 oradwhte 148 20 4729M 46960K sleep 10:04 15.64 15.61 ora_p009_dwhte 8 ? 12549 oradwhte 148 20 4729M 46960K sleep 10:01 15.63 15.61 ora_p006_dwhte 2 ? 12537 oradwhte 212 20 4729M 47100K run 10:03 15.50 15.47 ora_p000_dwhte 12 ? 12551 oradwhte 209 20 4729M 46956K run 10:00 15.41 15.38 ora_p007_dwhte 6 ? 2831 oradwhte 148 20 4729M 45168K sleep 9:20 15.30 15.28 ora_p011_dwhte 10 ? 2839 oradwhte 148 20 4729M 45164K sleep 9:16 15.28 15.25 ora_p015_dwhte 4 ? 2829 oradwhte 148 20 4729M 45164K sleep 9:22 15.19 15.17 ora_p010_dwhte 14 ? 2833 oradwhte 148 20 4729M 45248K sleep 9:19 14.79 14.77 ora_p012_dwhte 2 ? 12547 oradwhte 148 20 4729M 46960K sleep 10:23 14.69 14.67 ora_p005_dwhte 8 ? 12543 oradwhte 148 20 4729M 46960K sleep 10:14 13.98 13.96 ora_p003_dwhte 0 ? 2837 oradwhte 148 20 4729M 45164K sleep 9:24 12.75 12.73 ora_p014_dwhte 12 ? 3395 oradwhte 154 20 4683M 8128K sleep 1:32 1.33 1.32 ora_p021_dwhte 6 ? 3393 oradwhte 154 20 4683M 8316K sleep 1:32 1.26 1.26 ora_p020_dwhte 8 ? 3409 oradwhte 154 20 4683M 6716K sleep 1:32 1.24 1.24 ora_p028_dwhte 14 ? 3407 oradwhte 154 20 4683M 6652K sleep 1:32 1.22 1.22 ora_p027_dwhte 12 ? 3387 oradwhte 154 20 4683M 8316K sleep 1:32 1.21 1.21 ora_p017_dwhte 6 ? 3385 oradwhte 154 20 4683M 8124K sleep 1:33 1.17 1.17 ora_p016_dwhte 10 ? 3411 oradwhte 154 20 4683M 6652K sleep 1:31 1.17 1.16 ora_p029_dwhte 10 ? 3399 oradwhte 154 20 4683M 7356K sleep 1:32 1.16 1.16 ora_p023_dwhte 2 ? 3405 oradwhte 154 20 4683M 7100K sleep 1:31 1.15 1.15 ora_p026_dwhte 4 ? 3401 oradwhte 154 20 4683M 7292K sleep 1:31 1.13 1.13 ora_p024_dwhte 4 ? 3403 oradwhte 154 20 4683M 7100K sleep 1:32 1.12 1.12 ora_p025_dwhte 8 ? 3415 oradwhte 154 20 4683M 6848K sleep 1:31 1.10 1.10 ora_p031_dwhte 0 ? 3413 oradwhte 154 20 4683M 6844K sleep 1:32 1.09 1.09 ora_p030_dwhte 8 ? 3389 oradwhte 154 20 4683M 8316K sleep 1:31 1.09 1.08 ora_p018_dwhte 0 ? 3397 oradwhte 154 20 4683M 8316K sleep 1:32 1.09 1.08 ora_p022_dwhte 12 ? 3391 oradwhte 154 20 4683M 8124K sleep 1:31 1.05 1.04 ora_p019_dwhte |
I executed the command three times and got around 28, 33 and 36 minutes as execution time.
Increasing the parallelism to 32 is consuming higher resources but it is not particularly decreasing the execution time not to say it increases it…
Concurrent statistics testing
By default the feature is not activated, you can control this with:
SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM dual; DBMS_STATS.GET_PREFS('CONCURRENT') -------------------------------------------------------------------------------- FALSE |
Now let’s activate concurrent statistics. I start by changing the parameter from its default value (false):
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','true'); PL/SQL PROCEDURE successfully completed. SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM dual; DBMS_STATS.GET_PREFS('CONCURRENT') -------------------------------------------------------------------------------- TRUE |
Remark:
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.
You also need to have job_queue_processes parameter different from 0:
SQL> show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes INTEGER 100 |
You also need to deactivate parallel_adaptive_multi_user:
SQL> ALTER SYSTEM SET parallel_adaptive_multi_user=FALSE; SYSTEM altered. SQL> show parameter parallel_adaptive_multi_user NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_adaptive_multi_user BOOLEAN FALSE |
You also need to have an active resource plan, which is already my case (its aim is to limit few BO accounts). The aim of this resource plan activation is to control the resources used by concurrent statistics jobs (not my target):
SQL> show parameter resource_manager_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_manager_plan string LIMIT_REPORTING_PLAN |
The DEFAULT_PLAN available by default can do the job, I have tested when activating it with Cloud Control 12c:
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:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'bckgdba', tabname=>'interco_hinvoice',degree=>16); BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname=>'bckgdba', tabname=>'interco_hinvoice',degree=>16); END; * 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 |
Can be solve with below grants but is highly cumbersome to do it on all accounts of your database:
SQL> GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO bckgdba; GRANT succeeded. SQL> SELECT dbms_stats.get_prefs('CONCURRENT','bckgdba','hinvoice') flag FROM dual; FLAG -------------------------------------------------------------------------------- TRUE |
I got crazy while not seeing the feature in action when I had the great idea to disconnect and reconnect. Selecting from DBA_SCHEDULER_JOBS you can see ST$Txxx and ST$SDxxx jobs (for tables and partitioned tables):
SQL> col COMMENTS FOR a50 SQL> SELECT job_name, state, comments FROM dba_scheduler_jobs WHERE job_class LIKE 'CONC%'; JOB_NAME STATE COMMENTS ------------------------------ --------------- -------------------------------------------------- ST$SD1732_71 RUNNING "BCKGDBA"."GIT_EXT_DLY_20150304". ST$SD1732_72 RUNNING "BCKGDBA"."GIT_EXT_DLY_20150305". ST$SD1732_74 RUNNING "BCKGDBA"."GIT_EXT_DLY_FOR_TEST". ST$SD1732_75 RUNNING "BCKGDBA"."GIT_EXT_DLY_NP". ST$SD1732_76 RUNNING "BCKGDBA"."GIT_EXT_DLY_NP_20150304". ST$SD1732_77 RUNNING "BCKGDBA"."GIT_EXT_DLY_NP_20150305". ST$SD1732_79 RUNNING "BCKGDBA"."GIT_EXT_DLY_YE". ST$SD1732_80 RUNNING "BCKGDBA"."GIT_EXT_WLY". ST$SD1732_81 RUNNING "BCKGDBA"."GIT_EXT_WLY_OLD_100514". ST$SD1732_83 RUNNING "BCKGDBA"."GIT_EXT_WLY_YE". ST$SD1732_85 RUNNING "BCKGDBA"."GIT_WLY". ST$SD1732_88 RUNNING "BCKGDBA"."GIT_WLY_YE". ST$SD1732_89 RUNNING "BCKGDBA"."HINVOICE". ST$SD1732_90 RUNNING "BCKGDBA"."HINVOICE_BILL_MAX". ST$SD1732_91 RUNNING "BCKGDBA"."HINVOICE_DEC_2012". ST$SD1732_92 RUNNING "BCKGDBA"."HINV_3M_BF". ST$SD1732_93 RUNNING "BCKGDBA"."HINV_3M_CS". ST$SD1732_94 SCHEDULED "BCKGDBA"."HINV_ACCRUALS_0082_CP". ST$SD1732_95 SCHEDULED "BCKGDBA"."HINV_DEFER_3M_BF". ST$SD1732_96 SCHEDULED "BCKGDBA"."HINV_DEFER_3M_CS". ST$SD1732_97 SCHEDULED "BCKGDBA"."I2DM_ASP_NEW". ST$SD1732_98 SCHEDULED "BCKGDBA"."I2DM_ASP_NEW_BK". ST$SD1732_99 SCHEDULED "BCKGDBA"."I2DM_ASP_NEW_OLD". . . . |
You can also use DBA_SCHEDULER_RUNNING_JOBS to have only the running jobs:
SQL> SELECT job_name, elapsed_time FROM dba_scheduler_running_jobs WHERE job_name LIKE 'ST$%'; JOB_NAME ELAPSED_TIME ------------------------------ --------------------------------------------------------------------------- ST$SD1732_182 +000 00:00:26.16 ST$T1735_1 +000 00:00:25.70 ST$T1735_2 +000 00:00:25.58 ST$T1735_3 +000 00:00:25.58 ST$T1735_4 +000 00:00:25.51 ST$T1735_5 +000 00:00:25.51 ST$T1735_13 +000 00:00:25.22 7 ROWS selected. |
Remark:
You can also have additional historical and live information with DBA_OPTSTAT_OPERATIONS and DBA_OPTSTAT_OPERATION_TASKS views. The DBA_OPTSTAT_OPERATION_TASKS containing live information is available only starting with 12cR1.
My server was much more loaded, I can even say overloaded, this is where Database Resource Manager (DBRM) can help:
System: server1 Thu Jun 25 13:01:47 2015 Load averages: 1.49, 0.89, 0.44 509 processes: 398 sleeping, 111 running Cpu states: CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS 0 1.29 92.1% 0.0% 0.0% 7.9% 0.0% 0.0% 0.0% 0.0% 2 1.63 95.0% 0.0% 0.0% 5.0% 0.0% 0.0% 0.0% 0.0% 4 1.62 96.0% 0.0% 0.0% 4.0% 0.0% 0.0% 0.0% 0.0% 6 1.58 96.0% 0.0% 0.0% 4.0% 0.0% 0.0% 0.0% 0.0% 8 1.55 96.0% 0.0% 2.0% 2.0% 0.0% 0.0% 0.0% 0.0% 10 1.49 90.0% 0.0% 3.0% 7.0% 0.0% 0.0% 0.0% 0.0% 12 1.42 90.0% 0.0% 0.0% 10.0% 0.0% 0.0% 0.0% 0.0% 14 1.35 91.0% 0.0% 1.0% 8.0% 0.0% 0.0% 0.0% 0.0% --- ---- ----- ----- ----- ----- ----- ----- ----- ----- avg 1.49 93.9% 0.0% 0.0% 6.1% 0.0% 0.0% 0.0% 0.0% System Page Size: 4Kbytes Memory: 14535260K (7160236K) real, 25461384K (14330708K) virtual, 2972864K free Page# 1/14 CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND 2 ? 15717 oradwhte 241 20 4746M 55756K run 0:21 17.42 17.39 ora_j028_dwhte 14 ? 15729 oradwhte 154 20 4746M 50764K sleep 0:21 17.17 17.14 ora_j030_dwhte 10 ? 15584 oradwhte 148 20 4746M 57804K sleep 0:16 17.09 17.06 ora_j001_dwhte 12 ? 15616 oradwhte 241 20 4746M 50592K run 0:16 16.98 16.96 ora_j012_dwhte 14 ? 15743 oradwhte 241 20 4746M 50892K run 0:21 16.45 16.42 ora_j032_dwhte 2 ? 15628 oradwhte 154 20 4746M 50700K sleep 0:16 16.44 16.41 ora_j015_dwhte 8 ? 15590 oradwhte 241 20 4746M 50960K run 0:18 16.41 16.38 ora_j004_dwhte 12 ? 15681 oradwhte 241 20 4746M 51660K run 0:15 16.21 16.18 ora_j024_dwhte 12 ? 15612 oradwhte 154 20 4746M 53356K sleep 0:15 16.04 16.01 ora_p013_dwhte 8 ? 15735 oradwhte 154 20 4746M 51360K sleep 0:19 16.00 15.97 ora_j031_dwhte 0 ? 15606 oradwhte 154 20 4746M 50748K sleep 0:18 15.91 15.88 ora_j010_dwhte 4 ? 15645 oradwhte 154 20 4746M 48860K sleep 0:14 15.83 15.81 ora_p022_dwhte 10 ? 15598 oradwhte 154 20 4746M 50636K sleep 0:16 15.73 15.70 ora_j008_dwhte 14 ? 15636 oradwhte 240 20 4746M 57792K run 0:13 15.69 15.67 ora_j017_dwhte 6 ? 15649 oradwhte 239 20 4746M 53328K run 0:17 15.67 15.65 ora_p023_dwhte 2 ? 15755 oradwhte 148 20 4746M 50588K sleep 0:21 15.65 15.63 ora_j034_dwhte 12 ? 15647 oradwhte 241 20 4746M 51516K run 0:16 15.59 15.57 ora_j019_dwhte 6 ? 15632 oradwhte 154 20 4746M 50828K sleep 0:14 15.46 15.44 ora_j016_dwhte 2 ? 15634 oradwhte 154 20 4746M 53580K sleep 0:13 15.46 15.43 ora_p019_dwhte 8 ? 15651 oradwhte 154 20 4746M 53344K sleep 0:14 15.29 15.27 ora_p024_dwhte 4 ? 15622 oradwhte 238 20 4746M 53304K run 0:16 15.21 15.18 ora_p016_dwhte 6 ? 15767 oradwhte 154 20 4746M 50668K sleep 0:19 15.16 15.13 ora_j036_dwhte 4 ? 15687 oradwhte 154 20 4746M 57728K sleep 0:19 15.08 15.05 ora_j025_dwhte 14 ? 15638 oradwhte 154 20 4746M 53296K sleep 0:14 15.04 15.01 ora_p020_dwhte 10 ? 15659 oradwhte 154 20 4746M 51080K sleep 0:19 15.03 15.01 ora_p027_dwhte 10 ? 15657 oradwhte 148 20 4746M 52000K sleep 0:16 15.01 14.98 ora_p026_dwhte 2 ? 15667 oradwhte 154 20 4746M 46448K sleep 0:16 14.62 14.59 ora_p030_dwhte 0 ? 15661 oradwhte 239 20 4746M 52236K run 0:13 14.47 14.45 ora_p028_dwhte 10 ? 15665 oradwhte 154 20 4746M 52400K sleep 0:16 14.28 14.25 ora_p029_dwhte 12 ? 15630 oradwhte 154 20 4746M 53276K sleep 0:13 13.67 13.65 ora_p018_dwhte 0 ? 15655 oradwhte 154 20 4746M 51404K sleep 0:14 13.39 13.37 ora_p025_dwhte 4 ? 15643 oradwhte 154 20 4746M 46316K sleep 0:15 13.34 13.31 ora_p021_dwhte 2 ? 15626 oradwhte 154 20 4746M 53372K sleep 0:13 12.78 12.76 ora_p017_dwhte 12 ? 15596 oradwhte 154 20 4746M 59196K sleep 0:27 8.56 8.55 ora_j007_dwhte 4 ? 15620 oradwhte 154 20 4697M 13756K sleep 0:16 7.33 7.32 ora_j013_dwhte 14 ? 15624 oradwhte 154 20 4697M 13888K sleep 0:07 7.02 7.01 ora_j014_dwhte 8 ? 15675 oradwhte 154 20 4697M 13504K sleep 0:08 6.93 6.91 ora_j023_dwhte |
I have executed the concurrent statistics 8 times to test few monitoring views and over the 8 runs I got an average of a bit below 10 minutes so it divides by three the response time at a cost of higher server load…
So overall the feature is great if you are in a rush to gather statistics on multiple tables/schemas and have the whole server resources for yourself. Can also be used if your workload is located in a unique time zone. Where I work it would be a bit more complex as the server is covering three time zones (Asia/Pacific, Europe and United States). Also what I do not like too much is the binary approach when activating statistics: you cannot activate it only for your session it has to be for the whole database or not. And when activated you must not forget to grant the additional required privileges…
References
- FAQ: Gathering Concurrent Statistics Using DBMS_STATS Frequently Asked Questions (Doc ID 1555451.1)
- Gathering Optimizer Statistics Concurrently
- How to Gather Optimizer Statistics Fast!
