深入了解Oracle数据库优化器 更新到第（五）也许的完结篇

cc59

支持原创,楼主继续吧

NHCS_cn

影响优化器的参数
一个升级了数据库的挑战是：在新的数据库里，因为优化器的改变而会导致对于以前SQL语句的优化改变，为了减少升级所带来的影响，一个比较可行的方式是让升级后的数据库使用原来版本数据库的优化器，这个可以通过设置
OPTIMIZER_FEATURES_ENABLE=升级前版本号
的方式来让升级后的数据库具有原来数据库优化器的特征从而减小升级对于应用系统带来的影响。

CURSOR_SHARING
要了解这个参数对于成本优化的影响就需要了解数据库里histogram的概念，histogram是一个表的一个列的值对于具有类似的值的分布的信息。举个例子来说，假设一个1万条记录的客户表的某一列是客户的级别，普通客户是1，银卡客户是2，金卡客户是3，则所有的记录的级别的值都是1，2，3这三个值中的一个，关于级别的histogram信息就是级别是1的客户记录占有9000条，级别是2的客户记录占有800条，而金卡客户记录有200条，这个信息就是这个表上的级别字段上的histogram信息，这些信息对于产生高效的执行计划是非常重要的，我们在本系列的第一篇文章里的例子已经交代过，这种关于值的分布的不均匀的现象被称为skewed distribution of data，凡是对于这种值的非均匀分布，优化器就会根据具体的值进行执行计划的选择，而不是固定地使用索引还是全表扫描，但是这种优化仅当传入的值是个常数有效，而cursor_sharing参数的设置有可能是把常数转换成绑定变量，这样就让优化器无法使用histogram信息从而不能产生最佳的执行计划。

DB_FILE_MULTIBLOCK_READ_COUNT
这个参数的意思是在一次全表扫描或者索引全扫描中连续多几个数据块，这个参数会影响成本优化是因为它会降低全表扫描的代价，从而使优化器趋向于选择全表扫描的执行计划。写到这里我想说的一句话是Oracle数据库优化器的成本评估是受许多因素影响的，而且可以被有目的的影响，记得一些朋友老是抱怨说升级数据库之后本应该使用索引执行计划的SQL不用了，可是却不知道其实优化器是可以向我们所希望它的方式进行的。

OPTIMIZER_INDEX_COST_ADJ
这个参数应该是解决朋友们一直抱怨优化器不使用索引的最直接的参数了，它的取值是1到10000，缺省值是100，这个参数的缺省值代表了执行计划在索引选择和全表扫描选择之间的某种平衡，如果设置一个小于100的值，则优化器朝着利于索引选择的方向走，否则反之。举个例子来说，如果把这个值设置为10，则优化器则认为原来成本是100的索引扫描现在成本是原来的十分之一，也就是10，这样所有使用索引的执行计划就会成本下降，从而被更加倾向选择，所以如果发现有许多SQL语句应该使用索引效率会更高，但是却没有使用，就可以把这个值的参数调小。

OPTIMIZER_MODE
这个值大家应该不会陌生，在这里提这个参数是需要那些习惯于使用图形界面来测试SQL效率的人提个醒。这个参数的取值可以是ALL_ROWS，FIRST_ROW_n等，习惯于使用图形界面的开发人员往往习惯于把从下命令开始直到记录出现在屏幕上的时候作为SQL执行的时间，这个习惯的缺点在于，一般图形工具可能在取得前几十条记录的时候就返回了，所以这个时间只能说明前几十条记录返回的时间，却不能说明整个SQL语句执行完的时间。事实上这个参数被设置成FIRST_ROW_n的时候优化的目标就是前几十条记录返回的成本最小的执行计划，这种优化针对于在网上查询需要比较快展现前几十条记录的应用是合适的，但是如果是对于报表应用，我们关心的就应该是整个报表数据完全运行完整的时间，这个恰恰就是把这个参数设置成ALL_ROWS的时候选择的目标。当n值越小，优化器在表连接的时候更加倾向于使用nested loop和索引查找，当n值大的时候，优化器更倾向于使用hash连接和全表扫描。n值的选择完全取决于应用，比如一个internet查找应用，大部分的用户一般只查询整个结果的前几十条，而很少会去看后面几百条的数据，这个就是优化如何和应用结合的方法。

