Oracle RDBMS优化调整表连接

1、 2021-5-9oracle 数据库设计与性能2/53 10g9ir2 表的访问方式； oracle系统sql优化器； 调整表连接； 2021-5-9oracle 数据库设计与性能3/53 表的访问方式： 全表扫描-顺序读取每个数据块到末尾； hash(散列)获取-使用符合散列主键来为带有匹 配hash表中的记录创建rowid rowid访问-通过指定的rowid选定表的一个单 记录： rowid是最快的访问方式； rowid是oracle系统启动分配给表的每条记录的唯 一地址； 2021-5-9oracle 数据库设计与性能4/53 全表扫描： 顺序读取每个数据块到末尾，从中选择所有记 录

2、； 下面条件之一满足，oracle采用全表扫描： 当表不存在索引时； 当查询语句不包含where语句时； 当查询中使用like以%开始时； 引用函数索引时； 当使用基于cbo且表中的记录很少时； 当参数optimizer_mode=all_rows时； 2021-5-9oracle 数据库设计与性能5/53 hash访问： oracle 对多个表的cluster采用hash cluster存储，这 样两个表的记录存放在一个块内； hash访问是通过一个符号主键进行hash运算后得到散 列值(hash value)，该散列确定记录所在的块； 散列访问方法对于经常修改主符号键来说存在重定位记 录的

3、风险，所以建议在静态表的cluster中使用散列访 问方法(主键常改变不建议用)； oracle 的cluster另见9i 10g 分区与簇文档。 2021-5-9oracle 数据库设计与性能6/53 rowid访问： rowid访问是得到单个记录的最快方法； oracle 系统为每个表的每条记录自动分配 rowid，包括oooooofffbbbbbbrrr ： oooooo-对象的相对号； fff-文件的编号； bbbbbb-块的编号； rrr-块中的记录号； 2021-5-9oracle 数据库设计与性能7/53 索引访问方式： oracle有多种索引-b树索引、位图索引等； oracl

4、e 系统可使用下面索引，包括： 索引范围扫描； 单个索引扫描； 降序索引扫描； and_euql过滤器； 2021-5-9oracle 数据库设计与性能8/53 10g9ir2 表的访问方式； oracle系统sql优化器； 调整表连接； 2021-5-9oracle 数据库设计与性能9/53 sql优化器技术； 优化器模式； 基于规则的优化器调整； 基于成本的优化器调整(10g/11g)； 设置优化器模式； 迁移到基于成本的优化器调整； 2021-5-9oracle 数据库设计与性能10/53 sql优化器技术： sql优化器的目的是为sql语句生成最快、消耗资 源最少的执行计划； sql优

5、化器可以产生最快反应速度(first_rows模式)， 可产生最佳吞吐量的执行计划(all_rows模式) oracle提供基于规则(rbo)和基于成本(cbo)的优 化器模式； 设置optimizer_mode=choose时两种模式交替使用； 一般可通过alter session来设置模式，如： alter session set optimizer_goal= xxx； 2021-5-9oracle 数据库设计与性能11/53 query rewrite parse 选择优化 rbo / cbo query execution 产生执行计划 optimizer query result

6、2021-5-9oracle 数据库设计与性能12/53 基于规则的优化器(rbo)： 基本规则优化不使用表和索引的统计数据； rbo方法要探讨要实现最佳访问路径； rbo根据语句结构的不同来生成执行计划表； rbo采用迭代生成执行计划，并检查from后每个表以 及表间连接方式，根据每个执行路径所消耗的成本进行 排序，并选择最低的路径，下面是rbo步骤： 生成一个可执行计划列表(包含所有访问路径) ； 为每个执行计划指定级别数值； rbo选择级别低的计划； rbo对所有可连接的表与级别结果的连接作评估； 选择级别低的方法。 2021-5-9oracle 数据库设计与性能13/53 sql ex

