Oracle 19c OCM课程：应用SQL执行计划基线的案例

数据运维 2023-10-23 大猫手机阅读

修改配置，采集SQL执行计划基线

Oracle 19c与SQL执行计划基线相关的默认参数值如下：

`SQL> show parameter baseline`

`NAME TYPE VALUE`
`------------------------------------ ----------- ------------------------------`
`optimizer_capture_sql_plan_baselines boolean FALSE`
`optimizer_use_sql_plan_baselines boolean TRUE`

使用存储过程DBMS_SPM.CONFIGURE修改配置，自动捕捉TPCC用户执行的SQL，并创建基线：

`EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','TPCC',true);`
`alter system set optimizer_capture_sql_plan_baselines=true;`

修改后的参数存放在视图DBA_SQL_MANAGEMENT_CONFIG中，检查一下：

`COL PARAMETER_NAME FORMAT a32`
`COL PARAMETER_VALUE FORMAT a32`

`SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG ;`

`PARAMETER_NAME PARAMETER_VALUE`
`-------------------------------- --------------------------------`
`AUTO_CAPTURE_ACTION`
`AUTO_CAPTURE_MODULE`
`AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (TPCC)`
`AUTO_CAPTURE_SQL_TEXT`
`AUTO_SPM_EVOLVE_TASK OFF`
`AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 --`
`AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800`
`PLAN_RETENTION_WEEKS 53 -- 53不用的计划会被清理`
`SPACE_BUDGET_PERCENT 10 -- 占用SYSAUX的空间不超过10%，超过在alert中报警`

`9 rows selected.`

02

—

查看SQL执行计划基线的应用

先将一个索引改成不可见：

`SQL> alter index tpcc.CUSTOMER_I1 invisible;`

`Index altered.`
应用运行一段时间后，检查已经创建的SQL基线：

`SQL> select count(distinct sql_handle),count(distinct plan_name),count(distinct SIGNATURE) from DBA_SQL_PLAN_BASELINES;`

`COUNT(DISTINCTSQL_HANDLE) COUNT(DISTINCTPLAN_NAME) COUNT(DISTINCTSIGNATURE)`
`------------------------- ------------------------ ------------------------`
`30 30 30`

可以看到为30个SQL建立了基线，都是ACCEPTED，因为每个SQL只有一个执行计划。

检查与这个索引相关的SQL的执行情况：

`SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';`

`SQL_ID EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE`
`------------- ---------- -------------- ------------------------------`
`arykx3hpq9xsa 5102 1945 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea`

可以看到它用到了一个SQL基线的执行计划，成本是1945，查看这个SQL基线的执行计划：

`SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_2d858eea22dc0f79','SQL_PLAN_2v1cfx8jds3vt3a6ea7ea','basic') );`

`PLAN_TABLE_OUTPUT`
`--------------------------------------------------------------------------------`
`SQL handle: SQL_2d858eea22dc0f79`
`SQL text: UPDATE CUSTOMER SET C_BALANCE = C_BALANCE + :B1 WHERE C_W_ID = :B4`
`AND C_D_ID = :B3 AND C_ID = :B2`
`--------------------------------------------------------------------------------`

`--------------------------------------------------------------------------------`
`Plan name: SQL_PLAN_2v1cfx8jds3vt3a6ea7ea Plan id: 980330474`
`Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE`
`Plan rows: From dictionary`
`--------------------------------------------------------------------------------`

`Plan hash value: 3529770744`

`----------------------------------------`
`| Id | Operation | Name |`
`----------------------------------------`
`| 0 | UPDATE STATEMENT | |`
`| 1 | UPDATE | CUSTOMER |`
`| 2 | INDEX SKIP SCAN| CUSTOMER_I2 |`
`----------------------------------------`

`22 rows selected.`

可以看到这个SQL执行中使用了CUSTOMER_I2 索引，没有使用CUSTOMER_I1索引，因为CUSTOMER_I1这个索引被修改成了不可见。这种检查SQL执行计划的方法和在游标中查询SQL执行计划的方法得到同样的结果：

`set pagesize 200`
`SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('arykx3hpq9xsa'));`
将这个索引改成可见：

`alter index tpcc.CUSTOMER_I1 visible;`
第二次执行应用程序，然后再检查这个SQL的执行情况：

`SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';`

`SQL_ID EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE`
`------------- ---------- -------------- ------------------------------`
`arykx3hpq9xsa 2376 1945 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea`

