ORACLE DML执行计划频繁变更导致业务响应极慢问题的处理

2025-04-13 技术教程

最近，oracle rac维护中遇到一件很奇怪的事情，就是业务偶尔会出现极其缓慢的情况，查看服务器负载、数据库负载却很低，数据库和主机日志也没有明显报错，数据库内部也没有拥堵会话的情况。本文特此记录，以备后查！

背景：从12月份开始，应用开始反馈，没次做新品发布，下发单据就会特别慢，后来为了临时解决问题，将业务指向到单节点，发现业务下发速度恢复正常。后来，过了一周左右的时间，单据下发速度再次很慢。但是，查看服务器负载、数据库负载却很低，数据库和主机日志也没有明显报错，数据库内部也没有拥堵会话的情况。为了尽快恢复业务，重启了数据库服务，

业务暂时恢复正常。然而，三四天之后，也就是今天，又出现下发单据缓慢的情况。

问题分析：

1、基于以上背景，数据库和主机没有高负载，尤其是数据库内部没有会话拥堵的情况，我能想到的是SQL执行计划可能出现了变化。

2、查看业务承载节点，在执行的SQL语句

3、查看sql-5216pwt38ckhp的执行计划

--优秀的执行计划，一次单据下发3-8点能执行近30次

--变化后的低效执行计划，一次单据下发3-10点，执行只有1完多次

4、通过步骤3中变化前后的执行计划对比，我们发现

--变化前的执行计划走的主键唯一索引扫描，变化后的执行计划走的普通索引的范围扫描

--变化前的执行计划buffer get很低为11，且没有磁盘读，变化后的执行计划buffer很高为28万，出现了磁盘物理读

--变化前的执行计划唯一索引扫描后回表扫描一行数据，变化后的执行计划对索引全分区扫描后回表扫描一行数据

--变化前的执行计划单次单据下发3-8点能执行近30完成，变化后的执行计划单次单据下发3-10点才执行1万多次

问题处理：

基于以上问题分析，SQL-5216pwt38ckhp语句是update语句，且使用了绑定变量，因此，在不改变sql的情况下，我们

选择固定sql的执行计划。方法是使用脚本coe_load_sql_profile.sql进行执行计划固定，在执行计划固定的时候需要注意，

不能以sys登陆数据库执行固话执行计划，需要使用业务用户执行，固话过程中，需要输入两次sql_id，然后选择优异执行计划的plan hash value即可。

附上执行计划固定脚本coe_load_sql_profile.sql：

