通过分析执行计划优化SQL课件

通过分析执行计划优化SQL宇信易诚涂彪祯通过分析执行计划优化SQL宇信易诚涂彪祯

Agenda

:SQL语句执行的过程如何产生执行计划如何分析执行计划如何干预执行计划通过分析执行计划优化SQLSQL语句执行的过程通过分析执行计划优化SQL处理SELECT：语法分析：搜索同一语句检查语法、对象名和权限锁定语法分析过程中使用的对象创建和存储执行计划绑定：获取变量值执行：处理语句提取：将结果行返回用户进程处理SELECT：处理DML语句：

语法分析：与处理查询时的语法分析阶段相同。

绑定：与处理查询时的绑定阶段相同。

执行：

如果数据库缓冲区高速缓存中不存在某些数据块和还原块，服务器进程就从数据文件将它们读入数据库缓冲区高速缓存。

服务器进程锁定要进行修改的行.还原块用于存储数据的前像以便在需要时回退DML语句。

数据块记录数据的新值。

服务器进程将数据的前像记录到回退块中，并更新数据块。这两种更改都是在数据库缓冲区高速缓存中进行的。数据库缓冲区高速缓存中所有已更改的块都标记为灰数据缓冲区，即与磁盘中相应的块不同的缓冲区。

DELETE或INSERT命令的处理使用类似的步骤。DELETE命令的前像包含已

删除行中的列值，而INSERT命令的前像中包含行的位置信息。

处理DML语句：

语法分析：与处理查询时的语法分析阶段处理DDL语句：DDL（数据定义语言）语句的执行与DML（数据操纵语言）语句和查询的执行不尽相同，因为成功执行DDL语句需要对数据字典具有写权限。对于这些语句，语法分析阶段实际上包括分析、数据字典查找和执行。处理DDL语句：共享sql语句

为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置sharedbufferpool参数值)和尽可能的使用绑定变量的方法执行SQL语句。

当你向ORACLE提交一个SQL语句，ORACLE会首先在共享内存中查找是否有相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格,换行等)。共享sql语句

为了不重复解析相同的SQL语句(下面是判断SQL语句是否与共享内存中某一SQL相同的步骤：

1).对所发出语句的文本串进行hashed。如果hash值与已在共享池中SQL语句的hash值相同，则进行第2步：

2)

将所发出语句的文本串（包括大小写、空白和注释）与在第１步中识别的所有

已存在的SQL语句相比较。

例如：

SELECT*FROMempWHEREempno=1000;

和下列每一个都不同

SELECT*fromempWHEREempno=1000;

SELECT*FROMEMPWHEREempno=1000;

SELECT*FROMempWHEREempno=2000;

在上面的语句中列值都是直接SQL语句中的，这类sql称为硬编码SQL

使用绑定变量的SQL语句中必须使用相同的名字的绑定变量(bindvariables)，

例如：

a.该2个sql语句被认为相同

selectpin,namefrompeoplewherepin=:blk1.pin;

selectpin,namefrompeoplewherepin=:blk1.pin;

b.该2个sql语句被认为不相同

selectpin,namefrompeoplewherepin=:blk1.ot_ind;

selectpin,namefrompeoplewherepin=:blk1.ov_ind;

今后我们将上面的这类语句称为绑定变量SQL。

下面是判断SQL语句是否与共享内存中某一SQL相同的步骤：

3).将所发出语句中涉及的对象与第２步中识别的已存在语句所涉及对象相比较。

例如:

如用户user1与用户user2下都有EMP表，则

用户user1发出的语句：SELECT*FROMEMP;与

用户user2发出的语句：SELECT*FROMEMP;被认为是不相同的语句，

因为两个语句中引用的EMP不是指同一个表。

4).在SQL语句中使用的捆绑变量的捆绑类型必须一致。

如果语句与当前在共享池中的另一个语句是等同的话，Oracle并不对它进行语法分析。而直接执行该语句，提高了执行效率，因为语法分析比较耗费资源。SQL语句tips1．SELECT子句中避免使用‘*‘

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.

2．使用表的别名(Alias)

当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.3.

sql语句用大写,因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

