OracleRDBMS优化-调整表连接.ppt

,oracle rdbms 优化-调整表连接,赵元杰 中程在线(北京)科技有限公司 2009.8,2019/4/8,oracle 数据库设计与性能,2/53,内容提要,表的访问方式； oracle系统sql优化器； 调整表连接；,2019/4/8,oracle 数据库设计与性能,3/53,表的访问方式,表的访问方式： 全表扫描-顺序读取每个数据块到末尾； hash(散列)获取-使用符合散列主键来为带有匹配hash表中的记录创建rowid rowid访问-通过指定的rowid选定表的一个单记录： rowid是最快的访问方式； rowid是oracle系统启动分配给表的每条记录的唯一地址；,2019/4/8,oracle 数据库设计与性能,4/53,表的访问方式,全表扫描： 顺序读取每个数据块到末尾，从中选择所有记录； 下面条件之一满足，oracle采用全表扫描： 当表不存在索引时； 当查询语句不包含where语句时； 当查询中使用like以%开始时； 引用函数索引时； 当使用基于cbo且表中的记录很少时； 当参数optimizer_mode=all_rows时；,2019/4/8,oracle 数据库设计与性能,5/53,表的访问方式,hash访问： oracle 对多个表的cluster采用hash cluster存储，这样两个表的记录存放在一个块内； hash访问是通过一个符号主键进行hash运算后得到散列值(hash value)，该散列确定记录所在的块； 散列访问方法对于经常修改主符号键来说存在重定位记录的风险，所以建议在静态表的cluster中使用散列访问方法(主键常改变不建议用)； oracle 的cluster另见9i 10g 分区与簇文档。,2019/4/8,oracle 数据库设计与性能,6/53,表的访问方式,rowid访问： rowid访问是得到单个记录的最快方法； oracle 系统为每个表的每条记录自动分配rowid，包括oooooofffbbbbbbrrr ： oooooo-对象的相对号； fff-文件的编号； bbbbbb-块的编号； rrr-块中的记录号；,2019/4/8,oracle 数据库设计与性能,7/53,表的访问方式,索引访问方式： oracle有多种索引-b树索引、位图索引等； oracle 系统可使用下面索引，包括： 索引范围扫描； 单个索引扫描； 降序索引扫描； and_euql过滤器；,2019/4/8,oracle 数据库设计与性能,8/53,内容提要,表的访问方式； oracle系统sql优化器； 调整表连接；,2019/4/8,oracle 数据库设计与性能,9/53,sql优化器介绍,sql优化器技术； 优化器模式； 基于规则的优化器调整； 基于成本的优化器调整(10g/11g)； 设置优化器模式； 迁移到基于成本的优化器调整；,2019/4/8,oracle 数据库设计与性能,10/53,sql优化器介绍,sql优化器技术： sql优化器的目的是为sql语句生成最快、消耗资源最少的执行计划； sql优化器可以产生最快反应速度(first_rows模式)，可产生最佳吞吐量的执行计划(all_rows模式) oracle提供基于规则(rbo)和基于成本(cbo)的优化器模式； 设置optimizer_mode=choose时两种模式交替使用；一般可通过alter session来设置模式，如： alter session set optimizer_goal= xxx；,2019/4/8,oracle 数据库设计与性能,11/53,query rewrite,parse,选择优化 rbo / cbo,query execution,产生执行计划,optimizer,query,result,select 语句的执行过程(略),2019/4/8,oracle 数据库设计与性能,12/53,sql优化器介绍-rbo (略),基于规则的优化器(rbo)： 基本规则优化不使用表和索引的统计数据； rbo方法要探讨要实现最佳访问路径； rbo根据语句结构的不同来生成执行计划表； rbo采用迭代生成执行计划，并检查from后每个表以及表间连接方式，根据每个执行路径所消耗的成本进行排序，并选择最低的路径，下面是rbo步骤： 生成一个可执行计划列表(包含所有访问路径) ； 为每个执行计划指定级别数值； rbo选择级别低的计划； rbo对所有可连接的表与级别结果的连接作评估； 选择级别低的方法。,2019/4/8,oracle 数据库设计与性能,13/53,sql execution,分析 parser,optimizer mode?,数据字典 dictionary,cost-based optimizer,rule-based optimizer,row source generator,结果,统计数据,cbo,rbo,查询计划,用户,select 语句的处理过程(略),2019/4/8,oracle 数据库设计与性能,14/53,sql优化器介绍-rbo (略),rbo与sql操作： rbo将sql语句分为不同的级别，rbo根据这些级别确定执行计划：,1. rowid单行读取 2. cluster单行连接读取 (cluster) 3. cluster单行hash连接读取 (cluster) 4. 使用唯一索引的单行读取 5. cluster 连接 (cluster) 6. hash cluster连接 (cluster) 7. cluster key索引连接 (cluster) 8. 复合键,9. 单列非唯一索引. 10. 索引列的范围搜索 11.索引列的无范围搜索 12. 排序合并连接 13. 索引列的max 或 min 14.索引列的order by 15. 全表扫描.,2019/4/8,oracle 数据库设计与性能,15/53,sql优化器介绍-rbo (略),rbo的特性： 总是使用索引： 如果表有索引可用，则使用索引 ； 排序合并不使用索引； 总是从驱动表开始： from最后的表为驱动表； 总是不可避免情况下，才用全表扫描： rbo一般都用索引 ； 特别指定不用索引(hint-提示)； 任何索引都可以用： rbo用索引不一定好 ； 有时越简单越好： 8i之前系统可提供好的执行计划 ； 9i后不建议采用rbo； 10g建议采用 optimizer_mode = all_rows；,2019/4/8,oracle 数据库设计与性能,16/53,sql优化器介绍-cbo,cbo的特性： 使用统计数据和数据字典来确定代价； cbo只是一个数字处理程序，处理： 基本表访问代价； 所有数据源的访问方法； 并行是否可用； 连接的顺序与方法； optimizer_mode 可以设置： choose, first_rows, 或 all_rows 10g/11g 默认为all_rows,2019/4/8,oracle 数据库设计与性能,17/53,sql优化器介绍-cbo,cbo方法： 基于代价的优化方法是按如下几步来进行： a）优化器在可能的存取路径及用户提示的基础下制订执行sql语句的计划。 b）根据表、cluster和索引在数据字典中存放的特性统计信息和数据分布的统计信息,计算出执行每个计划的代价。 c）比较各个计划执行的代价,取其代价最低者来执行。 10g/11g版本optimizer_mode默认为all_rows,2019/4/8,oracle 数据库设计与性能,18/53,sql优化器介绍-cbo需要,table, 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,2019/4/8,oracle 数据库设计与性能,19/53,内容提要,表的访问方式； oracle系统sql优化器； 调整表连接；,2019/4/8,oracle 数据库设计与性能,20/53,表连接概念,oracle 查询语句中from 子句： from子句最后的表(driving table)将被最先处理 driving table驱动表，有时叫基础表或外部表,select /* example */ from big,small where big.object_id= samll_object_id call count cpu elapsed disk query - - - - - - parse 1 0.00 0.00 0 0 excute 1 0.00 0.00 0 0 fetch 32428 3.38 3.21 0 46977 - - - - - - total 32430 3.38 3.22 0 46977 rows row source operation - - 486400 hash join 1000 table access full samll 485400 table access full big,记录少的表作为驱动表,driving table,driven (inner) table,2019/4/8,oracle 数据库设计与性能,21/53,调整表连接-ansi表连接,ansi表连接标准： 等价连接： 是标准连接，其中两个表的一对记录通过一个公共字段的匹配进行连接 等价连接的oracle 表访问计划可以是nest loops，hash join或merge join 外部连接： 是一个确保不完整记录的连接，两个表不存在完全匹配条件 oracle 返回满足条件的所有记录 概念介绍参考10g sql-高级查询,2019/4/8,oracle 数据库设计与性能,22/53,调整表连接-ansi表连接,ansi表连接标准： 自连接： 是一种表和自身连接的特殊情况 例如emp表中mgr与empno列就可进行自我连接 反连接： 当使用带有not in或not exist子句的子查询时，经常采用反连接 反连接经常是table access full 访问方式 半连接： 半连接返回满足包含exist子句的查询记录，即使条件右边有多条记录满足子查询的条件，该连接也不会复制谓词左边的记录 半连接经常是table access full 访问方式,2019/4/8,oracle 数据库设计与性能,23/53,调整表连接-oracle表连接,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-merge join hash join anti-join(反连接) semi-join(半连接),2019/4/8,oracle 数据库设计与性能,24/53,1.调整表连接-nl,表连接方式-嵌套连接： 嵌套循环(nested loops, nl) 概念： 这种连接方法有驱动表的概念，该连接过程就是一个2层嵌套循环； 外层循环的次数越少越好(将小表或返回较小行表作为驱动表-用于外层循环) 外层循环的次数并不能总保证使语句产生的i/o次数最少，有时不遵守这个理论反而会获得更好的效率 内部连接过程(表a：row_source1、表b：row_source2)：,row source1的row 1 -探查-row source 2 row source1的row 2 -探查-row source 2 row source1的row 3 -探查-row source 2 . row source1的row n -探查-row source 2,2019/4/8,oracle 数据库设计与性能,25/53,1.调整表连接-nl,表连接方式-嵌套连接(续)： 嵌套循环(nested loops, nl) 概念： row source1为驱动表或外部表。row source2被称为被探查表或内部表； 在nl连接中，oracle读取row source1中的每一行，然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理row source1中的下一行； 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引或有高选择性非唯一索引时，使用这种方法可以得到较好的效率； nl有其它连接方法没有的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。,2019/4/8,oracle 数据库设计与性能,26/53,1.调整表连接-nl,表连接方式-嵌套连接(续)： 嵌套循环(nested loops, nl) 概念： 如果不使用并行，可在驱动表加where 条件以返回较少行数据 大表也可能作为驱动表，关键看限制条件； 对于并行查询，可选大表作为驱动表(充分利用并行)； 有时使用并行操作反而效率低(如该表有很少的行符合条件) 硬件配置是否支持并行(如是否有多个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 analyzedtable access full emp analyzed,2019/4/8,oracle 数据库设计与性能,27/53,1.调整表连接-nl,先扫描row source 1 (外部表 即驱动表) 每个行再驱动内部表的row source 2 返回连接成功的行 代价:读驱动表然后访问内部表. 性能依赖于内部表的索引,outer loop,inner loop,check for a match,nested loop,access a (full),access b (rowid),index access,2019/4/8,oracle 数据库设计与性能,28/53,1.调整表连接-nl示意图,嵌套连接: nested loops样例,2019/4/8,oracle 数据库设计与性能,29/53,2.调整表连接-smj,row source 1,row source 2,sort,sort,merge,表连接方式-排序合并连接(sort merge join )： sml步骤为： 1) 首先生成表a(row source1)需要的数据，然后对这些数据按照连接操作关联列进行排序。 2) 随后生成表b(row source2)需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列进行排序(后面示意图)。 3) 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来(即合并),2019/4/8,oracle 数据库设计与性能,30/53,2.调整表连接-smj示意图,排序合并连接: sort merge示意-注意左右两个表都先排序再比较,2019/4/8,oracle 数据库设计与性能,31/53,3.调整表连接-hj,表连接方式-(hash join)哈希连接： 理论上来说比nl与smj要高效，而且只用在cbo优化器中； 较小的row source1被用来构建hash table与bitmap，row source2被用于散列算法，并与row source1生成的hash table进行匹配，以便进行进一步连接。 bitmap被用来作为一种比较快的查找方法，检查在hash table中是否有匹配的行。 当hash 表较大而内存不能存放时，这种查找方法更为有用。 这种连接方法也有类似nl连接中驱动表的概念(构建hash table与bitmap表=驱动表),当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。,sql explain plan for select /*+ use_hash(emp) */ empno from emp, deptwhere emp.deptno = dept.deptno; query plan - select statementchoose cost=3 hash jointable access full depttable access full emp,2019/4/8,oracle 数据库设计与性能,32/53,3.调整表连接-hj,表连接方式-hash连接(续)： 要使hash连接有效，需要设置hash_join_enabled=true，默认为true； 还要设置hash_area_size参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低； hash_join_enabled在10g/11g 为过失的参数，建议不要设置该参数。,2019/4/8,oracle 数据库设计与性能,33/53,3.调整表连接-hj,最小的表用来做为hash表和bitmap 第2个行源表做hsah算法并与hash表比对 在hash表中，位图是快速反应搜索行的方法 每个源行只需要单次即完成，它比排序-合并高效,row source 1 (build input),row source 2 (probe),hash_area_size,output rows,temp,2019/4/8,oracle 数据库设计与性能,34/53,3.调整表连接-hj示意图,hash连接: hash join 样例,2019/4/8,oracle 数据库设计与性能,35/53,调整表连接-三种方法比较,三种连接方式比较： 排序 -合并连接(sort merge join, smj)： 对于非等值连接，这种连接方式的效率是比较高的； 如果在关联的列上都有索引，效果更好； 对于将2个较大的row source连接，比nl连接要好一些； 如果sort merge返回的row source过大，导致使用过多的rowid在表中查询数据时，数据库性能下降(过多的i/o) 嵌套循环(nested loops, nl)： 如果 外部表 比较小，并且在 内部表 上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。 nl比其它连接方法多优点：可先返回已经连接的行，而不必等待所有的连接操作处理完。 哈希连接(hash join, hj)： 这种方法好于其它2种连接(cbo优化器)，而且需要设置合适的hash_area_size参数，才能取得较好的性能。 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。,2019/4/8,oracle 数据库设计与性能,36/53,调整表连接-三种方法比较,三种连接方式欢迎程度(高到低)： 1.嵌套连接； 2.排序连接； 3.hash连接； 排序连接与hash连接类似； 下面是相对优越性：,2019/4/8,oracle 数据库设计与性能,37/53,调整表连接-三种方法比较,三种连接方法建议： 在sql下直接调试： sqlset timing on 执行各语句，观察所用的时间 时间一般为毫秒级：时：分：秒.毫秒 采用sql跟踪方式： 对复杂的程序采用跟踪 对结果的分析,2019/4/8,oracle 数据库设计与性能,38/53,调整表连接-要点,sql结果的排序问题： sql语句处理比我们想象的要复杂，可与sql*plus结合使用，以处理排序、转换、格式化等； oracle提供多种对结果进行排序： order by ； join； group by ； aggregate(聚集运算) ； select unique； select distinct； create index ；,2019/4/8,oracle 数据库设计与性能,39/53,调整表连接-关于提示,oracle 系统允许对优化器进行提示 提示符号为/*+ hint */ 提示可使优化器按照开发人员的进行执行计划 9i/10g可允许下面的提示： 优化路径与目标进行提示 查询转换与访问路径进行提示 连接顺序进行提示 连接的操作进行提示 并行执行进行提示 附加的其他提示,2019/4/8,oracle 数据库设计与性能,40/53,调整表连接-提示方法,oracle 表连接可采用优化器提示： use_hash hash连接 use_merge 合并连接 star 星形连接 merge_aj 合并反连接 hash_aj hash反连接(hash join anti-散列反连接 ),2019/4/8,oracle 数据库设计与性能,41/53,调整表连接-提示方法,使用use_hash提示： use_hash提示将对指定表执行hash连接，下面是一个使用提示强制执行hash连接的并行查询例子：,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 ;,2019/4/8,oracle 数据库设计与性能,42/53,调整表连接-提示方法,使用use_merge提示： use_merge提示强制调用排序合并连接操作；这种方法常和并行一起使用，如： 注意提示内的表别名,select /*+ use_merge(e,b) parallel(e,4) parallel(b,4) */ e.ename, hiredate,m from bouus b,emp e where e.ename = b.ename ;,2019/4/8,oracle 数据库设计与性能,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 = :dbid and instance_number = :inst_num ) or ( dbid != :dbid or instance_number != :inst_num) ),2019/4/8,oracle 数据库设计与性能,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) of reserves 3 1 table access (by index rowid) of sailors 4 3 index (unique scan) of sys_c00628777 (unique),2019/4/8,oracle 数据库设计与性能,45/53,调整表连接-提示方法,cbo根据统计数据自动使用nl： cbo经常自动使用小表作为驱动表，如：,select samlee.object_id,big.owner from samll,big where samll.object_id=big.object_id and samll.object_type=java resource rows row source operation - - 13312 table access by index rowid big 13339 nest loops 26 table access full samll 13312 index range sacn big_object_id(object id 33423),2019/4/8,oracle 数据库设计与性能,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 根据用户的要求进行参数的修改缩短反应时间 使用first_rows_n最优化，oracle查询能够使用最少的反应时间来给出最初的n行结果。,alter system set optimizer_mode = first_rows_10; alter session set optimizer_goal = all_rows; select /*+ first_rows(100) */ from student;,2019/4/8,oracle 数据库设计与性能,47/53,其他提示-访问路径,2019/4/8,oracle 数据库设计与性能,48/53,调整表连接-提示方法,提示 索引合并and_equal ： 如果一个表创建了多个索引，则可使用and_equal实现索引的合并：,sql create table tst1(a int , b int ,c int , d int ,e int ,f int); sql insert into