建议在11g上收集优化器统计
适用于:
Oracle服务器 - 企业版 - 版本:11.1.0.6到11.1.0.7 - 发行:11.1至11.1
本文档中信息适用于任何平台.
目标
本文件概述了推荐的方法使用Oracle 11g的收集成本的优化器的优化器统计的一套标准.
解决方案
快速重创建建议
实现了快速删除并重新对单个表的统计和它的索引(添加任何倾斜列列的统计信息),在这篇文章中使用的建议后:
exec dbms_stats.delete_table_stats(ownname=>'user_name',-
tabname=>'table_name',cascade_indexes=>true);
exec dbms_stats.gather_table_stats(ownname=>'user_name',
tabname= >'table_name',-
estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE,-
cascade= >true,-
method_opt= >'for all columns size AUTO');
对于这些建议的说明,见下文。如需使用范例,请参阅本文结尾.
请注意,从10gR2的统计数据可以恢复使用:
Note:452011.1 *从10G起恢复表的统计信息
重点: 请注意:
这些建议适用于大多数的数据库.
这些建议旨在生成统计尽可能地统计的准确性。为此,100%的样本大小样本量的减少,是因为任何总是让步的准确性。这是公认的,如100%的样本是潜在的耗时和需要作出考虑,以适应现有的维护窗口内的统计信息收集活动.
新的优化器统计信息的收集应保持或改善现有的执行计划,但它可能是某些查询的性能可能会降低。注意从10gR1统计以前的副本默认情况下,保持在过去30天里,可以在问题的情况下恢复。见:
Note:452011.1 *从10G起恢复表的统计信息
收集新的优化统计可能会使游标,所以它是审慎限制所有收集作业执行低活动期,在数据库中,如预定维护窗口,在共享池中.
对于非常大的系统,统计信息的收集可以是一个非常耗时和资源密集的activity.In这种环境下的样本量需要小心控制,以确保在可接受的时间表和资源的限制,并在维护window.For指导,收集完成这个主题,请参阅:
Note:44961.1 统计数据收集频率和战略指导方针
在这些环境中,还建议利用改变收集的统计数据,以避免重新收集信息unnecessarily.Please看到:
Note:237901.1收集模式或自动资料库统计 - 范例
Note:377152.1 在Oracle 10g中自动统计信息收集最佳实践
收集统计对象
基于成本的优化器(CBO)的使用统计信息来确定一个特定的查询的执行计划。潜在的,抽样与样本量减少,可以产生不同的机会分组数据的统计,由于可能的结果不同的加载方法等.
在11g上,建议使用定期统计收集脚本来收集统计信息。在大多数情况下,默认的脚本提供适当水平的抽样考虑以下建议:
使用足够大的样本大小.
对11g的支持,建议使用默认ESTIMATE_PERCENTDBMS_STATS.AUTO_SAMPLE_SIZE。这将产生100%的估计样本大小(如果它是可能的,以适应维护窗口内),即使这意味着统计数字聚集在降低频率。如果这100%的样本是不可行的,然后尝试使用至少30%的估计,但是,因为11g使用哈希算法来计算的统计,在大多数情况下应该是可以接受的性能.
一般来说,统计数据的准确性,整体超过了一天,在大多数应用中的日变化。见下文关于早期版本和此设置的说明.
确保所有对象(表和索引)聚集了统计。实现这一目标的一个简单的方法是使用CASCADE参数.
确保数据分布偏斜的任何列直方图收集,并在足够的分辨率,使用method_opt参数。支持建议使用默认的列统计设置为“AUTO”,这意味着使用DBMS_STATS将决定哪些列添加柱状图,它认为,它们可能有助于产生一个更好的计划。 “添加一个直方图,只有当它被称为需要”是一个保守和更稳定计划,而不是所有列列的统计信息收集方法.
在早期版本的method_opt参数的默认设置是“所有列的大小1”,这将收集只有高和低价值和有效的意思,有没有详细的列统计。据了解,在某些情况下,直方图的作用是产生一个更好的计划不利版本之间移动,使用户最初可能希望将此参数设置为预升级版本价值,后来调整到升级后释放的默认值。见:
Note:465787.1在升级到10g或11g管理CBO的统计信息
注意:如果统计数据不完全是最新的,当时在场的直方图可以引起麻烦时解析值超出范围,或“频率”直方图值之间。在这种情况下,优化,使猜测这可能是不准确的,有时,导致贫困的计划。一如以往,测试与应用程序的不同的价值观会产生最好的结果.
如果使用分区,收集全球统计数据,如果可能的话,由于时间限制。全球的统计是非常重要的,但聚会往往避免由于涉及的大小和时间长短向所需。如果有100%的样本不可能支持将建议最低的1%。收集小样本(如0.001,0.0001,0.00001等),可以是非常有效的,但同样大的比例,数据不会被审查,这可能证明是决定性的优化的计划选择。请注意,可用范围为ESTIMATE_PERCENT参数是一个非常灵活的[0.000001 - > 100],可以使用非常小的样本量,适用于巨大的分区表。测试将揭示最合适的设置,为每个系统.
见:
Note:236935.1全球统计-说明
11g 还提供了逐步收集全球信息统计的功能。见
Oracle数据库性能优化指南
11g第1版(11.1)
零件编号B28274-02
第13章管理优化统计
第13.3.1.3分区对象统计
收集系统的统计数据,以反映系统CPU负载和提高提供了CPU成本估计除了正常的I / O成本估计在CBO CBO的估计的准确性. 见:
Note:470316.1使用实际系统的统计(收集CPU和IO)息
Note:149560.1收集和显示系统统计(CBO的使用CPU和IO)
Note:153761.1缩放系统,以提高CBO优化
注意不同版本的甲骨文上收集统计数据的默认一定相同, 例如
ESTIMATE_PERCENT: defaults:
9i : 100%
10g : DBMS_STATS.AUTO_SAMPLE_SIZE (使用非常小的估计百分比)
11g : DBMS_STATS.AUTO_SAMPLE_SIZE (使用较大的估计百分比-100%)
METHOD_OPT: defaults:
9i : “所有列的大小1”有效没有详细列的统计信息.
10g and 11g : “所有列的大小自动” - 此设置意味着使用DBMS_STATS决定哪些列添加直方图,它认为,它们可能有助于产生一个更好的计划.
在11g中,使用100%ESTIMATE_PERCENT的默认自动调整大小,因此,尽可能准确。 100%的样本在以前的版本可能已经是不可能的,由于时间收集的限制,但是11克实现了一个新的哈希算法来计算,而不是排序统计(9i和10g的“慢”的一部分,是典型的排序),从而显着提高收集时间和资源的使用.
样本统计量的采集命令
统计信息收集个人表
exec dbms_stats.gather_table_stats( -
ownname => ' Schema_name ', -
tabname => ' Table_name ', -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
注:
一个更谨慎的做法,如在上面的文本概述列统计不知道是有益的,替换:
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
with
method_opt => 'FOR ALL COLUMNS SIZE 1'
注:架构名称取代的“SCHEMA_NAME'和'TABLE_NAME'
和表分别收集统计.
聚集在一个模式的所有对象的统计信息
exec dbms_stats.gather_schema_stats( -
ownname => ' Schema_name ', -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
注:更换架构的名称来收集统计 ‘Schema_name ‘。
收集在数据库中所有对象的统计:
exec dbms_stats.gather_database_stats( -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );