使用sql profile 稳定执行计划的案例

1.缘起

接上次一次hard parse处理过程
自从为了解决hard parse的问题而设置了cursor_sharing=force后，又衍生了其他的问题，那就是执行计划的稳定性，如下记录发生的一起强制绑定变量后引起的执行计划绑定的问题

2.变慢的sql

用戶反映早上接数据变得很慢，使用如下sql检查该时段最频繁的sql

select SQL_ID,COUNT(1) from dba_hist_active_sess_history where to_char(sample_time,'YYYY-MM-DD HH24') LIKE '2023-09-07 07%'
GROUP BY SQL_ID order by count(1) desc
SQL_ID         COUNT(1)
---------      --------- 
b9vfj9nfx8h96	343null        190
0dkf5ub9b50qz	189
5axg9sxr2hqzv	82
b2fmuanq720cn	59
ffsffxdmg6987	48
8pmcn7r4jak0b	38
d1hzqrwah70hv	27
by3ykxt3p3qv6	23
34jhhpgabascc	17

在跟系统维运人员确认,肇事的sql就是：

SELECT * FROM DBA_HIST_SQLTEXT WHERE SQL_ID='b9vfj9nfx8h96'
SQL_ID            SQL_TEXT
-------------   ----------------------------------------------------------------------
b9vfj9nfx8h96	"INSERT INTO VIE_BU_IN_EM_CARD_RECORD (FACT_NO,  PNL_NO, CARD_DATE,CARD_TM, SEQ_NO, MAR_NO, ADD_NAME, ADD_DATE, UPD_NAME, UPD_DATE,  SOURCE_MK, FACT_NO1, PNL_NO1)
select FACT_NO,  PNL_NO, CARD_DATE,CARD_TM, SEQ_NO, MAR_NO, ADD_NAME, TO_CHAR(SYSDATE, :"SYS_B_0") ADD_DATE, UPD_NAME, UPD_DATE,  SOURCE_MK, FACT_NO1, PNL_NO1FROM TMP_VIE_BU_IN_EM_CARD_RECORD awhere not exists(select :"SYS_B_1" from VIE_BU_IN_EM_CARD_RECORD b where a.fact_no1 = b.fact_no1 and a.pnl_no = b.pnl_no and a.card_date = b.card_date and a.card_tm = b.card_tm and b.card_date  BETWEEN TO_CHAR(SYSDATE -:"SYS_B_2", :"SYS_B_3") AND TO_CHAR(SYSDATE, :"SYS_B_4"))"

3.检查瓶颈

可以看没有"WAITING",全部"ON CPU"

select session_state,count(*) from dba_hist_active_sess_history where to_char(sample_time,'YYYY-MM-DD HH24') LIKE '2023-09-07 07%' AND SQL_ID='b9vfj9nfx8h96'
group by session_state
SESSION   COUNT(*)
------- ----------
ON CPU         343

确认运行时的执行计划
该时段仅3个session运行这条sql,并且使用相同的执行计划

select session_id,sql_id,sql_plan_hash_value,count(1)  from dba_hist_active_sess_history where to_char(sample_time,'YYYY-MM-DD HH24') LIKE '2023-09-07 07%' AND SQL_ID='b9vfj9nfx8h96'
group by session_id,sql_id,sql_plan_hash_value
SESSION_ID SQL_ID        SQL_PLAN_HASH_VALUE   COUNT(1)
---------- ------------- ------------------- ----------393 b9vfj9nfx8h96          3318088377         53460 b9vfj9nfx8h96          3318088377         97656 b9vfj9nfx8h96          3318088377        193

查看执行计划细节
可以看到这条sql有2个执行计划，sql却选择了比较高价的那个执行计划
究其原因，猜想应该是设定cursor_sharing=force之前，此sql执行一次分析一次，所以会比较精准的执行计划，而设定为force后，sql似乎与绑定了比较高价的执行计划

