收集oracle统计信息.docx
《收集oracle统计信息.docx》由会员分享，可在线阅读，更多相关《收集oracle统计信息.docx（24页珍藏版）》请在冰豆网上搜索。
收集oracle统计信息
dbms_stats使用
Dbms_stats是oracle8i新增的程序包，它使统计数据的生成和处理更加方便。
--参数
estimate_percent --估算抽样百分比
method_optfortable --只统计表
forallindexedcolumns--只统计有索引的表列
forallindexes --只分析统计相关索引
--创建统计信息历史保留表
sql>execdbms_stats.create_stat_table（ownname=>'scott',stattab=>'stat_table'）;
pl/sqlproceduresuccessfullycompleted
--导出整个scheme的统计信息
sql>execdbms_stats.export_schema_stats（ownname=>'scott',stattab=>'stat_table'）;
pl/sqlproceduresuccessfullycompleted
--分析scheme
Execdbms_stats.gather_schema_stats（
ownname=>'scott',
options=>'GATHERAUTO',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'forallindexedcolumns',
degree=>6）
--分析表
sql>execdbms_stats.gather_table_stats（ownname=>'scott',tabname=>'work_list',estimate_percent=>10,method_opt=>'forallindexedcolumns'）;
pl/sqlproceduresuccessfullycompleted
--分析索引
SQL>execdbms_stats.gather_index_stats（ownname=>'crm2',indname=>'IDX_ADM_PERMISSION_PID_MID',estimate_percent=>'10',degree=>'4'）;
pl/sqlproceduresuccessfullycompleted
--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats（ownname=>'scott',tabname=>'work_list'）;
pl/sqlproceduresuccessfullycompleted
--导入表的历史统计信息
sql>execdbms_stats.import_table_stats（ownname=>'scott',tabname=>'work_list',stattab=>'stat_table'）;
pl/sqlproceduresuccessfullycompleted
--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql>execdbms_stats.import_schema_stats（ownname=>'scott',stattab=>'stat_table'）;
pl/sqlproceduresuccessfullycompleted
--导入索引的统计信息
SQL>execdbms_stats.import_index_stats（ownname=>'crm2',indname=>'IDX_ADM_PERMISSION_PID_MID',stattab=>'stat_table'）
--检查是否导入成功
SQL>selecttable_name,num_rows,a.blocks,a.last_analyzedfromall_tablesawherea.table_name='WORK_LIST';
TABLE_NAMENUM_ROWSBLOCKSLAST_ANALYZED
---------------------------------------------------------------
dbms_stats简介
----------------------
dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。
这个包的下面四个存储过程分别收集index、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_stats.delete_table_stats 删除表的统计信息
dbms_stats.delete_index_stats 删除索引的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置表的统计
dbms_stats.auto_sample_size
统计收集的权限
==========================
必须授予普通用户权限
sys@ORADB>grantexecute_catalog_roletohr;
sys@ORADB>grantconnect,resource,analyzeanytohr;
统计收集的时间考虑
==========================
当参数STATISTICS_LEVEL设置为TYPICAL或者ALL，系统会在夜间自动收集统计信息。
查看系统自动收集统计信息的job：
SELECT*FROMdba_scheduler_jobsWHEREjob_name='GATHER_STATS_JOB';
也可以disable自动收集统计信息：
BEGIN
dbms_scheduler.disable（'GATHER_STATS_JOB'）;
END;
使用手工统计
对所有更改活动中等的对象自动统计应该足够充分，由于自动统计收集在夜间进行，因此对于一些更新频繁的对象其统计可能已经过期。
两种典型的对象：
1、高度变化的表在白天的活动期间被TRUNCATE/DROP并重建；
2、块加载超过本身总大小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将这些表上的统计设置为代表表典型状态的值。
在表具有某个有代表性的值时收集统计，然后锁住统计；
由于夜间收集的统计未必适合于白天的负载，因此这些情况下使用手工收集比GATHER_STATS_JOB更有效。
对于第二种情况块加载，统计应该在加载后立刻收集，通常合并在加载语句的后面防止遗忘。
对于外部表，统计不能通过GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自动统计收集收集。
因此需要使用GATHER_TABLE_STATS在单个表上收集统计，并且在外部表上不支持取样，ESTIMATE_PERCENT应该被显示设置为NULL。
如果STATISTICS_LEVEL设置为BASIC禁用了监控特征，自动统计收集将不会检测过期的统计，此时需要手工收集。
3需要手工收集的另一个地方是系统统计，其不会自动收集。
对于固定表，如动态性能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，这些表上的统计应该在数据库具有有代表性的活动后收集。
统计收集考虑
===================
1统计收集使用取样
不使用抽样的统计收集需要全表扫描并且排序整个表，抽样最小化收集统计的必要资源。
Oracle推荐设置DBMS_STATS的ESTIMATE_PERCENT参数为DBMS_STATS.AUTO_SAMPLE_SIZE在达到必要的统计精确性的同时最大化性能。
2并行统计收集
Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。
聚簇索引，域索引，位图连接索引不能并行收集。
3分区对象的统计收集、对于分区表和索引，DBMS_STATS可以收集单独分区的统计和全局分区，对于组合分区，可以收集子分区，分区，表/索引上的统计，分区统计的收集可以通过声明参数GRANULARITY。
根据将优化的SQL语句，优化器可以选择使用分区统计或全局统计，对于大多数系统这两种统计都是很重要的，Oracle推荐将GRANULARITY设置为AUTO同时收集全部信息。
4列统计和直方图
当在表上收集统计时，DBMS_STATS收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方图通常用来作为列统计的一部分。
直方图通过METHOD_OPT参数声明，Oracle推荐设置METHOD_OPT为FORALLCOLUMNSSIZEAUTO，使用该值时Oracle自动决定需要直方图的列以及每个直方图的桶数。
也可以手工设置需要直方图的列以及桶数。
如果在使用DBMS_STATS的时候需要删除表中的所有行，需要使用TRUNCATE代替drop/create，否则自动统计收集特征使用的负载信息以及RESTORE_*_STATS使用的保存的统计历史将丢失。
这些特征将无法正常发挥作用。
5确定过期的统计
对于那些随着时间更改的对象必须周期性收集统计，为了确定过期的统计，Oracle提供了一个表监控这些更改，这些监控默认情况下在STATISTICS_LEVEL为TYPICAL/ALL时启用，该表为USER_TAB_MODIFICATIONS。
使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以立刻反映内存中超过监控的信息。
在OPTIONS参数设置为GATHERSTALEorGATHERAUTO时，DBMS_STATS收集过期统计的对象的统计。
6用户定义统计
在创建了基于索引的统计后，应该在表上收集新的列统计，这可以通过调用过程设置METHOD_OPT的FORALLHIDDENCOLUMNS。
7何时收集统计
对于增量更改的表，可能每个月/每周只需要收集一次，而对于加载后表，通常在加载脚本中增加收集统计的脚本。
对于分区表，如果仅仅是一个分区有了较大改动，只需要收集一个分区的统计，但是收集整个表的分区也是必要的。
系统统计
==========================
系统统计描述系统硬件的特征，包括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过程转储先前版本的统计，这些过程使用一个时间戳作为参数，包含统计时间的视图包括：
1DBA_OPTSTAT_OPERATIONS：
其中包含了使用DBMS_STATS在模式/系统级别执行的统计操作；
2*_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：
得到可用的最旧的统计的时间戳。
转储的限制：
1不能转储用户定义统计；
2如果使用了ANALYZE收集，旧的统计将无法转储。
导入/导出统计
==========================
导出统计前需要使用DBMS_STATS.CREATE_STAT_TABLE创建一个统计表保留统计，在表创建后可以使用DBMS_STATS.EXPORT_*_STATS导出统计到自定义表，这些统计可以使用DBMS_STATS.IMPORT_*_STATS重新导入。
也可以使用IMP/EXP导到其他数据库。
转储统计与导入导出统计
使用转储的情况：
1恢复旧版本的统计；
2希望数据库管理统计历史的保留和刷新；
使用EXPORT/IMPORT_*_STATS的情况：
1实验各种值的不同情况；
2移动统计到不同数据库；
3保留统计数据更长的时间。
锁住表和模式的统计
==========================
一旦统计被锁住，将无法在更改这些统计直到被解锁。
DBMS_STAT提供两个过程用于解锁，两个用于加锁：
1LOCK_SCHEMA_STATS；¡¤LOCK_TABLE_STATS；
2UNLOCK_SCHEMA_STATS；¡¤UNLOCK_TABLE_STATS；
设置统计
==========================
可以使用SET_*_STATISTICS设置表，索引，列，系统统计。
使用动态取样评价统计
==========================
动态取样的目的是通过为谓词选择性和表/索引统计确定更加精确的估计提高服务器性能，估计越精确产生的性能更好。
可以使用动态取样的情况：
1在收集的统计不能使用或会导致严重的估计错误时估计单表的谓词选择性；
2估计没有统计的表/索引的统计；
3估计统计过期的表和索引的统计；
动态取样特征由参数OPTIMIZER_DYNAMIC_SAMPLING控制，默认级别为2。
动态取样的工作机制
主要的性能特征是编译时，Oracle在编译时决定一个查询是否能通过取样获益，如果可以，将用递归SQL随机扫描一小部分表块，然后应用相关的单表谓词评价谓词选择性。
使用动态取样的时间
使用动态取样将获益的情况：
1可以发现更好的执行计划；
2取样时间仅占总时间的一小部分；
3查询将执行多次；
取样级别
==========================
范围从1..10
缺失统计处理
==========================
当Oracle遇到丢失统计时，优化器动态必要的统计。
在某些情况下，Oracle无法执行动态取样，包括：
远程表/外部表，此时将使用默认统计。
缺失统计时的表默认值：
1Cardinality：
num_of_blocks*（block_size-cache_layer）/avg_row_len
2Averagerowlength：
100字节；
3Numberofblocks：
100或基于分区映射的实际值；
4Remotecardinality：
2000行；
5Remoteaveragerowlength：
100字节；
缺失统计时的索引默认值：
Levels：
1
Leafblocks：
25
Leafblocks/key：
1
Datablocks/key：
1
Distinctkeys：
100
Clusteringfactor：
800
gather_schema_stats语法
==========================
begin
dbms_stats.gather_schema_stats（wnname=>'SCOTT',
ptions=>'GATHERAUTO',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'forallcolumnssizerepeat',
degree=>15）;
end;
options参数使用4个预设的方法：
gather——重新分析整个架构（Schema）。
gatherempty——只分析目前还没有统计的表。
gatherstale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
gatherauto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。
类似于组合使用gatherstale和gatherempty。
注意，无论gatherstale还是gatherauto，都要求进行监视。
如果你执行一个altertablexxxmonitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。
这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
SELECT*FROMSys.Dba_Tab_ModificationsWHERETable_Owner='SCOTT';
使用altertablexxxmonitoring命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。
auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）
来创建直方图。
使用method_opt=>’auto’类似于在dbms_stats的option参数中使用gatherauto。
begin
dbms_stats.gather_schema_stats（ownname=>'SCOTT',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'forallcolumnssizeauto',
degree=>7）;
end;
estimate_percent选项
以下estimate_percent参数是一种比较新的设计
升级会员 