Oracle RDBMS优化-调整表连接.ppt

《Oracle RDBMS优化-调整表连接.ppt》由会员分享，可在线阅读，更多相关《Oracle RDBMS优化-调整表连接.ppt（53页珍藏版）》请在三一文库上搜索。

1、,Oracle RDBMS 优化-调整表连接,赵元杰 中程在线(北京)科技有限公司 2009.8,2020/1/29,Oracle 数据库设计与性能,2/53,内容提要,表的访问方式； Oracle系统SQL优化器； 调整表连接；,2020/1/29,Oracle 数据库设计与性能,3/53,表的访问方式,表的访问方式： 全表扫描-顺序读取每个数据块到末尾； Hash(散列)获取-使用符合散列主键来为带有匹配Hash表中的记录创建rowid ROWID访问-通过指定的rowid选定表的一个单记录： ROWID是最快的访问方式； Rowid是Oracle系统启动分配给表的每条记录的唯一地址；,2

2、020/1/29,Oracle 数据库设计与性能,4/53,表的访问方式,全表扫描： 顺序读取每个数据块到末尾，从中选择所有记录； 下面条件之一满足，Oracle采用全表扫描： 当表不存在索引时； 当查询语句不包含where语句时； 当查询中使用like以%开始时； 引用函数索引时； 当使用基于CBO且表中的记录很少时； 当参数optimizer_mode=all_rows时；,2020/1/29,Oracle 数据库设计与性能,5/53,表的访问方式,HASH访问： Oracle 对多个表的Cluster采用Hash Cluster存储，这样两个表的记录存放在一个块内； Hash访问是通过一

3、个符号主键进行Hash运算后得到散列值(Hash Value)，该散列确定记录所在的块； 散列访问方法对于经常修改主符号键来说存在重定位记录的风险，所以建议在静态表的Cluster中使用散列访问方法(主键常改变不建议用)； Oracle 的Cluster另见9i 10g 分区与簇文档。,2020/1/29,Oracle 数据库设计与性能,6/53,表的访问方式,ROWID访问： ROWID访问是得到单个记录的最快方法； Oracle 系统为每个表的每条记录自动分配ROWID，包括OOOOOOFFFBBBBBBRRR ： OOOOOO-对象的相对号； FFF-文件的编号； BBBBBB-块的编号

4、； RRR-块中的记录号；,2020/1/29,Oracle 数据库设计与性能,7/53,表的访问方式,索引访问方式： Oracle有多种索引-B树索引、位图索引等； Oracle 系统可使用下面索引，包括： 索引范围扫描； 单个索引扫描； 降序索引扫描； And_euql过滤器；,2020/1/29,Oracle 数据库设计与性能,8/53,内容提要,表的访问方式； Oracle系统SQL优化器； 调整表连接；,2020/1/29,Oracle 数据库设计与性能,9/53,SQL优化器介绍,SQL优化器技术； 优化器模式； 基于规则的优化器调整； 基于成本的优化器调整(10g/11g)； 设

5、置优化器模式； 迁移到基于成本的优化器调整；,2020/1/29,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；,2

6、020/1/29,Oracle 数据库设计与性能,11/53,Query rewrite,Parse,选择优化 RBO / CBO,Query Execution,产生执行计划,OPTIMIZER,Query,Result,SELECT 语句的执行过程(略),2020/1/29,Oracle 数据库设计与性能,12/53,SQL优化器介绍-RBO (略),基于规则的优化器(RBO)： 基本规则优化不使用表和索引的统计数据； RBO方法要探讨要实现最佳访问路径； RBO根据语句结构的不同来生成执行计划表； RBO采用迭代生成执行计划，并检查from后每个表以及表间连接方式，根据每个执行路径所消耗

7、的成本进行排序，并选择最低的路径，下面是RBO步骤： 生成一个可执行计划列表(包含所有访问路径) ； 为每个执行计划指定级别数值； RBO选择级别低的计划； RBO对所有可连接的表与级别结果的连接作评估； 选择级别低的方法。,2020/1/29,Oracle 数据库设计与性能,13/53,SQL execution,分析 PARSER,Optimizer Mode?,数据字典 Dictionary,Cost-Based Optimizer,Rule-Based Optimizer,Row source Generator,结果,统计数据,CBO,RBO,查询计划,用户,Select 语句的处理