select sql_id,plan_hash_value,id,cost,operation,options,object#,object_owner,object_name,object_type 
from dba_hist_sql_plan where sql_id='b9vfj9nfx8h96' --and plan_hash_value=3318088377
order by sql_id,plan_hash_value,id
SQL_ID        PLAN_HASH_VALUE         ID       COST OPERATION                 OPTIONS                 OBJECT# OBJECT_OWNER         OBJECT_NAME                    OBJECT_TYPE
------------- --------------- ---------- ---------- ------------------------- -------------------- ---------- -------------------- ------------------------------ --------------------
b9vfj9nfx8h96      1009235934          0         34 INSERT STATEMENT
b9vfj9nfx8h96      1009235934          1         34 HASH JOIN                 ANTI
b9vfj9nfx8h96      1009235934          2          2 TABLE ACCESS              FULL                     242122 CARD2HRIS            TMP_VIE_BU_IN_EM_CARD_RECORD   TABLE (TEMP)
b9vfj9nfx8h96      1009235934          3         31 VIEW                                                                           VW_SQ_1                        VIEW
b9vfj9nfx8h96      1009235934          4         31 NESTED LOOPS
b9vfj9nfx8h96      1009235934          5         31 TABLE ACCESS              BY INDEX ROWID           189258 IDHRIS               EM_CARD_RECORD                 TABLE
b9vfj9nfx8h96      1009235934          6          5 INDEX                     RANGE SCAN               276465 IDHRIS               IDX_ADD_DATE                   INDEX
b9vfj9nfx8h96      1009235934          7          0 INDEX                     UNIQUE SCAN               61593 IDHRIS               PK_SYS_DATA_PERMISSION         INDEX (UNIQUE)
b9vfj9nfx8h96      3318088377          0        615 INSERT STATEMENT
b9vfj9nfx8h96      3318088377          1            FILTER
b9vfj9nfx8h96      3318088377          2          2 TABLE ACCESS              FULL                     242122 CARD2HRIS            TMP_VIE_BU_IN_EM_CARD_RECORD   TABLE (TEMP)SQL_ID        PLAN_HASH_VALUE         ID       COST OPERATION                 OPTIONS                 OBJECT# OBJECT_OWNER         OBJECT_NAME                    OBJECT_TYPE
------------- --------------- ---------- ---------- ------------------------- -------------------- ---------- -------------------- ------------------------------ --------------------
b9vfj9nfx8h96      3318088377          3        613 NESTED LOOPS
b9vfj9nfx8h96      3318088377          4          1 INDEX                     UNIQUE SCAN               61593 IDHRIS               PK_SYS_DATA_PERMISSION         INDEX (UNIQUE)
b9vfj9nfx8h96      3318088377          5        612 TABLE ACCESS              BY INDEX ROWID           189258 IDHRIS               EM_CARD_RECORD                 TABLE
b9vfj9nfx8h96      3318088377          6        601 INDEX                     RANGE SCAN               245456 IDHRIS               IDX_ADD_DATE                   INDEX

对比两个执行计划

select sq.SNAP_ID,sq.SQL_ID,sq.EXECUTIONS_DELTA,sq.PARSE_CALLS_DELTA,sq.PLAN_HASH_VALUE,sq.DISK_READS_DELTA,
sq.BUFFER_GETS_DELTA,sq.ROWS_PROCESSED_DELTA,sq.CPU_TIME_DELTA,sq.ELAPSED_TIME_DELTA,sq.IOWAIT_DELTA
from dba_hist_sqlstat sq,dba_hist_snapshot sn where sq.snap_id=sn.snap_id and sq.sql_id='b9vfj9nfx8h96' and sq.plan_hash_value in (3318088377,1009235934)
and sn.snap_id in (54635,54563)SNAP_ID SQL_ID        EXECUTIONS_DELTA PARSE_CALLS_DELTA PLAN_HASH_VALUE DISK_READS_DELTA BUFFER_GETS_DELTA ROWS_PROCESSED_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA IOWAIT_DELTA
---------- ------------- ---------------- ----------------- --------------- ---------------- ----------------- -------------------- -------------- ------------------ ------------54563 b9vfj9nfx8h96                6                 6      1009235934              491            743341                20869        3643447            5020230      148105654635 b9vfj9nfx8h96                5                 6      3318088377              581           1399105                11071      168190432          165837994      1311920

