原 Oracle并行和并发收集统计信息（含常用SQL）
Tags： Oracle原创统计信息小麦苗常用并行收集统计信息并发
常用SQL
官网：https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | -- 常用 -- alter system set job_queue_processes=1000; EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(16); EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(); EXEC DBMS_STATS.gather_database_stats(degree=>16); EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES',cascade => TRUE, DEGREE=>16,no_invalidate => false); -- 针对分区表的单个分区进行收集统计信息 DBMS_STATS.GATHER_TABLE_STATS(USER,'TB_NAME',PARTNAME=>'PT_PART_NAME',GRANULARITY=>'PARTITION',CASCADE=>TRUE); -- 只收集数据变动的分区 EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TABLE_NAME','INCREMENTAL','TRUE'); -- 查看分区表INCREMENTAL的值 SELECT DBMS_STATS.GET_PREFS('INCREMENTAL',NULL,'TABLE_NAME') FROM DUAL; -- 收集数据库信息 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',cascade => TRUE, DEGREE=>24,no_invalidate => false); 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 2 | 1.通过DBMS_STATS.SET_*_PREFS包设置全局变量 2.通过DBMS_STATS.GATHER_*_STATS包设置某次执行变量 |
例：
1 2 3 4 | 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根据对象的大小和并行参数的设置情况来决定统计信息收集的并行度。
例：
1 | 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）也能从中受益使用并发收集统计信息。
并发收集统计信息主要使用了以下的几种技术：
1 2 3 | Oracle Scheduler：用于启动多个Job Advanced Queuing (AQ)：用于控制处理的排序 Resource Manager ：管理使用资源 |
要启用并发收集统计信息，需要设置以下的参数：
1 2 3 | 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的版本上，可以设置的值：
1 2 | TRUE ：并发有效 FALSE ：并发无效。 |
12c的版本上，可以设置以下的值：
