Oracle绑定执行计划
在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;