PGA_AGGREGATE_TARGET
可能大家会觉得奇怪的是怎么这个参数也能够影响优化器吗？事实上，原因是因为大的PGA能够分配更多的内存用于sort join和hash join，可以有效降低这些操作的成本，所以可以影响优化器的选择。

小结
通过前面的介绍，我们总结一下，实际上优化器在安装配置好数据库之后有自己默认的优化行为，但是这个行为并非是固定不变的，我们完全可以针对自己的应用，以自己的理解去影响优化器，让优化器能够朝着我们理解的方式去产生执行计划。以前在给一些客户进行培训的时候，我经常说的一句话就是，数据库软件就是一个软件，它毕竟不是一个会思考的智能的大脑，它比较不知道我们应用的特点，当有时候我们确信我们非常了解我们应用的时候，我们可以通过某种方式把这些我们知道的信息告诉它从而影响它的选择，这个才是数据库优化的一个重要目的。


-----------待续

[ 本帖最后由 NHCS_cn 于 2007-12-2 20:38 编辑 ]

oliver800329

写的好，顶一个 。

xilangxiangwa

学习了～

zhuojm

越来越有点意思了,继续.

hotpig

写的不错，支持一下。

NHCS_cn

基础的访问路径
前面的文章已经出现了一些关于访问路径的名词（Access Path），如全表扫描，索引扫描，但是有时候我们并没有对这些词做详细的解释，我们甚至没有区分索引扫描（index Scan）和行ID选取（rowid scan）两种方式，本篇文章的目的就是要解释Oracle数据库里的各种访问路径。它们主要有全表扫描（Full-table scan），行ID扫描（rowid scan），索引扫描（index scan）。一般而言，如果是存取一个表的少部分记录，则使用索引的方式是合适的，如果是要访问一个表的大部分记录，则全表扫描更为高效，这样就表现为对于联机交易而言（OLTP），因为大部分交易都是针对某一个账户或几个账户进行的交易，所以只会涉及到表的极少数的记录，所以对于OLTP系统一般使用索引访问的方式，而对于决策支持系统，因为通常需要从很多记录甚至全部记录去产生一个结论，因此很多时候需要至少需要全表访问所有交易的记录一次才可以，因而对于这种交易类型全表扫描是普遍的，下面就对这些访问路径逐一解释。

FULL-table Scan
一个表在Oracle数据库里的存储机制通常是由形成某种指针链接的数据块组成，所谓全表扫描的意思就是从组成表的起始数据块开始，一直跟着指针链接把所有的数据块读到内存里，然后根据Where语句里的条件把所有数据块里符合条件的记录选取出来（如果存在Where语句的话）。当表上面没有针对where条件里的字段建立索引的话，全表扫描是不可避免的，初一听可能觉得这种方式实在是很没有效率的一种方式，这个从某种程度上来说是正确的，从我的经验来看，在很多有性能问题的客户那里通常能够发现低效率的全表扫描，如果应用系统能够解决全表扫描问题，则系统至少解决了70%的效率问题，当然随着大家数据库使用水平的提高，想通过这个就解决系统大部分系统效率的可能性也就低了，但是无论如何，要看一个系统的问题，通常第一步还是去看看是否有低效率的全表扫描存在。
其实还是有一些参数能够提高全表扫描的效率的，比如我们上一篇文章里说的DB_FILE_MULTIBLOCK_READ_COUNT参数等，这个参数对于IO能力强劲的硬盘阵列能够极大加快全表扫描的效率。
当然，也存在另外的场景全表扫描是被认为比索引更加高效的，比如当一个表很小的时候，或者当需要访问组成一个表的绝大部分记录的时候。

