Oracle数据库固定执行计划的方法总结

最新推荐文章于 2024-07-12 16:19:57 发布

流浪的金鱼

最新推荐文章于 2024-07-12 16:19:57 发布

阅读量2.6k

点赞数 3

文章标签： oracle 运维

本文链接：https://blog.csdn.net/rishairu1/article/details/115167228

版权

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 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
										  
- 先确认一下目前该语句的执行计划

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中。

SQL>  DECLARE
my_plans pls_integer;
 BEGIN
  my_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 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。

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 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自动捕获设为有效
　
　SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
　
　Session altered.

- 执行2次sql语句
　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.
　
- 检查执行计划是否被固定
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

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的时间段。

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定位语句的信息。

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
SQL> EXEC dbms_sqltune.create_sqlset('SQLSET_TEST01');

- 将这个sql语句的信息加载到sqlset
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固定
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;
  /
  
- 固定后的状态
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
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收集完成之后结束。
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

SQL> ALTER SESSION SET use_stored_outlines = STORED_OUTLINE01;

Session altered.

-再次执行语句。

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

如果需要删除的话可以执行
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 > 0
0j8n1sw7ycj0u  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 > 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语句中执行。

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统计信息的方法
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('user name ','object name');

- 解除的方法
EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('user name','object name');