Oracle数据库中执行sql的时候，优化器会根据优化器统计信息和一些参数来生成“它认为最好的“执行计划。
但是并不能够保证每一次都会选择最优的哪个执行计划。如果遇到了sql语句在一定时间段执行时性能变差，
并且发现它的执行计划发生了变化，那么可以考虑固定哪个”好的”时候的执行计划。
当然，必须保证sql语句没有被修改，是同一个sql。
1. SQL Plan Management （SQL计划管理，简称SPM）
这是Oracle推荐使用的一种方法。使用起来也很简单。以下是一个手动固定的例子。
代码语言：javascript复制 create table tbl_01 (id number, name varchar2(30));
create table tbl_02 (id number, name varchar2(30));
insert into tbl_01 values (1,'jack');
insert into tbl_02 values (1,'mary');以如下语句为例：
- 先执行该语句。
代码语言：javascript复制 SQL> select * from tbl_01 a, tbl_02 b where a.id = b.id;
ID NAME ID NAME
---------- ------------------------------ ---------- ------------------------------
1 jack 1 mary- 收集改语句的SQL_ID 以及 PLAN_HASH_VALUE 。
代码语言：javascript复制 SQL> col sql_text for a30
SQL> select sql_id, hash_value, plan_hash_value, sql_text from v$sql where sql_text like '%select * from tbl_01 a, tbl_02 b where a.id = b.id%';
SQL_ID HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- --------------- ------------------------------
bgrjka5pt2zh6 1804697094 2482469159 select * from tbl_01 a, tbl_02
b where a.id = b.id- 先确认一下目前该语句的执行计划
代码语言：javascript复制SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'bgrjka5pt2zh6'));
PLAN_TABLE_OUTPUT
------------
SQL_ID bgrjka5pt2zh6, child number 0
-------------------------------------
select * from tbl_01 a, tbl_02 b where a.id = b.id
Plan hash value: 2482469159
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 1 | 60 | 6 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| TBL_01 | 1 | 30 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TBL_02 | 1 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
Note
-----
PLAN_TABLE_OUTPUT
--------------------
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
25 rows selected.- 将该语句的cursor信息载入到Baseline中。
代码语言：javascript复制 SQL> DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => 'bgrjka5pt2zh6', plan_hash_value => '2482469159');
END;
/- 确认载入之后的状态。
代码语言：javascript复制 SQL> select sql_handle,plan_name, origin, enabled, accepted, fixed, reproduced, autopurge from dba_sql_plan_baselines where sql_text like '%select * from tbl_01 a, tbl_02 b where a.id = b.id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX REP AUT
-------------------- ---------------------------------------- ----------------------------- --- --- --- --- ---
SQL_5513e2891f399884 SQL_PLAN_5a4z2j4gmm644812f821a MANUAL-LOAD-FROM-CURSOR-CACHE YES YES NO YES YES这里我们看 FIX 这列是 NO，说明目前改执行计划没有被固定。
- 执行如下将固定执行计划
代码语言：javascript复制 SQL> var res number
SQL> exec :res := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( -
sql_handle => 'SQL_5513e2891f399884', -
plan_name => 'SQL_PLAN_5a4z2j4gmm644812f821a', -
attribute_name => 'FIXED', -
attribute_value => 'YES' );
PL/SQL procedure successfully completed.- 再来看baseline信息，FIX列已经变成了YES。
代码语言：javascript复制 SQL> select sql_handle,plan_name, origin, enabled, accepted, fixed, reproduced, autopurge from dba_sql_plan_baselines where sql_text like '%select * from tbl_01 a, tbl_02 b where a.id = b.id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX REP AUT
-------------------- ---------------------------------------- ----------------------------- --- --- --- --- ---
SQL_5513e2891f399884 SQL_PLAN_5a4z2j4gmm644812f821a MANUAL-LOAD-FROM-CURSOR-CACHE YES YES YES YES YES这个时候我们再执行同样的语句，发现执行计划被固定，并被使用了。
代码语言：javascript复制 SQL> set autotrace on
SQL> select * from tbl_01 a, tbl_02 b where a.id = b.id;
ID NAME ID NAME
---------- ------------------------------ ---------- ------------------------------
1 jack 1 mary
Execution Plan
----------------------------------------------------------
Plan hash value: 2482469159
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 60 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TBL_01 | 1 | 30 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TBL_02 | 1 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- SQL plan baseline "SQL_PLAN_5a4z2j4gmm644812f821a" used for this statement <----- 这里表明baseline SQL_PLAN_5a4z2j4gmm644812f821a 被用于该语句。
Statistics
----------------------------------------------------------
39 recursive calls
15 db block gets
54 consistent gets
0 physical reads
3580 redo size
745 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed也可以使用如下的方法。
- 将baseline自动捕获设为有效
代码语言：javascript复制 SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
Session altered.- 执行2次sql语句
代码语言：javascript复制 SQL> SELECT * FROM tbl_01 WHERE id > 0;
ID NAME
---------- ------------------------------
1 jack- 检查baseline的状态
代码语言：javascript复制 SQL> SELECT sql_handle, plan_name,accepted,sql_text FROM dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ACC SQL_TEXT
-------------------- ---------------------------------------- --- ------------------------------
SQL_dfe03a6e36a44c1d SQL_PLAN_dzs1udsva8m0x2f8e26bd YES SELECT * FROM tbl_01 WHERE id
> 0- 将baseline自动捕获变回无效
代码语言：javascript复制 SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = false;
Session altered.- 将使用baseline的参数设为有效
代码语言：javascript复制 SQL> ALTER SESSION SET optimizer_use_sql_plan_baselines = TRUE;
Session altered.- 检查执行计划是否被固定
代码语言：javascript复制 SQL> set autotrace on
SQL> SELECT * FROM tbl_01 WHERE id > 0;
ID NAME
---------- ------------------------------
1 jack
Execution Plan
----------------------------------------------------------
Plan hash value: 928870161
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TBL_01 | 1 | 30 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">0)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- SQL plan baseline "SQL_PLAN_dzs1udsva8m0x2f8e26bd" used for this statement <--baseline已应用
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed之后如果不需要了可以删除该baseline
代码语言：javascript复制 SQL> var xx number
SQL> exec :xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_dfe03a6e36a44c1d',plan_name=>'SQL_PLAN_dzs1udsva8m0x2f8e26bd');
PL/SQL procedure successfully completed.如果性能问题已经发生，并且没有提前做准备，那么可以考虑从AWR报告来获得好的执行计划。
- 首先需要知道发生性能问题的sql语句，好的时候处于哪个AWR的snapshot的时间段。
代码语言：javascript复制 SQL> select * from ( select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1 desc ) where rownum < 10000;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------------- ------------------------------------------
313 05-NOV-19 08.04.56.000 AM 05-NOV-19 08.08.56.521 AM
312 05-NOV-19 07.00.41.704 AM 05-NOV-19 08.00.48.319 AM
311 05-NOV-19 06.00.34.747 AM 05-NOV-19 07.00.41.704 AM
310 05-NOV-19 05.00.28.246 AM 05-NOV-19 06.00.34.747 AM
309 05-NOV-19 04.00.22.059 AM 05-NOV-19 05.00.28.246 AM
308 05-NOV-19 03.00.15.591 AM 05-NOV-19 04.00.22.059 AM
...- 我的sql语句好的时候是处于311 到 313 这个时间段之间。用sql_text定位语句的信息。
代码语言：javascript复制 select value(p) from table(dbms_sqltune.select_workload_repository(begin_snap => 311, end_snap => 313, basic_filter => 'sql_text like ''%LARGETABLE%''')) p;
VALUE(P)(SQL_ID, FORCE_MATCHING_SIGNATURE, SQL_TEXT, OBJECT_LIST, BIND_DATA, PARSING_SCHEMA_NAME, MO
----------------------------------------------------------------------------------------------------
SQLSET_ROW('45h1kwhrw14qw', 1.5550E 19, 'select OBJECT_NAME from LARGETABLE where OBJECT_ID > 10000'
, NULL, NULL, 'SCOTT', 'SQL*Plus', NULL, 636550, 86986, 14164, 2263, 0, 180909, 12062, 1, 1, 616, 'E
289FB891242B700DA0110006EF9C3E2CFFA331056414555519521105545551545545558591555449665851D5511058555555
155515122555415A0EA0C5551454265455454449081566E001696C66355451501025415504416FD557151551555551001550
A96295545D1C25444A101105559554049C0544D5555555554FA0705A42521740B50200000200000000001000010000000020
02080007D00000000F50000003200101000008083BF3E00000000190000807A002021740B504646262040262320030020003
020A0A05050A04001200000401F000000A5A0A0A040863E000040060C382000200000F0FF0F0020200304000400803E00000
071020000000200A0E031E047860C008000800C710200304010A800688909803E0000B044F6FF0F00F0FF0F000000010000'
, NULL, 3, NULL, NULL, NULL, NULL, 1895827650, SQL_PLAN_TABLE_TYPE(SQL_PLAN_ROW_TYPE(NULL, NULL, '05
-NOV-19', NULL, 'SELECT STATEMENT', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ALL_ROWS', 0, 0, NULL
, 0, 616, 616, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N
ULL, NULL), SQL_PLAN_ROW_TYPE(NULL, NULL, '05-NOV-19', NULL, 'TABLE ACCESS', 'FULL', NULL, 'SCOTT',
'LARGETABLE', 'LARGETABLE@SEL$1', NULL, 'TABLE', NULL, 0, 1, 0, 1, 1, 616, 180909, 2351817, NULL, NU
LL, NULL, NULL, NULL, 59533979, 615, NULL, NULL, NULL, NULL, 1, 'SEL$1', '<other_xml><info type="db_
version">12.1.0.2</info><info type="parse_schema"><![CDATA["SCOTT"]]></info><info type="plan_hash_fu
ll">3496966798</info><info type="plan_hash">1895827650</info><info type="plan_hash_2">3496966798</in
fo><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATUR
ES_ENABLE(''12.1.0.2'')]]></hint><hint><![CDATA[DB_VERSION(''12.1.0.2'')]]></hint><hint><![CDATA[ALL
_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "LARGETAB
LE"@"SEL$1")]]></hint></outline_data></other_xml>')), NULL, 2368393994)- 创建sqlset
代码语言：javascript复制 SQL> EXEC dbms_sqltune.create_sqlset('SQLSET_TEST01');- 将这个sql语句的信息加载到sqlset
代码语言：javascript复制 SQL> DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_workload_repository(begin_snap => 311, end_snap => 313, basic_filter => 'sql_id = ''45h1kwhrw14qw'' and plan_hash_value = 1895827650')) p;
dbms_sqltune.load_sqlset('SQLSET_TEST01', cur);
close cur;
END;
/- 使用spm固定
代码语言：javascript复制 SQL> declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset(sqlset_name => 'SQLSET_TEST01', sqlset_owner => 'SCOTT', fixed => 'YES', enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/- 固定后的状态
代码语言：javascript复制 SQL> select sql_handle, substr(sql_text,1, 100) text, created, enabled, accepted, fixed
from DBA_SQL_PLAN_BASELINES where sql_text like '%LARGETABLE%';
SQL_HANDLE TEXT CREATED ENA ACC FIX
------------------------- -------------------------------------------------- ------------------------- --- --- ---
SQL_27fe3f0dcd799a17 select OBJECT_NAME from LARGETABLE where OBJECT_ID 05-NOV-19 08.16.01.000000 YES YES YES
> 10000 AM
set autotrace on
select OBJECT_NAME from LARGETABLE where OBJECT_ID > 10000;
...
Note
-----
- SQL plan baseline "SQL_PLAN_2gzjz1r6rm6hrd06f7a8e" used for this statement <--baseline已应用2. Stored outlines
最为被SPM替换的功能，通常在一些旧版本数据库中被使用。
- 创建stored outline
代码语言：javascript复制 SQL> alter session set create_stored_outlines = stored_outline01;
Session altered.
- 执行sql语句
SQL> SELECT * FROM tbl_02 WHERE id > 0;
ID NAME
---------- ------------------------------
1 mary- stored outline收集完成之后结束。
代码语言：javascript复制 SQL> alter session set create_stored_outlines = false;
Session altered.-检查创建的outline信息。
代码语言：javascript复制 SQL> SELECT category,name,used,enabled,sql_text FROM user_outlines;
CATEGORY NAME USED ENABLED SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------ -------- --------------------------------------------------------------------------------
STORED_OUTLINE01 SYS_OUTLINE_21032404393344901 UNUSED ENABLED SELECT * FROM tbl_02 WHERE id > 0-使用该outline
代码语言：javascript复制 SQL> ALTER SESSION SET use_stored_outlines = STORED_OUTLINE01;
Session altered.-再次执行语句。
代码语言：javascript复制 SQL> set autotrace on
SQL> SELECT * FROM tbl_02 WHERE id > 0;
ID NAME
---------- ------------------------------
1 mary
Execution Plan
----------------------------------------------------------
Plan hash value: 1072116749
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TBL_02 | 4 | 120 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">0)
Note
-----
- Degree of Parallelism is 1 because of hint
- outline "SYS_OUTLINE_21032404425866103" used for this statement <-- outline已经被作用
Statistics
----------------------------------------------------------
69 recursive calls
3 db block gets
90 consistent gets
2 physical reads
672 redo size
607 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed如果需要删除的话可以执行
代码语言：javascript复制 exec DBMS_OUTLN.DROP_BY_CAT ('STORED_OUTLINE01');3. 使用Outline Hint来固定执行计划。
可以通过 DBMS_XPLAN.DISPLAY_CURSOR 或者 收集 10053 trace 来获得好的时候的 Outline 信息。
我们使用DBMS_XPLAN.DISPLAY_CURSOR来举例。
- 先执行该语句。
代码语言：javascript复制 SQL> select * from tbl_02 where id > 0;
ID NAME
---------- ------------------------------
1 mary- 收据该语句sql_id。
代码语言：javascript复制 SQL> select sql_text, sql_id, hash_value, old_hash_value from v$sql where sql_text like '%select * from tbl_02 where id > 0%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID HASH_VALUE OLD_HASH_VALUE
------------- ---------- --------------
SELECT * FROM tbl_02 WHERE id > 0
0j8n1sw7ycj0u 266748954 3809977548- 通过该语句的sql_id获得outline hint
代码语言：javascript复制 SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('0j8n1sw7ycj0u', format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0j8n1sw7ycj0u, child number 0
-------------------------------------
SELECT * FROM tbl_02 WHERE id > 0
Plan hash value: 1072116749
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| TBL_02 | 1 | 30 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TBL_02@SEL$1
Outline Data
-------------
/*
PLAN_TABLE_OUTPUT <--- outline hint
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cbqt_or_expansion' 'on')
OPT_PARAM('_fix_control' '7658097:1')
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TBL_02"@"SEL$1")
END_OUTLINE_DATA
...- 将outline hint作为hint加入sql语句中执行。
代码语言：javascript复制 select /* BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cbqt_or_expansion' 'on')
OPT_PARAM('_fix_control' '7658097:1')
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TBL_02"@"SEL$1")
END_OUTLINE_DATA */ * FROM tbl_02 WHERE id > 0;10053trace也可以使用同样的方法。
4. 统计信息的固定
我们知道统计信息是影响sql语句选择执行计划的非常重要的要素。所以固定统计信息也能起到固定执行计划的效果。
但是这种方法不是那么的牢靠，因为对象是表等object，可能会影响访问这个表的其他语句性能，应尽量避免使用。
- 锁特定object统计信息的方法
代码语言：javascript复制 EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('user name ','object name');- 解除的方法
代码语言：javascript复制 EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('user name','object name');
