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

oracleace 2023-10-23

571

关于号主，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云最有价值专家
《MySQL 8.0运维与优化》的作者
拥有 Oracle 10g、12c和19c OCM等数十项数据库认证
曾任IBM公司数据库部门经理
20+年DBA经验，服务2万+客户
精通C和Java，发明两项计算机专利

使用SQL执行计划基线可以保证SQL的性能不下降，但实际生产中默认没有开启，这里是姚远老师在给OCM的学员授课中关于SQL执行计划基线的一个案例，大家可以借鉴一下。

—

修改配置，采集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.

—

查看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的基线居然是没有被接受的！

—

手工进化基线

因为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。

—

删除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