原 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）也能从中受益使用并发收集统计信息。
