Oracle培训之sql优化

SQL优化1Oracle基础培训之第一章:概述•什么样的SQL需要优化•常见的问题•发现问题的方法2什么样的SQL需要优化•引发严重的等待事件•消耗大量的系统资源（CPU/IO/MEM）•运行时间超长•不能满足压力测试指标3常见的问题•没有恰当的索引（全表扫描）•没有使用到恰当的索引•重编译问题•多表关联条件不当或关联太多•死锁(update,delete,insert,merge)4发现问题的方法•从v$session_wait查看等待事件•SQLPLUS使用AUTOTRACE查看执行计划•在PL/SQLDeveloper中直接查看执行计划•从STATSPACK查看资源(CPU、I/O)消耗状况•生成SESSIONTRACE文件（一般为DBA使用）•用命令tkprof对TRACE文件进行分析5第二章：从等待事件中发现问题•查看SESSIONWAIT的语句•典型事件：DbFileSequentialRead•典型事件：DbFileSequentialRead•典型事件：LatchFree（latch释放）6查看SESSIONWAIT的语句setpagesize2000setlinesize110coleventformata25colprogramformata20selecta.event,substr(b.program,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%'7典型事件：DbFileScatteredRead数据文件分散读取•这种情况通常显示与全表扫描相关的等待。•一般表明该表找不到索引，或者只能找到有限的索引。•特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。•建议将小而常用的表CACHE到内存中，以避免一次又一次地重复读取它们8典型事件：DbFileSequentialRead数据文件顺序读取•这一事件通常显示单个块的读取(如索引读取)•表示表的连接顺序不佳，或者使用了不恰当的索引•检查每个扫描是否必要的，并检查多表连接的连接顺序•一般会消耗大量PGA内存，从而在顺序读取时导致大量等待。9典型事件：LatchFree（latch释放）•latch是一种低级排队机制，用于保护系统全局区域（SGA）中共享内存结构。latch就像是一种快速地被获取和释放的内存锁。latch用于防止共享内存结构被多个用户同时访问。如果latch不可用，就会记录latch释放失败。•大多数latch问题都与以下操作相关：不能使用绑定变量（库缓存latch）、重复生成问题（重复分配latch）、缓冲存储器竞争问题（缓冲器存储LRU链），以及缓冲存储器中的“热”块（缓冲存储器链）。也有一些latch等待与bug（程序错误）有关•当latch不命中率大于0.5%时，就应当研究这一问题10第三章：SQL语句的执行计划•SQL语句的执行步骤•ORACLE的优化器•在SQLPLUS配置AUTOTRACE•使用QUESTTOAD查看执行计划•安装AUTOTRACE环境•使用QUESTTOAD查看执行计划•查看执行计划11SQL语句的执行步骤•语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。•语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。•视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。•表达式转换，将复杂的SQL表达式转换为较简单的等效连接表达式。•选择优化器，不同的优化器一般产生不同的“执行计划”•选择连接方式，ORACLE有三种连接方式，对多表连接ORACLE可选择适当的连接方式。•选择连接顺序，对多表连接ORACLE选择哪一对表先连接，选择这两表中哪个表做为源数据表。•选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。•运行“执行计划”12ORACLE的优化器•ORACLE有两种优化器：基于规则的优化器（RBO，RuleBasedOptimizer），和基于代价的优化器（CBO，CostBasedOptimizer）•ORACLEV7以来缺省的设置应是“choose”，即如果对已分析的表查询的话选择CBO，否则选择RBO。如果该参数设为“rule”，则不论表是否分析过，一概选用RBO，除非在语句中用hint强制•各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，须要分析表和相关的索引，才能搜集到CBO所需的数据13在SQLPLUS配置AUTOTRACEAUTOTRACE参数解释SETAUTOTRACEOFF不能获得AUTOTRACE报告.这是默认的.SETAUTOTRACEONEXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SETAUTOTRACEONSTATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SETAUTOTRACEON包括上面两项内容的AUTOTRACE报告SETAUTOTRACETRACEONLY与SETAUTOTRACEON类似,所有的统计和数据都在，但不可以打印14安装AUTOTRACE环境•用户必须被赋予PLUSTRACE角色，而PLUSTRACE角色需要DBA来赋予。另外，该用户必须创建PLAN_TABLE表•1、首先创建PLUSTRACE角色并且赋给DBA：CONNECTsys/sys’spasswordASSYSDBA@$ORACLE_HOME/sqlplus/admin/plustrce.sql•2、赋权限给用户CONNECT/ASSYSDBAGRANTPLUSTRACETOUSER（预赋权的用户名）;•这样，就可以在该用户下设置AUTOTRACE报告的显示与否了。15使用QUESTTOAD查看执行计划•安装QUESTTOAD软件•建立数据库连接•进入SQL语句执行窗口•输入并选定SQL语句•在’SQL-WINDOW’菜单中选‘EXPLAINPLANCURRENTSQL’，即可看到执行计划，•并不真正执行语句，不需要等待结果16查看执行计划ExecutionPlan•----------------------------------------------------------•0SELECTSTATEMENTOptimizer=CHOOSE•10TABLEACCESS(FULL)OF'TEST'•Statistics•----------------------------------------------------------•0recursivecalls•0dbblockgets•4consistentgets•0physicalreads•0redosize•547bytessentviaSQL*Nettoclient•655bytesreceivedviaSQL*Netfromclient•2SQL*Netroundtripsto/fromclient•0sorts(memory)•0sorts(disk)•4rowsprocessed17第四章：如何分析问题的原因•查找原因的一般步骤18查找原因的步骤（一）•检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句•看采用了哪种类型的连接方式。ORACLE的共有SortMergeJoin（归并SMJ）、HashJoin（散列HJ）和NestedLoopJoin（嵌套循环NL）。在两张表连接，且内表的目标列上建有索引时，只有NestedLoop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响19查找原因的步骤（二）•看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描20查找原因的步骤（三）•是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降•索引列是否函数的参数。如是，索引在查询时用不上•是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致第六种现象的发生21查找原因的步骤（四）•是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句“analyzetablexxxxcomputestatisticsforallindexes;”。ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择•索引列的选择性不高（字段值重复率高）22查找原因的步骤（五）•索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些需要返回NULL值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引中存储值不能为全空•看是否有用到并行查询（PQO）。并行查询将不会用到索引•看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值，在做非相等连接时，如“”，“”,“like”等。ORACLE将引用缺省值，在某些情况下会对执行计划造成影响23第五章：SQL重编译问题•SQL共享原理•SQL共享的三个条件•PROC程序的SQL共享•PROC程序中以下类型的语句不需进行变量绑定•PROC程序的CLIENT参数•存储过程的SQL共享•SQL共享的数据库参数的利弊24SQL共享原理•ORACLE将执行过的SQL语句存放在内存的共享池(sharedbufferpool)中，可以被所有的数据库用户共享•当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径.这个功能大大地提高了SQL的执行性能并节省了内存的使用25SQL共享的三个条件•当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）•两个语句所指的对象必须完全相同（同义词与表是不同的对象）•两个SQL语句中必须使用相同的名字的绑定变量(bindvariables)26PROC程序的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;•动态表也可以使用以上方式避免重编译27PROC程序中以下类型的语句不需进行变量绑定•for(i=0;i10000;i++)•{•EXECSQLinsertintotab_test1(id)selectidfromtab_testwhereid=:i;•EXECSQLCOMMIT;•}•本语句在数据库解析后的结果•insertintotab_test1(id)selec