如何使用命令行来evolve sql plan baselines

2025-03-12 技术教程

这篇文章主要介绍“如何使用命令行来evolve sql plan baselines”，在日常操作中，相信很多人在如何使用命令行来evolve sql plan baselines问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”如何使用命令行来evolve sql plan baselines”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

为了evolve一个特定的sql执行计划执行以下操作:
1.创建一个evolve任务
2.设置evolve任务参数
3.执行evolve任务
4.实现任务中给出的建议
5.显示任务执行的结果

下面将举例来说明，假设满足以下条件
.数据库没有启用自动evolve任务
.对下面的查询创建一个SQL Plan Baseline

SELECT/*q2_group_by*/prod_name,sum(quantity_sold)FROMproductsp,salessWHEREp.prod_id=s.prod_idANDp.prod_category_id=204GROUPBYprod_name;

.想要创建两个索引来提高查询语句的性能，如果使用索引的性能比SQL Plan Baseline中的当前执行计划的性能好那么就evolve该执行计划

为了evolve一个特定的执行计划需要执行以下操作
1.执行初始化设置操作
清空共享池与缓冲区缓存

SQL>ALTERSYSTEMFLUSHSHARED_POOL;Systemaltered.SQL>ALTERSYSTEMFLUSHBUFFER_CACHE;Systemaltered.

启用自动捕获SQL Plan Baselines

SQL>ALTERSYSTEMSETOPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;Systemaltered.SQL>showparametersql_planNAMETYPEVALUE-----------------------------------------------------------------------------optimizer_capture_sql_plan_baselinesbooleanTRUEoptimizer_use_sql_plan_baselinesbooleanTRUE

以sh用户登录到数据库，然后设置SQLPLUS的显示参数

[oracle@jytest1~]$sqlplussh/sh@jypdbSQL*Plus:Release12.2.0.1.0ProductiononThuFeb1423:30:092019Copyright(c)1982,2016,Oracle.Allrightsreserved.LastSuccessfullogintime:ThuFeb14201923:01:23+08:00Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>SETPAGES10000LINES140SQL>SETSERVEROUTPUTONSQL>COLSQL_TEXTFORMATA20SQL>COLSQL_HANDLEFORMATA20SQL>COLPLAN_NAMEFORMATA30SQL>COLORIGINFORMATA12SQL>SETLONGC60535SQL>SETLONG60535SQL>SETECHOON

2.执行SQL语句，因此可以自动捕获它
执行下面的SQL语句

SQL>SELECT/*q1_group_by*/prod_name,sum(quantity_sold)2FROMproductsp,saless3WHEREp.prod_id=s.prod_id4ANDp.prod_category_id=2035GROUPBYprod_name;PROD_NAMESUM(QUANTITY_SOLD)--------------------------------------------------------------------EnvoyExternal6XCD-ROM11526ModelSM26273BlackInkCartridge15910PCMCIAmodem/fax28800baud19278Multimediaspeakers-3"cones10969Internal8XCD-ROM11197DeluxeMouse11609ModelCD13272TricolorInkCartridge12321ModelNM500XHighYieldTonerCartridge646618"FlatPanelGraphicsMonitor4415External8XCD-ROM13886SIMM-8MBPCMCIAIIcard17544PCMCIAmodem/fax19200baud20467EnvoyExternal8XCD-ROM14850EnvoyExternalKeyboard2857External6XCD-ROM11732ModelA3827HBlackImageCartridge17314Internal6XCD-ROM853317"LCDw/built-inHDTVTuner4874SIMM-16MBPCMCIAIIcard14191Multimediaspeakers-5"cones10419StandardMouse871421rowsselected.

查询数据字典确认在SQL Plan Baseline中不存在执行计划，因为只有重复执行的SQL语句才会被捕获

SQL>SELECTSQL_HANDLE,SQL_TEXT,PLAN_NAME,ORIGIN,ENABLED,2ACCEPTED,FIXED,AUTOPURGE3FROMDBA_SQL_PLAN_BASELINES4WHERESQL_TEXTLIKE'%q1_group%';norowsselected

再次执行SQL语句

3.查询数据字典来确保执行计划已经被加载到SQL Plan Baseline中了，下面的查询显示执行计划已经被接受，这意味着执行计划已经存储在SQL Plan Baselines中了。origin列显示为AUTO-CAPTURE，这意味着执行计划是被自动捕获的

SQL>SELECTSQL_HANDLE,SQL_TEXT,PLAN_NAME,2ORIGIN,ENABLED,ACCEPTED,FIXED3FROMDBA_SQL_PLAN_BASELINES4WHERESQL_TEXTLIKE'%q1_group%';SQL_HANDLESQL_TEXTPLAN_NAMEORIGINENAACCFIX-------------------------------------------------------------------------------------------SQL_07f16c76ff893342SELECT/*q1_group_bSQL_PLAN_0gwbcfvzskcu242949306AUTO-CAPTUREYESYESNOy*/prod_name,sum(quantity_sold)FROMproductsp,salessWHEREp.prod_id=s.prod_idANDp.prod_category_id=203GROUPBYprod_name