Row ID Scan
我想大家应该清楚在Oracle数据库的表里，每一行记录都存在一个ID我们称之为Row ID，从很早版本的Oracle开始，RowID的值就是：
Row ID=数据文件编号+数据文件里数据块的编号+数据块的记录编号
所以一但知道了一条记录的Row ID，Oracle软件就可以马上把它翻译成硬件的IO指令去读到特定的记录，所以Row ID从某种程度上来说是Oracle数据库里最快的记录访问方式，如果大家想从图形工具里看看这个访问路径的话，可以试着把以下的SQL语句输入到执行计划解析器里：

select * from dual where rowid=:a

这个SQL语句出来的执行计划就是我们所说的Row ID Scan的含义，当然一般而言我们的应用可能很少有机会这么来写，那么除了上述的SQL语句，Row ID Scan一般还会出现在什么场景呢？
大家应该还记得Oracle索引的真正组成吧，索引一般是这样的：

索引=被索引的列+指向被索引的记录的Row ID
组成

所以大家在使用索引的过程中，很多时候就不自觉地使用了Row ID Scan，比如可以查看一下下面的SQL语句的执行计划

select * from emp where empno=:a

在执行计划里，首先最里层出现的是index unique Scan的访问路径，然后接着就出现了一个Row ID 方式，所以在很多时候，Row ID Scan的方式是经常能够见到的，只不过大家用到了却不知道而已。

Index Scans
要了解Index Scan首先是需要了解索引的存储结构，大家都知道一般索引的存储结构是有序的B*Tree存储，这就意味着索引的查找的基本方式是比较传统的类似于二分法的方式，比如举个例子来说，假如从1到100的键值分布在10个索引块里，每个索引块存储10条记录，则在根索引块里存储着键值1～10对应于索引块1，11到20对应索引块2的信息，则可以想像成在查找键值为49的记录的时候首先会定位到索引块4，然后找到所有键值为49的记录（这些记录一定是相邻的因为索引存储的有序存储特性），然后根据相对应的rowid去找到所有的数据块来完成index Range Scan的过程。
Index Scan又分为Unique Index Scan和Index Range Scan，他们的区别在于Unique Index Scan只会在主键上或唯一索引上发生，因为已经定义了该索引键值是唯一的，所以一旦找到值，索引的查找就停止了，而range Scan则要找到所有的值才停止查找。
当where语句里有c1=:a或者c1>:a或者c1<:a的语句或者他们的组合的时候，就有可能使用Index Scan的执行计划。另外当where语句有order by的时候，可能会使用一个index Scan来代替排序操作。

还有一些派生出来的Index Full Scan等执行路径我们就不详细讨论了，顺便说一下，如果SQL语句所需要的字段通过索引就能获得，则这个时候不需要使用索引去访问表，只需要Index Full Scan就可以完成SQL语句的执行，这个在有些时候是个不错的调优方法。

arentt

拜读！
感觉非常有收获。

“数据库软件就是一个软件，它毕竟不是一个会思考的智能的大脑，它比较不知道我们应用的特点，当有时候我们确信我们非常了解我们应用的时候，我们可以通过某种方式把这些我们知道的信息告诉它从而影响它的选择，这个才是数据库优化的一个重要目的。
”
说的太好了！期待下一篇

Yong Huang

原帖由 NHCS_cn 于 2007-12-6 10:04 发表
如果SQL语句所需要的字段通过索引就能获得，则这个时候不需要使用索引去访问表，只需要Index Full Scan就可以完成SQL语句的执行，这个在有些时候是个不错的调优方法。

I suggest you drop the word "Full", because Index Full Scan is a specific scan method. Select empno from emp where empno=123 (a silly query!) would not read the table but the scan should be index unique scan assuming there's a unique index or PK on empno.

Index Full Scan is sometimes seen in cases Oracle wants to avoid sorting, especially when it has sort merge join in the plan or when first_rows hint is used.

Yong Huang

NHCS_cn

这里就不改了，读者直接看Yong Huang的评论就行了，因为实际上访问路径还是有不少方式，我这里也不想展开把所有的方式都说清楚，所以就当一个问题给大家自己思考吧