深入解析oracle:有关Oracle中执行计划稳定性深入研究来源: 发布时间:星期日, 2009年1月18日 浏览:31次 评论:0
="t18">
什么是执行计划
所谓执行计划
 顾名思义就是对 个查询任务做出份怎样去完成任务 具体方案 举个生活中例子我从珠海要去英国我可以选择先去香港然后转机也可以先去北京转机或者去广州也可以
但是到底怎样去英国划算也就是我费用最少这是件值得考究事情同样对于查询而言我们提交SQL仅仅是描述出了我们目地是英国但至于如何去通常我们SQL中是没有给出提示信息是由数据库来决定
我们先简单看个执行计划对比:
SQL>  autotrace traceonly执行计划 :SQL> select count(*) from t; COUNT(*) ---------- 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE Access (FULL) OF 'T' 执行计划 2: SQL> select count(*) from t; COUNT(*) 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 Card=28180) 这两个执行计划中 第个表示求和是通过进行全表扫描来做把整个表中数据读入内存来逐条累加；第 2个表示根据表中索引把整个索引读进内存来逐条累加而不用去读表中数据但是这两种方式到底哪种快呢？通常来说可能 2比快但也不是绝对这是个很简单例子演示执行计划差异对于复杂SQL(表连接、嵌套子查询等)执行计划可能几十种甚至上百种但是到底那种最好呢？我们事前并不知道数据库本身也不知道但是数据库会根据定规则或者统计信息(statistics)去选择个执行计划通常来说选择是比较优但也有选择失误时候这就是这次讨论价值所在
Oracle优化器模式
Oracle优化器有两大类基于规则和基于代价在SQLPLUS中我们可以查看init文件中定义缺省优化器模式
SQL> show parameters optimizer_mode NAME TYPE VALUE optimizer_mode  CHOOSESQL> 这是Oracle8.1.7 企业版 我们可以看出默认安装后数据库优化器模式为CHOOSE,我们还可以设置为 RULE、FIRST_ROWS,ALL_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 10i版本数据库开始将不再支持 RULE)既然是基于代价模式也就是说执行计划选择是根据表、索引等定义和数据统计信息来决定这个统计信息是根据 analyze 命令或者dbms_stats包来定期搜集首先存在着种可能就是由于搜集信息是个很消耗资源和时间动作尤其当表数据量很大时候 搜集信息是对整个表数据进行重新完全统计所以这是我们必须慎重考虑问题我们只能在服务器空闲时候定期进行信息搜集这介绍说明我们在段时期内统计信息可能和数据库本身数据并不吻合；另外就是Oracle统计数据本身也存在着不精确部分(具体参考Oracle DOCUMENT)更重要个问题就是及时统计数据相对已经比较准确但是Oracle优化器选择也并不是始终是最优方案这也倚赖于Oracle对区别执行计划代价计算规则(我们通常是无法知道具体计算规则)这好比我们决定从香港还是从北京去英国车票、机票等实际价格到底是如何核算出来我们并不知道或者说我们现在了解价格信息在我们乘车前往时候真实价格跟我们预算已经发生了变化所有原因都将影响我们整个开销
执行计划稳定性能带给我们什么 Oracle存在着执行计划选择失误 可能这也是我们经常遇见些现象比如总有人说我 在测试数据库中跑很好但在产品数据库上就是跑很差甚至后者硬件条件比前者还好这到底是为什么？硬件资源、统计信息、参数设置都可能对执行计划产生影响由于原因太多我们总是对未来怀着种莫名惧怕我产品数据库上线后到底跑好不好？于是Oracle提供了种稳定执行计划能力也就是把在测试环境中运行良好执行计划所产生OUTLINES移植到产品数据库使得执行计划不会随着其他原因变化而变化
那么OUTLINES是什么呢？先要介绍个内容Oracle提供了在SQL中使用HINTS来引导优化器产生我们想要执行计划能力这在多表连接、复杂查询中非凡有效HINTS类型很多可以设置优化器目标(RULE、CHOOSE、FIRST_ROWS、ALL_ROWS)可以指定表连接顺序可以指定使用哪个表哪个索引等等可以对SQL进行很多精细控制通过这种方式产生我们想要执行计划这些HINTS,Oracle可以存储这些HINTS我们称的为OUTLINES通过STORE OUTLINES可以使得我们拥有以后产生相同执行计划能力也就是使我们拥有了稳定执行计划能力
这里想给出个附加介绍说明就是实际上我们通过工具改写SQL比如使用SQL EXPERT改写后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 EXPERTPlan Version Tracer是模拟区别环境并自动检查区别环境中执行计划变化工具)是变好了还是变差了我们可以把变差这部分在测试环境中使用h s或者利用工具(SQL EXPERT是在重写SQL这领域目前最强有力工具)产生良好执行计划SQL利用这些SQL可以产生OUTLINES,然后在产品数据库应用最新统计信息同时移植进这些OUTLINES
最后说下我们不得不使用执行计划稳定性能力场合我们假定Oracle优化器选择都是准确但是优化器选择基础就是我们SQL,这些SQL才从根本上决定了运行效率这是更重要个优化环节SQL是基础(当然数据库设计是基础基础)个SQL写好不好就相当于我们同样是要想去英国但是我起点在珠海你起点却在西藏最边缘偏僻个地方那不管你做怎样最优路线选择你都不如我在珠海去英国所花费代价小
0
相关文章读者评论发表评论 |
|
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
