【Oracle】Oracle绑定执行计划-20230916

Tzq@2018

已于 2024-07-12 14:40:48 修改

阅读量549

点赞数 1

分类专栏： Oracle 文章标签： oracle

于 2023-09-16 19:59:19 首次发布

本文链接：https://blog.csdn.net/tttzzzqqq2018/article/details/132922160

版权

Oracle 专栏收录该内容

121 篇文章 18 订阅

订阅专栏

【Oracle】Oracle绑定执行计划

一、执行计划管理（SPM：SQL Plan Management）
二、SQL Profile绑定

在Oracle中时长会出现一直运行正常的SQL突然运行的很慢，检查发现执行计划发生了改变，这时候就需要绑定执行计划。在Oracle 10G以后的版本中可以使用SQL profile或SPM（11g以后）来绑定执行计划。

一、执行计划管理（SPM：SQL Plan Management）

SPM简介

执行计划管理（SPM：SQL Plan Management）

SPM（SQL plan management）是通过执行计划绑定，对执行计划进行人为干预的一系列功能，包括执行计划绑定、自动捕获绑定、自动演进绑定等。
SPM（SQL plan management）是一种主动的稳定执行计划的手段，只有被验证过的执行计划才会被启用。彻底解决执行计划稳定性的问题，它既能主动的稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。当启用SPM之后每一个SQL都会存在对应的SQL plan baseline，这个SQL plan baseline是存储在执行计划中的，可以从DBA_SQL_PLAN_BASELINES中查看目标SQL的所有SQL plan baseline，只有DBA_SQL_PLAN_BASELINES中列ENABLED和ACCEPTED两个列都是“YES”，SQL plan baseline对应的执行计划才会被Oracle启用，如果一个SQL有超过1个以上ENABLED和ACCEPTED两个列都是“YES”，则Oracle会从中选择成本最低的执行计划作为该SQL的执行计划。

执行计划绑定 (SQL Binding)

执行计划绑定是 SPM 的基础。在优化器 Hints 中介绍了可以通过 Hint 的方式选择指定的执行计划，但有时需要在不修改 SQL 语句的情况下干预执行计划的选择。执行计划绑定功能使得可以在不修改 SQL 语句的情况下选择指定的执行计划。

实际上在Oracle升级或者正常运行中都需要使用SPM来稳定执行计划，主要由两种方法：

1.1、自动捕获

1.1.1、参数：OPTIMIZER_CAPTURE_SQL_PALN_BASELINES

用于控制是否启用自动捕获SQL plan baseline。
默认值是FALSE，不开启自动捕获SQL plan baseline。
该参数设置为TRUE后，Oracle会对该参数影响范围的所有重复执行的SQL自动捕获其SQL plan baseline，并且针对SQL第一次捕获的SQL plan baseline的ENABLED和ACCEPTED两个列都是“YES”。当该SQL执行计划发生改变再次捕获到SQL plan baseline的ENABLED依然是“YES”，但是ACCEPTED列为“NO”，表示后续变更执行计划虽然被捕获了，但是仍然使用第一次捕获的SQL plan baseline对应的执行计划。

1.1.2、参数：OPTIMIZER_USE_SQL_PALN_BASELINES

用于控制是否使用SQL plan baseline，默认为TRUE，默认Oracle在生成执行计划就会生成SPM，使用以后的SQL plan baseline。

1.1.3、演示自动捕获SPM

1.1.3.1、查看当前参数，修改为会话级别的自动捕获不开启SQL plan baseline。

1.1.3.1.1、查看当前参数show parameter sql_plan

执行SQL命令：

show parameter sql_plan

在这里插入图片描述
可以看到optimizer_capture_sql_plan_baselines是FALSE，optimizer_use_sql_plan_baselines是TRUE。

1.1.3.1.2、修改为会话级别的自动捕获不开启SQL plan baseline

执行SQL命令：

alter session set optimizer_capture_sql_plan_baselines=true;
alter session set optimizer_use_sql_plan_baselines=false;

在这里插入图片描述
修改后optimizer_capture_sql_plan_baselines是TRUE，optimizer_use_sql_plan_baselines是FALSE。

1.2、手工生成/批量导入（Oracle大版本升级中适用）

其本质是使用dbms_spm.load_plans_from_cursor_cache通过同SQL Profile一样“偷梁换柱”的方式修改目标SQL的执行计划。

