tech_memo/ORACLE/SPM

今日の20件

counter: 252
today: 1
yesterday: 0
online: 2

tech_memo/ORACLE/SQL_PLAN

SPM (SQL Plan Management)

SPM (SQL Plan Management) †

概要 †

SQLの実行計画を記録して、それを評価した後に、本番環境に適用することができる機能
実行計画がかわった時に、パフォーマンスがよくなるかを確認して、よくなるものだけを適用することができるという機能です。
Insight Technology, Inc.
- https://www.insight-tec.com/mailmagazine/ora3/vol372.html

使い方 †

実行計画自動取得モードをオン

SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
 
System altered.
 
SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE      VALUE
------------------------------------ --------- --------
optimizer_capture_sql_plan_baselines boolean   TRUE

上記以降に実行されたSQL文の実行計画は自動的にSQL Management Baseに格納される。(2回以上実行して、初めて格納される)

SQL Management Baseに格納された実行計画を使用する為には、optimizer_use_sql_plan_baselinesがTRUEである必要がある。デフォルトでTRUE。

SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES
 
NAME                                 TYPE      VALUE
------------------------------------ --------- --------
optimizer_use_sql_plan_baselines     boolean   TRUE

SQL Management Baseの確認方法

SQL> set autotrace off
SQL> SELECT sql_text,sql_handle
  2    FROM dba_sql_plan_baselines
  3   WHERE plan_name = 'SYS_SQL_PLAN_a52a4eafeb1890ae';

SQL_TEXT                                 SQL_HANDLE
---------------------------------------- -------------------------
select * from test_spm where seq_no = 1  SYS_SQL_5c11383aa52a4eaf

INDEX追加＋統計情報再取得

SQL> create index idx_test_spm on test_spm(seq_no);
 
Index created.
 
SQL> BEGIN
  2    DBMS_STATS.GATHER_INDEX_STATS(
  3       ownname      => 'TEST'
  4      ,indname      => 'IDX_TEST_SPM'
  5    );
  6  END;
  7  /
 
PL/SQL procedure successfully completed.

SPMを使っていると、統計情報を再取得しても、承認しないと、実行計画変わらない。

SQL> set autotrace off
SQL> select * from table(
  2      dbms_xplan.display_sql_plan_baseline(
  3          sql_handle=>'SYS_SQL_5c11383aa52a4eaf',
  4          format=>'basic'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
 
---------------------------------------------------------------------
SQL handle: SYS_SQL_5c11383aa52a4eaf
SQL text: select * from test_spm where seq_no = 1
---------------------------------------------------------------------
 
---------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a52a4eaf739aca96
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
---------------------------------------------------------------------
Plan hash value: 2439131642
 
----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_SPM     |
|   2 |   INDEX RANGE SCAN          | IDX_TEST_SPM |
----------------------------------------------------
 
---------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a52a4eafeb1890ae
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
---------------------------------------------------------------------
Plan hash value: 1145642998
 
--------------------------------------
| Id  | Operation         | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |          |
|   1 |  TABLE ACCESS FULL| TEST_SPM |
--------------------------------------
 
34 rows selected.

インデックススキャンのAcceptedがNOになっており、このインデックススキャンの実行計画がまだ未承認

実行計画の承認

SQL> DECLARE
  2      report clob;
  3  BEGIN
  4      report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
  5                    sql_handle => 'SYS_SQL_5c11383aa52a4eaf');
  6      DBMS_OUTPUT.PUT_LINE(report);
  7  END;
  8  /
 
 
----------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
----------------------------------------------------------------------
 
Inputs:
-------
  SQL_HANDLE = SYS_SQL_5c11383aa52a4eaf
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES
 
Plan: SYS_SQL_PLAN_a52a4eaf739aca96
-----------------------------------
  Plan was verified: Time used .04 seconds.
  Passed performance criterion: Compound improvement ratio >= 7.67.
  Plan was changed to an accepted plan.
 
                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):               0              0
  CPU Time(ms):                   0              0
  Buffer Gets:                   23              3              7.67
  Disk Reads:                     0              0
  Direct Writes:                  0              0
  Fetches:                        0              0
  Executions:                     1              1
 
----------------------------------------------------------------------
                                 Report Summary
----------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
 
 
 