SPOcoe_load_sql_profile.log;SETDEFONTERMOFFECHOONFEEDOFFVEROFFHEAONLIN2000PAGES100LONG8000000LONGC800000TRIMSONTIOFFTIMIOFFSERVEROUTONSIZE1000000NUM20SQLPSQL>;SETSERVEROUTONSIZEUNL;REMREM$Header:215187.1coe_load_sql_profile.sql11.4.5.52013/03/01carlos.sierra$REMREMCopyright(c)2000-2013,OracleCorporation.Allrightsreserved.REMREMAUTHORREMcarlos.sierra@oracle.comREMREMSCRIPTREMcoe_load_sql_profile.sqlREMREMDESCRIPTIONREMThisscriptloadsaplanfromamodifiedSQLintoaCustomSQLREMProfilefortheoriginalSQL.REMIfagoodperformingplanonlyreproduceswithCBOHintsREMthenyoucanloadtheplanofthemodifiedversionoftheREMSQLintoaCustomSQLProfilefortheorignalSQL.REMInotherwords,theoriginalSQLcanusetheplanthatwasREMgeneratedoutoftheSQLwithhints.REMREMPRE-REQUISITESREM1.HaveincacheorAWRthetextfortheoriginalSQL.REM2.HaveincacheorAWRtheplanforthemodifiedSQLREM(usuallywithhints).REMREMPARAMETERSREM1.ORIGINAL_SQL_ID(required)REM2.MODIFIED_SQL_ID(required)REM3.PLAN_HASH_VALUE(required)REMREMEXECUTIONREM1.ConnectintoSQL*PlusasuserwithaccesstodatadictionaryREMandprivilegestocreateSQLProfiles.DonotuseSYS.REM2.Executescriptcoe_load_sql_profile.sqlpassingfirsttwoREMparametersinlineoruntilrequestedbyscript.REM3.ProvideplanhashvalueofthemodifiedSQLwhenasked.REM4.UseaDBAuserbutnotSYS.DonotconnectasSYSasthestagingREMtablecannotbecreatedinSYSschemaandyouwillreceiveanerror:REMORA-19381:cannotcreatestagingtableinSYSschemaREMREMEXAMPLEREM#sqlplussystemREMSQL>STARTcoe_load_sql_profile.sqlgnjy0mn4y9pbmb8f3mbkd8bkghREMSQL>STARTcoe_load_sql_profile.sql;REMREMNOTESREM1.Thisscriptworkson10gorhigher.REM2.Forasimilarscriptfor11gusecoe_load_sql_baseline.sql,REMwhichusesSQLPlanBaselinesinsteadofCustomSQLProfiles.REM3.Forpossibleerrorsseecoe_load_sql_profile.logREM4.Ifyouget"ORA-06532:Subscriptoutsideoflimit,ORA-06512:atline1"REMThenyoumayconsiderthischange(onlyinatestanddisposablesystem):REMcreateorreplaceTYPEsys.sqlprof_attrASVARRAY(5000)ofVARCHAR2(500);REMSETTERMONECHOOFF;PROPROParameter1:PROORIGINAL_SQL_ID(required)PRODEForiginal_sql_id='&1';PROPROParameter2:PROMODIFIED_SQL_ID(required)PRODEFmodified_sql_id='&2';PROWITHpAS(SELECTplan_hash_valueFROMgv$sql_planWHEREsql_id=TRIM('&&modified_sql_id.')ANDother_xmlISNOTNULLUNIONSELECTplan_hash_valueFROMdba_hist_sql_planWHEREsql_id=TRIM('&&modified_sql_id.')ANDother_xmlISNOTNULL),mAS(SELECTplan_hash_value,SUM(elapsed_time)/SUM(executions)avg_et_secsFROMgv$sqlWHEREsql_id=TRIM('&&modified_sql_id.')ANDexecutions>0GROUPBYplan_hash_value),aAS(SELECTplan_hash_value,SUM(elapsed_time_total)/SUM(executions_total)avg_et_secsFROMdba_hist_sqlstatWHEREsql_id=TRIM('&&modified_sql_id.')ANDexecutions_total>0GROUPBYplan_hash_value)SELECTp.plan_hash_value,ROUND(NVL(m.avg_et_secs,a.avg_et_secs)/1e6,3)avg_et_secsFROMp,m,aWHEREp.plan_hash_value=m.plan_hash_value(+)ANDp.plan_hash_value=a.plan_hash_value(+)ORDERBYavg_et_secsNULLSLAST;PROPROParameter3:PROPLAN_HASH_VALUE(required)PRODEFplan_hash_value='&3';PROPROValuespassedtocoe_load_sql_profile:PRO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PROORIGINAL_SQL_ID:"&&original_sql_id."PROMODIFIED_SQL_ID:"&&modified_sql_id."PROPLAN_HASH_VALUE:"&&plan_hash_value."PROWHENEVERSQLERROREXITSQL.SQLCODE;SETTERMOFFECHOON;--trimparametersCOLoriginal_sql_idNEW_Voriginal_sql_idFORA30;COLmodified_sql_idNEW_Vmodified_sql_idFORA30;COLplan_hash_valueNEW_Vplan_hash_valueFORA30;SELECTTRIM('&&original_sql_id.')original_sql_id,TRIM('&&modified_sql_id.')modified_sql_id,TRIM('&&plan_hash_value.')plan_hash_valueFROMDUAL;--openlogfileSPOcoe_load_sql_profile_&&original_sql_id..log;GETcoe_load_sql_profile.log;.--getuserCOLconnected_userNEW_Vconnected_userFORA30;SELECTUSERconnected_userFROMDUAL;VARsql_textCLOB;VARother_xmlCLOB;VARsignatureNUMBER;VARnameVARCHAR2(30);EXEC:sql_text:=NULL;EXEC:other_xml:=NULL;EXEC:signature:=NULL;EXEC:name:=NULL;--getsql_textfrommemoryDECLAREl_sql_textVARCHAR2(32767);BEGIN--10gseebug5017909FORiIN(SELECTDISTINCTpiece,sql_textFROMgv$sqltext_with_newlinesWHEREsql_id=TRIM('&&original_sql_id.')ORDERBY1,2)LOOPIF:sql_textISNULLTHENDBMS_LOB.CREATETEMPORARY(:sql_text,TRUE);DBMS_LOB.OPEN(:sql_text,DBMS_LOB.LOB_READWRITE);ENDIF;l_sql_text:=REPLACE(i.sql_text,CHR(00),'');DBMS_LOB.WRITEAPPEND(:sql_text,LENGTH(l_sql_text),l_sql_text);ENDLOOP;IF:sql_textISNOTNULLTHENDBMS_LOB.CLOSE(:sql_text);ENDIF;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('gettingoriginalsql_textfrommemory:'||SQLERRM);:sql_text:=NULL;END;/--getsql_textfromawrBEGINIF:sql_textISNULLORNVL(DBMS_LOB.GETLENGTH(:sql_text),0)=0THENSELECTREPLACE(sql_text,CHR(00),'')INTO:sql_textFROMdba_hist_sqltextWHEREsql_id=TRIM('&&original_sql_id.')ANDsql_textISNOTNULLANDROWNUM=1;ENDIF;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('gettingoriginalsql_textfromawr:'||SQLERRM);:sql_text:=NULL;END;/--sql_textasfoundSELECT:sql_textFROMDUAL;--checkissql_textfororiginalsqlisavailableSETTERMON;BEGINIF:sql_textISNULLTHENRAISE_APPLICATION_ERROR(-20100,'SQL_TEXTfororiginalSQL_ID&&original_sql_id.wasnotfoundinmemory(gv$sqltext_with_newlines)orAWR(dba_hist_sqltext).');ENDIF;END;/SETTERMOFF;--getother_xmlfrommemoryBEGINFORiIN(SELECTother_xmlFROMgv$sql_planWHEREsql_id=TRIM('&&modified_sql_id.')ANDplan_hash_value=TO_NUMBER(TRIM('&&plan_hash_value.'))ANDother_xmlISNOTNULLORDERBYchild_number,id)LOOP:other_xml:=i.other_xml;EXIT;--1stENDLOOP;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('gettingmodifiedother_xmlfrommemory:'||SQLERRM);:other_xml:=NULL;END;/--getother_xmlfromawrBEGINIF:other_xmlISNULLORNVL(DBMS_LOB.GETLENGTH(:other_xml),0)=0THENFORiIN(SELECTother_xmlFROMdba_hist_sql_planWHEREsql_id=TRIM('&&modified_sql_id.')ANDplan_hash_value=TO_NUMBER(TRIM('&&plan_hash_value.'))ANDother_xmlISNOTNULLORDERBYid)LOOP:other_xml:=i.other_xml;EXIT;--1stENDLOOP;ENDIF;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('gettingmodifiedother_xmlfromawr:'||SQLERRM);:other_xml:=NULL;END;/--other_xmlasfoundSELECT:other_xmlFROMDUAL;--validateother_xmlSETTERMON;BEGINIF:other_xmlISNULLTHENRAISE_APPLICATION_ERROR(-20101,'PLANformodifiedSQL_ID&&modified_sql_id.andPHV&&plan_hash_value.wasnotfoundinmemory(gv$sql_plan)orAWR(dba_hist_sql_plan).');ENDIF;END;/SETECHOOFF;DECLAREhSYS.SQLPROF_ATTR:=SYS.SQLPROF_ATTR();idxINTEGER:=0;l_posNUMBER;l_hintVARCHAR2(32767);descriptionVARCHAR2(500);PROCEDUREadd_hint(p_hintINVARCHAR2)ISBEGINidx:=idx+1;DBMS_OUTPUT.PUT_LINE(LPAD(idx,4,'0')||''||p_hint);h.EXTEND;h(idx):=p_hint;ENDadd_hint;BEGINadd_hint('BEGIN_OUTLINE_DATA');FORiIN(SELECT/*+opt_param('parallel_execution_enabled','false')*/SUBSTR(EXTRACTVALUE(VALUE(d),'/hint'),1,4000)hintFROMTABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml),'/*/outline_data/hint')))d)LOOPl_hint:=i.hint;WHILENVL(LENGTH(l_hint),0)>0LOOPIFLENGTH(l_hint)<=500THENadd_hint(l_hint);l_hint:=NULL;ELSEl_pos:=INSTR(SUBSTR(l_hint,1,500),'',-1);add_hint(SUBSTR(l_hint,1,l_pos));l_hint:=''||SUBSTR(l_hint,l_pos);ENDIF;ENDLOOP;ENDLOOP;add_hint('END_OUTLINE_DATA');:signature:=DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(:sql_text);:name:=UPPER(TRIM('&&original_sql_id.'))||'_'||TRIM('&&plan_hash_value.');description:=UPPER('original:'||TRIM('&&original_sql_id.')||'modified:'||TRIM('&&modified_sql_id.')||'phv:'||TRIM('&&plan_hash_value.')||'signature:'||:signature||'createdbycoe_load_sql_profile.sql');--createcustomsqlprofilefororiginalsqlusingplanfrommodifiedsqlDBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text=>:sql_text,--originalsqlprofile=>h,--planfrommodifiedsqlname=>:name,description=>description,category=>'DEFAULT',validate=>TRUE,replace=>TRUE,force_match=>FALSE/*TRUE:FORCE(matchevenwhendifferentliteralsinSQL).FALSE:EXACT(similartoCURSOR_SHARING)*/);--dropsqlprofilestagingtablefororiginalsql(ifoneexists)BEGINDBMS_OUTPUT.PUT_LINE('droppingstagingtable"STGTAB_SQLPROF_'||UPPER(TRIM('&&original_sql_id.'))||'"');EXECUTEIMMEDIATE'DROPTABLESTGTAB_SQLPROF_'||UPPER(TRIM('&&original_sql_id.'));EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('stagingtable"STGTAB_SQLPROF_'||UPPER(TRIM('&&original_sql_id.'))||'"didnotexist');END;--createsqlprofilestagingtablefororiginalsqlDBMS_OUTPUT.PUT_LINE('creatingstagingtable"STGTAB_SQLPROF_'||UPPER(TRIM('&&original_sql_id.'))||'"');DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STGTAB_SQLPROF_'||UPPER(TRIM('&&original_sql_id.')),schema_name=>'&&connected_user.');--packsnewsqlprofilefororiginalsqlDBMS_OUTPUT.PUT_LINE('packagingnewsqlprofileintostagingtable"STGTAB_SQLPROF_'||UPPER(TRIM('&&original_sql_id.'))||'"');DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(profile_name=>:name,staging_table_name=>'STGTAB_SQLPROF_'||UPPER(TRIM('&&original_sql_id.')),staging_schema_owner=>'&&connected_user.');END;/--profile_nameCOLprofile_nameNEW_Vprofile_nameFORA30;SELECT:nameprofile_nameFROMDUAL;--displaydetailsofnewsql_profileSETECHOON;REMREMSQLProfileREM~~~~~~~~~~~REMSELECTsignature,name,category,type,statusFROMdba_sql_profilesWHEREname=:name;SELECTdescriptionFROMdba_sql_profilesWHEREname=:name;SETECHOOFF;PROPRO****************************************************************************PRO*Enter&&connected_user.passwordtoexportstagingtableSTGTAB_SQLPROF_&&original_sql_id.PRO****************************************************************************HOSexp&&connected_user.tables=&&connected_user..STGTAB_SQLPROF_&&original_sql_id.file=STGTAB_SQLPROF_&&original_sql_id..dmpstatistics=NONEindexes=Nconstraints=Ngrants=Ntriggers=NPROPROIfyouneedtoimplementthisCustomSQLProfileonasimilarsystem,PROimportandunpackusingthesecommands:PROPROimp&&connected_user.file=STGTAB_SQLPROF_&&original_sql_id..dmptables=STGTAB_SQLPROF_&&original_sql_id.ignore=YPROPROBEGINPRODBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(PROprofile_name=>'&&profile_name.',PROreplace=>TRUE,PROstaging_table_name=>'STGTAB_SQLPROF_&&original_sql_id.',PROstaging_schema_owner=>'&&connected_user.');;PROEND;;PRO/PROSPOOFF;HOSzip-mcoe_load_sql_profile_&&original_sql_id.coe_load_sql_profile_&&original_sql_id..logSTGTAB_SQLPROF_&&original_sql_id..dmpcoe_load_sql_profile.logHOSzip-dcoe_load_sql_profile_&&original_sql_id.coe_load_sql_profile.logWHENEVERSQLERRORCONTINUE;SETDEFONTERMONECHOOFFFEED6VERONHEAONLIN80PAGES14LONG80LONGC80TRIMSOFFTIOFFTIMIOFFSERVEROUTOFFNUM10SQLPSQL>;SETSERVEROUTOFF;UNDEFINE123original_sql_idmodified_sql_idplan_hash_valueprofile_nameCLCOLPROPROcoe_load_sql_profilecompleted.

问题处理结果：