–继续使用上述实验语句，加入hint使其奏full table scan。

SQL> select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
       101 FIXED_OBJ$

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5y5awha0zwr70, child number 0
-------------------------------------
select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101

Plan hash value: 2215152728

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   345 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB_2 |     1 |    30 |   345   (1)| 00:00:05 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / TAB_2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TAB_2"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=101)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]


43 rows selected.
--查看此时的SQL plan baseline，由于没有开启自动捕获，此时查到的为空。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';

no rows selected
--手动生成SQL plan baseline，这里的SQL_id是原SQL产生执行计划的SQL_id,plan_hash_value是原SQL产生执行计划的plan_hash_value，即以上执行计划的内容。
SQL> var temp number;
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'5y5awha0zwr70',plan_hash_value=>2215152728);

PL/SQL procedure successfully completed.
--再次查看SQL plan baseline，发现已经手动产生了baseline，origin=manual-load，此时的SQL plan baseline是full table scan 。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ---------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6           SQL_PLAN_5shy2dbabx2p62be364ab MANUAL-LOAD    YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--对原SQL进行hint调优，记录优化后的SQL_id，和plan_hash_value。
SQL> select /*+ index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
       101 FIXED_OBJ$
--查看执行计划
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3r73vgbjb12ww, child number 0
-------------------------------------
select /*+ index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101

Plan hash value: 1855613224

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |   465 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB_2   |     1 |    30 |   465   (0)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | OBJ_IND |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / TAB_2@SEL$1
   2 - SEL$1 / TAB_2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TAB_2"@"SEL$1" ("TAB_2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=101)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "TAB_2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]


46 rows selected.
--再次查看此时的SQL plan baseline，记录SQL_HANDLE
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ----------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6           SQL_PLAN_5shy2dbabx2p62be364ab MANUAL-LOAD    YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--修改原SQL的SQL plan baseline，这里sql_id是优化后SQL的sql_id，plan_hash_value是优化后SQL的plan_hash_value，sql_handle是原SQL产生SQL plan baseline的sql_handle。
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'3r73vgbjb12ww',plan_hash_value=>1855613224,sql_handle=>'SQL_5c43c26ad4be8aa6');

PL/SQL procedure successfully completed.
--查看此时的SQL plan baseline，发现已经对优化后的SQL产生了新的SQL plan baseline，注意手动产生的SQL plan baseline ENABLED和ACCEPTED状态都是YES。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ----------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6           SQL_PLAN_5shy2dbabx2p62be364ab MANUAL-LOAD    YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
SQL_5c43c26ad4be8aa6           SQL_PLAN_5shy2dbabx2p665249919 MANUAL-LOAD    YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--删除原SQL的SQL plan baseline，sql_handle是原SQL的sql_handle，plan_name是原SQL的plan_name。
SQL> exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_5c43c26ad4be8aa6',plan_name=>'SQL_PLAN_5shy2dbabx2p62be364ab');

PL/SQL procedure successfully completed.
--查看修改后的SQL plan baseline，已经是index range scan类型的执行计划了。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ----------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6           SQL_PLAN_5shy2dbabx2p665249919 MANUAL-LOAD    YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--运行原SQL再次查看执行计划，此时已经使用索引扫描了。
SQL> select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
       101 FIXED_OBJ$

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5y5awha0zwr70, child number 1
-------------------------------------
select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101

Plan hash value: 1855613224

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |   465 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB_2   |     1 |    30 |   465   (0)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | OBJ_IND |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / TAB_2@SEL$1
   2 - SEL$1 / TAB_2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TAB_2"@"SEL$1" ("TAB_2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=101)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "TAB_2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

Note
-----
   - SQL plan baseline SQL_PLAN_5shy2dbabx2p665249919 used for this statement


50 rows selected.

SQL>

二、SQL Profile绑定

2.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 = 'SQLID 值' --好的执行计划对应的SQL ID
                      AND plan_hash_value = VALUE
                    值 --好的执行计划对应SQL ID 的hash value值
                      AND other_xml IS NOT NULL)) d;
  SELECT sql_text
    INTO clsql_text
    FROM dba_hist_sqltext
   WHERE sql_id = ' SQLID 值'; --需要绑定的SQL ID的值,通过视图查询该SQL 对应的文本
  dbms_sqltune.import_sql_profile(sql_text => clsql_text
                                 ,profile => ar_profile_hints
                                 ,NAME => 'PROFILE_ SQLID 值'
                                 , --SQL Profile绑定的标记
                                  force_match => TRUE
                                 ,——true 表示对于谓词部分 具体值变化后的 sql_id 也能使用该 SQL profile REPLACE => TRUE);
END;
/

实际执行SQL

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 = '5yv7w368z62bz'
                      AND plan_hash_value = '3270942279'
                      AND other_xml IS NOT NULL)) d;
  SELECT sql_text
    INTO clsql_text
    FROM dba_hist_sqltext
   WHERE sql_id = 'bcq5f5sd2k5wu';
  dbms_sqltune.import_sql_profile(sql_text => clsql_text
                                 ,profile => ar_profile_hints
                                 ,NAME => 'PROFILE_ SQLID bcq5f5sd2k5wu'
                                 ,force_match => TRUE
                                 ,REPLACE => TRUE);
END;
/

2.2、好的SQL执行计划，可能并不在历史的视图中记录，可以换成如下在memory cache的视图

-- dba_hist_sql_plan视图找不到记录！
SELECT xmltype(other_xml) AS xmlval
  FROM v$sql_plan
 WHERE sql_id = '5yv7w368z62bz'
   AND plan_hash_value = '3270942279'
   AND other_xml IS NOT NULL;

实际执行SQL

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 v$sql_plan
                    WHERE sql_id = '5yv7w368z62bz'
                      AND plan_hash_value = '3270942279'
                      AND other_xml IS NOT NULL)) d;
  SELECT sql_text
    INTO clsql_text
    FROM dba_hist_sqltext
   WHERE sql_id = 'bcq5f5sd2k5wu';
  dbms_sqltune.import_sql_profile(sql_text => clsql_text
                                 ,profile => ar_profile_hints
                                 ,NAME => 'PROFILE_ SQLID bcq5f5sd2k5wu'
                                 ,force_match => TRUE
                                 ,REPLACE => TRUE);
END;

2.3、检查是否绑定成功

--SQL 能执行的情况下
SQL> select * from dba_sql_profiles WHERE name ='PROFILE_ SQLID bcq5f5sd2k5wu';
- SQL profile PROFILE_ SQLID bcq5f5sd2k5wu used for this statement

2.4、删除SQL Profile

BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE_ SQLID bcq5f5sd2k5wu');END;/

2.5、验证SQL执行效率对比，可以检查SQL的执行效率

输入sql_id：

select 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)
      ,disk_reads / DECODE(executions, 0, 1, EXECUTIONS)
      ,cpu_time / DECODE(executions, 0, 1, EXECUTIONS)/1000 cpu_time_ms
      ,last_load_time,last_active_time
      ,sql_text
      ,child_number 
  from v$sql 
 where SQL_ID IN ('&sql_id');

select * 
  from (select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') begin_time
              ,a.instance_number
              ,MODULE
              ,plan_hash_value
              ,EXECUTIONS_DELTA EXEC
              ,decode(EXECUTIONS_DELTA, 0, buffer_gets_deltA
                      ,round(BUFFER_GETS_DELTA / EXECUTIONS_DELTA)) per_get
              ,decode(EXECUTIONS_DELTA, 0, ROWS_PROCESSED_DELTA, round(ROWS_PROCESSED_DELTA / EXECUTIONS_DELTA, 3)) per_rows
              ,decode(EXECUTIONS_DELTA, 0, ELAPSED_TIME_DELTA, round(ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000, 2)) time_ms
              ,decode(EXECUTIONS_DELTA, 0, DISK_READS_DELTA, round(DISK_READS_DELTA / EXECUTIONS_DELTA, 2)) per_read 
          from dba_hist_sqlstat a
              ,DBA_HIST_SNAPSHOT b 
         where a.snap_id = b.snap_id 
           and a.instance_number = b.instance_number 
           and a.sql_id = '&sql_id' order by 1 desc)
 where rownum < 100;

Tzq@2018

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
【Oracle】Oracle绑定执行计划-20230916

SPM（SQL plan management）是通过执行计划绑定，对执行计划进行人为干预的一系列功能，包括执行计划绑定、自动捕获绑定、自动演进绑定等。SPM（SQL plan management）是一种主动的稳定执行计划的手段，只有被验证过的执行计划才会被启用。彻底解决执行计划稳定性的问题，它既能主动的稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。
复制链接

扫一扫