8、过程(略),2020/1/29,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. 索引列的范围搜索 1

9、1.索引列的无范围搜索 12. 排序合并连接 13. 索引列的Max 或 Min 14.索引列的Order by 15. 全表扫描.,2020/1/29,Oracle 数据库设计与性能,15/53,SQL优化器介绍-RBO (略),RBO的特性： 总是使用索引： 如果表有索引可用，则使用索引 ； 排序合并不使用索引； 总是从驱动表开始： From最后的表为驱动表； 总是不可避免情况下，才用全表扫描： RBO一般都用索引 ； 特别指定不用索引(HINT-提示)； 任何索引都可以用： RBO用索引不一定好 ； 有时越简单越好： 8i之前系统可提供好的执行计划 ； 9i后不建议采用RBO； 10g建

10、议采用 optimizer_mode = ALL_ROWS；,2020/1/29,Oracle 数据库设计与性能,16/53,SQL优化器介绍-CBO,CBO的特性： 使用统计数据和数据字典来确定代价； CBO只是一个数字处理程序，处理： 基本表访问代价； 所有数据源的访问方法； 并行是否可用； 连接的顺序与方法； OPTIMIZER_MODE 可以设置： CHOOSE, FIRST_ROWS, 或 ALL_ROWS 10g/11g 默认为ALL_ROWS,2020/1/29,Oracle 数据库设计与性能,17/53,SQL优化器介绍-CBO,CBO方法： 基于代价的优化方法是按如下几步来进

11、行： a）优化器在可能的存取路径及用户提示的基础下制订执行SQL语句的计划。 b）根据表、cluster和索引在数据字典中存放的特性统计信息和数据分布的统计信息,计算出执行每个计划的代价。 c）比较各个计划执行的代价,取其代价最低者来执行。 10g/11g版本OPTIMIZER_MODE默认为ALL_ROWS,2020/1/29,Oracle 数据库设计与性能,18/53,SQL优化器介绍-CBO需要,Table, cluster 统计: Number of rows Number of blocks Number of empty blocks Average row length 列统计:

12、 Number of distinct values (NDV) in column Number of nulls in column Data distribution (histogram) 索引统计: Number of Leaf blocks Levels Clustering factor,2020/1/29,Oracle 数据库设计与性能,19/53,内容提要,表的访问方式； Oracle系统SQL优化器； 调整表连接；,2020/1/29,Oracle 数据库设计与性能,20/53,表连接概念,Oracle 查询语句中FROM 子句： FROM子句最后的表(driving ta

13、ble)将被最先处理 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

14、 - - 486400 HASH JOIN 1000 TABLE ACCESS FULL SAMLL 485400 TABLE ACCESS FULL BIG,记录少的表作为驱动表,driving table,Driven (inner) table,2020/1/29,Oracle 数据库设计与性能,21/53,调整表连接-ANSI表连接,ANSI表连接标准： 等价连接： 是标准连接，其中两个表的一对记录通过一个公共字段的匹配进行连接 等价连接的Oracle 表访问计划可以是NEST LOOPS，HASH JOIN或MERGE JOIN 外部连接： 是一个确保不完整记录的连接，两个表不存在完

15、全匹配条件 Oracle 返回满足条件的所有记录 概念介绍参考10g SQL-高级查询,2020/1/29,Oracle 数据库设计与性能,22/53,调整表连接-ANSI表连接,ANSI表连接标准： 自连接： 是一种表和自身连接的特殊情况 例如EMP表中MGR与EMPNO列就可进行自我连接 反连接： 当使用带有NOT IN或NOT EXIST子句的子查询时，经常采用反连接 反连接经常是TABLE ACCESS FULL 访问方式 半连接： 半连接返回满足包含EXIST子句的查询记录，即使条件右边有多条记录满足子查询的条件，该连接也不会复制谓词左边的记录 半连接经常是TABLE ACCESS

16、FULL 访问方式,2020/1/29,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(半连接),2020/1/29,Or

17、acle 数据库设计与性能,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 sou

18、rce1的Row 3 -探查-Row source 2 . Row source1的Row n -探查-Row source 2,2020/1/29,Oracle 数据库设计与性能,25/53,1.调整表连接-NL,表连接方式-嵌套连接(续)： 嵌套循环(Nested Loops, NL) 概念： Row source1为驱动表或外部表。Row Source2被称为被探查表或内部表； 在NL连接中，Oracle读取row source1中的每一行，然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理row source1中的下一行； 如果driving ro

