Oracle优化之执行计划解析

Oracle优化之执行方案解析用友软件股份林世福2013年9月25日应用优化的重要性认识Oracle应用优化SQL语句的解析过程什么是SQL执行方案了解RBO和CBO如何解读执行方案NCSQL标准SQL语句的解析过程1语法分析分析语句的语法是否符合标准，衡量语句中各表达式的意义。

2语义分析检查语句中涉及的所有数据库对象是否存在,及用户权限。

3视图转换将涉及视图的查询语句转换为相应的对基表查询语句。

4表达式转换将复杂的SQL表达式转换为较简单的等效连接表达式。

5选择优化器不同的优化器一般产生不同的“执行方案”

SQL语句的解析过程6选择连接方式对多表连接ORACLE可选择适当的连接方式。

7选择连接顺序对多表连接ORACLE选择哪一对表先连接，选择这两表中哪个表做为驱动表。

8选择数据的搜索路径根据以上条件选择适宜的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。

9产生“执行方案”并执行。

Oracle应用优化SQL语句的解析过程什么是SQL执行方案了解RBO和CBO如何解读执行方案NCSQL标准什么是SQL执行方案所谓执行方案，就是对一个DMLSQL做出一份怎样去完成任务的执行路径。基于不同的优化方式，执行方案可能有很大的差异。什么是SQL执行方案Oracle应用优化SQL语句的解析过程什么是SQL执行方案了解RBO和CBO如何解读执行方案NCSQL标准了解RBO和CBO什么是RBO和CBO Oracle的优化规那么，主要有(RBO,RULE-BasedOptimizer基于内在规那么)(CBO,Cost-BasedOptimizer基于本钱)SinglerowbyROWIDSinglerowbyclusterjoinSinglerowbyhashclusterkeywithuniquekeySinglerowbyuniqueindexClusterjoinHashclusterkeyIndexedclusterkeyCompositekeySingle-columnnon-uniqueindexBoundedrangesearchonindexedcolumnsUnboundedrangesearchonindexedcolumnsSort-mergejoinMAXorMINofindexedcolumnORDERBYonindexedcolumnsFulltable-scan效率由高到低RBO的规那么CBO的规那么本钱指执行SQL语句导致的实际资源消耗；或者表示优化器对执行语句所用时间的最优估计。本钱优化策略演化传统的：仅仅计算读取请求的数目系统统计(1)：说明读取的大小和时间系统统计(2)：说明CPU本钱以及读取请求的大小和时间系统统计(3)：说明缓存、CPU本钱以及读取请求的大小和时间

Oracle应用优化SQL语句的解析过程什么是SQL执行方案了解RBO和CBO如何解读执行方案NCSQL标准如何解读执行方案执行方案阅读方法执行方法描述术语解释

执行方案阅读方法以树状格式进行读取，通过递归进入最底层，然后再返回该树的父〔第一〕。

实际演示数据扫描方式表连接算法执行方法描述表扫描1).全表扫描(FullTableScans,FTS)2).通过ROWID的表存取(TableAccessbyROWID)索引扫描1).索引唯一扫描(indexuniquescan)

2).索引范围扫描(indexrangescan)3).索引快速扫描(indexfastfullscan)

数据扫描方式全表扫描对于全表扫描,Oracle将顺序读取请求段中的所有数据块对于返回大量数据查询尤其有效可以防止由于索引访问带来的额外I/O可以通过多块访问(db_file_multiblock_read_count)全表扫描访问HWM下的所有数据块Index访问通过索引访问获得rowid进而通过rowid访问获取数据所在位置通过单块I/O读取Rowid是访问单行的最快的方法ROWID的格式如下(6位对象号，3位文件号，6位块号，3位行号)例子:AAABqHAADAAAC7EAAA数据对象编号文件编号块编号行编号OOOOOOFFFBBBBBBRRR数据扫描方式表连接算法执行方法描述

HashJoin(哈希连接)row1row2……rowmkey1……keynHashTable较小的建立Hash表较大的计算Hash值探测Hash表优点：ora10G后默认的连接方式，相当于对两张表分别扫描一次。ram表连接算法数据量大的不同的结果集进行连接，较小的结果集作为驱动表，创立基于内存的Hashtable，大的结果集计算hashvalue，然后在内存中进行匹配。较小的结果集的大小接近hash_area_size，即较小的结果集一次性加载到内存中表连接

