《Oracle RDBMS优化调整表连接.ppt》由会员分享，可在线阅读，更多相关《Oracle RDBMS优化调整表连接.ppt（53页珍藏版）》请在三一办公上搜索。
1、,Oracle RDBMS优化-调整表连接,赵元杰中程在线(北京)科技有限公司2009.8,2023/2/9,Oracle 数据库设计与性能,2/53,内容提要,表的访问方式；Oracle系统SQL优化器；调整表连接；,2023/2/9,Oracle 数据库设计与性能,3/53,表的访问方式,表的访问方式：全表扫描-顺序读取每个数据块到末尾；Hash(散列)获取-使用符合散列主键来为带有匹配Hash表中的记录创建rowidROWID访问-通过指定的rowid选定表的一个单记录：ROWID是最快的访问方式；Rowid是Oracle系统启动分配给表的每条记录的唯一地址；,2023/2/9,Orac
2、le 数据库设计与性能,4/53,表的访问方式,全表扫描：顺序读取每个数据块到末尾，从中选择所有记录；下面条件之一满足，Oracle采用全表扫描：当表不存在索引时；当查询语句不包含where语句时；当查询中使用like以%开始时；引用函数索引时；当使用基于CBO且表中的记录很少时；当参数optimizer_mode=all_rows时；,2023/2/9,Oracle 数据库设计与性能,5/53,表的访问方式,HASH访问：Oracle 对多个表的Cluster采用Hash Cluster存储，这样两个表的记录存放在一个块内；Hash访问是通过一个符号主键进行Hash运算后得到散列值(Hash
3、 Value)，该散列确定记录所在的块；散列访问方法对于经常修改主符号键来说存在重定位记录的风险，所以建议在静态表的Cluster中使用散列访问方法(主键常改变不建议用)；Oracle 的Cluster另见9i 10g 分区与簇文档。,2023/2/9,Oracle 数据库设计与性能,6/53,表的访问方式,ROWID访问：ROWID访问是得到单个记录的最快方法；Oracle 系统为每个表的每条记录自动分配ROWID，包括OOOOOOFFFBBBBBBRRR：OOOOOO-对象的相对号；FFF-文件的编号；BBBBBB-块的编号；RRR-块中的记录号；,2023/2/9,Oracle 数据库设
4、计与性能,7/53,表的访问方式,索引访问方式：Oracle有多种索引-B树索引、位图索引等；Oracle 系统可使用下面索引，包括：索引范围扫描；单个索引扫描；降序索引扫描；And_euql过滤器；,2023/2/9,Oracle 数据库设计与性能,8/53,内容提要,表的访问方式；Oracle系统SQL优化器；调整表连接；,2023/2/9,Oracle 数据库设计与性能,9/53,SQL优化器介绍,SQL优化器技术；优化器模式；基于规则的优化器调整；基于成本的优化器调整(10g/11g)；设置优化器模式；迁移到基于成本的优化器调整；,2023/2/9,Oracle 数据库设计与性能,10
5、/53,SQL优化器介绍,SQL优化器技术：SQL优化器的目的是为SQL语句生成最快、消耗资源最少的执行计划；SQL优化器可以产生最快反应速度(First_rows模式)，可产生最佳吞吐量的执行计划(all_rows模式)Oracle提供基于规则(RBO)和基于成本(CBO)的优化器模式；设置optimizer_mode=choose时两种模式交替使用；一般可通过Alter session来设置模式，如：Alter session set optimizer_goal=xxx；,2023/2/9,Oracle 数据库设计与性能,11/53,Query rewrite,Parse,选择优化RBO
6、/CBO,QueryExecution,产生执行计划,OPTIMIZER,Query,Result,SELECT 语句的执行过程(略),2023/2/9,Oracle 数据库设计与性能,12/53,SQL优化器介绍-RBO(略),基于规则的优化器(RBO)：基本规则优化不使用表和索引的统计数据；RBO方法要探讨要实现最佳访问路径；RBO根据语句结构的不同来生成执行计划表；RBO采用迭代生成执行计划，并检查from后每个表以及表间连接方式，根据每个执行路径所消耗的成本进行排序，并选择最低的路径，下面是RBO步骤：生成一个可执行计划列表(包含所有访问路径)；为每个执行计划指定级别数值；RBO选择级
7、别低的计划；RBO对所有可连接的表与级别结果的连接作评估；选择级别低的方法。,2023/2/9,Oracle 数据库设计与性能,13/53,SQLexecution,分析 PARSER,Optimizer Mode?,数据字典Dictionary,Cost-BasedOptimizer,Rule-BasedOptimizer,Row sourceGenerator,结果,统计数据,CBO,RBO,查询计划,用户,Select 语句的处理过程(略),2023/2/9,Oracle 数据库设计与性能,14/53,SQL优化器介绍-RBO(略),RBO与SQL操作：RBO将SQL语句分为不同的级别，
8、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 或 Min14.索引列的Order by15.全表扫描.,2023/2/9,Oracle 数据库设计与性能,15/53,SQL优化器介绍-RBO(略),
9、RBO的特性：总是使用索引：如果表有索引可用，则使用索引；排序合并不使用索引；总是从驱动表开始：From最后的表为驱动表；总是不可避免情况下，才用全表扫描：RBO一般都用索引；特别指定不用索引(HINT-提示)；任何索引都可以用：RBO用索引不一定好；有时越简单越好：8i之前系统可提供好的执行计划；9i后不建议采用RBO；10g建议采用 optimizer_mode=ALL_ROWS；,2023/2/9,Oracle 数据库设计与性能,16/53,SQL优化器介绍-CBO,CBO的特性：使用统计数据和数据字典来确定代价；CBO只是一个数字处理程序，处理：基本表访问代价；所有数据源的访问方法；并
10、行是否可用；连接的顺序与方法；OPTIMIZER_MODE 可以设置：CHOOSE,FIRST_ROWS,或 ALL_ROWS10g/11g 默认为ALL_ROWS,2023/2/9,Oracle 数据库设计与性能,17/53,SQL优化器介绍-CBO,CBO方法：基于代价的优化方法是按如下几步来进行：a）优化器在可能的存取路径及用户提示的基础下制订执行SQL语句的计划。b）根据表、cluster和索引在数据字典中存放的特性统计信息和数据分布的统计信息,计算出执行每个计划的代价。c）比较各个计划执行的代价,取其代价最低者来执行。10g/11g版本OPTIMIZER_MODE默认为ALL_ROW
11、S,2023/2/9,Oracle 数据库设计与性能,18/53,SQL优化器介绍-CBO需要,Table,cluster 统计:Number of rowsNumber of blocksNumber of empty blocksAverage row length列统计:Number of distinct values(NDV)in columnNumber of nulls in columnData distribution(histogram)索引统计:Number of Leaf blocksLevelsClustering factor,2023/2/9,Oracle 数据库
12、设计与性能,19/53,内容提要,表的访问方式；Oracle系统SQL优化器；调整表连接；,2023/2/9,Oracle 数据库设计与性能,20/53,表连接概念,Oracle 查询语句中FROM 子句：FROM子句最后的表(driving table)将被最先处理driving table驱动表，有时叫基础表或外部表,Select/*example*/FROM big,smallWHERE big.object_id=samll_object_idcall count cpu elapsed disk query-Parse 1 0.00 0.00 0 0Excute 1 0.00 0.0
13、0 0 0Fetch 32428 3.38 3.21 0 46977-Total 32430 3.38 3.22 0 46977Rows Row Source Operation-486400 HASH JOIN 1000 TABLE ACCESS FULL SAMLL 485400 TABLE ACCESS FULL BIG,记录少的表作为驱动表,driving table,Driven(inner)table,2023/2/9,Oracle 数据库设计与性能,21/53,调整表连接-ANSI表连接,ANSI表连接标准：等价连接：是标准连接，其中两个表的一对记录通过一个公共字段的匹配进行连接
14、等价连接的Oracle 表访问计划可以是NEST LOOPS，HASH JOIN或MERGE JOIN外部连接：是一个确保不完整记录的连接，两个表不存在完全匹配条件Oracle 返回满足条件的所有记录概念介绍参考10g SQL-高级查询,2023/2/9,Oracle 数据库设计与性能,22/53,调整表连接-ANSI表连接,ANSI表连接标准：自连接：是一种表和自身连接的特殊情况例如EMP表中MGR与EMPNO列就可进行自我连接反连接：当使用带有NOT IN或NOT EXIST子句的子查询时，经常采用反连接反连接经常是TABLE ACCESS FULL 访问方式半连接：半连接返回满足包含EX
15、IST子句的查询记录，即使条件右边有多条记录满足子查询的条件，该连接也不会复制谓词左边的记录半连接经常是TABLE ACCESS FULL 访问方式,2023/2/9,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 JoinOuter JoinSort-Merge Join
16、Hash JoinAnti-Join(反连接)Semi-Join(半连接),2023/2/9,Oracle 数据库设计与性能,24/53,1.调整表连接-NL,表连接方式-嵌套连接：嵌套循环(Nested Loops,NL)概念：这种连接方法有驱动表的概念，该连接过程就是一个2层嵌套循环；外层循环的次数越少越好(将小表或返回较小行表作为驱动表-用于外层循环)外层循环的次数并不能总保证使语句产生的I/O次数最少，有时不遵守这个理论反而会获得更好的效率内部连接过程(表A：row_source1、表B：row_source2)：,Row source1的Row 1-探查-Row source 2Ro
17、w source1的Row 2-探查-Row source 2 Row source1的Row 3-探查-Row source 2.Row source1的Row n-探查-Row source 2,2023/2/9,Oracle 数据库设计与性能,25/53,1.调整表连接-NL,表连接方式-嵌套连接(续)：嵌套循环(Nested Loops,NL)概念：Row source1为驱动表或外部表。Row Source2被称为被探查表或内部表；在NL连接中，Oracle读取row source1中的每一行，然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理r
18、ow source1中的下一行；如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引或有高选择性非唯一索引时，使用这种方法可以得到较好的效率；NL有其它连接方法没有的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。,2023/2/9,Oracle 数据库设计与性能,26/53,1.调整表连接-NL,表连接方式-嵌套连接(续)：嵌套循环(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 ANALYZEDTABLE ACCESS FULL EMP ANALYZED,2
20、023/2/9,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,2023/2/9,Oracle 数据库设计与性能,28/53,1.调整表连接-NL示意图,嵌套连接:Nested loops样例,2023/2/9,Oracle
21、数据库设计与性能,29/53,2.调整表连接-SMJ,Rowsource 1,Rowsource 2,Sort,Sort,MERGE,表连接方式-排序合并连接(Sort Merge Join)：SML步骤为：1)首先生成表A(row source1)需要的数据，然后对这些数据按照连接操作关联列进行排序。2)随后生成表B(row source2)需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列进行排序(后面示意图)。3)最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来(即合并),2023/2/9,Oracle 数据库设计与性能
22、,30/53,2.调整表连接-SMJ示意图,排序合并连接:Sort merge示意-注意左右两个表都先排序再比较,2023/2/9,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 表较大
23、而内存不能存放时，这种查找方法更为有用。这种连接方法也有类似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=3HASH JOINTABLE ACCESS FULL DEPTTABLE ACCESS FULL EMP,2
24、023/2/9,Oracle 数据库设计与性能,32/53,3.调整表连接-HJ,表连接方式-Hash连接(续)：要使HASH连接有效，需要设置HASH_JOIN_ENABLED=TRUE，默认为TRUE；还要设置HASH_AREA_SIZE参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低；HASH_JOIN_ENABLED在10g/11g 为过失的参数，建议不要设置该参数。,2023/2/9,Oracle 数据库设计与性能,33/53,3.调整表连接-HJ,最小的表用来做为HASH表和bitmap第2个行源表做HSAH算
25、法并与HASH表比对在HASH表中，位图是快速反应搜索行的方法每个源行只需要单次即完成，它比排序-合并高效,Row source 1(build input),Row source 2(probe),HASH_AREA_SIZE,Output rows,TEMP,2023/2/9,Oracle 数据库设计与性能,34/53,3.调整表连接-HJ示意图,Hash连接:Hash Join 样例,2023/2/9,Oracle 数据库设计与性能,35/53,调整表连接-三种方法比较,三种连接方式比较：排序-合并连接(Sort Merge Join,SMJ)：对于非等值连接，这种连接方式的效率是比较高
26、的；如果在关联的列上都有索引，效果更好；对于将2个较大的row source连接，比NL连接要好一些；如果sort merge返回的row source过大，导致使用过多的rowid在表中查询数据时，数据库性能下降(过多的I/O)嵌套循环(Nested Loops,NL)：如果 外部表 比较小，并且在 内部表 上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NL比其它连接方法多优点：可先返回已经连接的行，而不必等待所有的连接操作处理完。哈希连接(Hash Join,HJ)：这种方法好于其它2种连接(CBO优化器)，而且需要设置合适的hash_area_size参数，才能
27、取得较好的性能。在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。,2023/2/9,Oracle 数据库设计与性能,36/53,调整表连接-三种方法比较,三种连接方式欢迎程度(高到低)：1.嵌套连接；2.排序连接；3.Hash连接；排序连接与Hash连接类似；下面是相对优越性：,2023/2/9,Oracle 数据库设计与性能,37/53,调整表连接-三种方法比较,三种连接方法建议：在SQL下直接调试：SQLset timing on执行各语句，观察所用的时间时间一般为毫秒级：时：分：秒.毫秒 采用SQL跟踪方式：对复杂的程序
28、采用跟踪 对结果的分析,2023/2/9,Oracle 数据库设计与性能,38/53,调整表连接-要点,SQL结果的排序问题：SQL语句处理比我们想象的要复杂，可与SQL*Plus结合使用，以处理排序、转换、格式化等；Oracle提供多种对结果进行排序：Order By；Join；Group by；Aggregate(聚集运算)；Select Unique；Select distinct；Create Index；,2023/2/9,Oracle 数据库设计与性能,39/53,调整表连接-关于提示,Oracle 系统允许对优化器进行提示提示符号为/*+hint*/提示可使优化器按照开发人员的进
29、行执行计划9i/10g可允许下面的提示：优化路径与目标进行提示查询转换与访问路径进行提示连接顺序进行提示连接的操作进行提示并行执行进行提示附加的其他提示,2023/2/9,Oracle 数据库设计与性能,40/53,调整表连接-提示方法,Oracle 表连接可采用优化器提示：use_hash HASH连接use_merge 合并连接Star 星形连接merge_aj 合并反连接 hash_aj HASH反连接(HASH JOIN ANTI-散列反连接),2023/2/9,Oracle 数据库设计与性能,41/53,调整表连接-提示方法,使用USE_HASH提示：Use_hash提示将对指定表执
30、行HASH连接，下面是一个使用提示强制执行hash连接的并行查询例子：,SELECT/*+ordered use_hash(e,b)parallel(e,4)parallel(b,4)*/e.ename,hiredate,m FROM bouus b,emp eWHERE e.ename=b.ename;,2023/2/9,Oracle 数据库设计与性能,42/53,调整表连接-提示方法,使用USE_MERGE提示：Use_merge提示强制调用排序合并连接操作；这种方法常和并行一起使用，如：注意提示内的表别名,SELECT/*+use_merge(e,b)parallel(e,4)paral
31、lel(b,4)*/e.ename,hiredate,m FROM bouus b,emp eWHERE e.ename=b.ename;,2023/2/9,Oracle 数据库设计与性能,43/53,调整表连接-提示方法,使用HASH_AJ提示：在子查询中使用Hash_aj提示，如：,Delete from stats$sqltext stwhere(hash_value,text_subset)not in(select-+hash_ajhash_value,text_subsetfrom stats$sql_summary sswhere(snap_id:hi_snap)and dbid
32、=:dbid and instance_number=:inst_num)or(dbid!=:dbid or instance_number!=:inst_num),2023/2/9,Oracle 数据库设计与性能,44/53,调整表连接-提示方法,优化器自动采用的嵌套连接：档你没有明确提示连接方式时；优化器也会采用认为是最优的连接，如：,Select*from reserves r,sailors swhere r.sid=s.sid;-0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS(FULL)OF RE
33、SERVES3 1 TABLE ACCESS(BY INDEX ROWID)OF SAILORS4 3 INDEX(UNIQUE SCAN)OF SYS_C00628777(UNIQUE),2023/2/9,Oracle 数据库设计与性能,45/53,调整表连接-提示方法,CBO根据统计数据自动使用NL：CBO经常自动使用小表作为驱动表，如：,Select samlee.object_id,big.ownerFROM samll,big WHERE samll.object_id=big.object_id and samll.object_type=JAVA RESOURCEROWS ROW
34、 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),2023/2/9,Oracle 数据库设计与性能,46/53,调整表连接-提示方法,CBO下的提示FIRST_ROWS_n：Oracle 9i 版本前，只有ALL_ROWS与FIRST_ROWSOracle 9i 开始FIRST_ROWS_1、FIRST_ROWS_10、FIRST_ROWS
35、_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;,2023/2/9,Oracle 数据库设计与性能,47/53,其他提示-访问路径,2023/2/9,Oracle 数据库设计与性能,48/53,调整表连接-提示方法,
36、提示 索引合并AND_EQUAL：如果一个表创建了多个索引，则可使用AND_EQUAL实现索引的合并：,SQL create table tst1(a int,b int,c int,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(
37、c);SQL create index idx4 on tst1(d);SQL create index idx5 on tst1(e);SQL select*/*+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;,2023/2/9,Oracle 数据库设计与性能,49/53,其他提示-查询转换,2023/2/9,Oracle 数据库设计与性能,50/53,其他提示-查询转换,SELECT/*+USE_CONCAT*/h.customer_id
38、,l.line_id,l.revenue_amountFROM so_lines_all l,so_headers_all hWHERE l.s7=20AND h.original_system_reference=l.attribute5AND 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 RAN
39、GE SCAN SO_HEADERS_N9 NESTED LOOPS,USE_CONCAT：USE_CONCAT提示强制对查询语句中的WHERE从句的OR条件进行转换，转化成由UNION_ALL集合操作符连接的组合查询。如果采用连接查询比不用连接查询低，则转换为用连接查询：,2023/2/9,Oracle 数据库设计与性能,51/53,其他提示-连接顺序与连接操作,2023/2/9,Oracle 数据库设计与性能,52/53,其他提示-并行执行与其它,2023/2/9,Oracle 数据库设计与性能,53/53,参考资料,Oracle 原厂:Oracle Database Performance Tuning Guide B14211-01Oracle Database Administrators Guide B14231-01Donald K.Burleson:Oracle High-Performance SQL Tuning,
