oracle查询优化--修改执行计划_20090425.pptVIP

oracle查询优化 2009.04.25 --改变oracle执行计划 张磊 目录 优化的目标 认识优化的环境 基于规则的优化程序 基于成本的优化程序 优化模式的选择 Oracle表的连接及调整 嵌套循环连接 哈希连接 合并连接 跟表连接相关的hints 索引 实例 优化的目标 什么是优化 简单点说就是增加吞吐量，减少响应时间, 是一种利用已经证实过的方法, 反复试验/判断和实现优化方案的循序渐进的过程. 也就是说优化很多情况下要依靠我们对数据库的已有认识. 但这种认识未必是正确的. 为什么要优化 系统在设计之初没有对应用的可靠理解； 系统经过初始编码之后都对设计做过大的变动； 随着数据量的增加，原来的索引性能下降； 数据的分布不再均匀 优化到何种程度，何时停止优化 对oracle的优化可能会过头，系统的组件之间互相依赖， 更改一个查询有可能会妨碍另外一个的性能。 所以我们在执行优化之前一定要制定合适的目标。 认识优化的环境 基于规则的优化(RBO) 在oracle7.0版本之前, oracle内部只存在基于规则的优化程序. 优化器在分析SQL语句时,所遵循的是oracle内部预定的一些规则. . 规则1(强规则) oracle在建立执行树时,其关联数序是按照表在from后面出现的顺序按照倒叙排列的,这一点似乎正确.至少,大部分情况下,在from后面最后出现的表一般会作为第一个驱动表.但这不是绝对的, 规则2(弱规则) oracle为了避免更大的结果和另外一个未知(没执行analyze对表进行分析)大小的结果进行嵌套循环连接,会按照扫描表,扫描索引然后访问表,直接访问索引可以得到数据.这样的顺序对表进行关联. 比如说,如果一个sql查询语句中,需要对表进行acess full,那么其顺序肯定是靠前的,而相对于可以直接在index中获得所有的结果, TABLE ACCESS BY INDEX ROWID的表顺序也比较靠前.在这之后才会考虑表在from后面出现的位置 认识优化的环境 基于成本的优化(CBO) 从oracle7.3开始, oracle引入CBO. 它是看sql语句执行的代价来选择执行语法树的. 这里的代价主要指CPU和内存. 优化器在判断是否用某种执行方式时, 主要参照的是表及索引的统计信息. 统计信息存储在oracle的动态性能试图中, 记录了表的大小, 有多少行, 每行的长度等信息. 这些信息起初在库内是没有的. 需要定期对表对象和索引对象执行analyze后才出现. 很多的时候过期统计信息会令优化器做出一个错误的执行计划. SQL desc dba_tables; Name Default Comments ------------------------- ------------ TABLE_NAME Name of the table NUM_ROWS The number of rows in the table BLOCKS The number of used blocks in the table EMPTY_BLOCKS The number of empty (never used) blocks in the table AVG_SPACE The average available free space in the table AVG_ROW_LEN The average row length, including row overhead NUM_FREELIST_BLOCKS The number of blocks on the freelist 认识优化的环境 优化模式的选择 Oracle的优化模式有四种: Rule: 基于规则的方式; Choose: 默认情况下oracle使用的方式. 指的是当一个表或索引有统计信息,则走cbo的方式, 如果表或索引没有统计信息, 表又不是特别的小, 而且相应的列有索引时, 那么走rbo方式. First rows: 与choose方式类似, 不同处在于它将以最快的方式返回查询的最先的几行. All rows: 也就是所谓的cbo模式. 优化模式的设置 /*+ all_rows */ all_rows /*+ first_rows */ first_rows /*+ choo