4.下面对SQL语句进行解析并验证优化器是否会使用SQL Plan Baseline中的执行计划

SQL>EXPLAINPLANFOR2SELECT/*q1_group_by*/prod_name,sum(quantity_sold)3FROMproductsp,saless4WHEREp.prod_id=s.prod_id5ANDp.prod_category_id=2036GROUPBYprod_name;Explained.SQL>SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY(null,null,'basic+note'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------Planhashvalue:3535171836------------------------------------------|Id|Operation|Name|------------------------------------------|0|SELECTSTATEMENT|||1|HASHGROUPBY|||2|HASHJOIN|||3|TABLEACCESSFULL|PRODUCTS||4|PARTITIONRANGEALL|||5|TABLEACCESSFULL|SALES|------------------------------------------Note------SQLplanbaseline"SQL_PLAN_0gwbcfvzskcu242949306"usedforthisstatement16rowsselected.

从执行计划的Note部分可以看到SQL Plan Baseline已经应用到这个SQL语句了

5.创建两个索引用来提高上面SQL语句的性能

SQL>CREATEINDEXind_prod_cat_nameONproducts(prod_category_id,prod_name,prod_id);Indexcreated.SQL>CREATEINDEXind_sales_prod_qty_soldONsales(prod_id,quantity_sold);Indexcreated.

6.再次执行SQL语句，因为启用了自动捕获功能，所以新的执行计划会被加载到SQL Plan Baseline中

7.查询数据字典来确保新的执行计划被加载到SQL Plan Baseline中了

SQL>SELECTSQL_HANDLE,SQL_TEXT,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED2FROMDBA_SQL_PLAN_BASELINES3WHERESQL_HANDLEIN('SQL_07f16c76ff893342')4ORDERBYSQL_HANDLE,ACCEPTED;SQL_HANDLESQL_TEXTPLAN_NAMEORIGINENAACC----------------------------------------------------------------------------------------SQL_07f16c76ff893342SELECT/*q1_group_bSQL_PLAN_0gwbcfvzskcu20135fd6cAUTO-CAPTUREYESNOy*/prod_name,sum(quantity_sold)FROMproductsp,salessWHEREp.prod_id=s.prod_idANDp.prod_category_id=203GROUPBYprod_nameSQL_07f16c76ff893342SELECT/*q1_group_bSQL_PLAN_0gwbcfvzskcu242949306AUTO-CAPTUREYESYESy*/prod_name,sum(quantity_sold)FROMproductsp,salessWHEREp.prod_id=s.prod_idANDp.prod_category_id=203GROUPBYprod_name

上面的查询结果显示新的执行计划是为被接受的。

8.再次解析SQL语句并验证优化器是不是使用原始没有索引的执行计划

上面的Note部分指示优化器使用了原始的没有索引的执行计划

9.以管理员用户登录数据库，然后创建一个evolve任务它包含未被接受执行计划相关的所有SQL语句

[oracle@jytest1~]$sqlplussys/xxzx7817600@jypdbassysdbaSQL*Plus:Release12.2.0.1.0ProductiononThuFeb1423:48:512019Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>VARIABLEcntNUMBERSQL>VARIABLEtk_nameVARCHAR2(50)SQL>VARIABLEexe_nameVARCHAR2(50)SQL>VARIABLEevol_outCLOBSQL>EXECUTE:tk_name:=DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle=>'SQL_07f16c76ff893342',plan_name=>'SQL_PLAN_0gwbcfvzskcu20135fd6c');PL/SQLproceduresuccessfullycompleted.SQL>SELECT:tk_nameFROMDUAL;:TK_NAME--------------------------------------------------------------------------------------------------------------------------------TASK_1110.执行evolve任务SQL>EXECUTE:exe_name:=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name);PL/SQLproceduresuccessfullycompleted.SQL>SELECT:exe_nameFROMDUAL;:EXE_NAME---------------------------------------------------------------------------EXEC_1

11.查看报告

