ITコンサルの日常

ITコンサル会社に勤務する普通のITエンジニアの日常です。

SQL計画管理

Oracle

一番とっつき易そうなところからやってみる。


SQL計画管理とは

SQL計画の管理を使用すると、SQL計画の情報を取得、選択および改良するためのコンポーネントが用意されているため、SQL文の実行計画に対する突然の変更によるパフォーマンスの低下を回避できます。
15 SQL計画の管理の使用方法

シナリオ

  • フルスキャンの計画履歴が作成される
  • インデックスを作成
  • インデックススキャンの計画履歴が作成される(最初は未受入)
    • フルスキャンの計画履歴が使われる
  • 計画履歴をEVOLVE(受入)
    • インデックススキャンの計画履歴が使われるようになる
  • インデックススキャンの計画履歴を無効化
    • フルスキャンの計画履歴が使われる

実験対象のテーブルとデータ

SQL> desc person
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(20)

SQL> select * from person
  2  ;

        ID NAME
---------- --------------------
         1 hoge
         2 moge
         3 toge

SQL>

SQL計画管理の使用が有効、SQL計画履歴の取得が有効

SQL> select name, value from v$parameter
  2  where name IN ('optimizer_use_sql_plan_baselines', 'optimizer_capture_sql_p
lan_baselines');

NAME
--------------------------------------------------------------------------------

VALUE
--------------------------------------------------------------------------------

optimizer_capture_sql_plan_baselines
TRUE

optimizer_use_sql_plan_baselines
TRUE


SQL>
初期化パラメータ名 説明
optimizer_use_sql_plan_baselines OPTIMIZER_USE_SQL_PLAN_BASELINESでは、SQL Management Baseに保存されているSQLプラン・ベースラインを使用可能または使用禁止にします。使用可能な場合は、オプティマイザはコンパイル対象のSQL文の SQLプラン・ベースラインを検索します。SQLプラン・ベースラインがSQL Management Baseにある場合、オプティマイザは各ベースラインのプランを見積もり、コストが最も低いプランを取り出します。
optimizer_capture_sql_plan_baselines OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESでは、反復可能なSQL文の自動認識、およびそのような文のSQLプラン・ベースラインの生成を使用可能または使用禁止にします。

SQL計画履歴が無い状態

SQL> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         0

SQL>

最初はSQL計画履歴が空の状態である。

SQLを実行(1回目)

SQL> select /*+ AAA */ * from person where id = 1;

        ID NAME
---------- --------------------
         1 hoge

SQLが1回実行されただけだと、SQL計画履歴は入らない。

SQL> select sql_text, sql_handle, plan_name, origin, enabled, accepted, fixed fr
om dba_sql_plan_baselines where sql_text LIKE '%AAA%';

レコードが選択されませんでした。

SQL>

SQLを実行(2回目)

SQL> select /*+ AAA */ * from person where id = 1;

        ID NAME
---------- --------------------
         1 hoge

SQLが2回目実行されると、SQL計画履歴(plan_name=SYS_SQL_PLAN_d4bda9c08f0075a4)に入る。

SQL> select sql_text, sql_handle, plan_name, origin, enabled, accepted, fixed fr
om dba_sql_plan_baselines where sql_text LIKE '%AAA%';

SQL_TEXT
--------------------------------------------------------------------------------

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA

------------------------------ ------------------------------ -------------- ---

ACC FIX
--- ---
select /*+ AAA */ * from person where id = 1
SYS_SQL_39d8a16ad4bda9c0       SYS_SQL_PLAN_d4bda9c08f0075a4  AUTO-CAPTURE   YES

YES NO


SQL>

enabled=yes、かつ、accepted=yesなので、以降この計画が使われる。

インデックスを作成する

SQL> create index idx_person on person (id);

索引が作成されました。

SQL>

ここで同じSQLを実行してても、実行計画が変わるように、おもむろにインデックスを張ってみる。

SQLを実行(3回目)

SQL> select /*+ AAA */ * from person where id = 1;

        ID NAME
---------- --------------------
         1 hoge

新しい実行計画となったので、計画履歴(plan_name=SYS_SQL_PLAN_d4bda9c0f97a4e5d)が作成される。
(この場合は2回実行しなくてもよいらしい。SQL_HANDLEとしては既に登録されているから？)
ただし、後から作られた方はaccepted=NOになっているので、使われない。

SQL> select sql_text, sql_handle, plan_name, origin, enabled, accepted, fixed fr
om dba_sql_plan_baselines where sql_text LIKE '% AAA %';

SQL_TEXT
--------------------------------------------------------------------------------

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA

------------------------------ ------------------------------ -------------- ---

ACC FIX
--- ---
select /*+ AAA */ * from person where id = 1
SYS_SQL_39d8a16ad4bda9c0       SYS_SQL_PLAN_d4bda9c08f0075a4  AUTO-CAPTURE   YES

YES NO

select /*+ AAA */ * from person where id = 1
SYS_SQL_39d8a16ad4bda9c0       SYS_SQL_PLAN_d4bda9c0f97a4e5d  AUTO-CAPTURE   YES

NO  NO

SQL_TEXT
--------------------------------------------------------------------------------

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA

------------------------------ ------------------------------ -------------- ---

ACC FIX
--- ---


SQL>

計画を表示してみる（最初に作られた方）

SQL> select * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_39d8a16ad
4bda9c0', 'SYS_SQL_PLAN_d4bda9c08f0075a4'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------

SQL handle: SYS_SQL_39d8a16ad4bda9c0
SQL text: select /*+ AAA */ * from person where id = 1
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------

Plan name: SYS_SQL_PLAN_d4bda9c08f0075a4
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1493655343

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     4 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PERSON |     4 |   100 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - filter("ID"=1)

24行が選択されました。

SQL>

まだインデックスが無かったころの実行計画なので、フルスキャンになってます。

計画を表示してみる（次に作られた方）

SQL> select * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_39d8a16ad
4bda9c0', 'SYS_SQL_PLAN_d4bda9c0f97a4e5d'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------

SQL handle: SYS_SQL_39d8a16ad4bda9c0
SQL text: select /*+ AAA */ * from person where id = 1
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------

Plan name: SYS_SQL_PLAN_d4bda9c0f97a4e5d
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1414301407

--------------------------------------------------------------------------------

----------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------

----------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |     4 |   100 |     2   (0)|
00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON     |     4 |   100 |     2   (0)|
00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_PERSON |     2 |       |     1   (0)|
00:00:01 |

--------------------------------------------------------------------------------

----------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------



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

   2 - access("ID"=1)

25行が選択されました。

SQL>

インデックスが作られた後なので、インデックスが使われて、インデックススキャンになってます。

ちなみに今使われている計画は

SQL> set autotrace on explain
SQL> select /*+ AAA */ * from person where id = 1;

        ID NAME
---------- --------------------
         1 hoge


実行計画
----------------------------------------------------------
Plan hash value: 1493655343

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     4 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PERSON |     4 |   100 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("ID"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_d4bda9c08f0075a4" used for this statement

SQL>

インデックススキャンの方の計画履歴はaccepted=NOなので、まだ使われません。

計画履歴をEVOLVEする

SQL> exec MY_EVOLVE_SQL_PLAN_BASELINE('SYS_SQL_39d8a16ad4bda9c0');

PL/SQLプロシージャが正常に完了しました。

SQL>
MY_EVOLVE_SQL_PLAN_BASELINEの定義(SQL*PlusからFunction実行するのめんどいので、ラッパ作った)
CREATE OR REPLACE PROCEDURE MY_EVOLVE_SQL_PLAN_BASELINE(in_sql_handle VARCHAR2)
AUTHID CURRENT_USER
IS
  report CLOB;
BEGIN
  report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => in_sql_handle);
END;
/

計画履歴を表示する

SQL> select sql_text, sql_handle, plan_name, origin, enabled, accepted, fixed fr
om dba_sql_plan_baselines where sql_handle = 'SYS_SQL_39d8a16ad4bda9c0';

SQL_TEXT
--------------------------------------------------------------------------------

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA

------------------------------ ------------------------------ -------------- ---

ACC FIX
--- ---
select /*+ AAA */ * from person where id = 1
SYS_SQL_39d8a16ad4bda9c0       SYS_SQL_PLAN_d4bda9c08f0075a4  AUTO-CAPTURE   YES

YES NO

select /*+ AAA */ * from person where id = 1
SYS_SQL_39d8a16ad4bda9c0       SYS_SQL_PLAN_d4bda9c0f97a4e5d  AUTO-CAPTURE   YES

YES NO

SQL_TEXT
--------------------------------------------------------------------------------

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA

------------------------------ ------------------------------ -------------- ---

ACC FIX
--- ---


SQL>

インデックススキャンの方の計画履歴(plan_name=SYS_SQL_PLAN_d4bda9c0f97a4e5d)のacceptedがYESになりました。
これで、こちらの計画が使われるはずです。

ちなみに今使われている計画は

SQL> set autotrace on explain
SQL> select /*+ AAA */ * from person where id = 1;

        ID NAME
---------- --------------------
         1 hoge


実行計画
----------------------------------------------------------
Plan hash value: 1414301407

--------------------------------------------------------------------------------

----------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------

----------

|   0 | SELECT STATEMENT            |            |     1 |    25 |     2   (0)|
00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON     |     1 |    25 |     2   (0)|
00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_PERSON |     1 |       |     1   (0)|
00:00:01 |

--------------------------------------------------------------------------------

----------


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

   2 - access("ID"=1)

Note
-----
   - dynamic sampling used for this statement
   - SQL plan baseline "SYS_SQL_PLAN_d4bda9c0f97a4e5d" used for this statement

SQL>

想定通り、インデックススキャンになりました。

EVOLVEした計画履歴をDISABLEにしてみる

SQL> exec my_alter_sql_plan_baseline('SYS_SQL_39d8a16ad4bda9c0', 'SYS_SQL_PLAN_d
4bda9c0f97a4e5d', 'enabled', 'no');

PL/SQLプロシージャが正常に完了しました。

SQL>
MY_ALTER_SQL_PLAN_BASELINEの定義(SQL*Plusから以下略)
CREATE OR REPLACE PROCEDURE MY_ALTER_SQL_PLAN_BASELINE(in_sql_handle VARCHAR2, in_plan_name VARCHAR2, in_attribute_name VARCHAR2, in_attribute_value VARCHAR2)
AUTHID CURRENT_USER
IS
  rtn PLS_INTEGER;
BEGIN
  rtn := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => in_sql_handle
    , plan_name => in_plan_name
    , attribute_name => in_attribute_name
    , attribute_value => in_attribute_value);
END;
/

計画履歴を表示する

SQL> select sql_text, sql_handle, plan_name, origin, enabled, accepted, fixed fr
om dba_sql_plan_baselines where sql_handle = 'SYS_SQL_39d8a16ad4bda9c0';

SQL_TEXT
--------------------------------------------------------------------------------

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA

------------------------------ ------------------------------ -------------- ---

ACC FIX
--- ---
select /*+ AAA */ * from person where id = 1
SYS_SQL_39d8a16ad4bda9c0       SYS_SQL_PLAN_d4bda9c08f0075a4  AUTO-CAPTURE   YES

YES NO

select /*+ AAA */ * from person where id = 1
SYS_SQL_39d8a16ad4bda9c0       SYS_SQL_PLAN_d4bda9c0f97a4e5d  AUTO-CAPTURE   NO
YES NO

SQL_TEXT
--------------------------------------------------------------------------------

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA

------------------------------ ------------------------------ -------------- ---

ACC FIX
--- ---


SQL>

インデックススキャンの方の計画履歴(plan_name=SYS_SQL_PLAN_d4bda9c0f97a4e5d)のenabledがNOになりました。
これで、こちらの計画は使われないはずです。

ちなみに今使われている計画は

SQL> set autotrace on explain
SQL> select /*+ AAA */ * from person where id = 1;

        ID NAME
---------- --------------------
         1 hoge


実行計画
----------------------------------------------------------
Plan hash value: 1493655343

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     4 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PERSON |     4 |   100 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("ID"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_d4bda9c08f0075a4" used for this statement

SQL>

想定通り、フルスキャンになりました。

つまり

  • SQL実行計画が変わっても、履歴から元に戻せるから安心
  • インデックス追加したのに、有効にならないとかトラブルありそう

みたいなことでしょうか。