sql优化经验总结V课件

ORACLESQL优化经验交流

系统支撑部董建忠1ORACLESQL优化经验交流

第一章:概述数据库优化的几个环节什么样的SQL需要优化常见的问题发现问题的方法2第一章:概述数据库优化的几个环节2www.si-tech.c数据库优化的几个环节物理存储IO均衡数据库规划合理（参数、表空间分布）数据表和索引的设计SQL语句的优化3数据库优化的几个环节物理存储IO均衡3www.si-tech什么样的SQL需要优化引发严重的等待事件（IO、锁）消耗大量的系统资源（CPU/IO/MEM）运行时间超长（排序段、回滚段占用太大）不能满足压力测试指标4什么样的SQL需要优化引发严重的等待事件（IO、锁）4www常见的问题没有恰当的索引（全表扫描问题）索引不适当（索引低效、无用）重编译问题（程序开发问题）多表关联条件不当或关联太多分区表没有分析，未能使用索引（维护问题）死锁（应用逻辑问题）5常见的问题没有恰当的索引（全表扫描问题）5www.si-te发现问题的方法从v$session_wait查看等待事件SQLPLUS使用AUTOTRACE查看执行计划在TOAD中直接查看执行计划从STATSPACK查看资源(CPU、I/O)消耗状况生成SESSIONTRACE文件（一般为DBA使用）用命令tkprof对TRACE文件进行分析6发现问题的方法从v$session_wait查看等待事件6w第二章：从等待事件中发现问题查看SESSIONWAIT的语句典型事件：DbFileSequentialRead典型事件：DbFileSequentialRead典型事件：LatchFree（latch释放）7第二章：从等待事件中发现问题查看SESSIONWAIT的查看SESSIONWAIT的语句setpagesize2000setlinesize110coleventformata25colprogramformata20selecta.event,substr(gram,1,20)program,b.sid,a.p1,a.p2,a.p3fromgv$session_waita,v$sessionbwherea.sid=b.sidanda.eventnotlike'%SQL%'anda.eventnotlike'%message%'anda.eventnotlike'%time%'8查看SESSIONWAIT的语句setpagesize典型事件：DbFileScatteredRead

数据文件分散读取这种情况通常显示与全表扫描相关的等待。一般表明该表找不到索引，或者只能找到有限的索引。特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。建议将小而常用的表CACHE到内存中，以避免一次又一次地重复读取它们9典型事件：DbFileScatteredRead

数典型事件：DbFileSequentialRead

数据文件顺序读取这一事件通常显示单个块的读取(如索引读取)表示表的连接顺序不佳，或者使用了不恰当的索引检查每个扫描是否必要的，并检查多表连接的连接顺序一般会消耗大量PGA内存，从而在顺序读取时导致大量等待。10典型事件：DbFileSequentialRead

典型事件：LatchFree（latch释放）latch是一种低级排队机制，用于保护系统全局区域（SGA）中共享内存结构。latch就像是一种快速地被获取和释放的内存锁。latch用于防止共享内存结构被多个用户同时访问。如果latch不可用，就会记录latch释放失败。大多数latch问题都与以下操作相关：不能使用绑定变量（库缓存latch）、重复生成问题（重复分配latch）、缓冲存储器竞争问题（缓冲器存储LRU链），以及缓冲存储器中的“热”块（缓冲存储器链）。也有一些latch等待与bug（程序错误）有关当latch不命中率大于0.5%时，就应当研究这一问题11典型事件：LatchFree（latch释放）latc第三章：SQL语句的执行计划SQL语句的执行步骤ORACLE的优化器在SQLPLUS配置AUTOTRACE使用QUESTTOAD查看执行计划安装AUTOTRACE环境使用QUESTTOAD查看执行计划查看执行计划12第三章：SQL语句的执行计划SQL语句的执行步骤12www.SQL语句的执行步骤语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。表达式转换，将复杂的SQL表达式转换为较简单的等效连接表达式。选择优化器，不同的优化器一般产生不同的“执行计划”选择连接方式，ORACLE有三种连接方式，对多表连接ORACLE可选择适当的连接方式。选择连接顺序，对多表连接ORACLE选择哪一对表先连接，选择这两表中哪个表做为源数据表。选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。运行“执行计划”13SQL语句的执行步骤语法分析，分析语句的语法是否符合规范ORACLE的优化器ORACLE有两种优化器：基于规则的优化器（RBO，RuleBasedOptimizer），和基于代价的优化器（CBO，CostBasedOptimizer）ORACLEV7以来缺省的设置应是“choose”，即如果对已分析的表查询的话选择CBO，否则选择RBO。如果该参数设为“rule”，则不论表是否分析过，一概选用RBO，除非在语句中用hint强制各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，须要分析表和相关的索引，才能搜集到CBO所需的数据14ORACLE的优化器ORACLE有两种优化器：基于规则在SQLPLUS配置AUTOTRACEAUTOTRACE参数解释SETAUTOTRACEOFF不能获得AUTOTRACE报告.这是默认的.SETAUTOTRACEONEXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SETAUTOTRACEONSTATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SETAUTOTRACEON包括上面两项内容的AUTOTRACE报告SETAUTOTRACETRACEONLY与SET