4.解决办法

在 Oracle 10g 上，可以使用不同的选项将执行计划绑定到sql, 如果不能对源代码使用hint，则可以使用outline或 SQL Profile功能.
SQL profile 比outline更可取，因为管理outline可能会变得很麻烦，并且有时outline可能无法按预期工作。
这里使用SQL Profile稳定执行计划有两种方式：

4.1 SQLTXPLAIN 也称为 SQLT

4.11 下载coe_xfr_sql_profile.sql

oracle Server Technologies Center of Expertise - ST CoE 提供的工具：
oracle官网下载
github下载
SQLT 提供了脚本 coe_xfr_sql_profile.sql，有助于轻松地针对语句创建 SQL profile，帮我们将目标SQL_ID与理想的执行计划绑定起来

下载 SQLT 后，将存档解压缩到选择的工作目录中。
要使用该脚本，需要知道语句的 SQL_ID 以及要绑定到该语句的计划的 PLAN_HASH_VALUE

4.12 使用方法

Usage:

sqlplus / as sysdba
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

4.13 执行coe_xfr_sql_profile.sql

因为上面已经获取SQL_ID及理想的plan_hash_value,所以这里选择直接执行：

SQL> @./coe_xfr_sql_profile.sql b9vfj9nfx8h96 1009235934
Parameter 1:
SQL_ID (required)PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------3028670118        .6621009235934        .6973233671506       1.4723318088377     221.581429843609Parameter 2:
PLAN_HASH_VALUE (required)Values passed:
~~~~~~~~~~~~~
SQL_ID         : "b9vfj9nfx8h96"
PLAN_HASH_VALUE: "1009235934"Execute coe_xfr_sql_profile_b9vfj9nfx8h96_1009235934.sql
on TARGET system in order to create a custom SQL Profile
with plan 1009235934 linked to adjusted sql_text.COE_XFR_SQL_PROFILE completed.

这将在当前的工作目录中生成一个脚本和一个日志文件：

4.14 执行coe_xfr_sql_profile.sql产生的sql profile文件

SQL>host ls
coe_xfr_sql_profile_b9vfj9nfx8h96_1009235934.sql  coe_xfr_sql_profile.log  coe_xfr_sql_profile.sql

现在执行此脚本 (coe_xfr_sql_profile_b9vfj9nfx8h96_1009235934.sql ) 以生成 SQL profile文件，该文件将指示优化器使用指定的计划

SQL>@./coe_xfr_sql_profile_b9vfj9nfx8h96_1009235934.sql 

4.15 验证

SQL>select sql_id, sql_profile from V$SQLAREA where sql_id='b9vfj9nfx8h96'
SQL_ID        SQL_PROFILE
------------- ----------------------------
b9vfj9nfx8h96 coe_b9vfj9nfx8h96_1009235934

实际执行状况

select SAMPLE_ID,SAMPLE_TIME,SQL_ID,SQL_PLAN_HASH_VALUE
from v$active_session_history 
where sql_id='b9vfj9nfx8h96' AND to_char(sample_time,'YYYY-MM-DD HH24:MI:SS') LIKE '2023-09-08%'
SAMPLE_ID SAMPLE_TIME                              SQL_ID        SQL_PLAN_HASH_VALUE
---------- ---------------------------------------- ------------- -------------------194599239 08-SEP-23 10.50.10.108 AM                b9vfj9nfx8h96          1009235934194598642 08-SEP-23 10.40.11.518 AM                b9vfj9nfx8h96          1009235934194598641 08-SEP-23 10.40.10.518 AM                b9vfj9nfx8h96          1009235934

