Oracle并行和并发收集统计信息-阿里云开发者社区

常用SQL

官网：https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059

https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-01FAB8ED-E4A3-4C3E-8FE2-88717DCDDA06

 
-- 收集数据库信息
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(degree=>24);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

-- 收集schema信息
EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH', DEGREE=>4);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

-- 收集表信息
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

-- 收集index信息
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

-- 删除收集信息
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

-- 创建备份收集信息表
begin
dbms_stats.create_stat_table(USER,stattab => 'STAT_TABLE');
end;

-- 备份收集信息
BEGIN
dbms_stats.export_table_stats(USER,tabname => 'FEI_T',stattab => 'STAT_TABLE');
END;

-- 删除收集信息
BEGIN
DBMS_STATS.delete_table_stats(USER,tabname => 'FEI_T');
END;

-- 导入收集信息
BEGIN
dbms_stats.IMPORT_TABLE_STATS(USER,'FEI_T',stattab => 'STAT_TABLE');
END;

-- 说明：
当前用户可以使用user代替用户名
分析表相关对象信息cascade => true

并行收集统计信息(PARALLEL )

当某个表的Size特别大时，可以通过并行的Slave进程共同工作来加快统计信息收集的效率。
默认情况下，数据库能够根据在表或者索引级别设置的并行度（默认：1）进行并行统计信息收集。
但我们也可以通过显示地设置degree参数来控制并行统计信息收集的并行度。

DEGREE参数：

DEGREE参数用于控制统计信息收集的并行度。
你可以通过以下的方式进行赋值：

1.通过DBMS_STATS.SET_*_PREFS包设置全局变量
2.通过DBMS_STATS.GATHER_*_STATS包设置某次执行变量

例：

EXEC DBMS_STATS.SET_SCHEMA_PREFS('SH', 'DEGREE','4');

-- 或
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH', DEGREE=>4);

Oracle推荐指定degree参数为DBMS_STATS.AUTO_DEGREE，由Oracle根据对象的大小和并行参数的设置情况来决定统计信息收集的并行度。
例：

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'SH', DEGREE => DBMS_STATS.AUTO_DEGREE);

※注意：
Oracle不能并行收集某些类型的索引，如：cluster indexes, domain indexes, and bitmap join indexes。

并发收集统计信息(CONCURRENT)

并行的统计信息收集仅仅意味着：对某个对象进行统计收集时会采用多个并行Slave进行处理，但是对于多个对象(表、索引或分区)来讲，处理还是串行的。即：处理完一个对象后再去处理下一个对象。

从11.2.0.2 开始，Oracle为了能够使多个对象的统计信息收集也能够同时进行，推出了并发收集统计信息(CONCURRENT)模式，使多进程的环境更加有效率。即：同时启动多个JOB，并发地处理多个对象(表、索引或分区)。

从12.1.0.1 开始，Oracle进一步扩展了并发收集统计信息，使Oracle自动统计收集任务（automatic statistics gather task）也能从中受益使用并发收集统计信息。

并发收集统计信息主要使用了以下的几种技术：

Oracle Scheduler：用于启动多个Job
Advanced Queuing (AQ)：用于控制处理的排序
Resource Manager ：管理使用资源

要启用并发收集统计信息，需要设置以下的参数：

CONCURRENT：启用并发收集统计信息功能
JOB_QUEUE_PROCESSES：最大JOB数
RESOURCE_MANAGER_PLAN：启用Resource Manager有效

并发收集统计信息时，数据库生成的JOB数会根据具体情况来分配，大多数情况下，DBMS_STATS 程序会给每个对象分配一个JOB；但如果对象（表或者分区）的大小太小，为了节省资源，Oracle会合并多个表和分区在一个JOB中执行。

※注意：
为了防止同时处理多个分区表的分区时发生死锁，对于分区表是通过Queue的机制进行处理的。
即：每次只能处理一个分区表，其他的需要在Queue等待，待前一个分区表处理完后再处理下一个。

以下是Oracle白皮书中的一个并发收集统计信息的例图：

