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
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>
*/