3).将所发出语句中涉及的对象与第２步中识别的已存在语如何产生执行计划通过分析执行计划优化SQL1.SQLPLUStracesetautotraceon/off/trace[only]EXPLAINSTATISTICSSql>setautotraceonSql>select*fromdual;执行完语句后，会显示explainplan与统计信息。如果不想执行语句而只是想得到执行计划可以采用：Sql>setautotracetraceonly这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时1.SQLPLUStrace2.用explainplan命令settimingonEXPLAINplansetstatement_id='Excu_plan'FORSQLSTATEMENT;用此方法时，并不执行sql语句，所以只会列出执行计划，不会列出统计信,需要用下面的命令格式化输出SELECTLPAD('',2*(LEVEL-1))||operation||''||options||''||object_name||''||decode(id,0,'Cost='||position)"QueryPlan"FROMplan_tableStartwithid=0andstatement_id='Excu_plan'Connectbypriorid=parent_idandstatement_id='Excu_plan';2.用explainplan命令3:ExplainplanforSQLSTATEMENT;Select*fromtable(DBMS_XPLAN.display);4.Tools:Toad,PL/SQL3:5.用dbms_system存储过程生成执行计划1)识别要跟踪的客户端程序到数据库的数据库连接查询session信息selects.sidsid,s.SERIAL#"serial#",s.username,s.machine,gram,p.spidServPID,s.serverfromv$sessions,v$processpwherep.addr=s.paddr;通过分析执行计划优化SQL2)设定相应的参数参数说明：timed_statistics:收集跟踪信息时，是否将收集时间信息，如果收集，则可以知道一个sql的各个执行阶段耗费的时间情况user_dump_dest:存放跟踪数据的文件的位置max_dump_file_size:放跟踪数据的文件的最大值，防止由于无意的疏忽，使跟踪数据的文件占用整个硬盘，影响系统的正常运行SQL>execsys.dbms_system.set_bool_param_in_session(sid=>8,serial#=>3,parnam=>'timed_statistics',bval=>true);SQL>execsys.dbms_system.set_int_param_in_session(sid=>8,serial#=>3,parnam=>'max_dump_file_size',intval=>2147483647)通过分析执行计划优化SQL3)启动跟踪功能SQL>execsys.dbms_system.set_sql_trace_in_session(8,3,true);注意，只有跟踪的session再次发出sql语句后，才会产生trc文件4)让系统运行一段时间，以便可以收集到跟踪数据5)关闭跟踪功能SQL>execsys.dbms_system.set_sql_trace_in_session(8,3,false);6)格式化跟踪数据，得到我们易于理解的跟踪结果。对产生的trace文件进行格式化：在命令提示符下，运行下面的命令tkprofdsdb2_ora_18468.trcdsdb2_trace.outSYS=NOEXPLAIN=SCOTT/TIGER3)启动跟踪功能如何分析执行计划有了查询计划我们可以通过计划查看我们为表建立的索引是否管用，是否使用了我们所期待的索引，是否按照我们期待连接方式去连接.通过分析执行计划优化SQLselectA.col4fromA,B,CwhereB.col3=10andA.col1=B.col1andA.col2=C.col2andC.col3=5;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10MERGEJOIN21SORT(JOIN)32NESTEDLOOPS43TABLEACCESS(FULL)OF'B'53TABLEACCESS(BYINDEXROWID)OF'A'65INDEX(RANGESCAN)OF'INX_COL12A'(NON-UNIQUE)71SORT(JOIN)87TABLEACCESS(FULL)OF'C'Statistics----------------------------------------------------------0recursivecalls8dbblockgets6consistentgets0physicalreadsselectA.col4在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。具体解释如下：得到去除妨碍判断的索引扫描后的执行计划：ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10MERGEJOIN21SORT(JOIN)32NESTEDLOOPS43TABLEACCESS(FULL)OF'B'53TABLEACCESS(BYINDEXROWID)OF'A'71SORT(JOIN)87TABLEACCESS(FULL)OF'C'从这个图中我们可以看到，对于NESTEDLOOPS部分，最右、最上的操作是TABLEACCESS(FULL)OF'B'，所以这一操作先执行，所以该操作对应的B表为第一个驱动表(外部表)，自然，A表就为内部表了。从图中还可以看出，B与A表做嵌套循环后生成了新的rowsource，对该rowsource进行来排序后，与C表对应的排序了的rowsource(应用了C.col3=5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实：B表先与A表做嵌套循环，然后将生成的rowsource与C表做排序—合并连接。在从执行计划中判断出哪个表为驱动表后，根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适，如果不合适，对SQL语句进行更改，使优化器可以选择正确的驱动表。在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的如何干预执行计划