发现这个SQL的执行成本和使用执行计划基线仍然没有发生变化，检查这个SQL对应的执行计划基线：

`col plan_name form a30`
`col signature forma 99999999999999999999999`
`select sql_handle,plan_name,signature,accepted,optimizer_cost from DBA_SQL_PLAN_BASELINES`
`where SIGNATURE=(select EXACT_MATCHING_SIGNATURE from v$sql where sql_id='arykx3hpq9xsa');`

`SQL_HANDLE PLAN_NAME SIGNATURE ACC OPTIMIZER_COST`
`------------------------------ ------------------------------ ------------------------ --- --------------`
`SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt341d91fc 3280185039867613049 NO 3`
`SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea 3280185039867613049 YES 1945`

发现这个SQL对应了两个基线，期中成本小到3的基线居然是没有被接受的！

03

—

手工进化基线

因为SQL基线的进化任务要到晚上维护窗口时才会执行，新的基线没有进化成可接受的，所以SQL执行时不会选择这个基线，我们可以手工对这个SQL基线进行进化：

`VARIABLE cnt NUMBER`
`VARIABLE tk_name VARCHAR2(50)`
`VARIABLE exe_name VARCHAR2(50)`
`VARIABLE evol_out CLOB`

`begin`
`:tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(`
`sql_handle => 'SQL_2d858eea22dc0f79',`
`plan_name => 'SQL_PLAN_2v1cfx8jds3vt341d91fc');`
`end;`
`/`

`SELECT :tk_name FROM DUAL;`
`SQL> SELECT :tk_name FROM DUAL;`

`:TK_NAME`
`-----------------------`
`TASK_1551`

`EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name);`
`SELECT :exe_name FROM DUAL;`
`:EXE_NAME`
`--------------------------------`
`EXEC_3452`

检查这个进化计划的执行报告：

`EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );`
`SELECT :evol_out FROM DUAL;`
`SQL> SELECT :evol_out FROM DUAL;`

`:EVOL_OUT`
`--------------------------------------------------------------------------------`
`GENERAL INFORMATION SECTION`
`----------------------------------------------------`

`Task Information:`
`---------------------------------------------`
`Task Name : TASK_1551`

`Task Owner : SYS`
`Execution Name : EXEC_3452`
`Execution Type : SPM EVOLVE`
`Scope : COMPREHENSIVE`

`Status : COMPLETED`
`Started : 09/08/2023 15:21:50`
`Finished : 09/08/2023 15:21:50`
`Last Updated : 09/08/2023 15:21:50`
`Global Time Limit : 2147483646`
`Per-Plan Time Limit : UNUSED`
`Number of Errors : 0`
`-----------------------------------------`
`SUMMARY SECTION`
`--------------------------------------------------`
`7 Number of plans processed : 1`
`Number of findings : 1`
`Number of recommendations : 1`
`Number of errors : 0`
`--------------------------------------------------------`
`-------------------------------------`

`DETAILS SECTION`
`---------------------------------------------`
`----------------------------------------`
`--------`
`Object ID : 2`

`Test Plan Name : SQL_PLAN_2v1cfx8jds3vt341d91fc`
`Base Plan Name : SQL_PLAN_2v1cfx8jds3vt3a6ea7ea`
`SQL Handle : SQL_2d858eea22dc0f79`

`Parsing Schema : TPCC`

`Test Plan Creator : SYS`

`SQL Text : UPDATE CUSTOMER SET C_BALANCE = C_BALANCE + :B1 WHERE`
`C_W_ID = :B4 AND C_D_ID= :B3 AND C_ID = :B2`

`Bind Variables:`
`-----------------------------`
`2 - (NUMBER): 3`

`3 - (NUMBER): 1`

`4 - (NUMBER): 2285`

`Execution Statistics:`
`-----------------------------`
`Base Plan`
`Test Plan`
`--------------------`
`-------- ----------------------------`
`Elapsed Time (s): .00064 .000002`
`CPU Time (s): .000638 .000002`
`Buffer Gets: 117 0`
`Optimizer Cost: 1945 3`
`Disk Reads: 0 0`
`Direct Writes: 0 0`
`Rows Processed: 0 0`
`Executions: 10 10`

`FINDINGS SECTION`
`-----------------------------------------`
`Findings (1):`
`-----------------------------`
`1. The plan was verified in 0.12000 seconds. It passed the benefit criterion`
`because its verified performance was 390.90001 times better than that of`
`the baseline plan.`