7、ecution 分析分析 parser optimizer mode? 数据字典数据字典 dictionary cost-based optimizer rule-based optimizer row source generator 结果结果 统计数据统计数据 cbo rbo 查询计划查询计划 用户用户 2021-5-9oracle 数据库设计与性能14/53 rbo与sql操作： rbo将sql语句分为不同的级别，rbo根据这些级 别确定执行计划： 2021-5-9oracle 数据库设计与性能15/53 rbo的特性： 总是使用索引： 如果表有索引可用，则使用索引 ； 排序合并不使用索

8、引； 总是从驱动表开始： from最后的表为驱动表； 总是不可避免情况下，才用全表扫描： rbo一般都用索引 ； 特别指定不用索引(hint-提示)； 任何索引都可以用： rbo用索引不一定好 ； 有时越简单越好： 8i之前系统可提供好的执行计划 ； 9i后不建议采用rbo； 10g建议采用 optimizer_mode = all_rows； 2021-5-9oracle 数据库设计与性能16/53 cbo的特性： 使用统计数据和数据字典来确定代价； cbo只是一个数字处理程序，处理： 基本表访问代价； 所有数据源的访问方法； 并行是否可用； 连接的顺序与方法； optimizer_mode

9、 可以设置： choose, first_rows, 或 all_rows 10g/11g 默认为all_rows 2021-5-9oracle 数据库设计与性能17/53 cbo方法： 基于代价的优化方法是按如下几步来进行： a）优化器在可能的存取路径及用户提示的基础下制订 执行sql语句的计划。 b）根据表、cluster和索引在数据字典中存放的特性统 计信息和数据分布的统计信息,计算出执行每个计划的 代价。 c）比较各个计划执行的代价,取其代价最低者来执行。 10g/11g版本optimizer_mode默认为all_rows 2021-5-9oracle 数据库设计与性能18/53 t

10、able, cluster 统计: number of rows number of blocks number of empty blocks average row length 列统计: number of distinct values (ndv) in column number of nulls in column data distribution (histogram) 索引统计: number of leaf blocks levels clustering factor 2021-5-9oracle 数据库设计与性能19/53 表的访问方式； oracle系统sql优化器；

11、 调整表连接； 2021-5-9oracle 数据库设计与性能20/53 oracle 查询语句中from 子句： from子句最后的表(driving table)将被最先处理 driving table驱动表，有时叫基础表或外部表 select /select /* * example example * */ from big,small/ from big,small where big.object_id= samll_object_idwhere big.object_id= samll_object_id call count cpucall count cpu elapsed

12、disk query elapsed disk query - - - - - - - - - - - parse 1 0.00 0.00parse 1 0.00 0.00 0 0 0 0 excute 1 0.00 0.00excute 1 0.00 0.00 0 0 0 0 fetch 32428 3.38 3.21 0 46977fetch 32428 3.38 3.21 0 46977 - - - - - - - - - - - total 32430 3.38 3.22 0 46977total 32430 3.38 3.22 0 46977 rows row source oper

13、ationrows row source operation - - - 486400 hash join 486400 hash join 1000 table access full samll 1000 table access full samll 485400 table access full big 485400 table access full big 记录少的表作 为驱动表 driving table driven (inner) table 2021-5-9oracle 数据库设计与性能21/53 ansi表连接标准： 等价连接： 是标准连接，其中两个表的一对记录通过一个

14、公 共字段的匹配进行连接 等价连接的oracle 表访问计划可以是nest loops，hash join或merge join 外部连接： 是一个确保不完整记录的连接，两个表不存在完 全匹配条件 oracle 返回满足条件的所有记录 概念介绍参考10g sql-高级查询 2021-5-9oracle 数据库设计与性能22/53 ansi表连接标准： 自连接： 是一种表和自身连接的特殊情况 例如emp表中mgr与empno列就可进行自我连接 反连接： 当使用带有not in或not exist子句的子查询时，经常 采用反连接 反连接经常是table access full 访问方式 半连接：

