Oracle绑定SQL执行计划

挠背小能手

已于 2022-08-27 11:05:09 修改

阅读量1.9k

点赞数 2

分类专栏： Oracle日常运维文章标签： sql oracle 数据库

于 2022-08-27 10:49:32 首次发布

本文链接：https://blog.csdn.net/m0_37625564/article/details/126554252

版权

Oracle日常运维专栏收录该内容

129 篇文章 16 订阅

订阅专栏

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,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 

Using the report name awrsqlrpt_1_43729_43921.html

查看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;
/

查看绑定是否成功

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

如果需要取消绑定，删除profile即可

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE_bpzysbqpbr69u');
END;
/

查询还有没有绑定

SQL> select * from dba_sql_profiles WHERE name ='PROFILE_bpzysbqpbr69u';

no rows selected

查看绑定执行计划后的效率

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.

绑定后的执行计划已经生效，最后的执行都是走的新的执行计划，效率也比较高了。