Concurrent statistics activation and gathering

 

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:

concurrent_statistics01
concurrent_statistics01

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

This entry was posted in Oracle and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>