收集oracle统计信息.docx

1、收集oracle统计信息收集oracle统计信息优化器统计范围：表统计； -行数，块数，行平均长度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；列统计； -列中唯一值的数量（NDV），NULL值的数量，数据分布； -DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；索引统计；-叶块数量，等级，聚簇因子； -DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；系统统计；-I/O性能与使用率； -CPU性能与使用率； -存储在aux_stats$中，需要使用dbms_st

2、ats收集，I/O统计在X$KCFIO中；-analyze-需要使用ANALYZE统计的统计：使用LIST CHAINED ROWS和VALIDATE子句；收集空闲列表块的统计；Analyze table tablename compute statistics;Analyze index|cluster indexname estimate statistics;ANALYZE TABLE tablename COMPUTE STATISTICSFOR TABLEFOR ALL LOCAL INDEXESFOR ALL INDEXED COLUMNS;ANALYZE TABLE tablen

3、ame DELETE STATISTICSANALYZE TABLE tablename VALIDATE REF UPDATEANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE|INTO TableNameANALYZE TABLE tablename LIST CHAINED ROWS INTO TableNameANALYZE 不适合做分区表的分析-dbms_stats-dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。这个包的下面四个存储过程分别收集index

4、、table、schema、database的统计信息:dbms_stats.gather_table_stats 收集表、列和索引的统计信息；dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；dbms_stats.gather_index_stats 收集索引的统计信息；dbms_stats.gather_system_stats 收集系统统计信息dbms_stats.GATHER_DICTIONARY_STATS： 所有字典对象的统计；DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计dbms_s

5、tats.delete_table_stats 删除表的统计信息dbms_stats.delete_index_stats 删除索引的统计信息dbms_stats.export_table_stats 输出表的统计信息dbms_stats.create_state_tabledbms_stats.set_table_stats 设置表的统计dbms_stats.auto_sample_size统计收集的权限=必须授予普通用户权限sysORADB grant execute_catalog_role to hr;sysORADB grant connect,resource,analyze an

6、y to hr;统计收集的时间考虑=当参数STATISTICS_LEVEL设置为TYPICAL或者ALL，系统会在夜间自动收集统计信息。查看系统自动收集统计信息的job：SELECT * FROM dba_scheduler_jobs WHERE job_name = GATHER_STATS_JOB;也可以disable自动收集统计信息：BEGINdbms_scheduler.disable(GATHER_STATS_JOB);END;使用手工统计对所有更改活动中等的对象自动统计应该足够充分，由于自动统计收集在夜间进行，因此对于一些更新频繁的对象其统计可能已经过期。两种典型的对象：高度变化的

7、表在白天的活动期间被TRUNCATE/DROP并重建；块加载超过本身总大小10%的对象；对于第一种对象可以使用以下两种方法：1 将这些表上的统计设置为NULL，当Oracle遇到没有统计的表时，将动态收集必要的统计作为查询优化的一部分；动态收集特征由OPTIMIZER_DYNAMIC_SAMPLING控制，这个参数应该设置为大于等于2，默认为2。可以通过删除并锁住统计将统计设置为NULL：DBMS_STATS.DELETE_TABLE_STATS(SCHEMA,TABLE);DBMS_STATS.LOCK_TABLE_STATS(SCHEMA,TABLE);2 将这些表上的统计设置为代表表典型

8、状态的值。在表具有某个有代表性的值时收集统计，然后锁住统计；由于夜间收集的统计未必适合于白天的负载，因此这些情况下使用手工收集比GATHER_STATS_JOB更有效。对于块加载，统计应该在加载后立刻收集，通常合并在加载语句的后面防止遗忘。对于外部表，统计不能通过GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自动统计收集收集。因此需要使用GATHER_TABLE_STATS在单个表上收集统计，并且在外部表上不支持取样，ESTIMATE_PERCENT应该被显示设置为NULL。如果STATISTICS_LEVEL设置为BASIC禁用了监控特征，自动统计收集

9、将不会检测过期的统计，此时需要手工收集。3 需要手工收集的另一个地方是系统统计，其不会自动收集。对于固定表，如动态性能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，这些表上的统计应该在数据库具有有代表性的活动后收集。统计收集考虑=1 统计收集使用取样不使用抽样的统计收集需要全表扫描并且排序整个表，抽样最小化收集统计的必要资源。Oracle推荐设置DBMS_STATS的ESTIMATE_PERCENT参数为DBMS_STATS.AUTO_SAMPLE_SIZE在达到必要的统计精确性的同时最大化性能。2 并行统计收集Oracle推荐设置DBMS_STATS的DEGREE参数