PL/SQL procedure successfully completed.

以降、古い実行計画と、新しい実行計画のうち、コストが低いとオプティマイザに判断されたほうが利用される。

実行計画固定方法 †

参考 †

http://d.hatena.ne.jp/t-horikawa/20171027/1509073196

手順概要 †

遅いSQLのSQL_HANDLEに対してINDEXヒント等、改善したSQLのベースラインを作成し、遅いSQLのベースラインを削除する流れ

SQL計画の自動取得は行わず、使用のみを許可する設定

SQL計画の自動取得：OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
取得したSQL計画の使用：OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

手順詳細 †

V$SESSIONなどから、対象SQLのSQL_IDを取得
V$SQLから、上記SQL_IDをキーにして、「PLANL_HASH_VALUE」を取得

対象SQLの現状の実行計画をSPMへロードしベースライン作成

var res number
exec :res :=dbms_spm.load_plans_from_cursor_cache( sql_id => '問題SQL_ID', plan_hash_value => '問題PLAN_HASH_VALUE');

ロードしたSQLのSQL_HANDLE、PLAN_NAMEをベースラインから取得

select sql_handle,plan_name, sql_text,origin, enabled, accepted, fixed,
to_char(created,'YY/MM/DD HH24:MI:SS') created,
to_char(last_modified,'YY/MM/DD HH24:MI:SS') modified,
to_char(last_executed,'YY/MM/DD HH24:MI:SS') executed,
to_char(last_verified,'YY/MM/DD HH24:MI:SS') verified,
optimizer_cost, executions, elapsed_time, cpu_time,
buffer_gets, disk_reads, rows_processed, fetches
from dba_sql_plan_baselines
order by created;

より詳細をチェックする場合は、上記で取得したSQL_HANDLEをキーに以下を実行

select * from table(
dbms_xplan.display_sql_plan_baseline(
 sql_handle=>'SQL_c7fb9afce03237e6',
 format=>'basic'));

ヒントなどで作成した理想の実行計画のSQL_IDとPLANL_HASH_VALUEを取得

上記理想の実行計画を、SPMへロードしベースライン作成

同一SQL_HANDLEに対して2種のベースラインが存在する状態となる

var res number
exec :res := dbms_spm.load_plans_from_cursor_cache( sql_id => 'ヒント付きSQL_ID',plan_hash_value => 'ヒント付きPLAN_HASH_VALUE', sql_handle => 'SQL_HANDLE');

先述と同じ方法で、ロードしたSQLのSQL_HANDLE、PLAN_NAMEをベースラインから取得し、2種のベースラインが作成されたことを確認

遅いほうのベースライン（実行計画）を削除

var res number
exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_HANDLE','問題PLAN_NAME');

理想ペースラインを固定化（Fixed化）（※自動取得等による不要なベースラインORIGIN列「AUTO-CAPTURE」が増えることを抑止する）

var res number
execute :res := dbms_spm.alter_sql_plan_baseline(-
sql_handle =>'SQL_HANDLE',-
plan_name =>'固定するPLAN_NAME',-
attribute_name => 'FIXED',-
attribute_value => 'YES');

FIXED属性について

★FIXED属性=YES/NO における動作の違い
FIXED属性=YES →　SQL計画ベースラインの実行計画を固定する

FIXED属性=YES/NO で以下のような動作の違いがある
・FIXED属性=NO
 SQL計画ベースラインを使用した際に、すでに登録されている SQL計画ベースラインよりも
 コストが低い実行計画がある場合、その実行計画を新たな SQL計画ベースラインとして登録

・FIXED属性=YES
 新たなSQL計画ベースラインを登録しない

・1つのSQLに対してSQLFIXED属性=YES と NO の複数の SQL計画ベースラインが存在する場合、
FIXED属性=NO の SQL計画ベースラインの方がコストが低い場合であっても、FIXED属性=YESのSQL計画ベースラインの使用を優先する。

今日の20件

counter: 252
today: 1
yesterday: 0
online: 2

tech_memo / ORACLE / SPM

最新の20件

今日の20件

SPM (SQL Plan Management) †

概要 †

使い方 †

実行計画固定方法 †

参考 †

手順概要 †

手順詳細 †

最新の20件

今日の20件