oracle 如果使用基于cost的优化方式,那么statistics 就显得格外重要,因为它直接影响着sql的执行计划。
1 oracle需要的统计数据 以及各个数据的用途。
表: 列的数目、块的数目、平均列长。
列: NDV ( number of distinct values in column)
number of nulls in column
data distribution(histogram) 柱状图
索引:number of leaf blocks
levels
clustering factor
系统数据:
输入输出性能和利用程度
cpu性能 ----优化器可以通过这些数据,而采取更有效的执行计划,避免了cpu或其他系统资源的过分争用。
2 收集statistics 的时机和方法:
(1) auto statistics gathering:
oracle 依赖于一个gather_stats_jobs的调度程序进行可以在每晚的10点至次日早晨6点自动收集all db objects 的statistics, 如果objects的改变程度已经超过了10%的行。
优点:可以自动完成收集任务。
不足1:只能在每晚的10点至次日早晨6点进行收集,如果在白天数据已经发生了变化,oralce可能因为没有最新的statistics而采取了错误的执行计划,所以遇到这种情况需要手动收集statistics。
不足2: 由于每次分析之后,原来的执行计划都变的无效,而重新分析sql可能会引起latch的争用。而自动收集是系统的默认设置,如果确实出现了相关的latch free,可以考虑禁止这项功能。而在适当的时机再对对象进行统计信息收集。
方法:begin
dbms_scheduler.disable('gather_stats_jobs');
end;
(2) manual statistics gathering:
oracel 提供的dbms_stats 包提供了灵活的收集方案,可以指定一个模式、一个表、表中的个别列并能给指定了列生成柱状图(对于列值分配不均匀 的表非常重要)表的一个分区、也可以是一个索引等等。
可以参考dbms_stats包中过程的各个参数含义。
优点: 提供了更加灵活的收集方案,可以在任意时间对所指定的对象进行数据收集。
缺点: 必须人为地进行收集。
(3)system satatistics gathering:
dbms_stats包还提供了对系统的一些指标收集的过程PROCEDURE GATHER_SYSTEM_STATS,如系统的I/0能力,cpu性能等待,通过这些数据,优化器可以采取更为有效的执行计划。
优点:可以弥补上面两种收集方案的不足,另外,这类数据的收集并不会使已经分析的sql变得无效,只会对新的sql分析时用到这些数据。如果系统已经出现了cpu或I/O方面 的瓶颈,这些数据的分析就显得更为重要了。
不足:这种数据的收集是基于一定的时间间隔的。
建议:在一般情况下,采用auto statistics gathering。在必要是用manual statistics gathing 进行补充。可以较为经常地使用gather_system_stats过程,以便给优化器提供全面的依据。
在收集数据时考虑的相关事项:
在收集时可以采用样本的方法,以不至于全部分析了数据。参数:estimate_percent
using parallel : 参数 degree,建议值dbms_stats.auto_degree;
patitioned objects: 参数:granularity
produce histogram: 参数 mothod_opt
1 oracle需要的统计数据 以及各个数据的用途。
表: 列的数目、块的数目、平均列长。
列: NDV ( number of distinct values in column)
number of nulls in column
data distribution(histogram) 柱状图
索引:number of leaf blocks
levels
clustering factor
系统数据:
输入输出性能和利用程度
cpu性能 ----优化器可以通过这些数据,而采取更有效的执行计划,避免了cpu或其他系统资源的过分争用。
2 收集statistics 的时机和方法:
(1) auto statistics gathering:
oracle 依赖于一个gather_stats_jobs的调度程序进行可以在每晚的10点至次日早晨6点自动收集all db objects 的statistics, 如果objects的改变程度已经超过了10%的行。
优点:可以自动完成收集任务。
不足1:只能在每晚的10点至次日早晨6点进行收集,如果在白天数据已经发生了变化,oralce可能因为没有最新的statistics而采取了错误的执行计划,所以遇到这种情况需要手动收集statistics。
不足2: 由于每次分析之后,原来的执行计划都变的无效,而重新分析sql可能会引起latch的争用。而自动收集是系统的默认设置,如果确实出现了相关的latch free,可以考虑禁止这项功能。而在适当的时机再对对象进行统计信息收集。
方法:begin
dbms_scheduler.disable('gather_stats_jobs');
end;
(2) manual statistics gathering:
oracel 提供的dbms_stats 包提供了灵活的收集方案,可以指定一个模式、一个表、表中的个别列并能给指定了列生成柱状图(对于列值分配不均匀 的表非常重要)表的一个分区、也可以是一个索引等等。
可以参考dbms_stats包中过程的各个参数含义。
优点: 提供了更加灵活的收集方案,可以在任意时间对所指定的对象进行数据收集。
缺点: 必须人为地进行收集。
(3)system satatistics gathering:
dbms_stats包还提供了对系统的一些指标收集的过程PROCEDURE GATHER_SYSTEM_STATS,如系统的I/0能力,cpu性能等待,通过这些数据,优化器可以采取更为有效的执行计划。
优点:可以弥补上面两种收集方案的不足,另外,这类数据的收集并不会使已经分析的sql变得无效,只会对新的sql分析时用到这些数据。如果系统已经出现了cpu或I/O方面 的瓶颈,这些数据的分析就显得更为重要了。
不足:这种数据的收集是基于一定的时间间隔的。
建议:在一般情况下,采用auto statistics gathering。在必要是用manual statistics gathing 进行补充。可以较为经常地使用gather_system_stats过程,以便给优化器提供全面的依据。
在收集数据时考虑的相关事项:
在收集时可以采用样本的方法,以不至于全部分析了数据。参数:estimate_percent
using parallel : 参数 degree,建议值dbms_stats.auto_degree;
patitioned objects: 参数:granularity
produce histogram: 参数 mothod_opt
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12742547/viewspace-254412/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12742547/viewspace-254412/