NestedLoop(嵌套循环)外层数据小，内层被连接的字段已建索引，且内层数据量大；比方select*frombd_corpcinnerjoinic_general_hhonc.pk_corp=h.pk_corp1543712345…双重for逐行循环表连接算法外表(驱动表)内表

必须有一个table拥有index；只用一个table有index时，选择没有index的table作为驱动表；当两个table都有index时，选择结果集较小的table作为驱动表(CBO/RBO的区别)；

适用于有index的连接，两个有大小差异的结果集，数据量较小。表连接

SortMergeJoin(合并连接)1346710对合并列分别都要求排序，效率最低1234510表连接在不使用index的情况下使用MRjoin：在连接table的字段上不存在可用的index；查询将返回两个table中大局部的数据块；CBO认为tablescan比indexrangescan更少的cost；适用于没有index的连接，或两个大小接近的超大结果集。表连接消耗〔Cost〕 执行方案中该行的累计本钱。基数〔Card〕根据统计信息，执行方案中该行将产生的数据量。字节〔Bytes〕执行方案中该行将产生的数据总量。术语解释Oracle应用优化SQL语句的解析过程什么是SQL执行方案了解RBO和CBO如何解读执行方案NCSQL标准NCSQL标准1、不使用不必要的外连接

外连接(left/rightjoin)与内连接(innerjoin)作用不同。

例：selectpk_bdinfo,bdcode,………frombd_bdinfoawhereexists(select1frombd_subjassbleftouterjoinbd_accsubjacconb.pk_accsubj=acc.pk_accsubjwherea.pk_bdinfo=b.pk_bdinfoandacc.pk_corp='1057')NCSQL标准2、减少访问数据库的次数批修改、批插入，利用批量方法减少数据库访问次数。减少不必要的操作。不必要的unionall

NCSQL标准3、用Where子句替换HAVING子句防止使用HAVING子句作为条件，HAVING只会在检索出所有记录之后对结果集进行过滤，这个处理需要排序，总计等操作，如果能通过where子句限制记录的数目，那就能减少这方面开销。NCSQL标准4、维表的查询

尽量减少不必要的复杂表达式。Oracle解析SQL时需要将复杂的SQL表达式转换为较简单的等效连接表达式。

SELECTa.pk,(SELECTFROMbWHEREb.code=1anda.id=b.id)name,(SELECTb.typeFROMbWHEREb.code=1anda.id=b.id)typeFROMaWHEREa.code=2改写：

SELECTa.pk,name,b2.typetypeFROMa,bb1,bb2WHEREa.code=2Andb1.code=1anda.id=b1.idAndb2.code=1anda.id=b2.idNCSQL标准5、EXISTS和IN以及表连接通常优化器会将in或exists转换为连接来进行优化，但这个转换并不是100%会进行的，建议尽量使用表关联来替换in和existsNCSQL标准6、防止在索引列上使用计算如果列上有建索引，存在下面情况的SQL，不会用到索引：存在数据类型隐形转换的,如：select*fromstaff_memberwherestaff_id=’123’；列上有数学运算的，如：select*fromstaff_memberwheresalary*2<10000;使用不等于(<>)运算的，如：select*fromstaff_memberwheredept_no<>2001;NCSQL标准6、防止在索引列上使用计算使用substr字符串函数的，如：select*fromstaff_memberwheresubstr(last_name,1,4)=’FRED’;‘%’通配符在第一个字符的，如:select*fromstaff_memberwherefirst_namelike‘%DON’;字符串连接(||)的，如：select*fromstaff_memberwherefirst_name||’’=’DONALD’NCSQL标准7、IN、OR、UNION、UNIONALL

建议每条SQL语句中in中的元素个数在500以下，如果个数超过时，应拆分为多条SQL语句，禁止使用XXin(‘’,’’…)orxxin(‘’,’’,’’)。禁止使用or超过500，如XX=’123’orxx=’456’

NCSQL标准8、防止使用ISNULL设计中尽量防止字段为NULL，不能用NULL代表业务意义。例：总帐凭证的记帐标示等NC系统里常见〔col1=’’orcolxisnull〕造成诸多效率问题NCSQL标准9、将产生排他锁的操作放到事务的