4.2 SQL Tuning Advisor方式

透过bms_sqltune去创建tunning task，确认输出理想的执行计划后，将该sql profile与目表sql_id绑定
由于本文的重点只是想简单的替换较理想的执行计划，因此此种方式在这里仅作演示

4.21 第一次Tuning task

SQL>exec dbms_sqltune.drop_tuning_task('coe_b9vfj9nfx8h96_1009235934');

SQL> declare 
t_task_name varchar2(255);
t_sql_id v$session.prev_sql_id%type;
begin
t_sql_id:='b9vfj9nfx8h96';
t_task_name:='sqlb9vfj9nfx8h96';
t_task_name:=dbms_sqltune.create_tuning_Task(sql_id=>t_sql_id,task_name=>t_task_name);
dbms_sqltune.execute_tuning_task(t_task_name);
end;
PL/SQL procedure successfully completed.

4.22 输出第一次报告

由于此次执行，有建议，这里仅贴出建议的部分

SQL>select dbms_sqltune.report_tuning_task('sqlb9vfj9nfx8h96') from dual
1- Index Finding (see explain plans section below)
--------------------------------------------------The execution plan of this statement can be improved by creating one or moreindices.Recommendation (estimated benefit: 98.62%)------------------------------------------- Consider running the Access Advisor to improve the physical schema designor creating the recommended index.create index IDHRIS.IDX$$_339980001 onIDHRIS.EM_CARD_RECORD("PNL_NO","CARD_TM");

4.23 执行报告建议部分

第一次运行，建议创建一个索引，照做

SQL>create index IDHRIS.pnlno_cardtm on IDHRIS.EM_CARD_RECORD("PNL_NO","CARD_TM")
Index IDHRIS.PNLNO_CARDTM 已建立.

4.24 第二次tunning task

不要忘记Drop第一次产生的tuning task

SQL>exec dbms_sqltune.drop_tuning_task( 'sqlb9vfj9nfx8h96')；
已順利完成 PL/SQL 程序.

执行第二次tunning task
由于先前的sql_id:b9vfj9nfx8h96已经从share_pool age out,按第一次tuning task的方式执行会出错
故这里改为直接使用sql_text方式去执行tuning task

SQL>declare 
t_task_name varchar2(255);
t_sql_text clob;
begin
t_sql_text:='INSERT INTO VIE_BU_IN_EM_CARD_RECORD (FACT_NO,  PNL_NO, CARD_DATE,CARD_TM, SEQ_NO, MAR_NO, ADD_NAME, ADD_DATE, UPD_NAME, UPD_DATE,  SOURCE_MK, FACT_NO1, PNL_NO1)
select FACT_NO,  PNL_NO, CARD_DATE,CARD_TM, SEQ_NO, MAR_NO, ADD_NAME, TO_CHAR(SYSDATE, :"SYS_B_0") ADD_DATE, UPD_NAME, UPD_DATE,  SOURCE_MK, FACT_NO1, PNL_NO1
FROM TMP_VIE_BU_IN_EM_CARD_RECORD a
where not exists(select :"SYS_B_1" from VIE_BU_IN_EM_CARD_RECORD b where a.fact_no1 = b.fact_no1 and a.pnl_no = b.pnl_no and a.card_date = b.card_date and a.card_tm = b.card_tm and b.card_date  BETWEEN TO_CHAR(SYSDATE -:"SYS_B_2", :"SYS_B_3") AND TO_CHAR(SYSDATE, :"SYS_B_4"))';
t_task_name:='sqlb9vfj9nfx8h96';
t_task_name:=dbms_sqltune.create_tuning_Task(sql_text=>t_sql_text,user_name=>'CARD2HRIS', task_name=>t_task_name);
dbms_sqltune.execute_tuning_task(t_task_name);
end;

