金蝶中国大企业技术支持部Oracle性能调优之SQL执行计划解读以及自动优化应用2021.04罗振其2①了解oracle成本计算掌握如何收集执行计划掌握查看执行计划熟悉SQL自动调优方法课程收益3①目录1了解oracle成本计算2收集执行计划3查看执行计划4SQL自动调优CONTENTS4①•PART1:了解oracle成本计算了解成本计算5①基数基数是指某个列可能拥有的不重复数值的个数或从数据表，结果集或者索引返回多少行数据createtableluo.objectsasselect*fromdba_objectswhererownum<=90000;selectcount(*)astot_number,count(distinctowner)asowner_distnum,count(distinctobject_id)asid_distnum,count(distinctobject_name)asname_distnumfromluo.objects;TOT_NUMBEROWNER_DISTNUMID_DISTNUMNAME_DISTNUM---------------------------------------------86814318681452381Owner列选择率=1÷31=0.032258065单列基数=总记录数*选择率=0.032258065x86814=2800.451616①成本计算全表扫描成本计算公式cost=(#SRDs*SREADTIM+#MRDs*MREADTIM+#CPUCycles/cpuspeed)/SREADTIM=#SRDs+--因为是表扫描，所以该值为0#MRDs*MREADTIM/SREADTIM+--IOcost#CPUCycles/(cpuspeed*SREADTIM)--CPUcost索引的成本分析cost=blevel+ceil(leaf_blocks*effectiveindexselectivity)+ceil(clustering_factor*effectivetableselectivity)effectivetableselectivity：当我们计算使用索引的成本时，有效表选择率应该仅仅是基于那些在接触到表之前就能在索引中进行评估的谓词。blevel：二元高度=索引高度-17①成本计算8①PART2:收集执行计划收集执行计划9①方法汇总OracleEM图形工具DBMS_XPLAN的display_cursor或display_awrAwrsqrpt报告SQLTRACE10046事件dbms_monitor.session_trace查V$SQL_PLANsqlplusautotraceExplainPlanFor方法其它工具或方法10①OracleEM图形工具（真实、简单）顶级活动>搜索SQL>点击计划>查看Order列缺点：需要配置EM11①DISPLAY_CURSOR方法（真实）dbms_xplan.display_cursor(sql_id,childnumber,format)childnumber如果置为NULL，则返回所有子游标的执行计划推荐格式'ADVANCEDALLSTATSLASTPEEKED_BINDS'IOSTATS显示该游标累计执行的IO统计信息(Buffers,Reads)MEMSTATS累计执行的PGA使用信息(Omem1MemUsed-Mem)ALLSTATSOSTATS+MEMSTATSAdvanced显示outline、QueryBlockName、ColumnProjection等信息Typical不打印PROJECTION,ALIASPEEKED_BINDS打印解析时使用的绑定变量LAST仅显示最后一次执行的统计信息1.altersessionsetSTATISTICS_LEVEL=ALL;--会话级别altersystemsetSTATISTICS_LEVEL=ALL;--系统级别2.相同会话执行业务查询语句，并获取sql_id3.select*fromtable(dbms_xplan.display_cursor('sql_id',null,'advancedallstatslastpeeked_binds'));建议步骤缺点：具有时效性，在v$sqlarea中还存在12①display_cursor输出例子更丰富的信息:Starts,A-Rows,A-time等，助力分析哪一步执行计划走错13①DISPLAY_AWR（真实）select*fromtable(dbms_xplan.display_awr('sql_id',format=>'all'));更全的信息：select*fromtable(dbms_xplan.display_awr('sql_id',null,null,'advancedallstatslastpeeked_binds'))优点：在快照期内可用缺点：不能显示谓词信息14①awrsqrpt（真实）sqlplus/assysdba@?/rdbms/admin/awrsqrpt.sql优点：可了解执行计划变化情况了解资源消耗情况15①SQLTRACE（真实）当前session：SQL>altersessionsettracefile_identifier='my20210426';SQL>altersessionsetsql_trace=true;SQL>执行sqlSQL>altersessionsetsql_trace=false;系统级别：SQL>altersystemsetsql_trace=true;SQL>altersystemsetsql_trace=false;16①10046事件（真实）当前session：SQL>altersessionsettracefile_identifier='my10046';SQL>altersessionsetevents'10046tracenamecontextforever,level12';SQL>执行sqlSQL>altersessionsetevents'10046tracenamecontextoff';系统级别：SQL>altersystemsetevents'10046tracenamecontextforever,level12';SQL>执行sqlSQL>altersystemsetevents'10046tracenamecontextoff';17①dbms_monitor.session_trace（真实）selectsid,serial#fromv$sessionwheresid=&Input_sessionID;--查会话号信息execdbms_monitor.session_trace_enable(session_id=>&Input_sessionID,serial_num=>&Input_serial,waits=>true,binds=>true);--开始跟踪execdbms_monitor.session_trace_disable(session_id=>&Input_sessionID,serial_num=>&Input_serial);--结束跟踪selectspidfromv$processwhereaddr=(selectPADDRfromv$sessionwheresid=会话号andSERIAL#=会话serial号);--文件名与进程号相关联18①session_trace跟踪会话例子19①解析trace例子语法：tkproftrace文件名结果文件名sort=(fchdsk,fchcpu,prscnt)sys=no20①查V$SQL_PLAN获得（真实执行计划）select'|Operation|ObjectName|Rows|Bytes|Cost|'as"ExplainPlaninlibrarycache:"fromdualunionallselectrpad('|'||substr(lpad('',1*(depth-1))||operation||decode(options,null,'',''||options),1,35),36,'')||'|'||rpad(decode(id,0,'----------------------------',substr(decode(substr(object_name,1,7),'SYS_LE_',null,object_name)||'',1,30)),31,'')||'|'||lpad(decode(cardinality,null,'',decode(sign(cardinality-1000),-1,cardinality||'',decode(sign(cardinality-1000000),-1,trunc(cardinality/1000)||'K',decode(sign(cardinality-1000000000),-1,trunc(cardinality/1000000)||'M',trunc(cardinality/1000000000)||'G')))),7,'')||'|'||lpad(decode(bytes,null,'',decode(sign(bytes-1024),-1,bytes||'',decode(sign(bytes-1048576),-1,trunc(bytes/1024)||'K',decode(sign(bytes-1073741824),-1,trunc(bytes/1048576)||'M',trunc(bytes/1073741824)||'G')))),6,'')||'|'||lpad(decode(cost,null,'',decode(sign(cost-10000000),-1,cost||'',decode(sign(cost-1000000000),-1,trunc(cost/1000000)||'M',TRUNC(COST/1000000000)||'G'))),8,'')||'|'AS"Explainplan"FROMV$SQL_PLANWHERESQL_ID='sql_id'andPLAN_HASH_VALUE=哈希值andaddress='xxx'andCHILD_ADDRESS='xx'21①sqlplusautotrace（未必真实）setautotraceon开启--sql会真执行，且显示结果，setautotracetraceonly--sql会真执行，但不显示结果setautotraceexplain--sql不执行，仅解析Setautotraceoff关闭22①ExplainPlanFor（未必真实）1.不实际执行SQL语句2.速度快，可用于运行时间很长的SQL23①PART3:查看执行计划查看执行计划24①执行计划构成25①更改OutlineData改变执行计划26①执行计划步骤（行数及成本等）27①查看执行计划例子SQL>altersessionsetevents'10046tracenamecontextforever,level12';--打开事件，对当前会话进行跟踪SQL>select/*+luo6*/*fromluo.objectsO,luo.columnsCwhereO.object_name=c.table_nameandO.object_name='SEG$';--此处执行需要跟踪的SQLSQL>selectd.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc'trace_file_namefrom(selectp.spidfromv$mystatm,v$sessions,v$processpwherem.statistic#=1ands.sid=m.sidandp.addr=s.paddr)p,(selectt.instancefromv$threadt,v$parametervWHEREV.NAME='thread'AND(V.VALUE=0ORT.THREAD#=TO_NUMBER(V.VALUE)))I,(selectvaluefromv$parameterwherename='user_dump_dest')d;--查询跟踪文件名TRACE_FILE_NAME--------------------------------------------------------------------------------c:\app\diag\rdbms\luo\luo\trace\luo_ora_5068.trcSQL>altersessionsetevents'10046tracenamecontextoff';--关闭事件28①查看10046跟踪文件29①10046跟踪文件例子30①画执行树selectdistinctsql_id,operation,options,object_name,id,parent_id,position,depthfromv$SQL_PLANwheresql_id='dr277b6yv83uy'orderbyidasc;1.找出ID最小的节点，置于顶部2.查找父ID等于此值的节点3.按照Position从小到大按照从左到右的顺序，将节点置亍父节点之下4.对于新放置的这一层节点重复2-3的步骤，直到所有节点都被放置Depth步骤深度，如果depth是3则说明该步骤有3层父步骤在看执行计划的时候缩迚层数代表了Dept31①执行树顺序在树形图中，树中每个级别最左端的叶节点最先执行孩子执行了，父亲才执行32①执行计划顺序33①执行计划顺序练习34①执行计划顺序练习35①执行计划顺序练习36①优化建议在联机事务处理(OLTP)环境中优化SQL语句时，目标是将过滤性最强的表作为驱动表。这意味着，传递给下一步骤的行数较少。如果下一步骤执行联接，这意味着联接的行数较少。检查访问路径是否最佳。在检查优化程序执行计划时，请检查以下事项：在计划中，驱动表具有最强的过滤性。每个步骤的联接顺序都可保证返回给下一步的行数最少（即，联接顺序应使系统转到尚未使用的最强过滤器）。就返回的行数而言，相应的联接方法是适合的例如，返回的行很多时，使用索引的嵌套循环联接可能不是最佳方法。高效地使用视图。查看SELECT列表，确定访问的视图是否必需。是否存在预料之外的笛卡尔积（即使对于小表，也是如此）。高效地访问每个表：考虑SQL语句中的谓词和表的行数。查找可疑活动，例如对行数很多的表执行全表扫描（在WHERE子句中有谓词）。而对于小表，戒根据返回的行数利用更好的联接方法（例如hash_join）时，全表扫描也许更有效。如果这些条件中的任何一个都不是最佳的，请考虑调整SQL语句表上的索引。37①编码规范1.尽量简单、模块化；2.易读、易维护；3.充分利用索引，避免大表全表扫描；4.避免写过于复杂的SQL，不要用一条复杂的SQL就把功能做出来，能拆分成多个简单的，就要拆分成多个简单的SQL。5.避免复杂的多表关联，难以优化，随着数据量的增加性能的风险很大；6.避免使用硬编码，改用绑定变量，提高SQL工作效率；7.减少访问数据库的次数；8.合理使用临时表,子查询复杂的，一定要把子查询写成临时表。leftjoin后面跟复杂子查询，或连接复杂子查询后，又跟其它表连接，要把子查询写成临时表。9.在不影响业务的前提下减小事务的粒度；10.严禁视图中再引用其它视图，视图再引用视图，后期几乎无法维护，性能几乎没法保障38①•PART4:SQL自动调优SQL自动调优39①自动优化好处创建高效的索引的方法简单，粗暴，不需要专业的能力，也能达到非常专业的水平省时省力，提升生产力甄别出哪些SQL需要改写40①顶级SQL性能获取来源AWR报告execDBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(top_n_sql=>100);@?/rdbms/admin/awrrpt--收集方法v$sqlareadba_hist_sqlstat与dba_hist_sqltextOracleEMRPC41①v$sqlarea中的SQL的优化DECLAREmy_task_nameVARCHAR2(30);BEGIN--DBMS_SQLTUNE.drop_TUNING_TASK(task_name=>'eas_tuning_task1');my_task_name:=DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=>'89usvgkrj5kum',scope=>'COMPREHENSIVE',time_limit=>600,task_name=>'eas_tuning_task1',description=>'Tasktotuneaquery');DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'eas_tuning_task1');END;/SETLONGCHUNKSIZE1000SETLONG10000;selectdbms_sqltune.report_tuning_task('eas_tuning_task1')fromdual;--获取优化结果缺点：在v$sqlarea中能查到的SQL才可以用这种方法--替换成要优化的SQL_ID--时间单位为秒，根据实际情况更改42①AWR中单条SQL优化selectSNAP_ID,BEGIN_INTERVAL_TIME,FLUSH_ELAPSED,SNAP_LEVELfromdba_hist_snapshotorderbysnap_iddesc--取snap_id43①输出样例44①AWR快照期间内多条SQL优化45①输出样例46①Sqlprofilebeginsys.dbms_sqltune.accept_sql_profile(task_name=>'任务_8131',task_owner=>'EAS001',name=>'fixsqlplan',description=>'thiswilloverridebadsqlplan',category=>'default',replace=>true,force_match=>true);end;/--SIGNATURE相同的SQL都会生效47①Profile维护可以查询/删除或更改属性/删除profile:select*fromdba_sql_profiles;selectSQL_ID,SQL_PROFILE,SQL_PLAN_BASELINE,SQL_TEXT,SQL_FULLTEXTfromv$sqlareawhereSQL_ID='xxxxxxxxx';BEGINDBMS_SQLTUNE.DROP_SQL_PROFILE(name=>'my_sql_profile');END;/--删除sqlprofileBEGINDBMS_SQLTUNE.ALTER_SQL_PROFILE(name=>'my_sql_profile',attribute_name=>'status',value=>'disabled');END;/--更改属性为禁用48①SQLAccessAdvisor使用dbms_advisor.quick_tune来对单个SQL语句进行调整执行的分析层次比前面的方法却深入得多，生成的建议也更多。49①监控索引使用declareS_SQLVARCHAR2(500);beginFORLINE2IN(SELECTowner,INDEX_NAMEFROMDBA_INDEXESWHERETABLE_TYPE='TABLE'ANDTEMPORARY='N'andtable_ownerin('LUO')andtable_namenotlike'VT%'andINDEX_NAMEnotlike'PK%')LOOPS_SQL:='alterindex'||LINE2.OWNER||'.'||LINE2.INDEX_NAME||'monitoringusage';EXECUTEIMMEDIATES_SQL;DBMS_OUTPUT.PUT_LINE(S_SQL);ENDLOOP;end;/监控索引使用：selectA.TABLE_NAME,A.INDEX_NAME,COLUMN_NAME,COLUMN_POSITION"POSI",INDEX_TYPEfromuser_IND_COLUMNSA,user_indexesB,v$object_usageOwhereA.TABLE_NAME=B.TABLE_NAMEANDA.INDEX_NAME=B.INDEX_NAMEandA.TABLE_NAME=O.TABLE_NAMEANDA.INDEX_NAME=O.INDEX_NAMEandused='NO'orderbyTABLE_NAME,INDEX_NAME,COLUMN_POSITION;--导出没有使用到的索引列表select'dropindex'||index_name||';'fromv$object_usagewhereused='NO';--删除没有使用到的索引50①SQLT工具SQLTXPLAIN，也称为SQLT，是OracleServerTechnologiesCenterofExpertise提供的一款工具。SQLT主要方法通过输入的一个SQL语句，可输出一组诊断文件。这些文件通常用于诊断性能不佳或者产生错误结果的SQL语句。安装SQL>@D:\app\sqlt\install\sqcreate.sqlSQL>grantSQLT_USER_ROLEtoschema名;51①SQLT工具使用XTRACT方法:知道待分析SQL的SQL_ID或HASH_VALUEXECUTE方法:比XTRACT方法提供的信息更为详细（真实执行），要提供脚本文件XTRXEC方法:合并了XTRACT和XECUTE的功能，需提供sql_id/hash_value52①SQLT工具使用例：D:\>cdD:\app\sqlt\runD:\APP\sqlt\run>sqlplusluo/luoSQL>startsqltxtract.sql7smymt3m2skjsadding:sqlt_s16673_sqldx.zip(160bytessecurity)(stored0%)过程略#####TheSQLThascollectedinformationandplaceitinarepositoryinthedatabase,exporteditandzipit.Thecollectedinfocanbepurgedfromthedatabaseusingthefollowingfile:.gettingsqlt_s16673_purge.sqloutofsqltrepository.SQLTXTRACTcompleted.D:\APP\sqlt\run\sqlt_s16673_xtract_7smymt3m2skjs.zip--结果保存到该文件53①SQLT工具使用例：54①SQLHC工具startsqlhc\sqlhc.sqlTSQL_IDstartsqlhc\sqlhcxec.sqlTSQL脚本文件Thanksterimakasih感謝谢谢ありがとうขอบคุณ
