Oracle SQL計画ベースラインを使ってSQL実行計画を別インスタンスへ移行する方法

特定のSQLクエリが、あるデータベース(本番環境など)ではパフォーマンスが低下するのに、別のデータベース(開発環境など)では問題なく動作することがあります。これは、同じクエリでも各インスタンスで異なる実行計画が採用されている場合に起こり得ます。本記事では、Oracle® Database®が11gで初めて導入したSQL計画ベースライン(SQL Plan Baseline)機能を利用して、クエリが正常に動作しているインスタンスから、パフォーマンスが劣化しているインスタンスへ実行計画を転送する手順を解説します。

SQL計画管理(SPM)の概要

Oracle SQL Plan Management(SPM)は、Oracle Databaseに備わる機能で、クエリの過去の実行計画をすべて記録・保持します。これにより、SPMに保存された実行計画の中から良好なプランを選んでベースラインを作成し、それを有効化することで、システムが必ずベースライン内の良好なプランのみを採用するように制御できます。

この機能を活用するには、まずあるインスタンスでは良好なパフォーマンスを示し、別のインスタンスでは劣悪なパフォーマンスを示すクエリのsql_idを特定する必要があります。あわせて、正常に動作している側のインスタンスで、そのクエリの優れた実行計画IDであるplan_hash_valueも取得しておきましょう。

SQLベースプランをインスタンス間でコピーする手順

以下の手順に従って、ソースインスタンスからターゲットインスタンスへSQLベースプランをコピーします。

1. クエリが正常に動作しているソースインスタンス上でクエリを実行し、カーソルキャッシュにクエリを存在させます。
2. ソースインスタンス上で、カーソルキャッシュからSQL実行計画をSPMへベースラインとしてロードします。
3. ソースインスタンス上にステージング表を作成します。この表は、実行計画をソースインスタンスからターゲットインスタンスへ移行するために使用します。
4. ソースインスタンスのステージング表に、ソース側の実行計画(ベースライン)をパックします。
5. エクスポート/インポートユーティリティを使用して、ステージング表をソースインスタンスからターゲットインスタンスへ転送します。
6. ターゲットインスタンス上で、ステージング表からSQL計画をSPMへアンパックします。
7. ターゲットインスタンスに作成されたベースラインが「fixed(固定)」かつ「accepted(受け入れ済み)」になっていることを確認します。これにより、次回実行時にその計画が選択されるようになります。
8. ターゲットインスタンスでパフォーマンス問題が発生していたSQLを実行し、転送したベースラインが採用されていることを確認します。

実行例

上記の手順を実際に実行すると、以下のような出力結果が得られます。

ステップ1: ソースインスタンスでクエリを実行する

ソースインスタンス上でSQLを実行し、sql_idとplan_hash_valueを特定します。カーソルキャッシュを確認して値を取得してください。この例では、以下の値になります。

• sql_id: 9xva48wpnsmp6
• plan_hash_value: 1572948408

ソースインスタンスで以下のクエリを実行します。

SQL> select distinct plan_hash_value from v$sql where sql_id='9xva48wpnsmp6';

PLAN_HASH_VALUE
---------------
1572948408

ステップ2: 計画をSPMへロードする

以下のPL/SQLブロックを実行して、カーソルキャッシュ内の優れた実行計画をSPMへベースラインとしてロードします。

SQL> set serveroutput on
SQL> declare
2   ret binary_integer;
     l_sql_id varchar2(13);
3
4   l_plan_hash_value number;
5   l_fixed varchar2(3);
6   l_enabled varchar2(3);
7   Begin
8   l_sql_id := '&&sql_id';
9   l_plan_hash_value := to_number('&&plan_hash_value');
10   l_fixed := 'Yes';
11   l_enabled := 'Yes';
12   ret := dbms_spm.load_plans_from_cursor_cache(
13       sql_id=>l_sql_id,
14       plan_hash_value=>l_plan_hash_value,
15       fixed=>l_fixed,
16       enabled=>l_enabled);
17   end;
18  /

Enter value for sql_id: 9xva48wpnsmp6
old   8:  l_sql_id := '&&sql_id';
new   8:  l_sql_id := '9xva48wpnsmp6';

Enter value for plan_hash_value: 1572948408
old   9:  l_plan_hash_value := to_number('&&plan_hash_value');
new   9:  l_plan_hash_value := to_number('1572948408');

PL/SQL procedure successfully completed.

続いて以下の問い合わせを実行し、ソースインスタンス上にSQLベースラインが作成されたことを確認します。SQL_HANDLEとPLAN_NAMEは後続の手順で使用するため、控えておいてください。

SQL> select count(*) from dba_sql_plan_baselines ;

COUNT(*)
--------
  1

SQL> select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_d344aac395f978a4           SQL_PLAN_d6j5asfazky54868c96c3

ステップ3: ソースインスタンスにステージング表を作成する

以下のコマンドを実行して、ソースインスタンス上にステージング表を作成します。

