第十三章

第十三章  managing optimizer statistics

一、优化统计量
查询优化器使用优化统计量来获取最好的执行计划。优化统计量包括：
表统计信息：行数、块数、平均行长度
列统计信息：distinct值数量（NDV）、空值数量、数据的分布情况（柱状图histogram）、扩展信息
索引信息：叶子块数量、层数、簇因子（clustering factor）
系统信息：i/o性能和利用率、cpu性能和利用率

这些信息都存储在数据字典中，随着数据库的更新，统计信息也定时更新。
对于统计信息，我们可以将其导入或导出、锁定以防止改变。

二、自动统计信息收集的管理
oracle推荐打开automatic optimizer statistics collection功能，以便在没有或统计信息过久时自动收集。
自动统计信息收集减少了人为操作，避免了由于统计信息过久而生成错误执行计划的风险。

其原理是调用了dbms_stats.gather_database_stats_job_proc过程。其功能类似于dbms_stats.gather_database_stats的gather auto选项。

1.自动统计信息收集的开启关闭
开启：
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

关闭：
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

自动统计信息收集还依赖于修改监控特性（modification monitoring feature），当statistics_level设置为typical（默认）或all时才会收集。

2.统计信息收集需要考虑的问题

（1）什么时候手工收集：
在白天经常变化的表被删除或truncate、重建
进行大数据量导入的对应，大小增加了10%或更大

对于变化非常频繁的表，有两个方式：
1）统计信息可以为空。在oracle使用这个表时自动收集统计信息。  optimizer_dynamic_sampling参数控制着这种动态采样的特性。设置为2或更大。
可以通过将统计信息清空后锁定的方式实现：
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');
DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
END;
/
2）统计信息被设定为表的最典型状态，在表的数据量比较典型时收集统计信息并锁定。

对于批量导入的表，可以在导入后立即用存储过程收集统计信息，或者直接将这样的存储过程放入导入脚本中

收集统计信息的过程包括：
gather_table_stats
gather_schema_stats
gather_database_stats
自动统计信息收集的过程

表的信息收集可以指定estimate_percent为null、100或自动，但不支持增量收集。

如果监控特性statistics_level设置为basic，自动统计信息收集不会监测到统计信息过期。必须手工收集了。

系统的统计信息是必须手动收集的，数据库不会自动收集。

动态性能表等fixed objects必须手动收集统计信息，使用gather_fixed_objects_stats。fixed objects记录着当前的数据库活动。
Fixed objects record current database activity. You should gather statistics when the database has representative activity.

（2）原版本统计信息的存储
为了将原来的统计信息存起来备用，可以使用dbms_stats的restore过程。

（3）统计信息锁定

三、统计信息手动收集

1.用dbms_stats存储过程
该包不仅可以收集统计信息，还可以修改、查看、导出、导入或删除统计信息。
不要使用analyze语句的compute或estimate方法，这些只是为了向后兼容，未来版本中可能删除。

dbms_stats包可以为表、索引、单独的行、表分区收集统计信息，而不能为簇收集。
但可以使用dbms_stats为簇中的单个表收集统计信息。

收集后新的统计信息被更新到数据字典中，旧数据存起来备查。

可以使用dbms_stats.gather_dictionary_stats为系统用户收集信息。包括sys、system、ctxsys、drsys等。

统计信息更新后，当前访问对象的parsed sql statement置为不合法状态。在这条语句再次执行是重新编译并根据新的统计信息选择执行计划。
访问远程数据库上带有最新统计信息的分布式语句是合法的，最新的统计信息在sql语句下次编译时才生效。

统计信息收集需要考虑的问题有：
（1）使用的样本
通过给dbms_stats过程指定estimate_percent可以指定收集的百分比。
oracle推荐使用dbms_stats.auto_sample_size在保证统计信息准确的前提下提高效率。
如以自动采样比收集OE用户的所有统计信息：EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

如果指定的收集比率太小，oracle会自动增大该比例，以减少评估量的波动。

（2）并行统计量收集
可以通过degree参数告诉oracle并行度。推荐采用dbms_stats.auto_degree
需要注意的是，簇索引、域索引、位图连接索引不能指定并行度（Note that certain types of index statistics are not gathered in parallel, including cluster indexes, domain indexes, and bitmap join indexes.）

（3）分区对象的统计信息
对于分区表和分区索引，dbms_stats为每个分区收集统计信息，为复合分区对每个子分区进行信息收集。

