Concurrent Stats improvements in 12c

Concurrent stats gathering was introduced with 11g (11.2.0.2) database – ability to gather statistics on multiple tables and (sub)partitions concurrently to reduce the overall time it takes to gather statistics by enabling oracle to fully utilize multiprocessor environments.

If you call dbms_stats.gather_table_stats on a partitioned table when CONCURRENT is set to true, Oracle will create a separate statistics gathering job for each (sub)partition in the table. The Job Scheduler will decide how many of these jobs will execute concurrently, and how many will be queued based on available system resources. As the currently running jobs complete, more jobs will be dequeued and executed until all (sub)partitions have had their statistics gathered on them.

If you gather the statistics using gather_database_stats or gather_schema_stats, then Oracle will create a separate statistics gathering job for each non-partitioned table, and each (sub)partition for the partitioned table.Each (sub)partitioned table will also have a coordinator job that manages its (sub)partition jobs. The database will then run as many concurrent jobs as possible, and queue the remaining jobs until the executing jobs complete

However, multiple (sub)partitioned tables are not allowed to be processed simultaneously in 11g. Hence, if there are some jobs running for a (sub)partitioned table, other (sub)partitioned tables in a schema (or database) will be queued until the current one completes. There is no such restriction for non-partitioned tables – However this got relaxed in 12c, where multiple jobs can run concurrently on partition tables.

rajesh@ORA11G> select count(*) from big_table ;

  COUNT(*)

----------

  10000000

1 row selected.

rajesh@ORA11G> create table t1

  2  partition by hash(object_id)

  3  ( partition p1,

  4    partition p2,

  5    partition p3,

  6    partition p4 )

  7  as

  8  select * from big_table;

Table created.

rajesh@ORA11G> create table t2

  2  partition by hash(object_id)

  3  ( partition p1,

  4    partition p2,

  5    partition p3,

  6    partition p4 )

  7  as

  8  select * from big_table;

Table created.

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partitioned

  2  from user_tables ;

TABLE_NAME      PAR

--------------- ---

BIG_TABLE       NO

T2              YES

T1              YES

3 rows selected.

So we have totally three tables in the schema, out which two are partitioned and one is non-partitioned.

When CONCURRENT statistics were enabled in 11g (11.2.0.4) database - it goes this way.

rajesh@ORA11G> select dbms_stats.get_prefs('CONCURRENT') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')

------------------------------------------------------------------------------------

FALSE

1 row selected.

rajesh@ORA11G> exec dbms_stats.set_global_prefs('CONCURRENT','TRUE');

PL/SQL procedure successfully completed.

rajesh@ORA11G> select dbms_stats.get_prefs('CONCURRENT') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')

------------------------------------------------------------------------------------

TRUE

1 row selected.

rajesh@ORA11G> exec dbms_stats.gather_schema_stats(user);

/* Monitoring from a different connections

rajesh@ORA11G> select job_name,state,comments

  2  from dba_scheduler_jobs

  3  where job_class like 'CONC%'

  4  order by 1 ;

JOB_NAME                       STATE           COMMENTS

------------------------------ --------------- -----------------------------------

ST$SD2581_1                    RUNNING         "RAJESH"."BIG_TABLE".

ST$SD2581_2                    RUNNING         "RAJESH"."T1".

ST$T2582_1                     RUNNING         RAJESH.T1.P1

ST$T2582_2                     RUNNING         RAJESH.T1.P2

ST$T2582_3                     RUNNING         RAJESH.T1.P3

ST$T2582_4                     RUNNING         RAJESH.T1.P4

ST$T2582_5                     RUNNING         RAJESH.T1.

7 rows selected.

rajesh@ORA11G> show parameter job_queue

NAME                                          TYPE        VALUE

--------------------------------------------- ----------- ------------------------

job_queue_processes                           integer     1000

rajesh@ORA11G>

/*

All statistics collection job names start with 'ST$' for easy identification. The jobs whose name start with ST$T###_### are created by a coordinator job for a partitioned table, and works (sub)partition of the table.

The jobs whose name begin with ST$SD###_### are created for a table in a schema or database, and either works as a coordinator for its partition level jobs (if the table is partitioned), or directly performs the statistics collection for the table (if the table is not partitioned).

So looking at the output from DBA_SCHEDULER_JOBS in 11g database, it confirms that only one partitioned table can be run concurrently, even though the schema has more than one partitioned tables.

But when repeating this test in 12c (12.1.0.2) shows this.

rajesh@ORA12C> select table_name,partitioned

  2  from user_tables ;

TABLE_NAME      PAR

--------------- ---

BIG_TABLE       NO

T1              YES

T2              YES

3 rows selected.

rajesh@ORA12C> select dbms_stats.get_prefs('CONCURRENT') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')

-------------------------------------------------------------------------------------

OFF

1 row selected.

rajesh@ORA12C> exec dbms_stats.set_global_prefs('CONCURRENT','ALL');

PL/SQL procedure successfully completed.

rajesh@ORA12C> select dbms_stats.get_prefs('CONCURRENT') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')

-------------------------------------------------------------------------------------

ALL

1 row selected.

rajesh@ORA12C> exec dbms_stats.gather_schema_stats(user);

/* Monitoring from a different connections

rajesh@ORA12C> column job_name format a30

rajesh@ORA12C> column comments format a70

rajesh@ORA12C> select job_name,state,comments

  2  from dba_scheduler_jobs

  3  where job_class like 'CONC%'

  4  order by 1 ;

JOB_NAME        STATE     COMMENTS

--------------- --------- -----------------------------------------------------------

ST$SD5402_1     RUNNING   "RAJESH"."BIG_TABLE"

ST$SD5402_2     RUNNING   "RAJESH"."T1"

ST$SD5402_3     RUNNING   "RAJESH"."T2"

ST$T5442_1_B3   RUNNING   "RAJESH"."T1"."P1";"RAJESH"."T1"."P2";"RAJESH"."T1"."P3"

ST$T5442_2_B2   RUNNING   "RAJESH"."T1"."P4";"RAJESH"."T1"

ST$T5482_1_B2   RUNNING   "RAJESH"."T2"."P1";"RAJESH"."T2"."P2"

ST$T5482_2_B2   RUNNING   "RAJESH"."T2"."P3";"RAJESH"."T2"."P4"

ST$T5482_3      RUNNING   "RAJESH"."T2"

8 rows selected.

rajesh@ORA12C> show parameter job_queue_

NAME                                          TYPE        VALUE

--------------------------------------------- ----------- -------------------------

job_queue_processes                           integer     1000

rajesh@ORA12C>

*/

So looking at the output from DBA_SCHEDULER_JOBS in 12c database, it confirms that multiple partitioned tables can be run concurrently, while that is not possible in 11g.