4.25 输出第二次报告

输出完整报告如下，
需要注意的是：
1.original的plan_hash_value是1009235934，这是按步骤4.1中的方式绑定的执行计划，这也从侧面佐证4.1的方式已经生效
2.从输出报告来看，创建建议的index后，产生了更优秀的执行计划plan_hash_value:563030811

SQL>select dbms_sqltune.report_tuning_task('sqlb9vfj9nfx8h96') from dual
"GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sqlb9vfj9nfx8h96
Tuning Task Owner                 : SYSTEM
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 09/08/2023 15:22:25
Completed at                      : 09/08/2023 15:22:26
Number of SQL Profile Findings    : 1-------------------------------------------------------------------------------
Schema Name: CARD2HRIS
SQL ID     : a4q778rt9z6rw
SQL Text   : INSERT INTO VIE_BU_IN_EM_CARD_RECORD (FACT_NO,  PNL_NO,CARD_DATE,CARD_TM, SEQ_NO, MAR_NO, ADD_NAME, ADD_DATE, UPD_NAME,UPD_DATE,  SOURCE_MK, FACT_NO1, PNL_NO1)select FACT_NO,  PNL_NO, CARD_DATE,CARD_TM, SEQ_NO, MAR_NO,ADD_NAME, TO_CHAR(SYSDATE, :"SYS_B_0") ADD_DATE, UPD_NAME,UPD_DATE,  SOURCE_MK, FACT_NO1, PNL_NO1FROM TMP_VIE_BU_IN_EM_CARD_RECORD awhere not exists(select :"SYS_B_1" from VIE_BU_IN_EM_CARD_RECORD b where a.fact_no1 = b.fact_no1 and a.pnl_no = b.pnl_no anda.card_date = b.card_date and a.card_tm = b.card_tm andb.card_date  BETWEEN TO_CHAR(SYSDATE -:"SYS_B_2", :"SYS_B_3") ANDTO_CHAR(SYSDATE, :"SYS_B_4"))-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------A potentially better execution plan was found for this statement.Recommendation (estimated benefit: 99.65%)------------------------------------------- Consider accepting the recommended SQL profile.execute dbms_sqltune.accept_sql_profile(task_name => 'sqlb9vfj9nfx8h96',replace => TRUE);-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "coe_b9vfj9nfx8h96_1009235934" exists for this statement andwas ignored during the tuning process.-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------1- Original With Adjusted Cost
------------------------------
Plan hash value: 1009235934---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |                              |     1 |   394 |  1745   (1)| 00:00:21 |
|*  1 |  HASH JOIN ANTI                |                              |     1 |   394 |  1745   (1)| 00:00:21 |
|   2 |   TABLE ACCESS FULL            | TMP_VIE_BU_IN_EM_CARD_RECORD |     1 |   357 |     2   (0)| 00:00:01 |
|   3 |   VIEW                         | VW_SQ_1                      |  9153 |   330K|  1742   (1)| 00:00:21 |
|   4 |    NESTED LOOPS                |                              |  9153 |   455K|  1742   (1)| 00:00:21 |
|   5 |     TABLE ACCESS BY INDEX ROWID| EM_CARD_RECORD               | 30417 |  1009K|  1735   (1)| 00:00:21 |
|*  6 |      INDEX RANGE SCAN          | IDX_ADD_DATE                 | 54751 |       |   169   (1)| 00:00:03 |
|*  7 |     INDEX UNIQUE SCAN          | PK_SYS_DATA_PERMISSION       |     1 |    17 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("A"."FACT_NO1"="ITEM_1" AND "A"."PNL_NO"="ITEM_2" AND "A"."CARD_DATE"="ITEM_3" AND "A"."CARD_TM"="ITEM_4")6 - access("CARD_DATE">=TO_CHAR(SYSDATE@!-TO_NUMBER(:SYS_B_2),:SYS_B_3) AND "CARD_DATE"<=TO_CHAR(SYSDATE@!,:SYS_B_4))7 - access("DATA_TYPE"='A' AND "LOGIN_USER"=SYS_CONTEXT('USERENV','CURRENT_USER') AND "FACT_NO1"="DATA_NO")2- Using SQL Profile
--------------------
Plan hash value: 563030811--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                              |     1 |   357 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                       |                              |       |       |            |          |
|   2 |   TABLE ACCESS FULL           | TMP_VIE_BU_IN_EM_CARD_RECORD |     1 |   357 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS                |                              |     1 |    51 |     4   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN          | PK_SYS_DATA_PERMISSION       |     1 |    17 |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| EM_CARD_RECORD               |     1 |    34 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | PNLNO_CARDTM                 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter( NOT EXISTS (SELECT 0 FROM IDHRIS."EM_CARD_RECORD" "EM_CARD_RECORD",IDHRIS."SYS_DATA_PERMISSION" "SYS_DATA_PERMISSION" WHERE "DATA_NO"=:B1 AND "LOGIN_USER"=SYS_CONTEXT('USERENV','CURRENT_USER') AND "DATA_TYPE"='A' AND "CARD_TM"=:B2 AND "PNL_NO"=:B3 AND "FACT_NO1"=:B4 AND "CARD_DATE"=:B5 AND "CARD_DATE"<=TO_CHAR(SYSDATE@!,:SYS_B_4) AND "CARD_DATE">=TO_CHAR(SYSDATE@!-TO_NUMBER(:SYS_B_2),:SYS_B_3) AND "FACT_NO1"="DATA_NO"))4 - access("DATA_TYPE"='A' AND "LOGIN_USER"=SYS_CONTEXT('USERENV','CURRENT_USER') AND "DATA_NO"=:B1)5 - filter("FACT_NO1"=:B1 AND "CARD_DATE"=:B2 AND "CARD_DATE"<=TO_CHAR(SYSDATE@!,:SYS_B_4) AND "CARD_DATE">=TO_CHAR(SYSDATE@!-TO_NUMBER(:SYS_B_2),:SYS_B_3) AND "FACT_NO1"="DATA_NO")6 - access("PNL_NO"=:B1 AND "CARD_TM"=:B2)-------------------------------------------------------------------------------
"

