SQL計画ベースラインを使用して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にロードします

次のクエリを実行して、この適切なクエリ実行プランをカーソルキャッシュからベースラインとして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> 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.

次に、ターゲットインスタンスで次のコマンドを実行して、ステージングテーブルのexportbackupをターゲットインスタンスのホストに転送し、テーブルをターゲットインスタンスにインポートします。

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：ベースラインを確認する

ターゲットインスタンスで次のコマンドを実行して、ベースラインが受け入れられ、修正されていることを確認します。

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>

上記の出力は、ベースラインがターゲットインスタンスにインポートされたが、修正されていないことを示しています。次のクエリを実行してベースラインを修正し、オプティマイザがこのプランのみを選択できるようにします。

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自動SQLプランベースライン（ドキュメントID 1930525.1）