EXECUTE:evol_out:=DBMS_SPM.REPORT_EVOLVE_TASK(task_name=>:tk_name,execution_name=>:exe_name);SELECT:evol_outFROMDUAL;GENERALINFORMATIONSECTION--------------------------------------------------------------------------TaskInformation:---------------------------------------------TaskName:TASK_11TaskOwner:SYSExecutionName:EXEC_1ExecutionType:SPMEVOLVEScope:COMPREHENSIVEStatus:COMPLETEDStarted:02/15/201917:49:32Finished:02/15/201917:49:35LastUpdated:02/15/201917:49:35GlobalTimeLimit:2147483646Per-PlanTimeLimit:UNUSEDNumberofErrors:0---------------------------------------------------------------------------SUMMARYSECTION---------------------------------------------------------------------------Numberofplansprocessed:1Numberoffindings:1Numberofrecommendations:1Numberoferrors:0---------------------------------------------------------------------------DETAILSSECTION---------------------------------------------------------------------------ObjectID:2TestPlanName:SQL_PLAN_0gwbcfvzskcu20135fd6cBasePlanName:SQL_PLAN_0gwbcfvzskcu242949306SQLHandle:SQL_07f16c76ff893342ParsingSchema:SHTestPlanCreator:SHSQLText:SELECT/*q1_group_by*/prod_name,sum(quantity_sold)FROMproductsp,salessWHEREp.prod_id=s.prod_idANDp.prod_category_id=203GROUPBYprod_nameExecutionStatistics:-----------------------------BasePlanTestPlan----------------------------------------------------ElapsedTime(s):.044336.012649CPUTime(s):.044003.012445BufferGets:36099OptimizerCost:924891DiskReads:34182DirectWrites:00RowsProcessed:42Executions:59FINDINGSSECTION---------------------------------------------------------------------------Findings(1):-----------------------------1.Theplanwasverifiedin2.18seconds.Itpassedthebenefitcriterionbecauseitsverifiedperformancewas2.01timesbetterthanthatofthebaselineplan.Recommendation:-----------------------------Consideracceptingtheplan.Executedbms_spm.accept_sql_plan_baseline(task_name=>'TASK_11',object_id=>2,task_owner=>'SYS');EXPLAINPLANSSECTION---------------------------------------------------------------------------BaselinePlan-----------------------------PlanId:1PlanHashValue:1117033222---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||21|861|924|00:00:12||1|HASHGROUPBY||21|861|924|00:00:12||*2|HASHJOIN||267996|10987836|742|00:00:09||*3|TABLEACCESSFULL|PRODUCTS|21|714|2|00:00:01||4|PARTITIONRANGEALL||918843|6431901|662|00:00:08||5|TABLEACCESSFULL|SALES|918843|6431901|662|00:00:08|---------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):------------------------------------------*2-access("P"."PROD_ID"="S"."PROD_ID")*3-filter("P"."PROD_CATEGORY_ID"=203)TestPlan-----------------------------PlanId:2PlanHashValue:20315500---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||21|861|891|00:00:11||1|SORTGROUPBYNOSORT||21|861|891|00:00:11||2|NESTEDLOOPS||267996|10987836|891|00:00:11||*3|INDEXRANGESCAN|IND_PROD_CAT_NAME|21|714|1|00:00:01||*4|INDEXRANGESCAN|IND_SALES_PROD_QTY|12762|89334|42|00:00:01|---------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):------------------------------------------*3-access("P"."PROD_CATEGORY_ID"=203)*4-access("P"."PROD_ID"="S"."PROD_ID")

报告显示使用两个索引的执行计划比原始执行计划性能更好

12.实现evolve任务所给出的建议

SQL>EXECUTE:cnt:=DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name=>:tk_name,execution_name=>:exe_name);PL/SQLproceduresuccessfullycompleted.

13.查询数据字典来确保新的执行计划已经是接受状态

SQL>SELECTSQL_HANDLE,SQL_TEXT,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED2FROMDBA_SQL_PLAN_BASELINES3WHERESQL_HANDLEIN('SQL_07f16c76ff893342')4ORDERBYSQL_HANDLE,ACCEPTED;SQL_HANDLESQL_TEXTPLAN_NAMEORIGINENAACC------------------------------------------------------------------------------------------------SQL_07f16c76ff893342SELECT/*q1_group_bSQL_PLAN_0gwbcfvzskcu242949306AUTO-CAPTUREYESYESy*/prod_name,sum(quantity_sold)FROMproductsp,salessWHEREp.prod_id=s.prod_idANDp.prod_category_id=203GROUPBYprod_nameSQL_HANDLESQL_TEXTPLAN_NAMEORIGINENAACC------------------------------------------------------------------------------------------------SQL_07f16c76ff893342SELECT/*q1_group_bSQL_PLAN_0gwbcfvzskcu2ae9b4305AUTO-CAPTUREYESYESy*/prod_name,sum(quantity_sold)FROMproductsp,salessWHEREp.prod_id=s.prod_idANDp.prod_category_id=203GROUPBYprod_name

14.执行清除操作

SQL>VARIABLEcntNUMBERSQL>EXEC:cnt:=DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_07f16c76ff893342');PL/SQLproceduresuccessfullycompleted.SQL>DELETEFROMSQLLOG$;13rowsdeleted.SQL>commit;Commitcomplete.SQL>DROPINDEXIND_SALES_PROD_QTY_SOLD;Indexdropped.SQL>DROPINDEXIND_PROD_CAT_NAME;Indexdropped.

到此，关于“如何使用命令行来evolve sql plan baselines”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！