通过分析执行计划优化SQL1.使用hints提示基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。我们可以用hints来实现：1)使用的优化器的类型2)基于代价的优化器的优化目标，是all_rows还是first_rows。3)表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。4)表之间的连接类型5)表之间的连接顺序6)语句的并行程度除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行定期的分析。1.使用hints提示如何使用hints:Hints只应用在它们所在sql语句块(由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。我们可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT,UPDATE,orDELETE关键字的后面使用hints的语法：{DELETE|INSERT|SELECT|UPDATE}/*+hint[text][hint[text]]...*/如何使用hints:指示优化器的方法与目标的hints：ALL_ROWS--基于代价的优化器，以吞吐量为目标FIRST_ROWS(n)--基于代价的优化器，以响应时间为目标CHOOSE--根据是否有统计信息，选择不同的优化器RULE--使用基于规则的优化器例子：SELECT/*+FIRST_ROWS(10)*/employee_id,last_name,salary,job_idFROMemployeesWHEREdepartment_id=20;SELECT/*+CHOOSE*/employee_id,last_name,salary,job_idFROMemployeesWHEREemployee_id=7566;SELECT/*+RULE*/employee_id,last_name,salary,job_idFROMemployeesWHEREemployee_id=7566;指示优化器的方法与目标的hints：指示存储路径的hints：FULL/*+FULL(table)*/指定该表使用全表扫描SELECT/*+FULL(e)*/employee_id,last_nameFROMemployeeseWHERElast_nameLIKE:b1;ROWID/*+ROWID(table)*/指定对该表使用rowid存取方法，该提示用的较少SELECT/*+ROWID(employees)*/*FROMemployeesWHERErowid>'AAAAtkAABAAAFNTAAA'ANDemployee_id=155;INDEX/*+INDEX(table[index])*/使用该表上指定的索引对表进行索引扫描SELECT/*+INDEX(Asex_index)usesex_indexbecausetherearefewmalepatients*/A.name,A.height,A.weightFROMpatientsAWHEREA.sex=’m’;INDEX_FFS/*+INDEX_FFS(table[index])*/使用快速全表扫描NO_INDEX/*+NO_INDEX(table[index])*/不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描SELECT/*+NO_INDEX(employeesemp_empid)*/employee_idFROMemployeesWHEREemployee_id>200;指示存储路径的hints：指示连接顺序的hints:ORDERED/*+ORDERED*/按from字句中表的顺序从左到右的连接select/*+ORDERED*/A.col4fromC,A,BwhereC.col3=5andA.col1=B.col1andA.col2=C.col2andB.col3=10;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=5Card=1Bytes=110)10HASHJOIN(Cost=5Card=1Bytes=110)21HASHJOIN(Cost=3Card=1Bytes=84)32TABLEACCESS(FULL)OF'C'(Cost=1Card=1Bytes=26)42TABLEACCESS(FULL)OF'A'(Cost=1Card=82Bytes=4756)51TABLEACCESS(FULL)OF'B'(Cost=1Card=1Bytes=26)指示连接顺序的hints:指示连接类型的hints：USE_NL/*+USE_NL(table[,table,...])*/使用嵌套连接USE_MERGE/*+USE_MERGE(table[,table,...])*/使用排序--合并连接USE_HASH/*+USE_HASH(table[,table,...])*/使用HASH连接注意：如果表有alias(别名)，则上面的table指的是表的别名，而不是真实的表名指示连接类型的hints：连接类型排序--合并连接(SortMergeJoin(SMJ))嵌套循环(NestedLoops(NL))哈希连接(HashJoin)连接类型排序--合并连接(SortMergeJoin(SMJ))1)首先生成rowsource1需要的数据，然后对这些数据按照连接操作关联列进行排序。2)随后生成rowsource2需要的数据，然后对这些数据按照与sortsource1对应的连接操作关联列进行排序。3)最后两边已排序的行被放在一起执行合并操作,即将2个rowsource按照连接条件连接起来下面是连接步骤的图形表示：MERGE/\SORTSORT||RowSource1RowSource2排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个rowsource都已经预先排序，则这种连接方法的效率也是蛮高的排序--合并连接(SortMergeJoin(SSMJ连接的例子：SQL>explainplanforselect/*+ordered*/e.deptno,d.deptnofromempe,deptdwheree.deptno=d.deptnoorderbye.deptno,d.deptno;QueryPlan-------------------------------------SELECTSTATEMENT[CHOOSE]Cost=17MERGEJOINSORTJOINTABLEACCESSFULLEMP[ANALYZED]SORTJOINTABLEACCESSFULLDEPT[ANALYZED]SMJ连接的例子：嵌套循环(NestedLoops,NL)Rowsource1的Row1----------------Probe->Rowsource2Rowsource1的Row2----------------Probe->Rowsource2Rowsource1的Row3----------------Probe->Rowsource2…….Rowsource1的Rown----------------Probe->Rowsource2Oracle读取rowsource1中的每一行，然后在rowsourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理rowsource1中的下一行。这个过程一直继续，直到rowsource1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。如果使用这种方法，决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好,将小表或返回较小rowsource的表作为驱动表(用于外层循环)。嵌套循环(NestedLoops,NL)假定A、B、C都是不是小表，且在A表上一个组合索引：A(a.col1,a.col2)，注意a.col1列为索引的引导列。考虑下面的查询：selectA.col4fromA,B,CwhereB.col3=10andA.col1=B.col1andA.col2=C.col2andC.col3=5如果没有执行计划，分析一下，上面的3个表应该拿哪一个作为第一个驱动表？从SQL语句看来，只有B表与C表上有限制条件，所以第一个驱动表应该为这2个表中的一个，到底是哪一个呢？B表有谓词B.col3=10，这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3=10)用上，从而得到一个较小的rowsource,所以B表应该作为第一个驱动表。而且这样的话，如果再与A表做关联，可以有效利用A表的索引(因为A表的col1列为leadingcolumn)。当然上面的查询中C表上也有谓词(C.col3=5)，有人可能认为C表作为第一个驱动表也能获得较好的性能。让我们再来分析一下：如果C表作为第一个驱动表，则能保证驱动表生成很小的rowsource，但是看看连接条件A.col2=C.col2，此时就没有机会利用A表的索引，因为A表的col2列不为leadingcolumn，这样nestedloop的效率很差，从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。假定A、B、C都是不是小表，且在A表上一个组合索引：A(a.

select/*+ORDEREDUSE_NL(AC)*/A.col4fromC,A,BwhereC.col3=5andA.col1=B.col1andA.col2=C.col2andB.col3=10;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=4Card=1Bytes=110)10HASHJOIN(Cost=4Card=1Bytes=110)21NESTEDLOOPS(Cost=2Card=1Bytes=84)32TABLEACCESS(FULL)OF'C'(Cost=1Card=1Bytes=26)42TABLEACCESS(FULL)OF'A'(Cost=1Card=82Bytes=4756)51TABLEACCESS(FULL)OF'B'(Cost=1Card=1Bytes=26)

哈希连接(HashJoin,HJ)较小的rowsource被用来构建hashtable与bitmap，第2个rowsource被用来被hansed，并与第一个rowsource生成的hashtable进行匹配，以便进行进一步的连接。被构建为hashtable与bitmap的表为驱动表，当被构建的hashtable与bitmap能被容纳在内存中时，这种连接方式的效率极高。哈希连接(HashJoin,HJ)HASH连接的例子：SQL>explainplanforselect/*+use_hash(emp)*/empnofromemp,deptwhereemp.deptno=dept.deptno;QueryPlan----------------------------SELECTSTATEMENT[CHOOSE]Cost=3HASHJOINTABLEACCESSFULLDEPTTABLEACCESSFULLEMPHASH连接的例子：2.STOREDOUTLINES当搜集统计信息以采样方式运行、搜集统计信息可能给某些特定SQL带来危害、无法对源代码进行修改等情况下，为了保证产品数据库的良好运行，我们需要稳定执行计划。2.STOREDOUTLINESselectcount(*)fromTEST_OTL;执行计划|Id|Operation|Name|Rows|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|27(4)|00:00:01||1|SORTAGGREGATE||1||||2|INDEXFASTFULLSCAN|IDX1_TEST_OTL|49513|27(4)|00:00:01|-------------------------------------------------------------------------------select/*+index(IDX1_TEST_OTLTEST_OTL)*/count(*)fromTEST_OTL;执行计划|Id|Operation|Name|Rows|Cost(%CPU)|Time|-----------------------------------------------------------------------|0|SELECTSTATEMENT||1|157(2)|00:00:02||1|SORTAGGREGATE||1||||2|TABLEACCESSFULL|TEST_OTL|49513|157(2)|00:00:02|-----------------------------------------------------------------------通过分析执行计划优化SQL创建outline

语法如下:

create[orreplace]outlineoutline_name[forcategorycategory_name]onsql_statement;altersessionsetcreate_stored_outlines=demo_myol;createorreplaceoutlineoutln1forcategoryDEMO_MYOLonselectcount(*)fromTEST_OTL;createorreplaceoutlineoutln2forcategoryDEMO_MYOLonselect/*+index(IDX1_TEST_OTLTEST_OTL)*/count(*)fromTEST_OTL;altersessionsetcreate_stored_outlines=false;创建outline

语法如下:

create[orrepsql查询时启用stored_outlines

使用altersystemsetuse_stored_outlines=true/category(名)命令在系统层启用

使用altersessionsetuse_stored_outlines=true/category(名)命令在会话层启用e.g.altersessionsetuse_stored_outlines=true;altersessionsetuse_stored_outlines=DEMO_MYOL;sql查询时启用stored_outlines

确定和删除不再使用的stored_outlinesselect*fromdba_outlineswhereused='UNUSED'executeoutln_pkg.drop_unused;Execoutln_pkg.drop_by_cat('MY_TEST');用来删除某一特定category的所有outlinee.g.execoutline.drop_by_cat('DEMO_MYOL');确定和删除不再使用的stored_outlinesstored_outlines的考虑

如果表中记录数经常变动,你可能不希望使用优化器去稳定执行计划,因为,你希望CBO基于当前的CBO统计资料为每一个SQL语句重新生成执行计划

如果表相对稳定,那么使用stored_outlines将会很大程度提高SQL的性能,因为这样做将绕过SQL解析过程(即使Oracle初始化参数和CBO统计资料变更时)

如果你选择为所有的SQL实施存储框架,这种情况下只有新的查询才会使用CBO统计资料,因此,你可以使用这个办法降低表和索引进行重新分析的次数stored_outlines的考虑THANKYOUTHANKYOU通过分析执行计划优化SQL宇信易诚涂彪祯通过分析执行计划优化SQL宇信易诚涂彪祯

Agenda

:SQL语句执行的过程如何产生执行计划如何分析执行计划如何干预执行计划通过分析执行计划优化SQLSQL语句执行的过程通过分析执行计划优化SQL处理SELECT：语法分析：搜索同一语句检查语法、对象名和权限锁定语法分析过程中使用的对象创建和存储执行计划绑定：获取变量值执行：处理语句提取：将结果行返回用户进程处理SELECT：处理DML语句：

语法分析：与处理查询时的语法分析阶段相同。

绑定：与处理查询时的绑定阶段相同。

执行：

如果数据库缓冲区高速缓存中不存在某些数据块和还原块，服务器进程就从数据文件将它们读入数据库缓冲区高速缓存。

服务器进程锁定要进行修改的行.还原块用于存储数据的前像以便在需要时回退DML语句。

数据块记录数据的新值。

服务器进程将数据的前像记录到回退块中，并更新数据块。这两种更改都是在数据库缓冲区高速缓存中进行的。数据库缓冲区高速缓存中所有已更改的块都标记为灰数据缓冲区，即与磁盘中相应的块不同的缓冲区。

DELETE或INSERT命令的处理使用类似的步骤。DELETE命令的前像包含已

删除行中的列值，而INSERT命令的前像中包含行的位置信息。

处理DML语句：

语法分析：与处理查询时的语法分析阶段处理DDL语句：DDL（数据定义语言）语句的执行与DML（数据操纵语言）语句和查询的执行不尽相同，因为成功执行DDL语句需要对数据字典具有写权限。对于这些语句，语法分析阶段实际上包括分析、数据字典查找和执行。处理DDL语句：共享sql语句

为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置sharedbufferpool参数值)和尽可能的使用绑定变量的方法执行SQL语句。

当你向ORACLE提交一个SQL语句，ORACLE会首先在共享内存中查找是否有相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格,换行等)。共享sql语句