SQL> sho user
USER is "SYS"
SQL> BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
  table_name      => 'SPM_STAGETAB',
  table_owner     => 'APPS',
  tablespace_name => 'SYSAUX');
END;

2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

ステップ4: ベースラインをパックする

以下のコマンドを実行して、ソースインスタンスのステージング表にベースラインをパックします。

SQL> DECLARE
2      my_plans number;
3      BEGIN
4        my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
         table_name => 'SPM_STAGETAB',
         enabled => 'yes',
5
6
7        table_owner => 'APPS',
8        plan_name => 'SQL_PLAN_d6j5asfazky54868c96c3',
9      sql_handle => 'SQL_d344aac395f978a4');
10   END;
11  /

PL/SQL procedure successfully completed.

ステップ5: ステージング表をソースからターゲットインスタンスへ転送する

以下のコマンドを実行して、ソースインスタンス上のステージング表のエクスポートバックアップを取得します。

exp file=SPM_STAGETAB.dmp tables=APPS.SPM_STAGETAB log=SPM_STAGETAB.log compress=n
Export: Release 11.2.0.4.0 - Production on Sun Jun 3 13:14:50 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system/*******

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to APPS
. . exporting table             SPM_STAGETAB	         1 rows exported
Export terminated successfully without warnings.

次に、ステージング表のエクスポートファイルをターゲットインスタンスのホストへ転送し、ターゲットインスタンス上で以下のコマンドを実行して表をインポートします。

imp system file=SPM_STAGETAB.dmp log=imp_SPM_STAGETAB.log fromuser=apps touser=apps

Import: Release 11.2.0.4.0 - Production on Sun Jun 3 14:16:25 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing APPS's objects into APPS
. . importing table           "SPM_STAGETAB"   	       1 rows imported
Import terminated successfully without warnings.

ステップ6: ベースラインをアンパックする

以下のコマンドを実行して、ステージング表からターゲットインスタンスのSPMへベースラインをアンパックします。この例では、アンパック前後で件数をカウントし、ベースラインがターゲット側へ正しく取り込まれたことを確認しています。

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
--------
  2

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2      l_plans_unpacked  PLS_INTEGER;
3         BEGIN
4         l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
5               table_name      => 'SPM_STAGETAB',
6               table_owner     => 'APPS');
7
8            DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
9      END;
10  /
Plans Unpacked: 1

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
--------
  3

ステップ7: ベースラインを検証する

ターゲットインスタンス上で以下のコマンドを実行し、ベースラインがaccepted(受け入れ済み)およびfixed(固定)の状態になっているかを確認します。

SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                      ENA ACC FIX ORIGIN
--------------------- ------------------------------ --- --- --- ------------
SQL_d344aac395f978a4  SQL_PLAN_d6j5asfazky54868c96c3 YES YES NO  MANUAL-LOAD

SQL>

上記の出力を見ると、ベースラインはターゲットインスタンスへ取り込まれていますが、まだfixedになっていません。そこで、以下のクエリを実行してベースラインを固定(fixed)に設定し、オプティマイザがこの計画だけを選択するようにします。

SQL> DECLARE
2    l_plans_altered  PLS_INTEGER;
3  BEGIN
4    l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5      sql_handle      => 'SQL_d344aac395f978a4',
6      PLAN_NAME       => 'SQL_PLAN_d6j5asfazky54868c96c3',
7      ATTRIBUTE_NAME  => 'fixed',
8      attribute_value => 'YES');
9
10    DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
11  END;
12  /

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM   dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                      ENA ACC FIX ORIGIN
--------------------- ------------------------------ --- --- --- ------------
SQL_d344aac395f978a4  SQL_PLAN_d6j5asfazky54868c96c3 YES YES YES MANUAL-LOAD

SQL>

ステップ8: ターゲットインスタンスでSQLクエリをテストする

ターゲットインスタンス上で以下のコマンドを実行し、新しいベースラインが採用されていることを確認します。

SQL> select SQL_PLAN_BASELINE from v$sql where sql_id='9xva48wpnsmp6';

SQL_PLAN_BASELINE
------------------------------
SQL_PLAN_d6j5asfazky54868c96c3

SQL計画が選択される仕組み

ベースライン計画が存在する場合にSQL計画がどのように選択されるかは、以下の図のようになります。

画像出典: Metalink Note Automatic SQL Plan Baselines (Doc ID 1930525.1)

まとめ

本記事の手順は、単一のクエリに対してベースラインを転送したい場合に利用できます。また、アップグレードやマイグレーションなどのシナリオでは、すべてのクエリに対してSQLベースラインをまとめて生成することも可能です。SQL計画ベースラインを活用すれば、一貫性のあるSQL実行計画を維持でき、パフォーマンス問題の発生を未然に防ぐことができます。

ご意見やご質問がある場合は、フィードバックタブからお知らせください。

当社のデータベースサービスやRackspace Applicationサービスについて、詳しくはぜひご覧ください。