第二部分 ORACLE 高效SQL分析1(方法)

第部分高效分析第二部分ORACLE高效SQL分析亿阳信通无线与交换MSN:[e-mail]：[e-mail]@126.com张家宏2009-12-021培训大纲培训大纲¾SQL优化基础–Oracle内存空间–SQL的执行过程SQL的执行过程–使SQL语句共享Ol优化器–Oracle优化器¾数据读取方式(全表，ROWID，索引)¾表连接实现方式(SMJ,NL,HJ)¾用提示调整执行计划用提示调整执行计划¾用OutLine固定执行计划2Oracle内存结构SGA+PGAOracle内存结构SGAPGASGA（SystemGlobalArea）是Oracle各个进程共用的一块巨大的共享内存区域。SQLLibCh缓存解析后的SQLSharedpool共享池DataDictionaryCache：缓冲解析SQL或SQLLibraryCache：缓存解析后的SQL信息，供下次直接使用，避免再次解析DataBufferCache:缓存从磁盘读取的数据Oracle所有的数DataDictionaryCache：缓冲解析SQL或其他Oracle运作需要的数据字典据，Oracle所有的数据处理都在该内存区完成之后再写入磁盘RedologbufferJavaPoolLargePool3SGA和高效SQLSGA和高效SQL¾SGA是Oracle运作的核心区域，分析SGA的作用，不难得出以下¾SGA是Oracle运作的核心区域，分析SGA的作用，不难得出以下结论：–当该次访问的SQL语句已经缓存在SGA中那么该SQL就很可能不–当该次访问的SQL语句已经缓存在SGA中，那么该SQL就很可能不用再解析，缩短了SQL的执行时间；当该次访问的数据已经缓存在SGA中那么很可能就不会再从磁–当该次访问的数据已经缓存在SGA中，那么很可能就不会再从磁盘读取了，减少了磁盘的I/O；在中的数据和越多/开销就越少性能就越好–在SGA中的数据和SQL越多，I/O开销就越少，性能就越好；4SQL执行过程概述SQL执行过程概述¾方式一：硬解析SQL初次执行–硬解析：SQL初次执行（1）语法检测(selecselect)（2）语义检查(对象权限？)（2）语义检查(对象，权限？)–优化器(Optimizer)（1）WHERE子句信息（2）优化模式(CBO,RBO)（3）生成执行计划Plan–执行Plan取得所需数据¾方式二软解析SQL及Pl已在SGA中–软解析：SQL及Plan已在SGA中–直接执行Plan取得所需数据SQL共享可以实现软解析！5SQL共享可以实现软解析！SQL共享实现高速软解析SQL共享实现高速软解析¾SQL共享是实现软解析的方法，基本条件：–SQL语句文本的ASCII码值相同；比如：大小写，空格，换行等等；比如：大小写，空格，换行等等；例如:SELECT*FROMTB_EMP;和下列每一个都不同SELECT*fTBEMP(大小写)SELECT*fromTB_EMP;(大小写)SELECT*FROMTB_EMP;(空格不对)SELECT*换行FROMTB_EMP;(换行)–SQL语句所指的对象必须完全相同；表表TB_ABC：selectcount(1)fromtb_abc和同义词TB_ABC：selectcount(1)fromtb_abc是不相同的；6SQL变量绑定实现高速软解析SQL变量绑定实现高速软解析¾绑定变量并且同名如有以下100条语句：selectnamefromtb_awherepin=1;selectnamefromtb_awherepin=2;……selectnamefromtbawherepin=100;_p;上述100语句仅仅只是WHERpin=值不同，都需要硬解析，如果将pin的值1…100先分别赋给变量v_pin,再用selectnamefromtb_awherepin=:v_pin就可以使上述100条SQL语句共享的目的!注意，变量需同名如下变量绑定的sql之间不会共享：selectnamefromtb_awherepin=:v_pin7selectnamefromtb_awherepin=:t_pinJAVA中的变量绑定JAVA中的变量绑定¾oracle绑定变量的java代码如下：Stringv_id='xxxxx';//嵌入绑定变量Stringv_sql='selectnamefromtable_awhereid=?';stmt=conprepareStatement(vsql);stmt=con.prepareStatement(v_sql);stmt.setString(1,v_id);//为绑定变量赋值stmt.executeQuery();8优化器及模式¾优化器(Optimizer)的操作步骤：•计算表达式和条件优化器及模式计算表式和条件•语句转换：eg对View的访问转换为对基表的访问•选择优化模式：CBO还是RBO•选择访问路径：where子句，确定可用访问路径，索引扫描......•选择join顺序：from，统计，驱动表，第1步，第2步......•选择join方法，HashJoin/NestLoop/Sort-Merge•生成执行计划(RBO)，或者生成各执行计划并选成本最低的计划(CBO)RBO(Rule-BasedOptimizer)CBO(Cost-BasedOptimizer)(where/from)执行(where/from)执行计划Statistics统计信息计划Oracle规则计划9优化模式CBO¾CBO(CostBasedOptimizer)——依据统计分析估算成本并选择最低成本的执行计划优化模式CBO依据统计分析估算成本并选择最低成本的执行计划¾Oracle默认模式是CHOOSECBO可用则用不然用RBO；——CBO可用则用不然用RBO；¾使用CBO必须有统计信息：ltblbtttitianalyzetableabccomputestatistics;analyzetableabcestimatestatisticssample20percent;¾提示与¾提示与CBO：（1）除了rule之外的提示都会会激发CBO优化器;（2）如果有提示（hints）存在，那么会依提示确定获取路径;（3）对有提示的语句中引用的表和索引进行统计;¾FIRSTROWS：CHOOSE+优先返回10优先返回¾ALLROWS：CHOOSE+得到全部值再返回查看SQL执行计划执行计划主要查看：访问路径，连接顺序，连接方法OPTIMIZER当前优化程序的模式对具体执行步骤的描述对具体执行步骤的描述OBJECT_OWNER相关表或索引的所有者OBJECTNAME表或索引的名称OBJECT_NAME表或索引的名称COSTCBO估算的操作开销(RBO为空)，用于比较开销大小的权重值，无单位CARDINALITYCBO下对操作所访问行数的估计值11BYTESCBO对操作所访问字节的估计怎么看SqlPlan的执行顺序怎么看SqlPlan的执行顺序方法一：使用工具中的四个小箭头；方法二：内上原则：内层先执行，同层次时上层先执行。方法：内上原则：内层先执行，同层次时上层先执行。12SQL访问基础SQL访问基础¾Oracle访问数据的存取方法–1、全表扫描（FullTableScans,FTS）–2、用ROWID访问表（TableAccessbyROWID）–3、索引扫描（IndexScan）（3.1）索引唯一扫描(indexuniquescan)（）索扫描（3.2）索引范围扫描(indexrangescan)（3.3）索引全扫描(indexfullscan)（3.4）索引快速扫描(indexfastfullscan)¾表之间的连接方法–排序合并连接(SortMergeJoin,SMJ)(gJJ)–嵌套循环(NestedLoops,NL)–哈希连接(HashJoin,HJ)13哈希连接(HashJoin,HJ)Oracle访问数据之全表扫描Oracle访问数据之全表扫描¾全表扫描（FullTableScans,FTS）(1)全表扫描：Oracle顺序地读取分配给表的每个数据块的每一行，并检(1)全表扫描：Oracle顺序地读取分配给表的每个数据块的每行，并检查每一行是否满足语句的WHERE限制条件，直到读到表的最高水线处(highwatermark,HWM，标识表的最后一个数据块)。(2)可设定一次I/O读取多块数据块(dbfilemultiblockreadcount参数)这(2)可设定一次I/O读取多块数据块(db_file_multiblock_read_count参数)，这极大的减少了I/O次数，可以十分高效地完成全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。(3)由于dlt操作不影响HWM值所以dlt个表后其全表扫描的时(3)由于delete操作不影响HWM值，所以delete一个表后，其全表扫描的时间不会有改善。(4)缩减HWM的方法(a)truncate命令使HWM值归为0。(b)oracle10G后，可收缩的锁以Shrink收缩HWM的值，行级锁。(c)altertableMOVE，由FTS模式读入的数据被放到高速缓存的LeastRecentlyUsed(LRU)列表的尾部，这样可以使其快速交换出内存，不会影响内存中重要的数据。使用FTS的前提条件：大表上不建议使用全表扫描，除非取出数据的比14使用的前提条件大表不建使用全表扫描，除非取出数据的较多，超过总量的10%，或你想使用并行查询功能时。使用全表扫描的场合使用全表扫描的场合¾在必须使用全表扫描的场合：¾在必须使用全表扫描的场合：（1）无可用索引；（）返的数据量为大表的左右（2）返回的数据量为大表的25%左右；（3）对小表的访问；（4）SQL中有全表扫描提示/*+full(tab)*/；¾提高全表扫描的方法：（1）db_file_multiblock_read（2）/*+full(tab)parallel(tab,10)*/（）/()p(,)/并行度(10)：一般为CPU数和表所在的磁盘数的最小值15避免全表扫描的场合避免全表扫描的场合在¾在不必使用全表扫描时：消除全表扫描导致全表扫描的问题解决方法导致全表扫描的问题解决方法没有索引建立索引，索引提示(hints)使用NULL：wherecolisnull内置函数索引：nvl(col,0)=0使用llik‘%b’反向索引(l)lik‘%b’使用collike‘%abc’反向索引：reverse(col)like‘%abc’使用了不等:,!=,notin转化逻辑，关联查询内置函数索引使用不当规范使用16Oracle访问数据之用ROWID访问Oracle访问数据之用ROWI访问¾用ROWID访问表（TableAccessbyROWID）最快！¾用ROWID访问表（TableAccessbyROWID）最快！——行的ROWID指出了该行所在的数据文件、数据块以及行在块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle置，所以通过来存取数据可以快速定位到目标数据上，是存取单行数据的最快方法。为了通过ROWID存取表Ol首先要获取被选择行的ROWID或——为了通过ROWID存取表，Oracle首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。——这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。——ROWID方式几乎不能直接指定(由我们提供数据行的ROWID)，而是通过索引扫描来间接的实现，所以本质问题还是索引使用！17Oracle访问数据之索引扫描Oracle访问数据之索引扫描¾索引扫描（IndexScan