通过该例子我们可以看到，在针对并发收集统计信息时会有不同层级的JOB，
对于分区表除了一个协调JOB外还会针对各个分区分配一个JOB。
另外，如同前面所讲的，多个分区表不能同时处理。图中的COSTS表和SALES表的处理会被排序，即：COSTS表的JOB结束后SALES表的处理才会开始。

CONCURRENT参数：

CONCURRENT参数用于控制并发收集统计信息。需要通过DBMS_STATS.SET_GLOBAL_PREFS来进行全局设置。

在11.2.0.2~11.2.0.4的版本上，可以设置的值：

TRUE  ：并发有效
FALSE ：并发无效。

12c的版本上，可以设置以下的值：

'MANUAL'   :只有当手动收集时，并发有效
'AUTOMATIC':只有当自动收集时，并发有效   
'ALL'      : 当手动/自动收集，并发都有效
'OFF'      : 并发无效

并发执行例1（11.2.0.3）：

11.2.0.3环境上的测试：

1.测试数据的准备：

SQL> conn scott/tiger
Connected.
SQL> CREATE TABLE TBL1_NONPART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;
Table created.  
SQL> CREATE TABLE TBL2_NONPART2 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
Table created.  
SQL> CREATE TABLE TBL3_NONPART3 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
Table created.  
SQL> CREATE TABLE TBL4_PART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2))
PARTITION BY LIST (COL1)(
 PARTITION PTBL1 values ('1'),
 PARTITION PTBL2 values ('2'),
 PARTITION PTBL3 values ('3')
) ;  2    3    4    5    6  
Table created.
SQL> begin 
for i in 1..2000000 loop 
    insert into TBL1_NONPART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL2_NONPART2 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL3_NONPART3 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL4_PART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL4_PART1 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL4_PART1 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
commit; 
end loop; 
end; 
/ 
PL/SQL procedure successfully completed.

2.设置相关的参数：

---2.1.使Resource Manager有效（DEFAULT_PLAN）
SQL> conn /as sysdba
Connected.
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
System altered.

---2.2.查看JOB_QUEUE_PROCESSES设置，如果需要可以改变
SQL> SELECT VALUE FROM v$parameter WHERE NAME='job_queue_processes';
VALUE
--------------------------------------------------------------------------------
1000

---2.3.设置CONCURRENT为TRUE  ※
SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
OFF

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

3.执行统计信息收集

SQL> conn /as sysdba
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
PL/SQL procedure successfully completed.

4.监视结果

SQL> select job_name, state, comments
from dba_scheduler_jobs
where job_class like 'CONC%';

JOB_NAME       STATE                                         COMMENTS
-------------- --------------------------------------------- ------------------------
ST$SD1_7       RUNNING                                       "SCOTT"."TBL3_NONPART3".
ST$SD1_6       RUNNING                                       "SCOTT"."TBL2_NONPART2".
ST$SD1_5       RUNNING                                       "SCOTT"."TBL1_NONPART1".
....

并发执行例2（12.1.0.2 ）：

12.1.0.2 环境上的测试：

1.测试数据的准备：