`Recommendation:`
`-----------------------------`
`Consider accepting the plan. Execute`

`dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1551', object_id => 2,`
`task_owner => 'SYS');`

`EXPLAIN PLANS SECTION`
`---------------------------------------------`

`Baseline Plan`
`-----------------------------`
`Plan Id : 9217`
`Plan Hash Value : 980330474`

`-------------------------------------------`
`| Id | Operation | Name | Rows | Bytes | Cost | Time |`
`---------------------------------------------------`
`------------------------`
`| 0 | UPDATE STATEMENT | | 1 | 15 | 1945 | 00:00:01 |`
`| 1 | UPDATE | CUSTOMER | | | | |`
`| * 2 | INDEX SKIP SCAN | CUSTOMER_I2 | 1 | 15 | 1944 | 00:00:01 |`
`-----------------------------------------------`

`Predicate Information (identified by operation id)`
`:`
`------------------------------------------`
`* 2 - access("C_W_ID"=:B4 AND "C_D_ID"=:B3 AND "C_ID"=:B2)`
`* 2 - filter("C_ID"=:B2 AND "C_W_ID"=:B4 AND "C_D_ID"=:B3)`

`Test Plan`
`-----------------------------`
`Plan Id : 9218`
`Plan Hash Value : 874353148`

`----------------------------------------------------`
`| Id | Operation | Name | Rows | Bytes | Cost | Time |`
`--------------------------------------------------------`
`| 0 | UPDATE STATEMENT | | 1 | 15| 3 | 00:00:01 |`
`| 1 | UPDATE | CUSTOMER | | | | |`
`| * 2 | INDEX UNIQUE SCAN | CUSTOMER_I1 | 1 | 15 | 2 | 00:00:01 |`
`--------------------------------------------`

`Predicate Information (identified by operation id):`
`------------------------------------------`
`* 2 - access("C_W_ID"=:B4 AND "C_D_ID"=:B3 AND "C_ID"=:B2)`

`--------------------------------------------------`

根据报告中的建议，执行下面的SQL接受第二个基线：

`SQL> exec dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1551', object_id => 2, task_owner => 'SYS');`

`PL/SQL procedure successfully completed.`

再次检查这个SQL对应的执行计划基线：

`col plan_name form a30`
`col signature forma 99999999999999999999999`
`select sql_handle,plan_name,signature,accepted,optimizer_cost from DBA_SQL_PLAN_BASELINES`
`2 where SIGNATURE=(select EXACT_MATCHING_SIGNATURE from v$sql where sql_id='arykx3hpq9xsa');`

`SQL_HANDLE PLAN_NAME SIGNATURE ACC OPTIMIZER_COST`
`------------------------------ ------------------------------ ------------------------ --- --------------`
`SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt341d91fc 3280185039867613049 YES 3`
`SQL_2d858eea22dc0f79 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea 3280185039867613049 YES 1945`

发现两个基线都是接受的。

第三次执行应用后，然后检查SQL的执行情况：

`SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';`

`SQL_ID EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE`
`------------- ---------- -------------- ------------------------------`
`arykx3hpq9xsa 6849 3 SQL_PLAN_2v1cfx8jds3vt341d91fc`

发现应用的SQL采用了第二个基线，执行成本从1945降低到3。

04

—

删除SQL基线

使用dbms_spm.drop_sql_plan_baseline可以删除SQL基线，但不能一次删除所有的SQL基线，如果要删除所有的SQL基线，需要用游标进行循环删除，相关程序如下（参见Doc ID 790039.1）

`declare`
`pgn number;`
`sqlhdl varchar2(30);`
`cursor hdl_cur is`
`select distinct sql_handle from dba_sql_plan_baselines;`
`begin`
`open hdl_cur;`
`loop`
`fetch hdl_cur into sqlhdl;`
`exit when hdl_cur%NOTFOUND;`
`pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);`
`end loop;`
`close hdl_cur;`
`commit;`
`end;`
`/`

`PL/SQL procedure successfully completed.`

`SQL> select count() from dba_sql_plan_baselines;`

`COUNT()`
`----------`
`0`

相关推荐

数据运维 2023-12-15 大猫

数据运维 2023-04-27 大猫

数据运维 2023-10-31 大猫

数据运维 2023-04-20 大猫

数据运维 2023-10-24 大猫

回到顶部