津岛博士的性能讲座 第五回 优化与优化统计的收集

津岛博士的性能讲座

第5次 优化与优化统计的收集

 
这次我将对于优化统计的收集进行说明(用Statspack等等收集的话情报会有区别,所以为了生成优化的执行计划所要用到的东西,我将称之为优化统计信息)。最近,我有听到如下关于优化统计信息的问题,我将在此进行说明。

 

“因为近来变得只有CBO了,所以就必须进行优化统计收集,对象太多的话就不能小看收集时间了。为了缩短收集时间,就缩小样本尺寸,减少收集频率,从而导致执行计划不是最合适的情况,实在让人困扰。请告诉我优化统计的收集的方法。
对此,因为没有说“只要这么做就万事大吉”的方法,我想大家都应该对此挺烦恼的。对此,我整理了我的想法,希望能作为大家的参考。

 

首先我们说明下到底优化器optimizer是什么。从optimizer statistics 统计信息开始计算成本,然后选出其中成本最低的执行计划来执行就是CBO。(对此,直接由SQL文本所决定的执行计划就是RBO。以前的版本两种说法都可以使用,但现在只推荐用CBO了。

优化器统计信息分为如下几种

  1. Table表统计(行数、块数、平均行长等等)
  2. 列统计(基数,NULL数,最大值,最小值,历史数据等等。没有历史数据 情况时,用最小值和最大值将数据分布均一化。)
  3. 索引统计(叶块数Leaf block、树tree的高度、集群化洗漱等等索引的有效性。)
  4. 系统统计(CPU性能、I/O性能等等)

 

使用这个概念,CBO就是怎样考虑的(全扫描或索引扫描,以怎样的顺序怎样结合等等),以及如何使得成本最小化(使得访问行数最少)。如果这里出现纰漏的话,就做不出较好的执行计划(比如如图A的的数据分布,因为没有收集偏差部分的历史数据,就无法判断是否均一)。优化统计信息对于性能是非常重要的情报。

 

1

 

图(数据分布)

上述表的行A和行B是分别由各自的索引被制成的状态,假设用下述的1和2的SQL文执行的话,从选择率上来看,使用1的索引的B效率会更好。2是使用A的缩影效率会更好。如果A的数据分布均一化了请选择1的A索引。

 

那么要怎么去收集优化统计才好呢?基本上,使用如之后所述的准则来实行。因为其中有性能劣化的情况,我将在之后对此进行说明。

使用DBMS_STATS包package

 

ANALUZE命令也因为下位互换的原因被留下来了,所以请使用DBMS_STATS包package(将正确的统计情报会被效率地收集起来,历史管理也会自动执行。

对每个对象指定合适的收集频率

对于对象的变更量,根据因此优化统计变得不正确的等级来决定收集频率。这时,请注意对于静态的数据不要做没用的收集。我想这样表述大家还是很难明白,通常请按照下面的方法执行。(适合在Oracle里执行。)

存储暂时表和挥发性很高的数据的Table表频繁地大量行的DELETE或者被TRUNCATE的Table)是用动态的采样来执行(删除统计情报后,ROCK可以经常执行动态采样。观察以下的SQL文,我们可以确认统计ROCK状态的对象,

2

3

Table有合适的样本尺寸统计

因为在Table的统计收集中有必要执行分类处理,大规模的Table之中,这样的操作非常耗费时间。因此我们会设定Table的样本 尺寸来执行。(通常有5%的样本尺寸就没问题了)。

DBMS_STATS包的参数的值也,从Oracle Database 10g开始默认变成是estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE(依存于对象的行数的估计采样尺寸),所以我认为这里不存在问题(收集优化统计的的时候的样本尺寸是用以下SQL文来确认。)

 

4

索引中的完全统计

因为索引已经完成分类了(没必要分类处理),可以完全(100%)收集。

从Oracle Database 10g开始,制作索引或者再构筑时,优化统计会被自动收集。(Oracle9i中索引制作或者再构筑时,通过指定COMPUTE STATISTICS选项来完成收集)所以没有必要制作完成后再次收集。

这边的DBMS_STATS 包的参数值也是,Oracle Database 10g默认是cascade=>DBMS_STATS.AUTO_CASCADE(是否有必要去收集索引的统计以及醉适合的样本尺寸是什么是由Oracle决定的),我认为这里没有问题。用以下的SQL文来确认索引的优化统计。

 

 

5

数据中有偏差的列用直方图统计

像在WHERE语句的条件中 使用数据分布中有偏差的列的SQL文有不是最合适的执行计划的可能一样(像上次课程一样,姓是铃木和林的搜索结果不同一样,最适合的实行计划也有不同的可能性,有必要收集正确的数据分布。)

DBMS_STATS包的参数值也通常是用method_opt=>’FOR ALL COLUMNS SIZE AUTO’(从Oracle Database 10gR2开始的默认),我们可以判断没有问题。直方图的直方数用以下的SQL文来确认

 

SQL文的WHERE语句之中选定复数的列的话,会有列统计出现错误的情况,根据情况,请检查复数列的统计。

另外,在Oracle Database 10g以后追加的新功能也有让统计情报更便利的功能

以下我将介绍这些新功能,请大家一定要好好活用。

 

  • (1)自动优化统计收集

Oracle Database10g以后的版本中,默认的自动优化统计收集功能十分有效。因此,如下的优化就被自动收集到优化统计之中。

还没有收集到优化统计的对象。

有优化统计不是最新的状态的对象(10%以上的行被更新了)

 

被事先定义为周一到周五是22:00到第二天早上的2:00,周六周日是6:00到第二天早上的2:00之间收集(Oracle Database 10g以后之中周末是周六0点到周日的23:59:59之间),请调整时间使用。在之后的SQL文中可以确认自动维护任务窗口、执行日期、状态。

6

 

这时,dbms_stats.gather_database_stats_job_proc存储过程(与dbms_stats.gather_database_stats (options=>GATHER AUTO)的执行几乎同时)就会被同时执行。因为是使用被Table表的PARALLEL句指定的默认并列度,对于大规模Table表,想加速收集时间的时候,请调整对象Table的默认并列度)。要确认或者变更这些默认值,请使用get_prefs存储过程与set_*_prefs存储过程来执行。(直到Oracle Database 10g是使用dbms_stats.get_param和dbms_stats.set_param来运行),下面的SQL文就是变更和确认的例子。

7

 

(2)动态采样(初始化参数OPTIMIZER_DYNAMIC_SAMPLING)

优化统计不足的话,执行SQL文时就会自动地采样优化统计制作执行计划。(优化统计是被存储在词典Dictionary之中的删除共有区域的话就会被再次执行。因此,访问对象Table的SQL文很多的情况时,请手动收集)

Oracle Database 10g开始的默认之中(初始化参数Oracle Database 10g),所有没有被优化统计收集的Table表都将被执行动态采样。(因为采样被访问的块数从OD10g开始变成了64块。)通常这没有问题。(但是,复杂的SQL文等等需要在默认以外去做。关于这个我将另行说明。)

通常一直对Table表进行动态采样的情况下,如下图所示,请删除优化统计后再LOCK,这样,初始化参数OPTIMIZER_DYNAMIC_SAMPLING=2以上时通常就会被动态采样。

8

 

(3)复数列的统计

选定SQL文的WHERE语句里的复数列作为条件时,会出现列统计不正确的情况。比如如下图SQL文所示,制成c1和c2的复数列索引的情况时,判断进行索引扫描或全扫描时,在各自的列单独的统计中有无法正确判断的情况。如果不是根据两个列的组合决定数据分布的话就无法得出正确结果。(因为对两个列的值有必要估计到底有多少件是HIT的)8

9

 

 

这样的情况是收集Oracle Database 11g开始的复数列的统计(作为列Group的统计)(Oracle Database 10g以前有必要调整提示文)比如,假设将上述SQL文中WHERE语句里的c1列和c2列作为条件指定的话,就能如下所示一般收集。

11

 

 

  • 性能劣化的情况

优化统计和CBO也不是完美的。(也会有所有的优化统计不能说是完全正确的情况,以及即时使用完全的优化统计,CBO选择的执行计划也不是最适合的情况。因此,也有收集优化统计然后性能产生劣化的情况,出现这种情况时,请使用以下对策。

 

22

 

收集正确的优化统计(扩大样本尺寸和直方图的直方)

完全收集样本尺寸后,性能被改善了的话,然后就接着收集。(时间上出现问题时,将样本尺寸一点一点缩小,尽量不影响执行计划来进行调整。)

使用提示文来回避问题点

比较劣化前的执行计划,对于有差异的地方来插入提示文。比如,想使用索引ix_tab01_c1的时候如后图所示,插入INDEX提示(红字的部分)。

33

 

这样使用中性能出现急剧劣化的话很让人困扰,也能常常听到不想频繁改变执行计划。最后我将对此进行说明。

 

  • 不想改变实行计划

因为是用CBO,所以有可能让优化统计的执行计划发生改变。无论如何不想改变的话,按下述事项来做的话,也有不变更的方法。

提示文(无法变更SQL文且无法植入提示文的情况使用计划稳定性Plan Stability)

计划稳定性Plan Stability(为了实现执行计划的概要。作成“之后植入的提示文的集合”,执行时,在SQL文植入概要)

还有Table的数据分布等等发生改变,效率降低的情况,请在数据分布的倾向不发生改变的情况下使用。(比如列c1的索引访问效率较好,而改变数据分布之后效率就变差了)因此,改变数据分布的情况,请确认现在的执行计划是没有问题的。

因为也有上述这样的情况,Oracle Database 11g的话就如图一样使用SQL计划管理(SPM:SQL Plan Management)。执行实行计划的历史管理,就能采用最合适的执行计划。

44

 

  • 总结

这次我们对于优化统计的收集进行了各种各样的说明,正确把握所有数据是非常困难的。所以充分进行提前测试,再分别处理就显得很有必要了

有其他问题的话尽管来问,我在这里恭候大家。

那么,下次见。

Read More