为了不重复解析相同的SQL语句(下面是判断SQL语句是否与共享内存中某一SQL相同的步骤：

1).对所发出语句的文本串进行hashed。如果hash值与已在共享池中SQL语句的hash值相同，则进行第2步：

2)

将所发出语句的文本串（包括大小写、空白和注释）与在第１步中识别的所有

已存在的SQL语句相比较。

例如：

SELECT*FROMempWHEREempno=1000;

和下列每一个都不同

SELECT*fromempWHEREempno=1000;

SELECT*FROMEMPWHEREempno=1000;

SELECT*FROMempWHEREempno=2000;

在上面的语句中列值都是直接SQL语句中的，这类sql称为硬编码SQL

使用绑定变量的SQL语句中必须使用相同的名字的绑定变量(bindvariables)，

例如：

a.该2个sql语句被认为相同

selectpin,namefrompeoplewherepin=:blk1.pin;

selectpin,namefrompeoplewherepin=:blk1.pin;

b.该2个sql语句被认为不相同

selectpin,namefrompeoplewherepin=:blk1.ot_ind;

selectpin,namefrompeoplewherepin=:blk1.ov_ind;

今后我们将上面的这类语句称为绑定变量SQL。

下面是判断SQL语句是否与共享内存中某一SQL相同的步骤：