15、半连接返回满足包含exist子句的查询记录，即使条件右 边有多条记录满足子查询的条件，该连接也不会复制谓词 左边的记录 半连接经常是table access full 访问方式 2021-5-9oracle 数据库设计与性能23/53 oracle表连接方式，主要前3种： 1.嵌套循环(nested loops -nl ) 2.排序-合并连接(sort merge join -smj) 3.哈希连接(hash join) 4.星型连接(star join)-数据仓库常用 oracle 9i/10g cbo下支持的连接： nested loops join outer join sort-mer

16、ge join hash join anti-join(反连接) semi-join(半连接) 2021-5-9oracle 数据库设计与性能24/53 表连接方式-嵌套连接： 嵌套循环(nested loops, nl) 概念： 这种连接方法有驱动表的概念，该连接过程就是一个2层嵌套 循环； 外层循环的次数越少越好(将小表或返回较小行表作为驱动表- 用于外层循环) 外层循环的次数并不能总保证使语句产生的i/o次数最少，有 时不遵守这个理论反而会获得更好的效率 内部连接过程(表a：row_source1、表b：row_source2)： row source1的的row 1 -探查探查-row

17、 source 2 row source1的的row 2 -探查探查-row source 2 row source1的的row 3 -探查探查-row source 2 . row source1的的row n -探查探查-row source 2 2021-5-9oracle 数据库设计与性能25/53 表连接方式-嵌套连接(续)： 嵌套循环(nested loops, nl) 概念： row source1为驱动表或外部表。row source2 被称为被探查表或内部表； 在nl连接中，oracle读取row source1中的每一行， 然后在row sourc2中检查是否有匹配的行，所

18、有 被匹配的行都被放到结果集中，然后处理row source1中的下一行； 如果driving row source(外部表)比较小，并且在 inner row source(内部表)上有唯一索引或有高选 择性非唯一索引时，使用这种方法可以得到较好 的效率； nl有其它连接方法没有的一个优点是：可以先返 回已经连接的行，而不必等待所有的连接操作处 理完才返回数据，这可以实现快速的响应时间。 2021-5-9oracle 数据库设计与性能26/53 表连接方式-嵌套连接(续)： 嵌套循环(nested loops, nl) 概念： 如果不使用并行，可在驱动表加where 条件以返回较少行数据 大

19、表也可能作为驱动表，关键看限制条件； 对于并行查询，可选大表作为驱动表(充分利用并行)； 有时使用并行操作反而效率低(如该表有很少的行符合条件) 硬件配置是否支持并行(如是否有多个cpu，多个硬盘控制器)， 要具体问题具体对待。下面是nl连接的例子： sql explain plan for select a.dname, b.sqlfrom dept a,emp bwhere a.deptno = b.deptno; query plan - select statement choose cost=5nested loopstable access full dept analyzedta

20、ble access full emp analyzed 2021-5-9oracle 数据库设计与性能27/53 先扫描row source 1 (外 部表 即驱动表) 每个行再驱动内部表的 row source 2 返回连接成功的行 代价:读驱动表然后访问 内部表. 性能依赖于内部表的索引 outer loop inner loop check for a match nested loop access a (full) access b (rowid) index access 2021-5-9oracle 数据库设计与性能28/53 嵌套连接: nested loops样例样例 20

21、21-5-9oracle 数据库设计与性能29/53 row source 1 row source 2 sortsort merge 表连接方式-排序合并连接(sort merge join )： sml步骤为： 1) 首先生成表a(row source1)需 要的数据，然后对这些数据按照 连接操作关联列进行排序。 2) 随后生成表b(row source2)需 要的数据，然后对这些数据按照 与sort source1对应的连接操作关 联列进行排序(后面示意图后面示意图)。 3) 最后两边已排序的行被放在一 起执行合并操作，即将2个row source按照连接条件连接起来(即 合并) 202

22、1-5-9oracle 数据库设计与性能30/53 排序合并连接: sort merge示意-注意左右两个表都先排序再比较 2021-5-9oracle 数据库设计与性能31/53 表连接方式-(hash join)哈希连接： 理论上来说比nl与smj要高效，而且只用在cbo优化器中； 较小的row source1被用来构建hash table与bitmap，row source2被用于散列算法，并与row source1生成的hash table 进行匹配，以便进行进一步连接。 bitmap被用来作为一种比较快的查找方法，检查在hash table 中是否有匹配的行。 当hash 表较大而内

