供收藏：Oracle固定SQL执行计划的方法总结
编者按：
本文作者系流浪的金鱼（花名），甲骨文数据库工程师。个人主页：https://blog.csdn.net/rishairu1，经其本人授权发布。
【免责声明】本公众号文章仅代表个人观点，与任何公司无关。
Oracle数据库中执行sql的时候，优化器会根据优化器统计信息和一些参数来生成“它认为最好的“执行计划。
但是并不能够保证每一次都会选择最优的哪个执行计划。如果遇到了sql语句在一定时间段执行时性能变差，
并且发现它的执行计划发生了变化，那么可以考虑固定哪个”好的”时候的执行计划。
当然，必须保证sql语句没有被修改，是同一个sql。
1. SQL Plan Management （SQL计划管理，简称SPM）
这是Oracle推荐使用的一种方法。使用起来也很简单。以下是一个手动固定的例子。
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');
以如下语句为例：
- 先执行该语句。
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 。
SQL> col sql_text for a30SQL> 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_02b where a.id = b.id
- 先确认一下目前该语句的执行计划
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.idPlan 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 threshold25 rows selected.
- 将该语句的cursor信息载入到Baseline中。
SQL> DECLAREmy_plans pls_integer;BEGINmy_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => 'bgrjka5pt2zh6', plan_hash_value => '2482469159');END;/
- 确认载入之后的状态。
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，说明目前改执行计划没有被固定。
- 执行如下将固定执行计划
SQL> var res numberSQL> 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。
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
这个时候我们再执行同样的语句，发现执行计划被固定，并被使用了。
SQL> set autotrace onSQL> select * from tbl_01 a, tbl_02 b where a.id = b.id;ID NAME ID NAME---------- ------------------------------ ---------- ------------------------------1 jack 1 maryExecution 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 calls15 db block gets54 consistent gets0 physical reads3580 redo size745 bytes sent via SQL*Net to client607 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
也可以使用如下的方法。
- 将baseline自动捕获设为有效
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;Session altered.
- 执行2次sql语句
SELECT * FROM tbl_01 WHERE id > 0;ID NAME------------------------------1 jack
- 检查baseline的状态
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自动捕获变回无效
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = false;Session altered.
- 将使用baseline的参数设为有效
SQL> ALTER SESSION SET optimizer_use_sql_plan_baselines = TRUE;Session altered.
- 检查执行计划是否被固定
set autotrace onSELECT * FROM tbl_01 WHERE id > 0;ID NAME------------------------------1 jackExecution 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 thresholdSQL plan baseline "SQL_PLAN_dzs1udsva8m0x2f8e26bd" used for this statement <--baseline已应用Statistics----------------------------------------------------------6 recursive calls0 db block gets10 consistent gets0 physical reads0 redo size607 bytes sent via SQL*Net to client607 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
之后如果不需要了可以删除该baseline
SQL> var xx numberSQL> 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的时间段。
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 AM312 05-NOV-19 07.00.41.704 AM 05-NOV-19 08.00.48.319 AM311 05-NOV-19 06.00.34.747 AM 05-NOV-19 07.00.41.704 AM310 05-NOV-19 05.00.28.246 AM 05-NOV-19 06.00.34.747 AM309 05-NOV-19 04.00.22.059 AM 05-NOV-19 05.00.28.246 AM308 05-NOV-19 03.00.15.591 AM 05-NOV-19 04.00.22.059 AM...
- 我的sql语句好的时候是处于311 到 313 这个时间段之间。用sql_text定位语句的信息。
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, 'E289FB891242B700DA0110006EF9C3E2CFFA331056414555519521105545551545545558591555449665851D5511058555555155515122555415A0EA0C5551454265455454449081566E001696C66355451501025415504416FD557151551555551001550A96295545D1C25444A101105559554049C0544D5555555554FA0705A42521740B5020000020000000000100001000000002002080007D00000000F50000003200101000008083BF3E00000000190000807A002021740B504646262040262320030020003020A0A05050A04001200000401F000000A5A0A0A040863E000040060C382000200000F0FF0F0020200304000400803E00000071020000000200A0E031E047860C008000800C710200304010A800688909803E0000B044F6FF0F00F0FF0F000000010000', 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, NULL, 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, NULL, 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_full">3496966798</info><info type="plan_hash">1895827650</info><info type="plan_hash_2">3496966798</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_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" "LARGETABLE"@"SEL$1")]]></hint></outline_data></other_xml>')), NULL, 2368393994)
- 创建sqlset
SQL> EXEC dbms_sqltune.create_sqlset('SQLSET_TEST01'); - 将这个sql语句的信息加载到sqlset
SQL> DECLAREcur sys_refcursor;BEGINopen cur forselect 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固定
SQL> declaremy_integer pls_integer;beginmy_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;/
- 固定后的状态
SQL> select sql_handle, substr(sql_text,1, 100) text, created, enabled, accepted, fixedfrom 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 AMset autotrace onselect 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
alter session set create_stored_outlines = stored_outline01;Session altered.执行sql语句SELECT * FROM tbl_02 WHERE id > 0;ID NAME------------------------------1 mary
- stored outline收集完成之后结束。
SQL> alter session set create_stored_outlines = false;Session altered.
-检查创建的outline信息。
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
ALTER SESSION SET use_stored_outlines = STORED_OUTLINE01;Session altered.
-再次执行语句。
set autotrace onSELECT * FROM tbl_02 WHERE id > 0;ID NAME------------------------------1 maryExecution 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 hintoutline "SYS_OUTLINE_21032404425866103" used for this statement <-- outline已经被作用Statistics----------------------------------------------------------69 recursive calls3 db block gets90 consistent gets2 physical reads672 redo size607 bytes sent via SQL*Net to client607 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client3 sorts (memory)0 sorts (disk)1 rows processed
如果需要删除的话可以执行
exec DBMS_OUTLN.DROP_BY_CAT ('STORED_OUTLINE01');3. 使用Outline Hint来固定执行计划。
可以通过 DBMS_XPLAN.DISPLAY_CURSOR 或者 收集 10053 trace 来获得好的时候的 Outline 信息。
我们使用DBMS_XPLAN.DISPLAY_CURSOR来举例。
- 先执行该语句。
SQL> select * from tbl_02 where id > 0;ID NAME---------- ------------------------------1 mary
- 收据该语句sql_id。
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 > 00j8n1sw7ycj0u 266748954 3809977548
- 通过该语句的sql_id获得outline hint
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 > 0Plan 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$1Outline Data-------------/*+PLAN_TABLE_OUTPUT <--- outline hint--------------------------------------------------------------------------------BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_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_ROWSNO_PARALLELOUTLINE_LEAF(@"SEL$1")FULL(@"SEL$1" "TBL_02"@"SEL$1")END_OUTLINE_DATA...
- 将outline hint作为hint加入sql语句中执行。
select /*+ BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_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_ROWSNO_PARALLELOUTLINE_LEAF(@"SEL$1")FULL(@"SEL$1" "TBL_02"@"SEL$1")END_OUTLINE_DATA */ * FROM tbl_02 WHERE id > 0;
10053trace也可以使用同样的方法。
4. 统计信息的固定
我们知道统计信息是影响sql语句选择执行计划的非常重要的要素。所以固定统计信息也能起到固定执行计划的效果。
但是这种方法不是那么的牢靠，因为对象是表等object，可能会影响访问这个表的其他语句性能，应尽量避免使用。
- 锁特定object统计信息的方法
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('user name ','object name');
- 解除的方法
EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('user name','object name');后续文章更加精彩，欢迎关注本公众号或访问【阅读原文】。
——End——
专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注！
优化器相关功能介绍（了解CBO）：
基数反馈 （Cardinality Feedback）（一）
基数反馈 （Cardinality Feedback）（二）
自适应游标共享(Adaptive Cursor Sharing)
自适应游标共享(Adaptive Cursor Sharing)（二）
Oracle优化器之自动重新优化（Automatic Reoptimization）功能
Oracle优化器之自适应执行计划(Adaptive Execution Plans)
Oracle优化器之动态统计（Dynamic Statistics）
【SQL】历史SQL监控(Historical SQL Monitoring ) 功能（12c）
手把手系列（帮助个人技术成长）：
Oracle SQL 性能调优：使用SqlPatch固定执行计划
原文作者：SQL和数据库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