19、w source(外部表)比较小，并且在inner row source(内部表)上有唯一索引或有高选择性非唯一索引时，使用这种方法可以得到较好的效率； NL有其它连接方法没有的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。,2020/1/29,Oracle 数据库设计与性能,26/53,1.调整表连接-NL,表连接方式-嵌套连接(续)： 嵌套循环(Nested Loops, NL) 概念： 如果不使用并行，可在驱动表加where 条件以返回较少行数据 大表也可能作为驱动表，关键看限制条件； 对于并行查询，可选大表作为驱动表(充分利用并

20、行)； 有时使用并行操作反而效率低(如该表有很少的行符合条件) 硬件配置是否支持并行(如是否有多个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,2020/1/29,Ora

21、cle 数据库设计与性能,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,2020/1/29,Oracle 数据库设计与性能,28/53,1.调整表连接-NL示意图,嵌套连接: Nested loops样例,2020/1/29,Oracle 数

22、据库设计与性能,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按照连接条件连接起来(即合并),2020/1/29,Oracl

23、e 数据库设计与性能,30/53,2.调整表连接-SMJ示意图,排序合并连接: Sort merge示意-注意左右两个表都先排序再比较,2020/1/29,Oracle 数据库设计与性能,31/53,3.调整表连接-HJ,表连接方式-(Hash Join)哈希连接： 理论上来说比NL与SMJ要高效，而且只用在CBO优化器中； 较小的row source1被用来构建hash table与bitmap，row source2被用于散列算法，并与row source1生成的hash table进行匹配，以便进行进一步连接。 Bitmap被用来作为一种比较快的查找方法，检查在hash table中是否

24、有匹配的行。 当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

25、 DEPTTABLE ACCESS FULL EMP,2020/1/29,Oracle 数据库设计与性能,32/53,3.调整表连接-HJ,表连接方式-Hash连接(续)： 要使HASH连接有效，需要设置HASH_JOIN_ENABLED=TRUE，默认为TRUE； 还要设置HASH_AREA_SIZE参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低； HASH_JOIN_ENABLED在10g/11g 为过失的参数，建议不要设置该参数。,2020/1/29,Oracle 数据库设计与性能,33/53,3.调整表连接-HJ

26、,最小的表用来做为HASH表和bitmap 第2个行源表做HSAH算法并与HASH表比对 在HASH表中，位图是快速反应搜索行的方法 每个源行只需要单次即完成，它比排序-合并高效,Row source 1 (build input),Row source 2 (probe),HASH_AREA_SIZE,Output rows,TEMP,2020/1/29,Oracle 数据库设计与性能,34/53,3.调整表连接-HJ示意图,Hash连接: Hash Join 样例,2020/1/29,Oracle 数据库设计与性能,35/53,调整表连接-三种方法比较,三种连接方式比较： 排序 -合并连接

27、(Sort Merge Join, SMJ)： 对于非等值连接，这种连接方式的效率是比较高的； 如果在关联的列上都有索引，效果更好； 对于将2个较大的row source连接，比NL连接要好一些； 如果sort merge返回的row source过大，导致使用过多的rowid在表中查询数据时，数据库性能下降(过多的I/O) 嵌套循环(Nested Loops, NL)： 如果 外部表 比较小，并且在 内部表 上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。 NL比其它连接方法多优点：可先返回已经连接的行，而不必等待所有的连接操作处理完。 哈希连接(Hash Join,

28、 HJ)： 这种方法好于其它2种连接(CBO优化器)，而且需要设置合适的hash_area_size参数，才能取得较好的性能。 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。,2020/1/29,Oracle 数据库设计与性能,36/53,调整表连接-三种方法比较,三种连接方式欢迎程度(高到低)： 1.嵌套连接； 2.排序连接； 3.Hash连接； 排序连接与Hash连接类似； 下面是相对优越性：,2020/1/29,Oracle 数据库设计与性能,37/53,调整表连接-三种方法比较,三种连接方法建议： 在SQL下直接调试

29、： SQLset timing on 执行各语句，观察所用的时间 时间一般为毫秒级：时：分：秒.毫秒 采用SQL跟踪方式： 对复杂的程序采用跟踪 对结果的分析,2020/1/29,Oracle 数据库设计与性能,38/53,调整表连接-要点,SQL结果的排序问题： SQL语句处理比我们想象的要复杂，可与SQL*Plus结合使用，以处理排序、转换、格式化等； Oracle提供多种对结果进行排序： Order By ； Join； Group by ； Aggregate(聚集运算) ； Select Unique； Select distinct； Create Index ；,2020/1/2

30、9,Oracle 数据库设计与性能,39/53,调整表连接-关于提示,Oracle 系统允许对优化器进行提示 提示符号为/*+ hint */ 提示可使优化器按照开发人员的进行执行计划 9i/10g可允许下面的提示： 优化路径与目标进行提示 查询转换与访问路径进行提示 连接顺序进行提示 连接的操作进行提示 并行执行进行提示 附加的其他提示,2020/1/29,Oracle 数据库设计与性能,40/53,调整表连接-提示方法,Oracle 表连接可采用优化器提示： use_hash HASH连接 use_merge 合并连接 Star 星形连接 merge_aj 合并反连接 hash_aj HA

31、SH反连接(HASH JOIN ANTI-散列反连接 ),2020/1/29,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 ;,2020/1/29,Oracle 数据库设计与性能,42/53,调

32、整表连接-提示方法,使用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 ;,2020/1/29,Oracle 数据库设计与性能,43/53,调整表连接-提示方法,使用HASH_AJ提示： 在子查询中使用Hash_aj提示，如：,Delete from stats$sqltext s

33、t 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) ),2020/1/29,Oracle 数据库设计与性能,44/53,调整表连接-提示方法,优化器自动采用的嵌套连接：

34、 档你没有明确提示连接方式时；优化器也会采用认为是最优的连接，如：,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),2020/1/29,Oracle 数据库设计与性能

35、,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

36、RANGE SACN BIG_OBJECT_ID(object id 33423),2020/1/29,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 o

37、ptimizer_mode = first_rows_10; alter session set optimizer_goal = all_rows; select /*+ first_rows(100) */ from student;,2020/1/29,Oracle 数据库设计与性能,47/53,其他提示-访问路径,2020/1/29,Oracle 数据库设计与性能,48/53,调整表连接-提示方法,提示 索引合并AND_EQUAL ： 如果一个表创建了多个索引，则可使用AND_EQUAL实现索引的合并：,SQL create table tst1(a int , b int ,c in

38、t , d int ,e int ,f int); SQL insert into tst1 values(1,1,1,1,1,1); SQL insert into tst1 values(2,2,2,2,2,2); SQL commit; SQL create index idx1 on tst1(a); SQL create index idx2 on tst1(b); SQL create index idx3 on tst1(c); SQL create index idx4 on tst1(d); SQL create index idx5 on tst1(e); SQL sele

39、ct * /*+ and_equal(tst1 idx1 idx2 idx3 idx4 idx5 ) */ from t1 where a=1 and b=1 and c=1 and d=1 and e=1 and f=1;,2020/1/29,Oracle 数据库设计与性能,49/53,其他提示-查询转换,2020/1/29,Oracle 数据库设计与性能,50/53,其他提示-查询转换,SELECT /*+USE_CONCAT*/ h.customer_id, l.line_id, l.revenue_amount FROM so_lines_all l, so_headers_all h

40、 WHERE l.s7 = 20 AND h.original_system_reference = l.attribute5 AND h.original_system_source_code IN (1013,1014); Plan - SELECT STATEMENT CONCATENATION NESTED LOOPS TABLE ACCESS FULL SO_LINES_ALL TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL INDEX RANGE SCAN SO_HEADERS_N9 NESTED LOOPS,USE_CONCAT： USE_C

41、ONCAT提示强制对查询语句中的WHERE从句的OR条件进行转换，转化成由UNION_ALL集合操作符连接的组合查询。 如果采用连接查询比不用连接查询低，则转换为用连接查询：,2020/1/29,Oracle 数据库设计与性能,51/53,其他提示-连接顺序与连接操作,2020/1/29,Oracle 数据库设计与性能,52/53,其他提示-并行执行与其它,2020/1/29,Oracle 数据库设计与性能,53/53,参考资料,Oracle 原厂: Oracle Database Performance Tuning Guide B14211-01 Oracle Database Administrators Guide B14231-01 Donald K.Burleson: Oracle High-Performance SQL Tuning,