23、存不能存放时，这种查找方法更为有用。 这种连接方法也有类似nl连接中驱动表的概念(构建hash table与bitmap表=驱动表),当被构建的hash table与bitmap能被 容纳在内存中时，这种连接方式的效率极高。 sql explain plan for select /sql explain plan for select /* *+ use_hash(emp+ use_hash(emp) ) * */ / empno from emp, deptwhere emp.deptno = dept.deptnoempno from emp, deptwhere emp.deptno

24、= dept.deptno; ; query planquery plan - select statementchoose cost=3select statementchoose cost=3 hash jointablehash jointable access full depttable access full emp access full depttable access full emp 2021-5-9oracle 数据库设计与性能32/53 表连接方式-hash连接(续)： 要使hash连接有效，需要设置 hash_join_enabled=true，默认为true； 还要

25、设置hash_area_size参数，以使哈希连接 高效运行，因为哈希连接会在该参数指定大小的内 存中运行，过小的参数会使哈希连接的性能比其他 连接方式还要低； hash_join_enabled在10g/11g 为过失的参数， 建议不要设置该参数。 2021-5-9oracle 数据库设计与性能33/53 最小的表用来做为hash 表和bitmap 第2个行源表做hsah算 法并与hash表比对 在hash表中，位图是快 速反应搜索行的方法 每个源行只需要单次即完 成，它比排序-合并高效 row source 1 (build input) row source 2 (probe) hash

26、_area_size output rows temp 2021-5-9oracle 数据库设计与性能34/53 hash连接: hash join 样例 2021-5-9oracle 数据库设计与性能35/53 三种连接方式比较： 排序 -合并连接(sort merge join, smj)： 对于非等值连接，这种连接方式的效率是比较高的； 如果在关联的列上都有索引，效果更好； 对于将2个较大的row source连接，比nl连接要好一些； 如果sort merge返回的row source过大，导致使用过多的rowid在 表中查询数据时，数据库性能下降(过多的i/o) 嵌套循环(neste

27、d loops, nl)： 如果 外部表 比较小，并且在 内部表 上有唯一索引，或有高选 择性非唯一索引时，使用这种方法可以得到较好的效率。 nl比其它连接方法多优点：可先返回已经连接的行，而不必等 待所有的连接操作处理完。 哈希连接(hash join, hj)： 这种方法好于其它2种连接(cbo优化器)，而且需要设置合适的 hash_area_size参数，才能取得较好的性能。 在2个较大的row source之间连接时会取得相对较好的效率，在 一个row source较小时则能取得更好的效率。 2021-5-9oracle 数据库设计与性能36/53 三种连接方式欢迎程度(高到低)： 1

28、.嵌套连接； 2.排序连接； 3.hash连接； 排序连接与hash连接类似； 下面是相对优越性： 连接方式连接方式优越性优越性是否排序是否排序大记录子集大记录子集是否需索引是否需索引 排序合并连接排序合并连接更大的吞吐量更大的吞吐量排序排序快快不需要不需要 hash连接连接 嵌套连接嵌套连接更短的反应时间更短的反应时间不排序不排序慢慢需要需要 2021-5-9oracle 数据库设计与性能37/53 三种连接方法建议： 在sql下直接调试： sqlset timing on 执行各语句，观察所用的时间 时间一般为毫秒级：时：分：秒.毫秒 采用sql跟踪方式： 对复杂的程序采用跟踪 对结果的分

29、析 2021-5-9oracle 数据库设计与性能38/53 sql结果的排序问题： sql语句处理比我们想象的要复杂，可与 sql*plus结合使用，以处理排序、转换、格式 化等； oracle提供多种对结果进行排序： order by ； join； group by ； aggregate(聚集运算) ； select unique； select distinct； create index ； 2021-5-9oracle 数据库设计与性能39/53 oracle 系统允许对优化器进行提示 提示符号为/*+ hint */ 提示可使优化器按照开发人员的进行执行计划 9i/10g可允许

