-
Oracle绑定SQL执行计划
SQL在执行过程中，如果走了较差的执行计划，执行效率将大幅度下降，本文介绍如何将SQL绑定较好的执行计划。
上图中，sqlid为 bpzysbqpbr69u的SQL语句，在AWR统计时间（1小时）内，总计造成了2.8E次的逻辑读，在以往的报告中，没有这么高，怀疑可能是执行计划出现了偏差。通过awrsqrpt.sql查看SQL详情。
SQL> @?/rdbms/admin/awrsqrpt.sql ... Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 43729 Begin Snapshot Id specified: 43729 Enter value for end_snap: 43921 End Snapshot Id specified: 43921 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ Enter value for sql_id: bpzysbqpbr69u SQL ID specified: bpzysbqpbr69u Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_43729_43921.html. To use this name, pressto continue, otherwise enter an alternative. Enter value for report_name: Using the report name awrsqlrpt_1_43729_43921.html - 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
查看sql详情
该SQL有8个执行计划。
第1个执行计划：
第二个执行计划
第三个执行计划
第四个执行计划
第五个执行计划
第六个执行计划
第七个执行计划
第八个执行计划
综上比较，第五个执行计划看上去最优，为了防止执行计划偏差，我们手工将最优的执行计划绑定。
此处采用sql profile的方式绑定执行计划
declare ar_profile_hints sys.sqlprof_attr; clsql_text CLOB; begin select extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan where sql_id = 'bpzysbqpbr69u' and plan_hash_value = 48916261 and other_xml is not null)) d; SELECT sql_text INTO clsql_text FROM dba_hist_sqltext where sql_id = 'bpzysbqpbr69u'; DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text, profile => ar_profile_hints, name => 'PROFILE_bpzysbqpbr69u', force_match => TRUE, REPLACE => TRUE); end; /- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
查看绑定是否成功
select * from dba_sql_profiles WHERE name ='PROFILE_bpzysbqpbr69u'; NAME ------------------------------------------------------------------------------ CATEGORY ------------------------------------------------------------------------------ SIGNATURE ---------- SQL_TEXT ------------------------------------------------------------------------------ CREATED --------------------------------------------------------------------------- LAST_MODIFIED --------------------------------------------------------------------------- DESCRIPTION ------------------------------------------------------------------------------ TYPE STATUS FOR TASK_ID ------- -------- --- ---------- TASK_EXEC_NAME ------------------------------------------------------------------------------ TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID ----------- ----------- ----------- ------------- PROFILE_bpzysbqpbr69u DEFAULT 1.2213E+19 SELECT a2.ORDER_APPLY_ID, t.ORD_REPORT_ID, t.ORD_RESULT_ID, t.REPORT_NAM 27-AUG-22 09.10.08.000000 AM 27-AUG-22 09.10.08.000000 AM MANUAL ENABLED YES- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
如果需要取消绑定，删除profile即可
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE_bpzysbqpbr69u'); END; /- 1
- 2
- 3
- 4
查询还有没有绑定
SQL> select * from dba_sql_profiles WHERE name ='PROFILE_bpzysbqpbr69u'; no rows selected- 1
- 2
- 3
查看绑定执行计划后的效率
col SQL_PROFILE for a25 col last_load_time for a25 set linesize 1000 select inst_id,sql_id,sql_profile,executions,plan_hash_value, elapsed_time / DECODE(executions, 0, 1, EXECUTIONS) / 1000 elasp_time_ms, buffer_gets / DECODE(executions, 0, 1, EXECUTIONS) buffer_gets, disk_reads / DECODE(executions, 0, 1, EXECUTIONS) disk_reads, cpu_time / DECODE(executions, 0, 1, EXECUTIONS)/1000 cpu_time_ms, last_load_time,last_active_time,child_number from gv$sql where SQL_ID IN ('&sql_id'); Enter value for sql_id: bpzysbqpbr69u old 8: where SQL_ID IN ('&sql_id') new 8: where SQL_ID IN ('bpzysbqpbr69u') INST_ID SQL_ID SQL_PROFILE EXECUTIONS PLAN_HASH_VALUE ELASP_TIME_MS BUFFER_GETS DISK_READS CPU_TIME_MS LAST_LOAD_TIME LAST_ACTIVE_TIME CHILD_NUMBER ---------- ------------- ------------------------- ---------- --------------- ------------- ----------- ---------- ----------- ------------------------- ------------------ ------------ 1 bpzysbqpbr69u 10466 1021131900 566.205715 209522.498 .008981464 212.727675 2022-08-26/11:43:49 27-AUG-22 2 1 bpzysbqpbr69u PROFILE_bpzysbqpbr69u 1337 48916261 .787545999 97.3328347 .008227375 .220566193 2022-08-27/10:24:21 27-AUG-22 3 2 bpzysbqpbr69u 67 1021131900 240.534134 64721.1045 0 73.5290149 2022-08-27/06:45:53 27-AUG-22 0 2 bpzysbqpbr69u 2 48916261 45.053 90.5 .5 16.0225 2022-08-27/08:00:16 27-AUG-22 2 2 bpzysbqpbr69u 3 48916261 40.0836667 149.333333 0 11.2676667 2022-08-27/08:01:18 27-AUG-22 3 2 bpzysbqpbr69u 2 48916261 59.091 39.5 1 15.928 2022-08-27/08:08:35 27-AUG-22 4 2 bpzysbqpbr69u 4 48916261 25.19425 209.25 0 7.96175 2022-08-27/08:10:53 27-AUG-22 5 2 bpzysbqpbr69u 1 48916261 80.24 339 0 27.211 2022-08-27/08:14:22 27-AUG-22 6 2 bpzysbqpbr69u 1 48916261 78.607 125 0 30.215 2022-08-27/08:14:27 27-AUG-22 7 2 bpzysbqpbr69u 1 48916261 121.975 199 0 34.482 2022-08-27/08:15:29 27-AUG-22 8 2 bpzysbqpbr69u 1 48916261 77.39 205 0 29.668 2022-08-27/08:15:36 27-AUG-22 9 INST_ID SQL_ID SQL_PROFILE EXECUTIONS PLAN_HASH_VALUE ELASP_TIME_MS BUFFER_GETS DISK_READS CPU_TIME_MS LAST_LOAD_TIME LAST_ACTIVE_TIME CHILD_NUMBER ---------- ------------- ------------------------- ---------- --------------- ------------- ----------- ---------- ----------- ------------------------- ------------------ ------------ 2 bpzysbqpbr69u 1 1021131900 244.875 22429 0 77.448 2022-08-27/08:15:46 27-AUG-22 10 2 bpzysbqpbr69u 1 3718162699 59.588 129 0 20.543 2022-08-27/08:16:32 27-AUG-22 11 2 bpzysbqpbr69u 1 48916261 74.156 86 0 30.944 2022-08-27/08:17:01 27-AUG-22 12 2 bpzysbqpbr69u 17 3718162699 61.5604118 405.117647 .117647059 5.667 2022-08-27/08:17:08 27-AUG-22 13 2 bpzysbqpbr69u 1 48916261 103.079 149 0 32.874 2022-08-27/08:20:39 27-AUG-22 14 2 bpzysbqpbr69u 2 48916261 41.833 166.5 0 14.03 2022-08-27/08:24:25 27-AUG-22 15 2 bpzysbqpbr69u 4 48916261 38.2875 187 0 10.3445 2022-08-27/08:53:54 27-AUG-22 16 2 bpzysbqpbr69u 2 48916261 59.1735 4.5 0 16.0195 2022-08-27/08:24:48 27-AUG-22 17 2 bpzysbqpbr69u 1 48916261 99.7 96 0 33.909 2022-08-27/08:25:48 27-AUG-22 18 2 bpzysbqpbr69u 1 48916261 85.022 127 0 29.2 2022-08-27/08:30:13 27-AUG-22 19 2 bpzysbqpbr69u 2 48916261 47.5845 137.5 0 14.911 2022-08-27/08:31:50 27-AUG-22 20 INST_ID SQL_ID SQL_PROFILE EXECUTIONS PLAN_HASH_VALUE ELASP_TIME_MS BUFFER_GETS DISK_READS CPU_TIME_MS LAST_LOAD_TIME LAST_ACTIVE_TIME CHILD_NUMBER ---------- ------------- ------------------------- ---------- --------------- ------------- ----------- ---------- ----------- ------------------------- ------------------ ------------ 2 bpzysbqpbr69u 2 48916261 64.379 4.5 0 15.879 2022-08-27/08:31:55 27-AUG-22 21 2 bpzysbqpbr69u 8 48916261 17.952625 177.875 0 4.43825 2022-08-27/08:35:10 27-AUG-22 22 2 bpzysbqpbr69u 2 48916261 43.222 125 0 15.2065 2022-08-27/08:35:56 27-AUG-22 23 2 bpzysbqpbr69u 8 48916261 14.901375 173.5 0 4.65825 2022-08-27/08:54:17 27-AUG-22 24 2 bpzysbqpbr69u 8 48916261 18.79675 393.25 0 4.858 2022-08-27/08:55:57 27-AUG-22 25 2 bpzysbqpbr69u PROFILE_bpzysbqpbr69u 105 48916261 5.40520952 119.457143 .380952381 .735647619 2022-08-27/10:24:34 27-AUG-22 27 28 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
绑定后的执行计划已经生效，最后的执行都是走的新的执行计划，效率也比较高了。
-
相关阅读:
【c#】判断double值是否等于NaN
Tomcat：Java Web
【python】python+numpy模块读、写raw图并使用opencv显示图片
【大屏项目】SpringBoot + Vue 实现的可视化拖拽编辑的
【】如何实现异步通知的重试机制
前端er须知的Nginx技巧
Mysql8创建用户以及赋权操作
编写高效的代码，你应该了解Array、Memory、ReadOnlySequence . . .
聊聊运营活动的设计与实现逻辑
LeetCode 48. 旋转图像
- 原文地址：https://blog.csdn.net/m0_37625564/article/details/126554252
