|
什么是执行计划 所谓执行计划顾名思义就是对一个查询任务做出一份怎样去完成任务的详细方案举个生活中的例子我从珠海要去英国我可以选择先去香港然后转机也可以先去北京转机或者去广州也可以但是到底怎样去英国划算也就是我的费用最少这是一件值得考究的事情同样对于查询而言我们提交的SQL仅仅是描述出了我们的目的地是英国但至于怎么去通常我们的SQL中是没有给出提示信息的是由数据库来决定的 我们先简单的看一个执行计划的对比 SQL> set autotrace traceonly 执行计划一 SQL> select count(*) from t; COUNT(*)
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE SORT (AGGREGATE) TABLE ACCESS (FULL) OF T 执行计划二 SQL> select count(*) from t; COUNT(*)
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card=) SORT (AGGREGATE) INDEX (FULL SCAN) OF T_INDEX (NONUNIQUE) (Cost= Card=) 这两个执行计划中第一个表示求和是通过进行全表扫描来做的把整个表中数据读入内存来逐条累加第二个表示根据表中索引把整个索引读进内存来逐条累加而不用去读表中的数据但是这两种方式到底哪种快呢？通常来说可能二比一快但也不是绝对的这是一个很简单的例子演示执行计划的差异对于复杂的SQL（表连接嵌套子查询等）执行计划可能几十种甚至上百种但是到底那种最好呢？我们事前并不知道数据库本身也不知道但是数据库会根据一定的规则或者统计信息（statistics）去选择一个执行计划通常来说选择的是比较优的但也有选择失误的时候这就是这次讨论的价值所在 ORACLE优化器模式 ORACLE优化器有两大类基于规则的和基于代价的在SQLPLUS中我们可以查看init文件中定义的缺省的优化器模式 SQL> show parameters optimizer_mode NAME TYPEVALUE optimizer_mode string CHOOSE SQL> 这是ORACLE 企业版我们可以看出默认安装后数据库优化器模式为CHOOSE我们还可以设置为 RULEFIRST_ROWSALL_ROWS可以在init文件中对整个instance的所有会话设置也可以单独对某个会话设置 SQL> ALTER SESSION SET optimizer_mode= RULE; 会话已更改 SQL>ALTER SESSION SET optimizer_mode= FIRST_ROWS; 会话已更改 SQL>ALTER SESSION SET optimizer_mode= ALL_ROWS; 会话已更改 基于规则的查询数据库根据表和索引等定义信息按照一定的规则来产生执行计划基于代价的查询数据库根据搜集的表和索引的数据的统计信息（通过analyze 命令或者使用dbms_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划（实际上不一定最优）RULE是基于规则的CHOOSE表示如果查询的表存在搜集的统计信息则基于代价来执行(在CHOOSE模式下ORACLE采用的是 FIRST_ROWS)否则基于规则来执行在基于代价的两种方式中FIRST_ROWS指执行计划采用最少资源尽快的返回部分结果给客户端对于排序分页页显示这种查询尤其适用ALL_ROWS指以总体消耗资源最少的方式返回结果给客户端 基于规则的模式下数据库的执行计划通常比较稳定但在基于代价的模式下我们才有更大的机会选择最优的执行计划也由于ORACLE的很多查询方面的特性必须在基于代价的模式下才能体现出来所以我们通常不选择RULE(并且ORACLE宣称从 ORACLE i版本数据库开始将不再支持 RULE)既然是基于代价的模式也就是说执行计划的选择是根据表索引等定义和数据的统计信息来决定的这个统计信息是根据 analyze 命令或者dbms_stats包来定期搜集的首先存在着一种可能就是由于搜集信息是一个很消耗资源和时间的动作尤其当表数据量很大的时候因为搜集信息是对整个表数据进行重新的完全统计所以这是我们必须慎重考虑的问题我们只能在服务器空闲的时候定期的进行信息搜集这说明我们在一段时期内统计信息可能和数据库本身的数据并不吻合另外就是ORACLE的统计数据本身也存在着不精确部分（详细参考ORACLE DOCUMENT）更重要的一个问题就是及时统计数据相对已经比较准确但是ORACLE的优化器的选择也并不是始终是最优的方案这也倚赖于ORACLE对不同执行计划的代价的计算规则（我们通常是无法知道具体的计算规则的）这好比我们决定从香港还是从北京去英国车票机票等实际价格到底是怎么核算出来的我们并不知道或者说我们现在了解的价格信息在我们乘车前往的时候真实价格跟我们的预算已经发生了变化所有的因素都将影响我们的整个开销 执行计划稳定性能带给我们什么 ORACLE存在着执行计划选择失误的可能这也是我们经常遇见的一些现象比如总有人说我的程序在测试数据库中跑的很好但在产品数据库上就是跑的很差甚至后者硬件条件比前者还好这到底是为什么？硬件资源统计信息参数设置都可能对执行计划产生影响由于因素太多我们总是对未来怀着一种莫名的恐惧我的产品数据库上线后到底跑的好不好？于是ORACLE提供了一种稳定执行计划的能力也就是把在测试环境中的运行良好的执行计划所产生的OUTLINES移植到产品数据库使得执行计划不会随着其他因素的变化而变化 那么OUTLINES是什么呢？先要介绍一个内容ORACLE提供了在SQL中使用HINTS来引导优化器产生我们想要的执行计划的能力这在多表连接复杂查询中特别有效HINTS的类型很多可以设置优化器目标(RULECHOOSEFIRST_ROWSALL_ROWS)可以指定表连接的顺序可以指定使用哪个表的哪个索引等等可以对SQL进行很多精细的控制通过这种方式产生我们想要的执行计划的这些HINTSORACLE可以存储这些HINTS我们称之为OUTLINES通过STORE OUTLINES可以使得我们拥有以后产生相同执行计划的能力也就是使我们拥有了稳定执行计划的能力 这里想给出一个附加的说明就是实际上我们通过工具改写SQL比如使用SQLEXPERT改写后的SQL这些不仅仅是加了HINTS而且文本都已经发生了变化的SQL也可以存储OUTLINES并可被应用到应用中但这不是一定生效我们必须测试检查是否生效但由于就算给了错误的OUTLINES数据库在执行的时候也只是忽略过去重新生成执行计划而不会返回错误所以我们才敢放心的这么使用当然在ORACLE文档中并没有指明可以这样做文档中只是说明如果存在OUTLINES的同时又在SQL中加了HINTS则会使用OUTLINES而忽略HINTS这个功能在LECCO将发布的产品中会使用这一功能这样可以将SQL EXPERT的改写SQL的能力和稳定执行计划的能力结合起来那么我们就对不能更改源代码的应用具有了相当强大的SQL优化能力 也许我们会有疑问假如稳定了执行计划那还搜集统计信息干吗？这是因为几个原因造成的首先现在的执行计划对于未来发生了变化的数据未必就是合适的存在着当前的执行计划不满足未来数据的变化后的效率而新的统计信息的情况下所产生的执行计划也并不是全部都合理的那这个时候我们可以采用新搜集的统计信息但是却对新统计信息下不良的执行计划采用ORACLE提供的执行计划稳定性这个能力固定执行计划这样结合起来我们可以建立满意的高效的数据库运行环境 我们还需要关注的一个东西ORACLE提供的dbms_stats包除了具有搜集统计信息的能力还具有把数据库中统计信息（statistics）export/import的能力还具有只搜集统计信息而使得统计信息不应用于数据库的能力（把统计信息搜集到一个特定的表中而不是立即生效）在这个基础上我们就可以把统计信息export出来再import到一个测试环境中再运行我们的应用在测试环境中我们观察最新的统计信息会导致哪些执行计划发生变化（DB EXPERT的Plan Version Tracer是模拟不同环境并自动检查不同环境中执行计划变化的工具）是变好了还是变差了我们可以把变差的这一部分在测试环境中使用hints或者利用工具（SQL EXPERT是在重写SQL这一领域目前最强有力的工具）产生良好的执行计划的SQL利用这些SQL可以产生OUTLINES然后在产品数据库应用最新的统计信息的同时移植进这些OUTLINES 最后说一下我们不得不使用执行计划稳定性能力的场合我们假定ORACLE的优化器的选择都是准确的但是优化器选择的基础就是我们的SQL这些SQL才从根本上决定了运行效率这是更重要的一个优化的环节SQL是基础（当然数据库的设计是基础的基础）一个SQL写的好不好就相当于我们同样是要想去英国但是我的起点在珠海你的起点却在西藏的最边缘偏僻的一个地方那不管你做怎样的最优路线选择你都不如我在珠海去英国所花费的代价小由于这个原因通常如果是我们自己设计程序我们可以尝试着修改SQL代码但是如果应用程序是第三方开发的或者我们是在别人的基础 |