（4）统计信息收集的粒度
根据待调优的语句，优化器选择使用分区、子分区或全局的统计信息。对于大部分应用来说，全局的和分区的统计信息都很重要。

可以使用dbms_stats的granularity参数指定粒度，推荐将此参数设为自动

统计信息增量收集
对于分区表，如果新建了分区并插入数据，可以只收集新分区的统计信息。
使用incremental可以决定数据库是否对整个表进行扫描来更新分区表的全局统计信息。
使用dbms_stats.set_table_pref过程可以改变incremental值。
该值为false（默认）是，数据库总是扫描全表来获得统计信息。对于达标来说这种方式是很耗资源和时间的。
满足下列条件时，只收集被改变的分区的统计信息：
incremental=true
publish=true
estimate_percent=auto_sample_size、granularity=auto

（5）列统计信息和柱状图
列信息最基本的是最大值和最小值，但如果数据严重倾斜，需要柱状图来具体描述。
可以使用dbms_stats的method_opt指定收集柱状图信息。推荐设置为method_opt=>for all columns size auto
这样会自动判断是否需要。

如果要删除表的所有数据，可以truncate而不是删除重建。
当表被删除后，自动柱状图收集特性使用的负载信息和restore_*_stats过程保存的历史统计信息将丢失，没有这些数据，对应的特性就失效了。

（6）扩展统计信息
可以为表中的一组列或列的一个表达式收集统计信息

（7）multicolumn statistics

（8）expression statistics
如果表达式与列相关，可以通过为表达式收集统计信息来获取准确信息：
exec dbms_stats.gather_table_stats('sh','customers', method_opt =>
'for all columns size skewonly
for columns (lower(cust_state_province)) size skewonly');

（9）判断统计信息是否过旧
当statistics_level为typical或all时，可以查看oracle提供的监测统计信息是否过旧的设置。
在user_tab_modifications视图。

（10）用户自定义的统计信息

2.合理进行手工数据收集参数
可以通过dbms_stats.set_*_prefs设置dbms_stats过程收集统计信息的默认参数。可以为每个表、用户、数据库或全局设置参数。

可以改变的参数包括：
AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)
■CASCADE
■DEGREE
■ESTIMATE_PERCENT
■GRANULARITY
■INCREMENTAL
■METHOD_OPT
■NO_INVALIDATE
■PUBLISH
■STALE_PERCENT

3.什么时候收集统计信息
对于增量变化的应用表，可以通过脚本，创建job定时执行用户或数据库的统计信息收集。
对于批量加载的表，统计信息收集作为脚本的一部分。
对于只有单个分区变化的情况，可能只需要收集分区的统计信息。

4.用dbms_stats函数比较统计信息
比较两个数据的统计信息：
diff_table_stats_in_pending 未来的统计信息和某个时间点或数据字典中的统计信息比较
diff_table_stats_in_stattab 两个来源的表信息比较
diff_table_stats_in_history 两个时间点的信息比较

还可以对索引、列、分区进行类似比较。

四、系统的统计信息
描述了系统的硬件特性，包括查询优化器上i/o、cpu性能和使用率。
操作系统的统计信息使优化器更准确地获得i/o、CPU的评估，获得较好的执行计划。

oracle收集系统统计信息时，是分析过去一段时间的系统活动或者模拟一个负载。
统计信息使用dbms_stats.gather_system_stats过程收集。
收集系统统计信息需要dba权限

oracle提供了两种收集方式

1.workload statistics
These options better facilitate the gathering process to the physical database and workload: when workload system statistics are gathered, noworkload system statistics are ignored. Noworkload system statistics are initialized to default values at the first database startup.

包括内容为：
单块和多块读的时间
mbrc
cpu速度
最大系统吞吐量
平均吞吐量

方法：
在负载开始前执行dbms_stats.gather_system_stats('start')
结束时执行dbms_stats.gather_system_stats('stop')
dbms_stats.gather_system_stats('interval',interval=>N)指定N分钟后停止
dbms_stats.delete_system_Stats()删除统计信息

2.noworkload statistics
包括i/o 转换速度、i/o 查询时间、cpu速度
主要区别在于收集的方法。
Noworkload statistics gather data by submitting random reads against all data files, while workload statistics uses counters updated when database activity occurs.

收集方法：DBMS_STATS.GATHER_SYSTEM_STATS()

五、统计信息的管理

待续……

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-751476/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-751476/