SQL> conn scott/tiger
Connected.
SQL> CREATE TABLE TBL1_NONPART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;
Table created.  
SQL> CREATE TABLE TBL2_NONPART2 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
Table created.  
SQL> CREATE TABLE TBL3_NONPART3 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2)) ;   
Table created.  
SQL> CREATE TABLE TBL4_PART1 (COL1 NUMBER, COL2 NUMBER, COL3 CHAR(10), COL4 VARCHAR(10), COL5 DATE, PRIMARY KEY (COL1,COL2))
PARTITION BY LIST (COL1)(
 PARTITION PTBL1 values ('1'),
 PARTITION PTBL2 values ('2'),
 PARTITION PTBL3 values ('3')
) ;  2    3    4    5    6  
Table created.
SQL> begin 
for i in 1..4000000 loop 
    insert into TBL1_NONPART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL2_NONPART2 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL3_NONPART3 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL4_PART1 values(1,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL4_PART1 values(2,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
    insert into TBL4_PART1 values(3,lpad(i,10,'0'),'1234500001','abcdefghij',sysdate);
commit; 
end loop; 
end; 
/ 
PL/SQL procedure successfully completed.

2.设置相关的参数：

---2.1.使Resource Manager有效（DEFAULT_PLAN）
SQL> conn /as sysdba
Connected.
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
System altered.

---2.2.查看JOB_QUEUE_PROCESSES设置，如果需要可以改变
SQL> SELECT VALUE FROM v$parameter WHERE NAME='job_queue_processes';
VALUE
--------------------------------------------------------------------------------
1000

---2.3.设置CONCURRENT为ALL
SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
OFF

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
ALL

3.执行统计信息收集

SQL> conn /as sysdba
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
PL/SQL procedure successfully completed.

4.监视结果

SQL> SET LINESIZE 200
SQL> COLUMN TARGET FORMAT a25
SQL> COLUMN TARGET_TYPE FORMAT a25
SQL> COLUMN JOB_NAME FORMAT a14
SQL> COLUMN START_TIME FORMAT a40
SQL> SELECT TARGET, TARGET_TYPE, JOB_NAME,
       TO_CHAR(START_TIME, 'dd-mon-yyyy hh24:mi:ss')
FROM   DBA_OPTSTAT_OPERATION_TASKS
WHERE  STATUS = 'IN PROGRESS'
AND    OPID = (SELECT MAX(ID)
               FROM   DBA_OPTSTAT_OPERATIONS
               WHERE  OPERATION = 'gather_schema_stats');
  2    3    4    5    6    7  

TARGET                    TARGET_TYPE               JOB_NAME       TO_CHAR(START_TIME,'D
------------------------- ------------------------- -------------- ---------------------
SCOTT.TBL4_PART1          TABLE (COORDINATOR JOB)   ST$SD42_2      18-jun-2016 14:07:56
SCOTT.TBL4_PART1          TABLE (GLOBAL STATS ONLY) ST$T44_2       18-jun-2016 14:07:57
SCOTT.SYS_C0010346        INDEX                     ST$T44_2       18-jun-2016 14:08:11
SCOTT.TBL1_NONPART1       TABLE                     ST$SD42_1_B7   18-jun-2016 14:07:58
SCOTT.SYS_C0010343        INDEX                     ST$SD42_1_B7   18-jun-2016 14:08:09

并发统计信息收集的监视

可以通过以下的视图，对并发统计信息收集进行监视

DBA_OPTSTAT_OPERATION_TASKS：当前和历史的统计信息收集的执行任务 （12c）
DBA_OPTSTAT_OPERATIONS     ：当前和历史的统计信息收集的执行操作 （12c）
DBA_SCHEDULER_JOBS         ： SCHEDULER JOBS信息

并行 VS 并发

有时候可能对并行和并发统计信息收集的概念有些混淆，下面我们通过一张表来总结对比一下并行和并发统计信息收集：

这里写图片描述

并发和并行执行统计信息收集组合

为了提高效率，可以使并发和并行执行统计信息收集同时有效，这种组合使用对于非常大的表和分区非常有效。
要使组合有效，你需要再设置PARALLEL_ADAPTIVE_MULTI_USER参数为False，以防止自适应导致的并行无效。

例：

ALTER SYSTEM SET PARALLEL_ADAPTIVE_MULTI_USER=false;

关于PARALLEL_ADAPTIVE_MULTI_USER参数，可以参考在线文档：

Database Reference
PARALLEL_ADAPTIVE_MULTI_USER

咨询案例：

在笔者的经验中，曾遇到过以下的几个咨询案例，在这里对调查方法和结果进行一些分享。

SE版本并发统计信息收集是否有效？

虽然并发执行统计信息收集使用的技术中包括Resource Manager，并且Resource Manager是Enterprise Edition版本才能使用的功能，但是Standard Edition内部的一些动作也会用到Resource Manager功能，所以不明确使用Resource Manager的情况下，也能够进行并发执行统计信息收集。
通过上面的例子在Standard Edition运行，也可以验证这个结论。

设定相关内容，并发统计信息收集却无效?

有用户咨询，为什么我的环境中设定了并发执行统计信息收集，查看相关的视图却发现，统计信息收集时并没有并发执行？
这个问题，其实由于进行并发执行时，Oracle内部事实上是有一定临界值设定的。当这些表很小和其他一些环境因素时，Oracle会合并多个表和分区在一个JOB中批量执行，就会产生统计信息收集时没有并发执行的表象。

我们可以通过跟踪dbms_stats可以查看到相关的一些内容。
例：

SQL> conn /as sysdba
SQL> exec dbms_stats.set_global_prefs('TRACE', 4+8+16+128+2048);
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_global_prefs('TRACE', 0);
PL/SQL procedure successfully completed.

查看相关的跟踪文件：

DBMS_STATS: Estimate cost for target:  ownname: "SCOTT" tabname: "TBL2_NONPART2" partname:  subpartname:  type: 1
DBMS_STATS:   --> Estimated cost for 12137 blocks is : 12.137
DBMS_STATS: process_task: cost: 12.137, batching threshold: 50 ★
DBMS_STATS: priority: 6 ownname: "SCOTT" stattab: 
DBMS_STATS: Adding task SCOTT.TBL2_NONPART2 into the current batch.★ Task Cost: 12.137, current batch size: 5, current batch cost: 13.195299, batching_coeff: .24274
DBMS_STATS: @ Adding params into cctx..
DBMS_STATS: Scheduling Manager State (has_more): sofar: 6 sofar_dl: 0 deadlockQueue.count: 0 sofar_ix: 0 ixDependencyQueue.count: 0 concurrent: TRUE

我们可以看到，在DBMS_STATS执行过程中在做一些比较，如果预估值没有满足临界值的话，就会进行批量处理。

※注意：
这个临界值（batching threshold）由多方面因素影响，会根据环境不同而不同。

如何限定仅对一部分表进行并发统计信息收集？

在过去的咨询案件中，确实有些客户希望仅对某Schema的一部分表进行并发统计信息收集。
针对这种需求可以通过DBMS_STATS的“obj_filter_list”参数来实现。

例：

DECLARE
      filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
      obj_lst     DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
  filter_lst.extend(5);
  filter_lst(1).ownname := 'SH';
  filter_lst(1).objname := 'SALES';
  filter_lst(2).ownname := 'SH';
  filter_lst(2).objname := 'COSTS';
  filter_lst(3).ownname := 'SH';
  filter_lst(3).objname := 'SALES2';
  filter_lst(4).ownname := 'SH';
  filter_lst(4).objname := 'COSTS2';
  filter_lst(5).ownname := 'SH';
  filter_lst(5).objname := 'SALES3';
 DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SH',objlist=>obj_lst, obj_filter_list=>filter_lst);
END;
/

关于这点，详细可以参考 Maria Colgan-Oracle写的一个Blog：
Oracle Optimizer Blog
>How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

参考：

https://blog.csdn.net/lukeunique/article/details/51705922

Oracle并行和并发收集统计信息

常用SQL

并行收集统计信息(PARALLEL )

DEGREE参数：

并发收集统计信息(CONCURRENT)

CONCURRENT参数：

并发执行例1（11.2.0.3）：

并发执行例2（12.1.0.2 ）：

并发统计信息收集的监视

并行 VS 并发

并发和并行执行统计信息收集组合

咨询案例：

SE版本并发统计信息收集是否有效？

设定相关内容，并发统计信息收集却无效?

如何限定仅对一部分表进行并发统计信息收集？

参考：

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle并行和并发收集统计信息

常用SQL

并行收集统计信息(PARALLEL )

DEGREE参数：

并发收集统计信息(CONCURRENT)

CONCURRENT参数：

并发执行例1（11.2.0.3）：

并发执行例2（12.1.0.2 ）：

并发统计信息收集的监视

并行 VS 并发

并发和并行执行统计信息收集组合

咨询案例：

SE版本并发统计信息收集是否有效？

设定相关内容，并发统计信息收集却无效?

如何限定仅对一部分表进行并发统计信息收集？

参考：

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像