10、为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。聚簇索引，域索引，位图连接索引不能并行收集。3 分区对象的统计收集对于分区表和索引，DBMS_STATS可以收集单独分区的统计和全局分区，对于组合分区，可以收集子分区，分区，表/索引上的统计，分区统计的收集可以通过声明参数GRANULARITY。根据将优化的SQL语句，优化器可以选择使用分区统计或全局统计，对于大多数系统这两种统计都是很重要的，Oracle推荐将GRANULARITY设置为AUTO同时收集全部信息。4 列统计和直方图当在表上收集统计时，DBMS_STAT

11、S收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方图通常用来作为列统计的一部分。直方图通过METHOD_OPT参数声明，Oracle推荐设置METHOD_OPT为FOR ALL COLUMNS SIZE AUTO，使用该值时Oracle自动决定需要直方图的列以及每个直方图的桶数。也可以手工设置需要直方图的列以及桶数。如果在使用DBMS_STATS的时候需要删除表中的所有行，需要使用TRUNCATE代替drop/create，否则自动统计收集特征使用的负载信息以及RESTORE_*_STATS

12、使用的保存的统计历史将丢失。这些特征将无法正常发挥作用。5 确定过期的统计对于那些随着时间更改的对象必须周期性收集统计，为了确定过期的统计，Oracle提供了一个表监控这些更改，这些监控默认情况下在STATISTICS_LEVEL为TYPICAL/ALL时启用，该表为USER_TAB_MODIFICATIONS。使用DBMS_STATS.FLUSH_DATABASE _MONITORING_INFO可以立刻反映内存中超过监控的信息。在OPTIONS参数设置为GATHER STALE or GATHER AUTO时，DBMS_STATS收集过期统计的对象的统计。6 用户定义统计在创建了基于索引的

13、统计后，应该在表上收集新的列统计，这可以通过调用过程设置METHOD_OPT的FOR ALL HIDDEN COLUMNS。7 何时收集统计对于增量更改的表，可能每个月/每周只需要收集一次，而对于加载后表，通常在加载脚本中增加收集统计的脚本。对于分区表，如果仅仅是一个分区有了较大改动，只需要收集一个分区的统计，但是收集整个表的分区也是必要的。系统统计=系统统计描述系统硬件的特征，包括I/O和CPU。在选择执行计划时，优化器考虑查询所需的CPU和I/O代价。系统统计允许优化器更加精确的评价CPU和IO代价，选择更好的查询计划。使用DBMS_STATS.GATHER_SYSTEM_STATS收集系

14、统统计，Oracle推荐收集系统统计。收集系统统计需要DBA权限。收集的优化器系统统计包括：cpuspeedNW：代表无负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位Millions/sec。ioseektim：I/O查找时间=查找时间+延迟时间+OS负载时间；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为ms。Iotfrspeed：I/O传输速度；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为Bytes/ms.Cpuspeed：代表有

15、负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Millions/sec。Maxthr：最大I/O吞吐量；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Bytes/sec.Slavethr：服务I/O吞吐量是平均并行服务I/O吞吐量；通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；Bytes/sec.Sreadtim：随机读取单块的平均时间；通过设置g

16、athering_mode =INTERVAL，START|STOP或手工设置统计；单位为ms。Mreadtim：顺序读取多块的平均时间，通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为ms。Mbrc: 多块读平均每次读取的块数量；通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为blocks。系统统计的重新收集不会导致当前的SQL无效，只是所有的新SQL语句使用新的统计。Oracle提供两个选项收集统计：负载统计；非负载统计。负载统计=在负载窗口的开始运行dbms_sta

17、ts.gather_system_stats(start)，然后运行dbms_stats.gather_system_stats(stop)结束负载窗口。运行dbms_stats.gather_system_stats(interval, interval=N)，N表示N分钟后系统统计收集结束。运行dbms_stats.delete_system_stats()删除负载统计。非负载统计=运行不带参数的dbms_stats.gather_system_stats()收集非负载统计，运行非负载统计时会有一定的I/O负载。在某些情况下，非负载统计的值可能会保持默认，此时需要使用dbms_stats.

18、set_system_stats设置。管理统计=转储先前版本的统计使用RESTORE过程转储先前版本的统计，这些过程使用一个时间戳作为参数，包含统计时间的视图包括：1 DBA_OPTSTAT_OPERATIONS：其中包含了使用DBMS_STATS在模式/系统级别执行的统计操作；2 *_TAB_STATS_HISTORY：包含了表统计更改的历史。旧的统计定期刷新，根据DBMS_STATS的ALTER_STATS_HISTORY_RETENTION过程设置而定，默认为31天。默认情况下，如果STATISTICS_LEVEL为TYPICAL/ALL，自动刷新启用；否则需要使用PURGE_STAT手

19、工刷新。其他转储与刷新相关的信息包括：PURGE_STATS： 手工刷新超过某个时间戳的旧统计；GET_STATS_HISTORY_RENTENTION： 得到当前历史统计保留值；GET_STATS_HISTORY_AVAILABILTY： 得到可用的最旧的统计的时间戳。转储的限制：1 不能转储用户定义统计；2 如果使用了ANALYZE收集，旧的统计将无法转储。导入/导出统计=导出统计前需要使用DBMS_STATS.CREATE_STAT_TABLE创建一个统计表保留统计，在表创建后可以使用DBMS_STATS.EXPORT_*_STATS导出统计到自定义表，这些统计可以使用DBMS_STAT

20、S.IMPORT_*_STATS重新导入。也可以使用IMP/EXP导到其他数据库。转储统计与导入导出统计使用转储的情况：1 恢复旧版本的统计；2 希望数据库管理统计历史的保留和刷新；使用EXPORT/IMPORT_*_STATS的情况：1 实验各种值的不同情况；2 移动统计到不同数据库；3 保留统计数据更长的时间。锁住表和模式的统计=一旦统计被锁住，将无法在更改这些统计直到被解锁。DBMS_STAT提供两个过程用于解锁，两个用于加锁：1 LOCK_SCHEMA_STATS；LOCK_TABLE_STATS；2 UNLOCK_SCHEMA_STATS；UNLOCK_TABLE_STATS；设置统

21、计=可以使用SET_*_STATISTICS设置表，索引，列，系统统计。使用动态取样评价统计=动态取样的目的是通过为谓词选择性和表/索引统计确定更加精确的估计提高服务器性能，估计越精确产生的性能更好。可以使用动态取样的情况：1 在收集的统计不能使用或会导致严重的估计错误时估计单表的谓词选择性；2 估计没有统计的表/索引的统计；3 估计统计过期的表和索引的统计；动态取样特征由参数OPTIMIZER_DYNAMIC_SAMPLING控制，默认级别为2。动态取样的工作机制主要的性能特征是编译时，Oracle在编译时决定一个查询是否能通过取样获益，如果可以，将用递归SQL随机扫描一小部分表块，然后应用

22、相关的单表谓词评价谓词选择性。使用动态取样的时间使用动态取样将获益的情况：1 可以发现更好的执行计划；2 取样时间仅占总时间的一小部分；3 查询将执行多次；取样级别=范围从1.10缺失统计处理=当Oracle遇到丢失统计时，优化器动态必要的统计。在某些情况下，Oracle无法执行动态取样，包括：远程表/外部表，此时将使用默认统计。缺失统计时的表默认值：1 Cardinality：num_of_blocks * (block_size - cache_layer) / avg_row_len2 Average row length：100字节；3 Number of blocks：100或基于分

23、区映射的实际值；4 Remote cardinality：2000行；5 Remote average row length：100字节；缺失统计时的索引默认值：Levels：1Leaf blocks：25Leaf blocks/key：1Datablocks/key：1Distinct keys：100Clustering factor：800gather_schema_stats=begindbms_stats.gather_schema_stats( wnname = SCOTT, ptions = GATHER AUTO, estimate_percent = dbms_stats.a

24、uto_sample_size, method_opt = for all columns size repeat, degree = 15 );end;options参数使用4个预设的方法：gather重新分析整个架构（Schema）。gather empty只分析目前还没有统计的表。gather stale只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。gather auto重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。类似于组合使用gather stale和gather empty。注意，无论gather stale还是gather auto，都要求进行监视

25、。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = SCOTT;使用alter table xxx monitoring命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创

26、建直方图。使用method_opt=auto类似于在dbms_stats的option参数中使用gather auto。begindbms_stats.gather_schema_stats(ownname = SCOTT, estimate_percent = dbms_stats.auto_sample_size, method_opt = for all columns size auto, degree = 7);end;estimate_percent选项以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采

27、样的一个segment的最佳百分比：estimate_percent = dbms_stats.auto_sample_size要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。method_opt选项dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还

28、是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：method_opt=for all columns size skewonlymethod_opt=for all columns size repeatmethod_opt=for all columns size autoskewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，那么为了检索这些行，全表扫描的速度会快于索引扫描。-*- SKEWO