AUTOTRACE

ON类似,所有的统计和数据都在，但不可以打印15在SQLPLUS配置AUTOTRACEAUTOTRACE安装AUTOTRACE环境1、首先创建PLUSTRACE角色并且赋给DBA：Sql>@$ORACLE_HOME/sqlplus/admin/plustrce.sqlSql>grantplustracetopublic2、赋权限给用户Sql>GRANTPLUSTRACETOUSER（预赋权的用户名）;3、以SYSTEM用户创建PLAN_TABLE表Sql>@$ORACLE_HOME/rdbms/admin/utlplan.sqlSql>createpublicsynonymplan_tableforplan_table;Sql>grantallonplan_tabletopublic;在每个用户下设置AUTOTRACE可显示其执行计划。16安装AUTOTRACE环境1、首先创建PLUSTRACE角使用QUESTTOAD查看执行计划安装QUESTTOAD软件建立数据库连接进入SQL语句执行窗口输入并选定SQL语句在’SQL-WINDOW’菜单中选‘EXPLAINPLANCURRENTSQL’，即可看到执行计划，并不真正执行语句，不需要等待结果17使用QUESTTOAD查看执行计划安装QUESTTOA查看执行计划ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'TEST'Statistics0recursivecalls0dbblockgets4consistentgets0physicalreads0redosize547bytessentviaSQL*Nettoclient655bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)4rowsprocessed18查看执行计划ExecutionPlan18www.si-t第四章：如何分析问题的原因查找原因的一般步骤19第四章：如何分析问题的原因查找原因的一般步骤19www.si查找原因的步骤（一）检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句看采用了哪种类型的连接方式。ORACLE的共有SortMergeJoin（归并SMJ）、HashJoin（散列HJ）和NestedLoopJoin（嵌套循环NL）。在两张表连接，且内表的目标列上建有索引时，只有NestedLoop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响20查找原因的步骤（一）检查被索引的列或组合索引的首列是否出现在查找原因的步骤（二）看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描21查找原因的步骤（二）看连接顺序是否允许使用相关索引。假设表e查找原因的步骤（三）是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降索引列是否函数的参数。如是，索引在查询时用不上是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致第六种现象的发生22查找原因的步骤（三）是否用到系统数据字典表或视图。由于系统数查找原因的步骤（四）需要定期分析表和索引如果数据经常有增、删、改的表，最好定期对表和索引进行分析，可用SQL语句“analyzetablexxxxcomputestatisticsforallindexes;”。ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择23查找原因的步骤（四）需要定期分析表和索引23www.si-t查找原因的步骤（五）索引列的选择性不高（字段值重复率高）假设有表emp，共有一百万行数据，但其中的emp.deptno列数据只有4种不同的值，如10、20、30、40。ORACLE缺省认定表中列的值是在所有数据行均匀分布的。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE因此对索引“视而不见”，认为该索引的选择性不高24查找原因的步骤（五）索引列的选择性不高（字段值重复率高）2查找原因的步骤（六）索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些需要返回NULL值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引中存储值不能为全空看是否有用到并行查询（PQO）。并行查询将不会用到索引看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值，在做非相等连接时，如“<”，“>”,“like”等。ORACLE将引用缺省值，在某些情况下会对执行计划造成影响25查找原因的步骤（六）索引列值是否可为空（NULL）。如果索引第五章：SQL重编译问题SQL共享原理SQL共享的三个条件PROC程序的SQL共享PROC程序中以下类型的语句不需进行变量绑定PROC程序的CLIENT参数存储过程的SQL共享SQL共享的数据库参数的利弊26第五章：SQL重编译问题SQL共享原理26www.si-teSQL共享原理ORACLE将执行过的SQL语句存放在内存的共享池(sharedbufferpool)中，可以被所有的数据库用户共享当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径.这个功能大大地提高了SQL的执行性能并节省了内存的使用27SQL共享原理ORACLE将执行过的SQL语句存放在内存的SQL共享的三个条件当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）两个语句所指的对象必须完全相同（同义词与表是不同的对象）两个SQL语句中必须使用相同的名字的绑定变量(bindvariables)28SQL共享的三个条件当前被执行的语句和共享池中的语句必须完全PROC程序的SQL共享未使用绑定变量的语句sprintf(sqlstr,"insertintoscott.test1(num1,num2)values(%d,%d)",n_var1,n_var2);EXECSQLEXECUTEIMMEDIATE:sqlstr;EXECSQLCOMMIT;使用绑定变量的语句 strcpy(sqlstr,"insertintotest(num1,num2)values(:v1,:v2)"); EXECSQLPREPAREsql_stmtFROM:sqlstr; EXECSQLEXECUTEsql_stmtUSING:n_var1,:n_var2; EXECSQLCOMMIT;动态表也可以使用以上方式避免重编译29PROC程序的SQL共享未使用绑定变量的语句29www.sPROC程序中以下类型的语句不需进行变量绑定for(i=0;i<10000;i++){EXECSQLinsertintotab_test1(id)selectidfromtab_testwhereid=:i;EXECSQLCOMMIT;}本语句在数据库解析后的结果insertintotab_test1(id)selectidfromtab_testwhereid=:b030PROC程序中以下类型的语句不需进行变量绑定for(iPROC程序的CLIENT参数（一）PROC编译参数，控制客户端游标缓存，使用的是本地的内存，与服务器无关HOLD_CURSOR=yes－－保留游标在缓存中，默认为noRELEASE_CURSOR=no－－释放缓存,默认为no这两个参数也可以在程序中设置EXECORACLEOPTION(HOLD_CURSOR=NO);EXECORACLEOPTION(RELEASE_CURSOR=YES);31PROC程序的CLIENT参数（一）PROC编译参数，控制客PROC程序的CLIENT参数（二）数据库参数OPEN_CURSORS指保留在SERVER的sharedpool中的游标数（默认为50）PROC参数MAXOPENCURSORS指保留在CLIENT端的游标数（默认为10）32PROC程序的CLIENT参数（二）数据库参数OPEN_CU存储过程的SQL共享存储过程中以下类型的语句不需要绑定CREATEORREPLACEprocedureproc_testasn_idint:=0;BEGINFORiIN1..1000LOOPinsertintoTAB_TEST(ID)values(i);commit;ENDLOOP;END;33存储过程的SQL共享存储过程中以下类型的语句不需要绑定33w强制SQL共享的数据库参数的利弊ORACLE8i以后，可以使用数据库参数cursor_sharing＝FORCE或者SIMILAR（9i）使程序SQL在数据库中硬分析之前共享类似SQL分析的结果，只进行软分析，避免重编译，设置了该参数之后，程序将不需要使用绑定变量－－此参数会触发一些BUG，建议小范围使用或不使用34强制SQL共享的数据库参数的利弊ORACLE8i以后，可以使第六章：索引35第六章：索引35索引的类型B-tree索引位图索引（一般用于数据仓库中的静态数据）函数索引－－需要设置两个数据库参数QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTED反向索引（一般少用）36索引的类型B-tree索引36www.si-tech.co表和索引的分析execdbms_stats.GATHER_TABLE_STATS('ST',‘DCUSTMSG',ESTIMATE_PERCENT=>50);ANALYZEtableTABLE_NAMEESTIMATESTATISTICSSAMPLE50PERCENT;ORACLE9i建议使用dbms_stats.GATHER_TABLE_STATS37表和索引的分析execdbms_stats.GATHER_分区表、索引的特点分区表应尽量建立分区索引分区表的主键索引若不包含分区字段，则只能建为全局索引分区表和索引便于管理，但对于底层IO均匀的存储，并不能提高查询性能分区表和索引需要定期分析，才能恰当的被执行计划使用38分区表、索引的特点分区表应尽量建立分区索引38www.si-第七章：杂项39第七章：杂项39IN和EXISTS...wherecolumnin(select*from...where...);...whereexists(select'X'from...where...);第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中40IN和EXISTS...wherecolumnin(ISNULL与ISNOTNULL不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的41ISNULL与ISNOTNULL不能用null作Orderby语句ORDERBY语句决定了Oracle如何将返回的查询结果排序任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度42Orderby语句ORDERBY语句决定了Oracle用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销43用Where子句替换HAVING子句避免使用HAVING子句带通配符（%）的like语句select*fromemployeewherelast_namelike'%cliton%';select*fromemployeewherelast_namelike'c%';第二句能够使用到字段‘last_name’的索引44带通配符（%）的like语句select*frome找使用CPU多的用户session’12’是指被这个SESSION使用的CPUselecta.sid,spid,status,substr(gram,1,40)prog,a.terminal,osuser,value/60/100valuefromv$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;45找使用CPU多的用户session’12’是指被这个SESS典型SQLselectid_no,cust_idfromdCustMsgDeadwherephone_nondsubstr(run_code,2,1)>='a'问题1：字符字段的‘phone_no’没有加引号！问题2：应该将该索引建成分区索引（表为分区表）问题3：组合索引中包含RUN_CODE毫无意义，应只含PHONE_NO字段46典型SQLselectid_no,cust_idfromORACLESQL优化经验交流

系统支撑部董建忠47ORACLESQL优化经验交流

第一章:概述数据库优化的几个环节什么样的SQL需要优化常见的问题发现问题的方法48第一章:概述数据库优化的几个环节2www.si-tech.c数据库优化的几个环节物理存储IO均衡数据库规划合理（参数、表空间分布）数据表和索引的设计SQL语句的优化49数据库优化的几个环节物理存储IO均衡3www.si-tech什么样的SQL需要优化引发严重的等待事件（IO、锁）消耗大量的系统资源（CPU/IO/MEM）运行时间超长（排序段、回滚段占用太大）不能满足压力测试指标50什么样的SQL需要优化引发严重的等待事件（IO、锁）4www常见的问题没有恰当的索引（全表扫描问题）索引不适当（索引低效、无用）重编译问题（程序开发问题）多表关联条件不当或关联太多分区表没有分析，未能使用索引（维护问题）死锁（应用逻辑问题）51常见的问题没有恰当的索引（全表扫描问题）5www.si-te发现问题的方法从v$session_wait查看等待事件SQLPLUS使用AUTOTRACE查看执行计划在TOAD中直接查看执行计划从STATSPACK查看资源(CPU、I/O)消耗状况生成SESSIONTRACE文件（一般为DBA使用）用命令tkprof对TRACE文件进行分析52发现问题的方法从v$session_wait查看等待事件6w第二章：从等待事件中发现问题查看SESSIONWAIT的语句典型事件：DbFileSequentialRead典型事件：DbFileSequentialRead典型事件：LatchFree（latch释放）53第二章：从等待事件中发现问题查看SESSIONWAIT的查看SESSIONWAIT的语句setpagesize2000setlinesize110coleventformata25colprogramformata20selecta.event,substr(gram,1,20)program,b.sid,a.p1,a.p2,a.p3fromgv$session_waita,v$sessionbwherea.sid=b.sidanda.eventnotlike'%SQL%'anda.eventnotlike'%message%'anda.eventnotlike'%time%'54查看SESSIONWAIT的语句setpagesize典型事件：DbFileScatteredRead

数据文件分散读取这种情况通常显示与全表扫描相关的等待。一般表明该表找不到索引，或者只能找到有限的索引。特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。建议将小而常用的表CACHE到内存中，以避免一次又一次地重复读取它们55典型事件：DbFileScatteredRead

数典型事件：DbFileSequentialRead

数据文件顺序读取这一事件通常显示单个块的读取(如索引读取)表示表的连接顺序不佳，或者使用了不恰当的索引检查每个扫描是否必要的，并检查多表连接的连接顺序一般会消耗大量PGA内存，从而在顺序读取时导致大量等待。56典型事件：DbFileSequentialRead

典型事件：LatchFree（latch释放）latch是一种低级排队机制，用于保护系统全局区域（SGA）中共享内存结构。latch就像是一种快速地被获取和释放的内存锁。latch用于防止共享内存结构被多个用户同时访问。如果latch不可用，就会记录latch释放失败。大多数latch问题都与以下操作相关：不能使用绑定变量（库缓存latch）、重复生成问题（重复分配latch）、缓冲存储器竞争问题（缓冲器存储LRU链），以及缓冲存储器中的“热”块（缓冲存储器链）。也有一些latch等待与bug（程序错误）有关当latch不命中率大于0.5%时，就应当研究这一问题57典型事件：LatchFree（latch释放）latc第三章：SQL语句的执行计划SQL语句的执行步骤ORACLE的优化器在SQLPLUS配置AUTOTRACE使用QUESTTOAD查看执行计划安装AUTOTRACE环境使用QUESTTOAD查看执行计划查看执行计划58第三章：SQL语句的执行计划SQL语句的执行步骤12www.SQL语句的执行步骤语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。表达式转换，将复杂的SQL表达式转换为较简单的等效连接表达式。选择优化器，不同的优化器一般产生不同的“执行计划”选择连接方式，ORACLE有三种连接方式，对多表连接ORACLE可选择适当的连接方式。选择连接顺序，对多表连接ORACLE选择哪一对表先连接，选择这两表中哪个表做为源数据表。选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。运行“执行计划”59SQL语句的执行步骤语法分析，分析语句的语法是否符合规范ORACLE的优化器ORACLE有两种优化器：基于规则的优化器（RBO，RuleBasedOptimizer），和基于代价的优化器（CBO，CostBasedOptimizer）ORACLEV7以来缺省的设置应是“choose”，即如果对已分析的表查询的话选择CBO，否则选择RBO。如果该参数设为“rule”，则不论表是否分析过，一概选用RBO，除非在语句中用hint强制各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，须要分析表和相关的索引，才能搜集到CBO所需的数据60ORACLE的优化器ORACLE有两种优化器：基于规则在SQLPLUS配置AUTOTRACEAUTOTRACE参数解释SETAUTOTRACEOFF不能获得AUTOTRACE报告.这是默认的.SETAUTOTRACEONEXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SETAUTOTRACEONSTATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SETAUTOTRACEON包括上面两项内容的AUTOTRACE报告SETAUTOTRACETRACEONLY与SET

AUTOTRACE

ON类似,所有的统计和数据都在，但不可以打印61在SQLPLUS配置AUTOTRACEAUTOTRACE安装AUTOTRACE环境1、首先创建PLUSTRACE角色并且赋给DBA：Sql>@$ORACLE_HOME/sqlplus/admin/plustrce.sqlSql>grantplustracetopublic2、赋权限给用户Sql>GRANTPLUSTRACETOUSER（预赋权的用户名）;3、以SYSTEM用户创建PLAN_TABLE表Sql>@$ORACLE_HOME/rdbms/admin/utlplan.sqlSql>createpublicsynonymplan_tableforplan_table;Sql>grantallonplan_tabletopublic;在每个用户下设置AUTOTRACE可显示其执行计划。62安装AUTOTRACE环境1、首先创建PLUSTRACE角使用QUESTTOAD查看执行计划安装QUESTTOAD软件建立数据库连接进入SQL语句执行窗口输入并选定SQL语句在’SQL-WINDOW’菜单中选‘EXPLAINPLANCURRENTSQL’，即可看到执行计划，并不真正执行语句，不需要等待结果63使用QUESTTOAD查看执行计划安装QUESTTOA查看执行计划ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'TEST'Statistics0recursivecalls0dbblockgets4consistentgets0physicalreads0redosize547bytessentviaSQL*Nettoclient655bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)4rowsprocessed64查看执行计划ExecutionPlan18www.si-t第四章：如何分析问题的原因查找原因的一般步骤65第四章：如何分析问题的原因查找原因的一般步骤19www.si查找原因的步骤（一）检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句看采用了哪种类型的连接方式。ORACLE的共有SortMergeJoin（归并SMJ）、HashJoin（散列HJ）和NestedLoopJoin（嵌套循环NL）。在两张表连接，且内表的目标列上建有索引时，只有NestedLoop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响66查找原因的步骤（一）检查被索引的列或组合索引的首列是否出现在查找原因的步骤（二）看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描67查找原因的步骤（二）看连接顺序是否允许使用相关索引。假设表e查找原因的步骤（三）是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降索引列是否函数的参数。如是，索引在查询时用不上是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致第六种现象的发生68查找原因的步骤（三）是否用到系统数据字典表或视图。由于系统数查找原因的步骤（四）需要定期分析表和索引如果数据经常有增、删、改的表，最好定期对表和索引进行分析，可用SQL语句“analyzetablexxxxcomputestatisticsforallindexes;”。ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择69查找原因的步骤（四）需要定期分析表和索引23www.si-t查找原因的步骤（五）索引列的选择性不高（字段值重复率高）假设有表emp，共有一百万行数据，但其中的emp.deptno列数据只有4种不同的值，如10、20、30、40。ORACLE缺省认定表中列的值是在所有数据行均匀分布的。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE因此对索引“视而不见”，认为该索引的选择性不高70查找原因的步骤（五）索引列的选择性不高（字段值重复率高）2查找原因的步骤（六）索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些需要返回NULL值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引中存储值不能为全空看是否有用到并行查询（PQO）。并行查询将不会用到索引看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值，在做非相等连接时，如“<”，“>”,“like”等。ORACLE将引用缺省值，在某些情况下会对执行计划造成影响71查找原因的步骤（六）索引列值是否可为空（NULL）。如果索引第五章：SQL重编译问题SQL共享原理SQL共享的三个条件PROC程序的SQL共享PROC程序中以下类型的语句不需进行变量绑定PROC程序的CLIENT参数存储过程的SQL共享SQL共享的数据库参数的利弊72第五章：SQL重编译问题SQL共享原理26www.si-teSQL共享原理ORACLE将执行过的SQL语句存放在内存的共享池(sharedbufferpool)中，可以被所有的数据库用户共享当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径.这个功能大大地提高了SQL的执行性能并节省了内存的使用73SQL共享原理ORACLE将执行过的SQL语句存放在内存的SQL共享的三个条件当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）两个语句所指的对象必须完全相同（同义词与表是不同的对象）两个SQL语句中必须使用相同的名字的绑定变量(bindvariables)74SQL共享的三个条件当前被执行的语句和共享池中的语句必须完全PROC程序的SQL共享未使用绑定变量的语句sprintf(sqlstr,"insertintoscott.test1(num1,num2)values(%d,%d)",n_var1,n_var2);EXECSQLEXECUTEIMMEDIATE:sqlstr;EXECSQLCOMMIT;使用绑定变量的语句 strcpy(sqlstr,"insertintotest(num1,num2)values(:v1,:v2)"); EXECSQLPREPAREsql_stmtFROM:sqlstr; EXECSQLEXECUTEsql_stmtUSING:n_var1,:n_var2; EXECSQLCOMMIT;动态表也可以使用以上方式避免重编译75PROC程序的SQL共享未使用绑定变量的语句29www.sPROC程序中以下类型的语句不需进行变量绑定for(i=0;i<10000;i++){EXECSQLinsertintotab_test1(id)selectidfromtab_testwhereid=:i;EXECSQLCOMMIT;}本语句在数据库解析后的结果insertintotab_test1(id)selectidfromtab_testwhereid=:b076PROC程序中以下类型的语句不需进行变量绑定for(iPROC程序的CLIENT参数（一）PROC编译参数，控制客户端游标缓存，使用的是本地的内存，与服务器无关HOLD_CURSOR=yes－－保留游标在缓存中，默认为noRELEASE_CURSOR=no－－释放缓存,默认为no这两个参数也可以在程序中设置EXECORACLEOPTION(HOLD_CURSOR=NO);EXECORACLEOPTION(RELEASE_CURSOR=YES);77PROC程序的CLIENT参数（一）PROC编译参数，控制客PROC程序的CLIENT参数（二）数据库参数OPEN_CURSORS指保留在SERVER的sharedpool中的游标数（默认为50）PROC参数MAXOPENCURSORS指保留在CLIENT端的游标数（默认为10）78PROC程序的CLIENT参数（二）数据库参数OPEN_CU存储过程的SQL共享存储过程中以下类型的语句不需要绑定CREATEORREPLACEprocedureproc_testasn_idint:=0;BEGINFORiIN1..1000LOOPinsertintoTAB_TEST(ID)values(i);commit;ENDLOOP;END;79存储过程的SQL共享存储过程中以下类型的语句不需要绑定33w强制SQL共享的数据库参数的利弊ORACLE8i以后，可以使用数据库参数cursor_sharing＝FORCE或者SIMILAR（9i）使程序SQL在数据库中硬分析之前共享类似SQL分析的结果，只进行软分析，避免重编译，设置了该参数之后，程序将不需要使用绑定变量－－此参数会触发一些BUG，建议小范围使用或不使用80强制SQL共享的数据库参数的利弊ORACLE8i以后，可以使第六章：索引81第六章：索引35索引的类型B-tree索引位图索引（一般用于数据仓库中的静态数据）函数索引－－需要设置两个数据库参数QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEG