接上次一次hard parse处理过程
自从为了解决hard parse的问题而设置了cursor_sharing=force后，又衍生了其他的问题，那就是执行计划的稳定性，如下记录发生的一起强制绑定变量后引起的执行计划绑定的问题
用戶反映早上接数据变得很慢，使用如下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 343
null 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_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"))"
可以看没有"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 53
460 b9vfj9nfx8h96 3318088377 97
656 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 1481056
54635 b9vfj9nfx8h96 5 6 3318088377 581 1399105 11071 168190432 165837994 1311920
在 Oracle 10g 上，可以使用不同的选项将执行计划绑定到sql, 如果不能对源代码使用hint，则可以使用outline或 SQL Profile功能.
SQL profile 比outline更可取，因为管理outline可能会变得很麻烦，并且有时outline可能无法按预期工作。
这里使用SQL Profile稳定执行计划有两种方式：
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
Usage:
sqlplus / as sysdba
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
因为上面已经获取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 .662
1009235934 .697
3233671506 1.472
3318088377 221.581
429843609
Parameter 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.
这将在当前的工作目录中生成一个脚本和一个日志文件：
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
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 1009235934
194598642 08-SEP-23 10.40.11.518 AM b9vfj9nfx8h96 1009235934
194598641 08-SEP-23 10.40.10.518 AM b9vfj9nfx8h96 1009235934
透过bms_sqltune去创建tunning task，确认输出理想的执行计划后，将该sql profile与目表sql_id绑定
由于本文的重点只是想简单的替换较理想的执行计划，因此此种方式在这里仅作演示
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.
由于此次执行，有建议，这里仅贴出建议的部分
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 more
indices.
Recommendation (estimated benefit: 98.62%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index IDHRIS.IDX$$_339980001 on
IDHRIS.EM_CARD_RECORD("PNL_NO","CARD_TM");
第一次运行，建议创建一个索引，照做
SQL>create index IDHRIS.pnlno_cardtm on IDHRIS.EM_CARD_RECORD("PNL_NO","CARD_TM")
Index IDHRIS.PNLNO_CARDTM 已建立.
不要忘记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;
输出完整报告如下，
需要注意的是：
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_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"))
-------------------------------------------------------------------------------
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 and
was 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)
-------------------------------------------------------------------------------
"
接下来要做的是使用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就将失去作用
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 563030811
194617210 08-SEP-23 03.50.28.430 PM b9vfj9nfx8h96 563030811
194617209 08-SEP-23 03.50.27.430 PM b9vfj9nfx8h96 563030811