SQL语句常用的优化方法.ppt

背景：OLTP系统，ORACLE10G,作者:ZALBB,SQL语句常用的调优方法,1为什么要调优SQL?2哪些SQL需要调优?3如何获取需要调优的SQL?4如何手工调优SQL?5另外一些调优方法和工具。611G在执行计划上的一些改进。,目录,为什么要调优SQL？,通常来讲，要打造高效快捷的应用系统，需要从最初的业务需求入手，在分析、整理出闭环的业务操作流程后，按照范式的要求，尽量用简单的数据结构，来实现业务的运行和流转（可以考虑对基础数据作少量的数据冗余，以减少关联）；同时，根据业务的需求，兼考虑对历史业务数据的迁移，只保留最近一段时期内的数据，以便让系统轻装运行。但是，由于业务的复杂性，设计人员的知识、视野、前瞻性等的局限，在系统结构设计时，难以考虑周全；并且，由于开发人员的水平参差不齐，编写的代码也存在缺陷。经统计评估，排除系统结构设计不善导致的因素外，新的应用系统，有80%的效率问题，是因为低效的SQL导致，这就需要DBA找出这些低效的SQL，加以优化。,例子,哪些SQL需要优化？,运行时间较长的SQL。逻辑读较高的SQL。物理读较高的SQL。,从哪里获取需要调优的SQL？,*AWR(ASH,ADDM)，1ElapsedTime(含CPU较高者)2BufferGets3PhysicalReads,*EM，性能分析-SQLTuning,*当前库，根据V$SESSION.LAST_CALL_ET，找到运行时间最长的进程，获取SQL_ID，再找出SQL语句和执行计划。,AWR上要关注的SQL项,如何手工调优SQL?,A如何获取语句的执行计划？B如何解读执行计划中的执行顺序？CSQL语句的调优原则。D一些调优常识。E手工调优的粗略思路。F10046事件的使用方法。G两个案例。,如何获取语句的执行计划？,2根据SQL_ID查询，select*fromtable(dbms_xplan.display_cursor(还有：advanced,typical,serial,basic.v$session.sql_child_number=0,1,3从视图v$sql_plan中获取。,1直接解析SQL语句.ExplainplanforXXX；Select*fromtable(dbms_xplan.display);,如何解读执行计划中的执行顺序？,在获取SQL语句的执行计划后，这样解读执行顺序：*对同一凹层，先上后下执行，*对不同凹层，先里后外执行。,对于同一凹层，先上后下,对于不同凹层，先里后外。所以先NL,后hash。,真正的执行顺序,执行顺序:3,5,4,2,7,6,1,0,SQL语句的调优原则,在一个OLTP系统里，优化SQL语句的原则，就是尽量减少数据的读取。调优的目的，实际是设法让语句在执行过程中，尽可能地只读取必要的数据，不读或尽量少读不符合要求的数据。,SQL调优中的一些常识执行计划中涉及的一些概念,*不论SQL中读取多少个表，在执行过程中，每次都是两个表/结果集操作，得到新的结果后，再和下一个表/结果集操作，直到结束。在一个多表关联的执行计划中，必须包括这3要素：*表/对象/数据集的读取顺序（joinorder）。*数据的读取方法（accesspath）。*表/数据的关联方法（joinmethod）。这3个要素是判断执行计划优秀与否的关键。*可选择性(Selectivity)，=0and=,selectspidfromv$processwhereaddr=0000000376B84438;SPID-75482查看文件路径：SQLShowparameteruser_dump_destElapsed:00:00:00.013在操作系统下，调用Tkprof格式化裸文件：C:Oraclediagrdbmsncbincbitracetkprofncbi_ora_7548.trcncbi_ora_7548.log,1执行计划的详细过程.2每个步骤一致读的个数.3每个步骤的耗时，可根据耗时来判断步骤的优劣.,语句在执行过程中，各事件的耗时，,使用10046事件的前提条件TIMED_STATISTICS=TRUE,SESSION级可设置。MAX_DUMP_FILE_SIZE要有足够的空间，通常设置为Umlimited.10046事件为何有时没有执行计划？这是因为该语句在执行后，该语句的游标没有关闭，导致没写入执行计划，可以在执行完该语句后，执行一简单语句，如：select*fromdual;促使之前的游标结束，即可得到执行计划信息。,列表分区优化一例,SQLselectsid,serial#,seq#,erminal,machine,last_call_etcall_et,module,2(selectobject_namefromuser_objectswherea.row_wait_obj#=object_id)object_name,event,wait_class,3row_wait_file#r_w_f#,row_wait_block#r_w_b#,row_wait_row#r_w_r#,p1,p2,p3fromgv$sessiona4wherea.status=ACTIVEanda.usernameisnotnullandwait_class!=Idleorderbylast_call_et;SIDSERIAL#SEQ#TERMINALMACHINECALL_ETMODULEOBJECT_NAMEEVENT-154039155154unknowns551038JDBCThinClientIC_GENERAL_Bdbfilesequentialread21502338417019unknowns302395JDBCThinClientARAP_DJFBdbfilesequentialread23rowsselected.Last_call_et：当前状态的持续时间，若是某个查询，则意味着该查询已经执行的时长。上面SID=2150的进程中，当前语句已经运行了2395秒。,运行的语句,selectzb.vouchid,fb.fb_oid,xyb.fkxyb_oid,zb.ywbm,fb.hbbm,fb.deptid,fb.ywybm,fb.jobid,fb.szxmid,fb.cinventoryid,fb.ddh,fb.fph,zb.djdl,zb.djbh,fb.flbh,zb.djrq,zb.shrq,zb.effectdate,fb.ordercusmandoc,xyb.xydqr,fb.hsdj,zb.kmbm,fb.kmbm,xyb.ybye,xyb.fbye,xyb.bbye,ductline,zb.xslxbm,pk_salestru,(fb.jfybje+fb.dfybje),fb.ybye,zb.dwbm,fb.wldxfromarap_djfkxybxyb,arap_djfbfb,bd_cumandoc,arap_djzbzbwherexyb.fb_oid=fb.fb_oidandfb.ksbm_cl=bd_cumandoc.pk_cumandocandbd_cumandoc.pk_salestru=0001AA1000000001ELSEandfb.ksbm_clisnotnullandfb.vouchid=zb.vouchidand(zb.dwbm=1023andfb.dwbm=1023andfb.wldx=0andzb.sxbz=10andfb.verifyfinshed=Nand(fb.xgbh1)and(fb.pausetransactisnullorfb.pausetransact=N)andxyb.dr=0andfb.fx=1andzb.djdl=ysand(fb.ybye0)andfb.bzbm=00010000000000000001and(fb.bz_date=2009-07-01andzb.djrq=2009-07-01andfb.billdate0ORFB.YBYE=2009-07-01ANDFB.DWBM=1023ANDFB.VERIFYFINSHED=NANDFB.WLDX=0ANDFB.BILLDATE1AND(FB.PAUSETRANSACTISNULLORFB.PAUSETRANSACT=N)ANDFB.BZBM=00010000000000000001AND(FB.BZ_DATEISNULLORFB.BZ_DATE=2009-07-01ANDZB.DWBM=1023ANDZB.DJDL=ysANDZB.DJRQ=2009-07-01anddjrqcreateindexIDX_ARAP_DJZB_20090326_0703onARAP_DJZB_20090326(DJRQ,DJDL,LRR)tablespacennc_index03local;Indexcreated.Elapsed:00:01:34.69,创建列表分区表,改成分区表后语句的耗时，不到原来的1/330。,使用MV优化SQL,某个报表，过程语句有40+条，其中关键耗时的语句有8条，经调优，在系统空闲时间运行，大概耗时25M，但在系统运行时查询，耗时40-50M，无法满足需求，求改善。经了解，业务人员只要求查询昨天某分公司的数据，此条件一直不变。由于之前已经获知此报表涉及到的SQL，经思考，决定使用MV+OUTLINE来实现此需求。1对此8条耗时语句，修改其中的日期后，建成MV。2对此8条耗时语句，修改其中的日期，加入提示/*+rewrite*/，之后生成OUTLINE。3对此8条耗时语句，修改其中的日期，生成OUTLINE(此是生成的是正常查询语句的OUTLINE)。4对2，3步的OUTLINE作交换，目的是迫使正常查询时，使用步骤2的执行计划，此时该语句将取读取步骤1生成的MV里的数据。5修改参数query_rewrite_integrity=stale_tolerated，并建一SCHEDULERJOB，定时运行上述过程。经上述优化后，业务人员在查询报表时，CBO将直接读取事先生成的MV里的数据，在本地查询时，2分钟内出结果。语句的执行计划如下：,另外一些调优方法和工具,执行大纲的本意，就是对某些特定语句，使用指定/固定的执行计划。步骤如下：1先创建原始语句的OUTLINE。2创建特定语句的OUTLINE，此语句通常是加了HINT来达到使用指定执行计划。3交换步骤1，2的执行计划。4启用outline目录。,执行大纲的使用方法,SQLTXPLAN,SQLTXPLAN，ORACLE的内部工具，可用来协助诊断效率差的SQL，详细用法请上MOS查阅相关文档ID215187.1,DBMS_SQLTUNE,Dbms_sqltune是ORACLE在10G版本里推出的Sql调优工具。用户可以通过创建作业来把要调优的Sql语句放入Dbms_sqltune包，运行该作业，之后查询相关视图，获取该语句最佳的执行计划及建议。如下步骤：1创建优化任务。DBMS_SQLTUNE.CREATE_TUNING_TASK.2运行优化任务。3查询视图，获取任务的当前状态。selectstatusfromuser_advisor_takswheretask_name=;4查询语句的优化结果。selectdbms_sqltune.report_tuning_task()fromdual;此时可以看到最佳的执行计划及ORACLE的优化建议。5删除优化任务。execdbms_sqltune.drop_tuning_task();,DBMS_ADVISOR,10G版本新推出的顾问框架，支持各种性能调优需求，通过DBMS_ADVISOR包来支持各种需求，如，SQL调优，MV，索引建议等。,DBMS_PROFILE,DBMS_PROFILE包主要用来调优存储过程的执行过程，通过跟踪记录存储过程中每条SQL的执行时间，来确定哪些SQL语句最耗时，从而定位出需要调优的SQL语句。,11G在执行计划上的一些改进,自适应共享游标(ACS)，克服带绑定变量的执行计划存在的弊端。2在收集统计信息上的改进A设置收集统计信息时的选项，可对指定的表作单独设置。B对联合列收集统计信息。C对函数以及表达式收集统计信息。Spm，Sqlpalnmanagement，Sql计划管理，实际是10G中顾问框架（DBMS_ADVISOR）的升级版，通过这个特性，ORACLE自动判断某个SQL新的执行计划是否更加优秀（成本更低），只有新的执行计划比原来的更佳，才会被使用。,例子:联合列收集统计信息对执行计划的改善。,selectzb.vouchid,fb.fb_oid,xyb.fkxyb_oid,zb.ywbm,fb.hbbm,fb.deptid,fb.ywybm,fb.jobid,fb.szxmid,fb.cinventoryid,fb.ddh,fb.fph,zb.djdl,zb.djbh,fb.flbh,zb.djrq,zb.shrq,zb.effectdate,fb.ordercusmandoc,xyb.xydqr,fb.hsdj,zb.kmbm,fb.kmbm,xyb.ybye,xyb.fbye,xyb.bbye,ductline,zb.xslxbm,pk_salestru,(fb.jfybje+fb.dfybje),fb.ybye,zb.dwbm,fb.wldxfromarap_djfkxybxyb,arap_djfbfb,bd_cumandoc,arap_djzbz