新博客http://www.cnblogs.com/zhjh256 欢迎访问
分类： Oracle
2008-04-18 15:01:00
统计
优化器统计范围：
·表统计；
--行数，块数，行平均长度；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_stats收集，I/O统计在X$KCFIO中；
以上统计为优化器统计，其创建的目的用于查询优化并且存储在DD中，这些统计不同于动态性能视图V$。
自动统计收集
GATHER_STATS_JOB
该过程自动收集数据库中所有以下条件之一的对象的统计：1. 缺少统计；2. 统计过期；
该job由数据库在数据库创建时自动创建，并由调度器管理，调度器在数据库维护窗口打开时运行该job，默认情况下为10:00pm到06:00pm以及周末。
stop_on_window_close属性控制维护窗口关闭时GATHER_STATS_JOB是否继续，默认为true，即调度器在维护窗口关闭时终止GATHER_STATS_JOB。
GATHER_STATS_JOB通过调用DBMS_STATS.GATHER_DATABASE_STATS_JOB _PROC收集优化器统计，GATHER_DATABASE_STATS_JOB_PROC在对象先前没有统计或统计过期（底层对象改变超过10%）时收集数据库对象的统计。该过程是一个内部过程，不过其操作类似于使用GATHER AUTO选项的DBMS_STATS.GATHER_DATABASE _STATS；主要的不同是该过程使用优先级，因此那些最需要更新统计的对象将被先处理。
启用自动统计收集
默认情况下，自动统计收集在数据库创建时被启动，以及在数据库升级过来时启用，可以通过DBA_SCHEDULER_JOBS视图验证：
SELECT * FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';
如果要禁用，最直接的方法是使用如下过程：
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
自动统计收集依赖于更改监控特征，如果禁用，自动统计收集不会检测过期的统计，当设置STATISTICS_LEVEL=TYPICAL/ALL时该特征启用，默认为TYPICAL。
统计收集的时间考虑
使用手工统计
对所有更改活动中等的对象自动统计应该足够充分，由于自动统计收集在夜间进行，因此对于一些更新频繁的对象其统计可能已经过期。两种典型的对象：
·高度变化的表在白天的活动期间被TRUNCATE/DROP并重建；
·块加载超过本身总大小10%的对象；
对于第一种对象可以使用以下两种方法：
·将这些表上的统计设置为NULL，当Oracle遇到没有统计的表时，将动态收集必要的统计作为查询优化的一部分；动态收集特征由OPTIMIZER_DYNAMIC_SAMPLING控制，这个参数应该设置为大于等于2，默认为2。可以通过删除并锁住统计将统计设置为NULL：
DBMS_STATS.DELETE_TABLE_STATS('SCHEMA','TABLE');
DBMS_STATS.LOCK_TABLE_STATS('SCHEMA','TABLE');
·将这些表上的统计设置为代表表典型状态的值。在表具有某个有代表性的值时收集统计，然后锁住统计；
由于夜间收集的统计未必适合于白天的负载，因此这些情况下使用手工收集比GATHER_STATS_JOB更有效。
对于块加载，统计应该在加载后立刻收集，通常合并在加载语句的后面防止遗忘。
对于外部表，统计不能通过GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自动统计收集收集。因此需要使用GATHER_TABLE_STATS在单个表上收集统计，并且在外部表上不支持取样，ESTIMATE_PERCENT应该被显示设置为NULL。
如果STATISTICS_LEVEL设置为BASIC禁用了监控特征，自动统计收集将不会检测过期的统计，此时需要手工收集。
需要手工收集的另一个地方是系统统计，其不会自动收集。
对于固定表，如动态性能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，这些表上的统计应该在数据库具有有代表性的活动后收集。
手工收集统计
使用DBMS_STATS过程收集统计
该包用来更改，查看，导入，导出，删除统计。
不要使用ANALYZE的COMPUTE/ESTIMATE子句收集优化器统计，这些子句仅为了向后兼容。DBMS_STATS收集更加广泛，精确的统计，并且收集更加有效。
需要使用ANALYZE统计的统计：
·使用LIST CHAINED ROWS和VALIDATE子句；
·收集空闲列表块的统计；
DBMS_STATS可以收集表，索引，列，分区的统计，但不收集聚簇统计（需要在各个表上收集替代整个聚簇）。
在收集表，索引，列上的统计时，如果已存在统计，这些统计将被更新并保存，并且可以在必要的时候转储。
收集系统模式的统计时，可以使用DBMS_STATS.GATHER_DICTIONARY_STATS，其收集所有系统模式的统计。
统计更新后，所有访问这些对象的语句都将无效。
收集数据库对象统计的过程：
GATHER_INDEX_STATS：索引统计；
GATHER_TABLE_STATS：表，列，索引统计；
GATHER_SCHEMA_STATS：模式中所有对象的统计；
GATHER_DICTIONARY_STATS：所有字典对象的统计；
GATHER_DATABASE_STATS：所有对象的统计；
统计收集考虑
·统计收集使用取样
不使用抽样的统计收集需要全表扫描并且排序整个表，抽样最小化收集统计的必要资源。
抽样通过DBMS_STATS的ESTIMATE_PERCENT参数设置，虽然该值可以设置为任何值，Oracle推荐设置DBMS_STATS的ESTIMATE_PERCENT参数为DBMS_STATS.AUTO_SAMPLE_SIZE在达到必要的统计精确性的同时最大化性能。
·并行统计收集
并行度可以通过DBMS_STATS的DEGREE参数设置，并行统计收集可以与抽样一起使用，Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。
并且，聚簇索引，域索引，位图连接索引不能并行收集。
·分区对象的统计收集
对于分区表和索引，DBMS_STATS可以收集单独分区的统计和全局分区，对于组合分区，可以收集子分区，分区，表/索引上的统计，分区统计的收集可以通过声明参数GRANULARITY。根据将优化的SQL语句，优化器可以选择使用分区统计或全局统计，对于大多数系统这两种统计都是很重要的，Oracle推荐将GRANULARITY设置为AUTO同时收集全部信息。
·列统计和直方图
当在表上收集统计时，DBMS_STATS收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方图通常用来作为列统计的一部分。
直方图通过METHOD_OPT参数声明，Oracle推荐设置METHOD_OPT为FOR ALL COLUMNS SIZE AUTO，使用该值时Oracle自动决定需要直方图的列以及每个直方图的桶数。也可以手工设置需要直方图的列以及桶数。
如果在使用DBMS_STATS的时候需要删除表中的所有行，需要使用TRUNCATE代替drop/create，否则自动统计收集特征使用的负载信息以及RESTORE_*_STATS使用的保存的统计历史将丢失。这些特征将无法正常发挥作用。
·确定过期的统计
对于那些随着时间更改的对象必须周期性收集统计，为了确定过期的统计，Oracle提供了一个表监控这些更改，这些监控默认情况下在STATISTICS_LEVEL为TYPICAL/ALL时启用，该表为USER_TAB_MODIFICATIONS。使用DBMS_STATS.FLUSH_DATABASE _MONITORING_INFO可以立刻反映内存中超过监控的信息。在OPTIONS参数设置为GATHER STALE or GATHER AUTO时，DBMS_STATS收集过期统计的对象的统计。
·用户定义统计
在创建了基于索引的统计后，应该在表上收集新的列统计，这可以通过调用过程设置METHOD_OPT的FOR ALL HIDDEN COLUMNS。
何时收集统计
对于增量更改的表，可能每个月/每周只需要收集一次，而对于皮加载后表，通常在加载脚本中增加收集统计的脚本。对于分区表，如果仅仅是一个分区有了较大改动，只需要收集一个分区的统计，但是收集整个表的分区也是必要的。
系统统计
系统统计描述系统硬件的特征，包括I/O和CPU。在选择执行计划时，优化器考虑查询所需的CPU和I/O代价。系统统计允许优化器更加精确的评价CPU和IO代价，选择更好的查询计划。
使用DBMS_STATS.GATHER_SYSTEM_STATS收集系统统计，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：代表有负载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：随机读取单块的平均时间；通过设置gathering_mode =INTERVAL，START|STOP或手工设置统计；单位为ms。
Mreadtim：顺序读取多块的平均时间，通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为ms。
Mbrc: 多块读平均每次读取的块数量；通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为blocks。
系统统计的重新收集不会导致当前的SQL无效，只是所有的新SQL语句使用新的统计。
Oracle提供两个选项收集统计：负载统计；非负载统计。
负载统计
在负载窗口的开始运行dbms_stats.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.set_system_stats设置。
管理统计
转储先前版本的统计
使用RESTORE过程转储先前版本的统计，这些过程使用一个时间戳作为参数，包含统计时间的视图包括：
·DBA_OPTSTAT_OPERATIONS：其中包含了使用DBMS_STATS在模式/系统级别执行的统计操作；
·*_TAB_STATS_HISTORY：包含了表统计更改的历史。
旧的统计定期刷新，根据DBMS_STATS的ALTER_STATS_HISTORY_RETENTION过程设置而定，默认为31天。
默认情况下，如果STATISTICS_LEVEL为TYPICAL/ALL，自动刷新启用；否则需要使用PURGE_STAT手工刷新。
其他转储与刷新相关的信息包括：
PURGE_STATS：手工刷新超过某个时间戳的旧统计；
GET_STATS_HISTORY_RENTENTION：得到当前历史统计保留值；
GET_STATS_HISTORY_AVAILABILTY：得到可用的最旧的统计的时间戳。
转储的限制：
·不能转储用户定义统计；
·如果使用了ANALYZE收集，旧的统计将无法转储。
导入/导出统计
导出统计前需要使用DBMS_STATS.CREATE_STAT_TABLE创建一个统计表保留统计，在表创建后可以使用DBMS_STATS.EXPORT_*_STATS导出统计到自定义表，这些统计可以使用DBMS_STATS.IMPORT_*_STATS重新导入。
也可以使用IMP/EXP导到其他数据库。
转储统计与导入导出统计
使用转储的情况：
·恢复旧版本的统计；
·希望数据库管理统计历史的保留和刷新；
使用EXPORT/IMPORT_*_STATS的情况：
·实验各种值的不同情况；
·移动统计到不同数据库；
·保留统计数据更长的时间。
锁住表和模式的统计
一旦统计被锁住，将无法在更改这些统计直到被解锁。DBMS_STAT提供两个过程用于解锁，两个用于加锁：
·LOCK_SCHEMA_STATS；·LOCK_TABLE_STATS；
·UNLOCK_SCHEMA_STATS；·UNLOCK_TABLE_STATS；
设置统计
可以使用SET_*_STATISTICS设置表，索引，列，系统统计。
使用动态取样评价统计
动态取样的目的是通过为谓词选择性和表/索引统计确定更加精确的估计提高服务器性能，估计越精确产生的性能更好。
可以使用动态取样的情况：
·在收集的统计不能使用或会导致严重的估计错误时估计单表的谓词选择性；
·估计没有统计的表/索引的统计；
·估计统计过期的表和索引的统计；
动态取样特征由参数OPTIMIZER_DYNAMIC_SAMPLING控制，默认级别为2。
动态取样的工作机制
主要的性能特征是编译时，Oracle在编译时决定一个查询是否能通过取样获益，如果可以，将用递归SQL随机扫描一小部分表块，然后应用相关的单表谓词评价谓词选择性。
使用动态取样的时间
使用动态取样将获益的情况：
·可以发现更好的执行计划；
·取样时间仅占总时间的一小部分；
·查询将执行多次；
取样级别
范围从1..10。
缺失统计处理
当Oracle遇到丢失统计时，优化器动态必要的统计。在某些情况下，Oracle无法执行动态取样，包括：远程表/外部表，此时将使用默认统计。
缺失统计时的表默认值：
·Cardinality：num_of_blocks * (block_size - cache_layer) / avg_row_len
·Average row length：100字节；
·Number of blocks：100或基于分区映射的实际值；
·Remote cardinality：2000行；
·Remote average row length：100字节；
缺失统计时的索引默认值：
Levels：1
Leaf blocks：25
Leaf blocks/key：1
Data blocks/key：1
Distinct keys：100
Clustering factor：800
查看统计
表/索引/列上的统计
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
直方图统计
直方图的类型存储在*TAB_COL_STATISTICS视图的HISTOGRAM列上。
