网站栏目 英文,企业门户网站建设情况,长春今天最新通告,重庆公司章程如何查询下载文章目录 1.缘起2.变慢的sql3.检查瓶颈4.解决办法4.1 SQLTXPLAIN 也称为 SQLT4.11 下载coe_xfr_sql_profile.sql4.12 使用方法4.13 执行coe_xfr_sql_profile.sql4.14 执行coe_xfr_sql_profile.sql产生的sql profile文件4.15 验证 4.2 SQL Tuning Advisor方式4.21 第一次Tuning … 文章目录 1.缘起2.变慢的sql3.检查瓶颈4.解决办法4.1 SQLTXPLAIN 也称为 SQLT4.11 下载coe_xfr_sql_profile.sql4.12 使用方法4.13 执行coe_xfr_sql_profile.sql4.14 执行coe_xfr_sql_profile.sql产生的sql profile文件4.15 验证 4.2 SQL Tuning Advisor方式4.21 第一次Tuning task4.22 输出第一次报告4.23 执行报告建议部分4.24 第二次tunning task4.25 输出第二次报告4.26 绑定执行计划4.27 验证 1.缘起
接上次一次hard parse处理过程 自从为了解决hard parse的问题而设置了cursor_sharingforce后又衍生了其他的问题那就是执行计划的稳定性如下记录发生的一起强制绑定变量后引起的执行计划绑定的问题
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_IDb9vfj9nfx8h96
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_IDb9vfj9nfx8h96
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_IDb9vfj9nfx8h96
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_sharingforce之前此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_idb9vfj9nfx8h96 --and plan_hash_value3318088377
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_idsn.snap_id and sq.sql_idb9vfj9nfx8h96 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 13119204.解决办法
在 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: 1009235934Execute 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文件
SQLhost 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 验证
SQLselect sql_id, sql_profile from V$SQLAREA where sql_idb9vfj9nfx8h96
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_idb9vfj9nfx8h96 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 10092359344.2 SQL Tuning Advisor方式
透过bms_sqltune去创建tunning task确认输出理想的执行计划后将该sql profile与目表sql_id绑定 由于本文的重点只是想简单的替换较理想的执行计划因此此种方式在这里仅作演示
4.21 第一次Tuning task
SQLexec 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_idt_sql_id,task_namet_task_name);
dbms_sqltune.execute_tuning_task(t_task_name);
end;
PL/SQL procedure successfully completed.4.22 输出第一次报告
由于此次执行有建议这里仅贴出建议的部分
SQLselect 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 执行报告建议部分
第一次运行建议创建一个索引照做
SQLcreate index IDHRIS.pnlno_cardtm on IDHRIS.EM_CARD_RECORD(PNL_NO,CARD_TM)
Index IDHRIS.PNLNO_CARDTM 已建立.4.24 第二次tunning task
不要忘记Drop第一次产生的tuning task
SQLexec dbms_sqltune.drop_tuning_task( sqlb9vfj9nfx8h96)
已順利完成 PL/SQL 程序.执行第二次tunning task 由于先前的sql_id:b9vfj9nfx8h96已经从share_pool age out,按第一次tuning task的方式执行会出错 故这里改为直接使用sql_text方式去执行tuning task
SQLdeclare
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_textt_sql_text,user_nameCARD2HRIS, task_namet_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
SQLselect 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_NO1ITEM_1 AND A.PNL_NOITEM_2 AND A.CARD_DATEITEM_3 AND A.CARD_TMITEM_4)6 - access(CARD_DATETO_CHAR(SYSDATE!-TO_NUMBER(:SYS_B_2),:SYS_B_3) AND CARD_DATETO_CHAR(SYSDATE!,:SYS_B_4))7 - access(DATA_TYPEA AND LOGIN_USERSYS_CONTEXT(USERENV,CURRENT_USER) AND FACT_NO1DATA_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_USERSYS_CONTEXT(USERENV,CURRENT_USER) AND DATA_TYPEA AND CARD_TM:B2 AND PNL_NO:B3 AND FACT_NO1:B4 AND CARD_DATE:B5 AND CARD_DATETO_CHAR(SYSDATE!,:SYS_B_4) AND CARD_DATETO_CHAR(SYSDATE!-TO_NUMBER(:SYS_B_2),:SYS_B_3) AND FACT_NO1DATA_NO))4 - access(DATA_TYPEA AND LOGIN_USERSYS_CONTEXT(USERENV,CURRENT_USER) AND DATA_NO:B1)5 - filter(FACT_NO1:B1 AND CARD_DATE:B2 AND CARD_DATETO_CHAR(SYSDATE!,:SYS_B_4) AND CARD_DATETO_CHAR(SYSDATE!-TO_NUMBER(:SYS_B_2),:SYS_B_3) AND FACT_NO1DATA_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_matchtrue);
PL/SQL procedure successfully completed.参数force_match默认值是false表示只有在SQL文本完全匹配的情况下才会应用SQL Profile这种情况下只要目标SQL的SQL文本发生一点变动原有的SQL profile就将失去作用
4.27 验证
SQLselect sql_id, sql_profile from V$SQLAREA where sql_idb9vfj9nfx8h96
SQL_ID SQL_PROFILE
------------- ----------------------------
b9vfj9nfx8h96 SYS_SQLPROF_01629d1759d48000从最近的结果来看已经选择走新的执行计划了
SQLselect SAMPLE_ID,SAMPLE_TIME,SQL_ID,SQL_PLAN_HASH_VALUE
from v$active_session_history
where sql_idb9vfj9nfx8h96 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
