分类： Oracle
2008-05-13 16:09:05
本文是有关Oracle 10g基于成本的内在优化（CBO）和结构化查询语言（SQL）性能最优化的一系列文章（共12部分）中的第11部分。每个技巧类的文章都摘录自即将由Rampant科技出版社出版的书《Oracle 结构化查询语言（SQL）和内在索引》，作者是Kimberly Floss。从该系列的主页上你可以看到其他即将发布的部分。
柱状图信息
在某些情况下，表中某一列值的分布情况将影响优化器决定是使用索引还是执行全表扫描。这种情况发生在当一个where子句的值在表中不是均匀分布时，此时，使用全表扫描比使用索引访问的开销更低。问题是，优化器并不能区分它正在使用的某个值只在少量行出现，还是在大量行都出现。
当某列的数据存在倾斜或者我们怀疑其存在倾斜时，才应该对该列创建柱状图。在现实世界中，发生这种情况的可能性很小，而且优化器所犯的最常见的错误之一就是将不必要的柱状图引入到优化器的统计信息中。这就会向优化器发出该列不是线性分布的信号，从而优化器将查看SQL语句的where子句中的该列的字面值，然后把它和柱状图统计表中的柱状图桶进行比较。
尽管柱状图可以被用来作为是否使用索引来访问表的判定依据，但是它更常被用来预见来自多表连接的中间结果集的大小。
例如，假如我们有一个五路表连接，它的结果集只有10行。Oracle将试图以第一次表连接的结果集（集的势）最小的方式来连接这些表。通过在中间结果集中放置更少的包，查询的速度会很快。为了最小化中间结果集，在分析SQL语句执行的阶段，优化器试图来估计每个结果集的势。在歪斜的列上建立柱状图信息将极大地帮助优化器作出正确决定。（请记住，即使某一列上没有索引而且这一列也不是连接键的一部分，也能在该列上创建柱状图信息）。
由于一个复杂的表模式可能含有上万个列，评估每一列的倾斜程度是不现实的，因此Oracle提供了一个创建柱状图信息的自动化方法，它是dbms_stats工具包的一部分。通过使用dbms_stats的选项_opt=>'for all columns size skewonly'，你能够指示Oracle来为那些其值高度倾斜的列自动创建柱状图信息。
通常情况下，柱状图是用来预见集的势和结果集中返回的行的数量的。例如，假如我们有一个_type索引而且这一列的99％取值都是。当SQL语句的where子句中出现product_type='HARDWARE'时，全表扫描是最快的执行计划，而当SQL语句的where子句中出现product_type='SOFTWARE'时，使用索引来访问是最快的。
由于创建柱状图给SQL语句的分析阶段增加了额外的开销，你应该避免使用它们，除非它们对于更快的优化器执行计划是必须的。但是，也有一些情况下创建柱状图是推荐的，如下：
* 当该列在某个查询中被参考时 － 假如没有任何查询参考这一列的话，就没有必要给该列创建柱状图。
* 当该列的值的分布非常倾斜时 － 该列值的倾斜应该足够大，以至于where子句中的值将使得优化器选择其他的执行计划。
* 当该列的值导致某个不正确的假定时 － 如果优化器对某个中间结果集的大小作出了错误的猜测，那么它可能选择一个次优化的表连接方法。给该列添加一个柱状图通常情况下会向优化器提供选择最好的表连接方法的所需信息。
因此，如何来发现哪些列适合在其上创建柱状图？dbms_stats有一个特色提供了自动查找某些列应该在其上创建柱状图并自动在其上创建柱状图的能力。多桶柱状图给分析SQL语句增加了很大的开销，而且仅当SQL根据列值选择不同执行计划时才应该使用柱状图。
为了帮助智能化产生柱状图，Oracle使用dbms_stats的method_opt参数。在method_opt子句中还有新的重要选项，即，skewonly 和 auto (和 others)
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size auto'
第一个选项是skewonly，它很消耗时间，因为对于每个索引内的每个列它都会检查其值的分布情况。假如dbms_stats发现某个索引包含分布不均匀的列，它将会为那个索引创建柱状图来帮助“基于代价的SQL优化器”作出是使用索引还是使用全表扫描进行访问的决定。例如，如果某个索引有一列在所有行中50％的值都是相同的，那么全表扫描比使用索引来扫描检索这些列快得多。
柱状图也被用于含有绑定变量的SQL和开启cursor_sharing的SQL。在这些情况下，优化器判定列的值是否能影响执行计划，如果能的话，它会使用某个字面值来代替该绑定变量，并执行一个硬分析。
begin
dbms_stats.gather__stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
/
当实现监视（通过命令alter table xxx monitoring）时，使用选项auto，它会根据数据的分布情况和应用程序访问该列的方式（例如，监视器决定的该列的工作量）来创建柱状图。使用method_opt=>'auto'与使用dbms_stats中的gather auto选项参数类似：
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
/
找出运行效率低下的SQL语句
虽然复杂的查询可能包含极其复杂的执行计划，但是大多数Oracle专业人员必须对含有以下问题的SQL语句进行性能调优：
* 对一个表的次优化索引访问 － 这个问题出现在当优化器不能找到某个索引或者SQL中的最受限制的where子句不能和某个索引相匹配时。当优化器不能找到某个合适的索引来访问表中的行时，优化器总是会调用一个全表扫描，读取表中的每一行。因而，对一个非常大的表执行全表扫描可能暗示了，某个次优化SQL语句能够通过增加一个匹配该查询所用到的where子句的索引来调优。
* 次优化连接方法 － 优化器有很多连接方法供使用，包括归并连接、嵌套循环连接、哈希连接和星型连接。为了选择正确的连接方法，优化器必须猜测多路表连接的中间结果集的大小。为了作出这个猜测，优化器拥有不完全的信息。即使创建了柱状图，优化器仍不能非常肯定某个连接返回的结果集的精确行数。最常见的解决方法是使用暗示来改变这个连接（use_nl, use_hash），或者对目标表上重新分析其统计信息。
v$sql_plan试图能够帮助定位SQL性能调优的可能性。当搜索性能调优的机会时，从询问v$sql_plan试图来发现那些对大表进行全表扫描开始。
这是业务知识起作用的地方。根据我的经验，经常性地，查询都是很差劲儿的，由于开发者不懂得他的目的是什么以及数据的真正含义。老谚语“没有任何东西可以代替经验”在这里又一次被验证。