4.26 绑定执行计划

接下来要做的是使用dbms_sqltune.accept_sql_profile去将目标sql与新的执行计划作绑定

execute dbms_sqltune.accept_sql_profile(task_name =>'sqlb9vfj9nfx8h96',replace => TRUE,force_match=>true);
PL/SQL procedure successfully completed.

参数force_match默认值是false，表示只有在SQL文本完全匹配的情况下才会应用SQL Profile，这种情况下只要目标SQL的SQL文本发生一点变动，原有的SQL profile就将失去作用

4.27 验证

SQL>select sql_id, sql_profile from V$SQLAREA where sql_id='b9vfj9nfx8h96'
SQL_ID        SQL_PROFILE
------------- ----------------------------
b9vfj9nfx8h96 SYS_SQLPROF_01629d1759d48000

从最近的结果来看，已经选择走新的执行计划了

SQL>select SAMPLE_ID,SAMPLE_TIME,SQL_ID,SQL_PLAN_HASH_VALUE
from v$active_session_history 
where sql_id='b9vfj9nfx8h96' AND to_char(sample_time,'YYYY-MM-DD HH24:MI:SS') LIKE '2023-09-08%';
SAMPLE_ID SAMPLE_TIME                    SQL_ID        SQL_PLAN_HASH_VALUE
---------- ------------------------------ ------------- -------------------194617211 08-SEP-23 03.50.29.430 PM      b9vfj9nfx8h96           563030811194617210 08-SEP-23 03.50.28.430 PM      b9vfj9nfx8h96           563030811194617209 08-SEP-23 03.50.27.430 PM      b9vfj9nfx8h96           563030811