oracle查询优化
--改变oracle执行计划
2009.04.25 张磊
目录
• 优化的目标 • 认识优化的环境 • 基于规则的优化程序
• • 基于成本的优化程序 优化模式的选择
• Oracle表的连接及调整 • 嵌套循环连接
• • • 哈希连接 合并连接 跟表连接相关的hints
• 索引 • 实例
100 200 300 400
..
100 101 .. 200 .. 300 .. 400 ..
• 优势
• • • • 1，对于非等值连接，效果较高， 2，如果关联列上都有索引，效果更好； 3，如果两个row source都比较大，本方法要稍好于NL； 4，如果返回的row source过大则又会导致过多的IO。
•
•
Oracle表的连接方式及调整
• 排序合并连接(merge join)的执行方式
• • • 1，先生成驱动数据，然后对这些数据按照连接操作关联列进行排序； 2，生成被探查数据，然后对这些数据按照连接操作关联列进行排序； 3，对已经排序的两个结果放在一起执行合并操作。 .
1 2
• 缺点
• • 排序是一个费时、费资源的操作，特别对于大表。 基于这个原因，SMJ经常不是一个特别有效的连接方法，
认识优化的环境
• 基于成本的优化(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表的连接方式及调整
• 嵌套循环连接(nested loop)的执行方式
• • • 1、 Oracle首先选择一张表作为连接的驱动表 2、 逐条提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的 记录. Nested loop Outer loop Inner loop
Oracle表的连接方式及调整
• 哈希(hash join)连接
自从oracke 7.3以来，oracle提供了一种新的join技术，就是hash join。Hash Join只 能用于相等连接，且只能在CBO优化器模式下。相对于nested loop join，hash join更 适合处理大型结果集。Hash join不需要在驱动表上存在索引。
• •
• 优化模式的设置
优化模式 rule choose first_rows all_rows 修改initsid.ora设置 optimizer_mode= rule/choose/first_rows/ all_rows alter session set optimizer_mode =rule/choose/first_ro ws/all_rows instance session sql /*+ rule */ /*+ choose */ /*+ first_rows */ /*+ all_rows */
• • 表示对表t1、t2关联时采用嵌套循环连接。 select /*+ use_nl(dpi,psj)*/ dpi.model_name,psj.day_sale from dw.d_product_info dpi, dw.pm_stock_sale_jyb psj where dpi.product_id = psj.product_id
B0 0,0,10,1 0 √
B1 1,1,1,1,1 1
B2 2,2, 2
B 3 3
B 4 4
B5 5
B 6 6
B 7 7
B 8 8
B9 9,9, 9
√
√ √
S5
S6 S7 S8
5
NUL L NUL L 8,8,8, 8
√
√
S9
NUL L
Oracle表的连接方式及调整
• 哈希(hash join)连接步骤
hash join的要求和优势
• • • 哈希连接比较适用于返回大数据量结果集的连接。 使用哈希连接必须是在CBO模式下，参数hash_join_enabled设置为true， 哈希连接只适用于等值连接
跟表连接相关的hints
• • • • • 测试环境 数据库版本： oracle9.2.8.0 系统环境 ：aix 数据库实例：BI 查询代码
• 规则1(强规则)
• oracle在建立执行树时,其关联数序是按照表在from后面出现的顺序按照倒叙排列的, 这一点似乎正确.至少,大部分情况下,在from后面最后出现的表一般会作为第一个驱动表. 但这不是绝对的,
• 规则2(弱规则)
• • oracle为了避免更大的结果和另外一个未知(没执行analyze对表进行分析)大小的结果进 行嵌套循环连接,会按照扫描表,扫描索引然后访问表,直接访问索引可以得到数据.这样 的顺序对表进行关联. 比如说,如果一个sql查询语句中,需要对表进行acess full,那么其顺序肯定是靠前的,而 相对于可以直接在index中获得所有的结果, TABLE ACCESS BY INDEX ROWID的表 顺序也比较靠前.在这之后才会考虑表在from后面出现的位置
• Hash join 原理
• • • • • 考虑以下两个数据集, 假设hash函数为求余函数。 S={1,1,1,3,3,4,4,4,4,5,8,8,8,8,10} B={0,0,1,1,1,1,2,2,2, 3,4,5,6,7,8,9,9,9,10,10,11}
分 区 值 S0 S1 S2 S3 S4 10 1,1,1 Null 3,3 4,4,4, 4
• use_merge（t1,t2）
• • 表示对表t1、t2关联时采用排序合并连接。 select /*+ use_merge(dpi,psj)*/ dpi.model_name,psj.day_sale from dw.d_product_info dpi, dw.pm_stock_sale_jyb psj where dpi.product_id = psj.product_id
• • • • • • • • • •
认识优化的环境
• 优化模式的选择
• • • Oracle的优化模式有四种: Rule: 基于规则的方式; Choose: 默认情况下oracle使用的方式. 指的是当一个表或索引有统计信息,则走cbo的 方式, 如果表或索引没有统计信息, 表又不是特别的小, 而且相应的列有索引时, 那么走 rbo方式. First rows: 与choose方式类似, 不同处在于它将以最快的方式返回查询的最先的几行. All rows: 也就是所谓的cbo模式.
select dpi.model_name,psj.day_sale from dw.d_product_info dpi, dw.pm_stock_sale_jyb psj where dpi.product_id = psj.product_id;
•
跟表连接相关的hints
• use_nl（t1,t2）
考虑查询 select * from t1,t2 where t1.f1 = t2.f1
Oracle表的连接方式及调整
• 驱动表和被探查表的概念
• 驱动表又称为外层表（outer table），这个概念用于嵌套与hash连接中，如果驱动 表row source返回较多的行数据，则对所有的后续操作都有负面的影响，因为将大的 row source作为驱动表时在后续的操作中返回的中间结果都比较大。这是比较容易理解 的。 另外在驱动表的选择上，应该选择经限制条件选择之后结果集比较小的row source 作为驱动表，而不是单纯选择原始数据较小的数据表作为driving table。 驱动表的识别，在执行计划那个，靠上的那个row source应该是驱动表，一般都是 在from关键字后靠前位置的表。
1、 构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进行计算 产生一张哈希表。通常这个步骤是在内存（hash_area_size）里面进行的，因此运算 很快。 2、 探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面 形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值 没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测