3).将所发出语句中涉及的对象与第２步中识别的已存在语句所涉及对象相比较。

例如:

如用户user1与用户user2下都有EMP表，则

用户user1发出的语句：SELECT*FROMEMP;与

用户user2发出的语句：SELECT*FROMEMP;被认为是不相同的语句，

因为两个语句中引用的EMP不是指同一个表。

4).在SQL语句中使用的捆绑变量的捆绑类型必须一致。

如果语句与当前在共享池中的另一个语句是等同的话，Oracle并不对它进行语法分析。而直接执行该语句，提高了执行效率，因为语法分析比较耗费资源。SQL语句tips1．SELECT子句中避免使用‘*‘

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.

2．使用表的别名(Alias)

当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.3.

sql语句用大写,因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

3).将所发出语句中涉及的对象与第２步中识别的已存在语如何产生执行计划通过分析执行计划优化SQL1.SQLPLUStracesetautotraceon/off/trace[only]EXPLAINSTATISTICSSql>setautotraceonSql>select*fromdual;执行完语句后，会显示explainplan与统计信息。如果不想执行语句而只是想得到执行计划可以采用：Sql>setautotracetraceonly这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时1.SQLPLUStrace2.用explainplan命令settimingonEXPLAINplansetstatement_id='Excu_plan'FORSQLSTATEMENT;用此方法时，并不执行sql语句，所以只会列出执行计划，不会列出统计信,需要用下面的命令格式化输出SELECTLPAD('',2*(LEVEL-1))||operation||''||options||''||object_name||''||decode(id,0,'Cost='||position)"QueryPlan"FROMplan_tableStartwithid=0andstatement_id='Excu_plan'Connectbypriorid=parent_idandstatement_id='Excu_plan';2.用explainplan命令3:ExplainplanforSQLSTATEMENT;Select*fromtable(DBMS_XPLAN.display);4.Tools:Toad,PL/SQL3:5.用dbms_system存储过程生成执行计划1)识别要跟踪的客户端程序到数据库的数据库连接查询session信息selects.sidsid,s.SERIAL#"serial#",s.username,s.machine,gram,p.spidServPID,s.serverfromv$sessions,v$processpwherep.addr=s.paddr;通过分析执行计划优化SQL2)设定相应的参数参数说明：timed_statistics:收集跟踪信息时，是否将收集时间信息，如果收集，则可以知道一个sql的各个执行阶段耗费的时间情况user_dump_dest:存放跟踪数据的文件的位置max_dump_file_size:放跟踪数据的文件的最大值，防止由于无意的疏忽，使跟踪数据的文件占用整个硬盘，影响系统的正常运行SQL>execsys.dbms_system.set_bool_param_in_session(sid=>8,serial#=>3,parnam=>'timed_statistics',bval=>true);SQL>execsys.dbms_system.set_int_param_in_session(sid=>8,serial#=>3,parnam=>'max_dump_file_size',intval=>2147483647)通过分析执行计划优化SQL3)启动跟踪功能SQL>execsys.dbms_system.set_sql_trace_in_session(8,3,true);注意，只有跟踪的session再次发出sql语句后，才会产生trc文件4)让系统运行一段时间，以便可以收集到跟踪数据5)关闭跟踪功能SQL>execsys.dbms_system.set_sql_trace_in_session(8,3,false);6)格式化跟踪数据，得到我们易于理解的跟踪结果。对产生的trace文件进行格式化：在命令提示符下，运行下面的命令tkprofdsdb2_ora_18468.trcdsdb2_trace.outSYS=NOEXPLAIN=SCOTT/TIGER3)启动跟踪功能如何分析执行计划有了查询计划我们可以通过计划查看我们为表建立的索引是否管用，是否使用了我们所期待的索引，是否按照我们期待连接方式去连接.通过分析执行计划优化SQLselectA.col4fromA,B,CwhereB.col3=10andA.col1=B.col1andA.col2=C.col2andC.col3=5;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10MERGEJOIN21SORT(JOIN)32NESTEDLOOPS43TABLEACCESS(FULL)OF'B'53TABLEACCESS(BYINDEXROWID)OF'A'65INDEX(RANGESCAN)OF'INX_COL12A'(NON-UNIQUE)71SORT(JOIN)87TABLEACCESS(FULL)OF'C'Statistics----------------------------------------------------------0recursivecalls8dbblockgets6consistentgets0physicalreadsselectA.col4在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。具体解释如下：得到去除妨碍判断的索引扫描后的执行计划：ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10MERGEJOIN21SORT(JOIN)32NESTEDLOOPS43TABLEACCESS(FULL)OF'B'53TABLEACCESS(BYINDEXROWID)OF'A'71SORT(JOIN)87TABLEACCESS(FULL)OF'C'从这个图中我们可以看到，对于NESTEDLOOPS部分，最右、最上的操作是TABLEACCESS(FULL)OF'B'，所以这一操作先执行，所以该操作对应的B表为第一个驱动表(外部表)，自然，A表就为内部表了。从图中还可以看出，B与A表做嵌套循环后生成了新的rowsource，对该rowsource进行来排序后，与C表对应的排序了的rowsource(应用了C.col3=5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实：B表先与A表做嵌套循环，然后将生成的rowsource与C表做排序—合并连接。在从执行计划中判断出哪个表为驱动表后，根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适，如果不合适，对SQL语句进行更改，使优化器可以选择正确的驱动表。在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的如何干预执行计划