30、下面的提示： 优化路径与目标进行提示 查询转换与访问路径进行提示 连接顺序进行提示 连接的操作进行提示 并行执行进行提示 附加的其他提示 2021-5-9oracle 数据库设计与性能40/53 oracle 表连接可采用优化器提示： use_hash hash连接 use_merge 合并连接 star 星形连接 merge_aj 合并反连接 hash_aj hash反连接(hash join anti-散 列反连接 ) 2021-5-9oracle 数据库设计与性能41/53 使用use_hash提示： use_hash提示将对指定表执行hash连接， 下面是一个使用提示强制执行hash连

31、接的 并行查询例子： select /*+ ordered use_hash(e,b) parallel(e,4) parallel(b,4) */ e.ename, hiredate,m from bouus b,emp e where e.ename = b.ename ; 2021-5-9oracle 数据库设计与性能42/53 使用use_merge提示： use_merge提示强制调用排序合并连接操作； 这种方法常和并行一起使用，如： 注意提示内的表别名 select /*+ use_merge(e,b) parallel(e,4) parallel(b,4) */ e.ename,

32、 hiredate,m from bouus b,emp e where e.ename = b.ename ; 2021-5-9oracle 数据库设计与性能43/53 使用hash_aj提示： 在子查询中使用hash_aj提示，如： delete from stats$sqltext st where (hash_value, text_subset) not in (select -+ hash_aj hash_value, text_subset from stats$sql_summary ss where ( ( snap_id :hi_snap ) and dbid = :dbi

33、d and instance_number = :inst_num ) or ( dbid != :dbid or instance_number != :inst_num) ) 2021-5-9oracle 数据库设计与性能44/53 优化器自动采用的嵌套连接： 档你没有明确提示连接方式时；优化器也 会采用认为是最优的连接，如： select * from reserves r, sailors s where r.sid=s.sid; - 0 select statement optimizer=choose 1 0 nested loops 2 1 table access (full)

34、 of reserves 3 1 table access (by index rowid) of sailors 4 3 index (unique scan) of sys_c00628777 (unique) 2021-5-9oracle 数据库设计与性能45/53 cbo根据统计数据自动使用nl： cbo经常自动使用小表作为驱动表，如： select samlee.object_id,big.ownerselect samlee.object_id,big.owner from samll,bigfrom samll,big where samll.object_id=big.obje

35、ct_idwhere samll.object_id=big.object_id and samll.object_type and samll.object_type=java resource=java resource rows row source operationrows row source operation - - - 13312 table access by index rowid big 13312 table access by index rowid big 13339 nest loops 13339 nest loops 26 table access full

36、 samll 26 table access full samll 13312 index range sacn big_object_id(object 13312 index range sacn big_object_id(object id 33423) id 33423) 2021-5-9oracle 数据库设计与性能46/53 cbo下的提示first_rows_n： oracle 9i 版本前，只有all_rows与 first_rows oracle 9i 开始first_rows_1、 first_rows_10、first_rows_100、 first_rows_1000

37、 根据用户的要求进行参数的修改缩短反应时间 使用first_rows_n最优化，oracle查询能够使 用最少的反应时间来给出最初的n行结果。 2021-5-9oracle 数据库设计与性能47/53 提示提示说明说明 full表明对表选择全局扫描的方法 rowid提示明确表明对指定表根据rowid进行访问 cluster提示明确表明对指定表选择簇扫描的访问方法,它只 对簇对象有效 index表明对表选择索引的扫描方法 index_asc表明对表选择索引升序的扫描方法 index_desc表明对表选择索引降序的扫描方法 index_join提示明确命令优化器使用索引作为访问路径 index_ffs对指定的表执行快速全索引扫描 no_index提示不要用索引 and_equal提示优化器使用合并索引 2021-5-9oracle 数据库设计与性能48/53 提示 索引合并and_equal ： 如果一个表创建了多个索引，则可使用and_equal 实现索引的合并： 2021-5-9oracle 数据库设计与性能49/53 提示提示说明说明 use_concat