通过分析执行计划优化SQL1.使用hints提示基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。我们可以用hints来实现：1)使用的优化器的类型2)基于代价的优化器的优化目标，是all_rows还是first_rows。3)表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。4)表之间的连接类型5)表之间的连接顺序6)语句的并行程度除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行定期的分析。1.使用hints提示如何使用hints:Hints只应用在它们所在sql语句块(由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。我们可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT,UPDATE,orDELETE关键字的后面使用hints的语法：{DELETE|INSERT|SELECT|UPDATE}/*+hint[text][hint[text]]...*/如何使用hints:指示优化器的方法与目标的hints：ALL_ROWS--基于代价的优化器，以吞吐量为目标FIRST_ROWS(n)--基于代价的优化器，以响应时间为目标CHOOSE--根据是否有统计信息，选择不同的优化器RULE--使用基于规则的优化器例子：SELECT/*+FIRST_ROWS(10)*/employee_id,last_name,salary,job_idFROMemployeesWHEREdepartment_id=20;SELECT/*+CHOOSE*/employee_id,last_name,salary,job_idFROMemployeesWHEREemployee_id=7566;SELECT/*+RULE*/employee_id,last_name,salary,job_idFROMemployeesWHEREemployee_id=7566;指示优化器的方法与目标的hints：指示存储路径的hints：FULL/*+FULL(table)*/指定该表使用全表扫描SELECT/*+FULL(e)*/employee_id,last_nameFROMemployeeseWHERElast_nameLIKE:b1;ROWID/*+ROWID(table)*/指定对该表使用rowid存取方法，该提示用的较少SELECT/*+ROWID(employees)*/*FROMemployeesWHERErowid>'AAAAtkAABAAAFNTAAA'ANDemployee_id=155;INDEX/*+INDEX(table[index])*/使用该表上指定的索引对表进行索引扫描SELECT/*+INDEX(Asex_index)usesex_indexbecausetherearefewmalepatients*/A.name,A.height,A.weightFROMpatientsAWHEREA.sex=’m’;INDEX_FFS/*+INDEX_FFS(table[index])*/使用快速全表扫描NO_INDEX/*+NO_INDEX(table[index])*/不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描SELECT/*+NO_INDEX(employeesemp_empid)*/employee_idFROMemployeesWHEREemployee_id>200;指示存储路径的hints：指示连接顺序的hints:ORDERED/*+ORDERED*/按from字句中表的顺序从左到右的连接select/*+ORDERED*/A.col4fromC,A,BwhereC.col3=5andA.col1=B.col1andA.col2=C.col2andB.col3=10;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=5Card=1Bytes=110)10HASHJOIN(Cost=5Card=1Bytes=110)21HASHJOIN(Cost=3Card=1Bytes=84)32TABLEACCESS(FULL)OF'C'(Cost=1Card=1Bytes=26)42TABLEACCESS(FULL)OF'A'(Cost=1Card=82Bytes=4756)51TABLEACCESS(FULL)OF'B'(Cost=1Card=1Bytes=26)指示连接顺序的hints:指示连接类型的hints：USE_NL/*+USE_NL(table[,table,...])*/使用嵌套连接USE_MERGE/*+USE_MERGE(table[,table,...])*/使用排序--合并连接USE_HASH/*+USE_HASH(table[,table,...])*/使用HASH连接注意：如果表有alias(别名)，则上面的table指的是表的别名，而不是真实的表名指示连接类型的hints：连接类型排序--合并连接(SortMergeJoin(SMJ))嵌套循环(NestedLoops(NL))哈希连接(HashJoin)连接类型排序--合并连接(SortMergeJoin(SMJ))1)首先生成rowsource1需要的数据，然后对这些数据按照连接操作关联列进行排序。2)随后生成rowsource2需要的数据，然后对这些数据按照与sortsource1对应的连接操作关联列进行排序。3)最后两边已排序的行被放在一起执行合并操作,即将2个rowsource按照连接条件连接起来下面是连接步骤的图形表示：MERGE/\SORTSORT||RowSource1RowSource2排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个rowsource都已经预先排序，则这种连接方法的效率也是蛮高的排序--合并连接(SortMergeJoin(SSMJ连接的例子：SQL>explainplanforselect/*+ordered*/e.deptno,d.deptnofromempe,deptdwheree.deptno=d.deptnoorderbye.deptno,d.deptno;QueryPlan-------------------------------------SELECTSTATEMENT[CHOOSE]Cost=17MERGEJOINSORTJOINTABLEACCESSFULLEMP[ANALYZED]SORTJOINTABLEACCESSFU