キャッシュやAWRから任意の実行計画でSQLプロファイルを簡単に作成する方法

### はじめに

ここでは、SQLプロファイルをシェアードプールやAWRのヒストリーから手軽に作成する方法を説明します。これを利用すると、チューニングアドバイザを使わず自分でヒントを使ってチューニングした実行計画を元のSQLに割り当てることも簡単にできるようになります。SQLプロファイルの作成には、``DBMS_SQLTUNE.IMPORT_SQL_PROFILE``プロシジャを使用しますが、このプロシジャは見たところマニュアルには記載されていません。しかし、オラクルの提供する```SQLT```というチューニングツールで作成されるスクリプトで普通に利用されていることと、いくつかのMOSノートでも言及されてる(e.g. ID 1581549.1)ことから、使用すること自体に問題はないと思われます。

この手法は特に以下のいずれかの条件下で威力を発揮します。

- **ヒントを駆使してSQLクエリのチューニングに成功したが、プロダクションコードの変更ができないため実行計画だけ変更したい。**

- **実行計画が変わって性能が劣化した。キャッシュやAWRに残っている特定の実行計画に戻して固定化したい。**

### DBMS_SQLTUNE.IMPORT_SQL_PROFILE

まずはSQL作成するためのプロシジャ``DBMS_SQLTUNE.IMPORT_SQL_PROFILE``のインターフェイス見てみます。

必須なのは最初の２つ。SQLコードとSQLプロファイルの定義です。当然ですね。この２つはキャッシュやAWRから取得することができます。ちなみに、プロファイルの定義を``CLOB``型のXMLで渡せるようになったのは11gからで10gでは``VARCHAR2``型のコレクションでした。

```
PROCEDURE IMPORT_SQL_PROFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 PROFILE_XML                    CLOB                    IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT
 REPLACE                        BOOLEAN                 IN     DEFAULT
 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT
```


### 必須権限
前提としてSQLプロファイルを作成するユーザは、以下の権限が必要です。事前にGRANTしておくか、SYSDBAユーザを使用します。

| バージョン | 権限 |
|:-:|:-:|
| 12c/11g  |ADMINISTER SQL MANAGEMENT OBJECT    |
| 10g  |CREATE ANY SQL PROFILE<br>DROP ANY SQL PROFILE<br>ALTER ANY SQL PROFILE   |

### テストデータ

簡単なテストデータをつくります。実行計画を変更する予定ですのでインデックスも作っておきます。

```sql:
SQL> create table test_tbl as select level id, 'A' || lpad(level, 4, '0') s from dual connect by level <= 10;

SQL> create index test_tbl_idx on test_tbl(id);

SQL> select * from test_tbl;

        ID S
---------- -----------------
         1 A0001
         2 A0002
         3 A0003
         4 A0004
         5 A0005
         6 A0006
         7 A0007
         8 A0008
         9 A0009
        10 A0010
```

### テストコードと実行計画

上記テーブルのIDカラムで検索をしてみます。インデックスがあるので当然インデックスアクセスです。さらにFULLヒント加えて同じ検索を行い、テーブルフルスキャンに実行計画を変更します。

```sql:ヒントなし(INDEX使用)
SQL> select * from test_tbl where id = 1;

SQL_ID  7un2pa6mr7q37, child number 0

Plan hash value: 1331819983

------------------------------------------------------------
| Id  | Operation                           | Name         |
------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TBL     |
|*  2 |   INDEX RANGE SCAN                  | TEST_TBL_IDX |
------------------------------------------------------------
```

```sql:ヒントあり(フルスキャン)
SQL> select /*+ full(test_tbl) */ * from test_tbl where id = 1;

SQL_ID  1hxbghyabqndp, child number 0

Plan hash value: 602094504

--------------------------------------
| Id  | Operation         | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |          |
|*  1 |  TABLE ACCESS FULL| TEST_TBL |
--------------------------------------
```

### SQLプロファイルの作成

ここで、ヒントなしのSQL（インデックスアクセス）をチューニング対象の元コード、ヒント付きのSQL（フルテーブルスキャン）をチューニング後の実行計画が得られるコードと「仮定」します。もちろんフルスキャンのほうが効率は悪いのですが、SQLプロファイルを使って強制的に実行計画を変更するというただのテストですので気にしないでください(^^)。

それでは、**上記ヒント無しクエリでフルスキャンの実行計画を選択するSQLプロファイル**を作っていきましょう。

| アクセス | SQL_ID | プランハッシュ値 |
|:-:|:-:|:-:|
| インデックス使用  | 7un2pa6mr7q37  | 1331819983  |
| フルスキャン  | 1hxbghyabqndp  | 602094504  |

まずは、ヒントなしの元コードの取得。キャッシュからは``V$SQL.SQL_FULLTEXT``を使用し、AWRからであれば``DBA_HIST_SQLTEXT.SQL_TEXT``を用います。

```sql:SQLコードの取得
SQL> select sql_fulltext from v$sql where sql_id = '7un2pa6mr7q37';
SQL_FULLTEXT
------------------------------------------------------------------------
select * from test_tbl where id = 1
```

次にヒントを付けた欲しい実行計画のSQLプロファイル定義の取得。キャッシュは``V$SQL_PLAN.OTHER_XML``、AWRは``DBA_HIST_SQL_PLAN.OTHER_XML``ですね。``<outline_data>``から``</outline_data>``までを抜き出して取得します。

```sql:SQLプロファイル設定部分の取得
SQL> select regexp_substr(other_xml, '<outline_data>.*</outline_data>') profile 
from v$sql_plan 
where sql_id = '1hxbghyabqndp' and plan_hash_value = 602094504 and other_xml is not null;

PROFILE
--------------------------------------------------------------------------------
<outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA
[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.
0.1')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SE
L$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint></outline_data
>
```

上記で取得した２つのデータをIMPORT_SQL_PROFILEに突っ込めば完成。

```sql:
declare
  sql_text     CLOB; 
  outline_hint CLOB; 
begin

select sql_fulltext
into sql_text
from v$sql
where sql_id = '7un2pa6mr7q37' and rownum = 1;

select regexp_substr(other_xml, '<outline_data>.*</outline_data>') 
into outline_hint
from v$sql_plan 
where sql_id = '1hxbghyabqndp' and plan_hash_value = 602094504 and 
      other_xml is not null and rownum = 1;

DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 
    sql_text    => sql_text,
    profile_xml => outline_hint,
    name        => 'TEST SQL PROFILE',
    category    => 'DEFAULT', 
    validate    => TRUE, 
    replace     => TRUE, 
    force_match => true);
end;
/
PL/SQL procedure successfully completed.

SQL> select name, status from dba_sql_profiles where name = 'TEST SQL PROFILE';

NAME                 STATUS
-------------------- --------
TEST SQL PROFILE     ENABLED
```

### 実行計画の確認

無事SQLプロファイルが作成されました。それでは、ヒント無しで先程の検索を実行して実行計画を確認します。以下の通り、SQLプロファイルが使用されフルテーブルスキャンが行われました。実行計画にSQLプロファイルが使用されるとその旨Noteセクションに表示されます。``V$SQL.SQL_PROFILE``や``DBA_HIST_SQLSTAT.SQL_PROFILE``でも確認可能です。

```sql:SQLプロファイル作成後の実行計画
SQL> select * from test_tbl where id = 1;

SQL_ID  7un2pa6mr7q37, child number 1

--------------------------------------
| Id  | Operation         | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |          |
|*  1 |  TABLE ACCESS FULL| TEST_TBL |
--------------------------------------

Note
-----
   - SQL profile TEST SQL PROFILE used for this statement
```


ここで、IDに異なるリテラル値を与えてSQLコードを変えてみましょう。SQL_IDが変わりますが問題なくSQLプロファイルが適用されています。これはSQLプロファイル作成時に、``force_match => true``と設定したからです。本来SQLプロファイルはSQLコード依存の適用なのですが、このオプションを有効にするとリテラル値が変更されたSQLコードでも適用してくれます。便利ですね。

```sql:リテラル値の変更
SQL> select * from test_tbl where id = 2;    <-- 2に変更

SQL_ID  c93sc5bnwfhbr, child number 0        <-- 異なるSQL_ID

--------------------------------------
| Id  | Operation         | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |          |
|*  1 |  TABLE ACCESS FULL| TEST_TBL |
--------------------------------------

Note
-----
   - SQL profile TEST SQL PROFILE used for this statement
```

### スクリプトの作成

では、この動きをスクリプトに落とし込んでみましょう。せっかくなのでキャシュだけでなくAWRも検索するようにしておきます。

```sql:スクリプト(create_sqlprofile.sql)
DECLARE 
  p_sql_id_code VARCHAR2(13) := '&SQL_ID_FOR_CODE'; -- original sql code
  p_sql_id_plan VARCHAR2(13) := '&SQL_ID_FOR_PLAN'; -- sql code with hints
  p_phv         number 	     := to_number('&PLAN_HASH_VALUE_FOR_PLAN');
  p_name        VARCHAR2(30) := '&&SQL_PROFILE_NAME';
  p_force_match boolean      := case when upper('&FORCE_MATCH_Y_OR_N') = 'Y' then true else false end;

  sql_txt      CLOB; 
  outline_hint CLOB; 
BEGIN
 
select sql_fulltext into sql_txt from (
    select sql_fulltext from gv$sql where sql_id = p_sql_id_code
    union all
    select sql_text from dba_hist_sqltext where sql_id = p_sql_id_code)
where rownum = 1;

select regexp_substr(other_xml,'<outline_data>.*</outline_data>')
into outline_hint
from (
    select other_xml from gv$sql_plan
	where sql_id = p_sql_id_plan and plan_hash_value = p_phv and 
              other_xml is not null
    union all
    select other_xml from dba_hist_sql_plan
	where sql_id = p_sql_id_plan and plan_hash_value = p_phv and 
    other_xml is not null)
where rownum = 1;

DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 
    sql_text    => sql_txt, 
    profile_xml => outline_hint, 
    name        => p_name,
    category    => 'DEFAULT', 
    validate    => TRUE, 
    replace     => TRUE, 
    force_match => p_force_match 
); 

END; 
/
```

スクリプトを使用して同じSQLプロファイルを作ってみます。以下の通り、SQL_ID、PHV等を与えるだけです。なんてお手軽！

ちなみに単純に過去の実行計画に戻したいときなど、**SQLコードが変わらない場合は同一のSQL_IDを与え、プランハッシュ値で最適な実行計画を選択します。**

```sql:
SQL> @create_sqlprofile

Enter value for sql_id_for_code: 7un2pa6mr7q37        <--- 実行計画を変えたいコード
Enter value for sql_id_for_plan: 1hxbghyabqndp        <--- ヒントで最適化されたコード
Enter value for plan_hash_value_for_plan: 602094504   <--- 最適な実行計画のプランハッシュ値
Enter value for sql_profile_name: TEST SQL PROFILE    <--- SQLプロファイル名
Enter value for force_match_y_or_n: y                 <--- リテラル値除外マッチ

PL/SQL procedure successfully completed.

SQL> select cast(created as date) created, type, status, force_matching from dba_sql_profiles where name = 'TEST SQL PROFILE';

CREATED             TYPE    STATUS   FORCE_MATCHING
------------------- ------- -------- --------------
2018/09/08 15:59:32 MANUAL  ENABLED  YES

```

### SQLプロファイル設定の参照

SQLプロファイルのステータスは``DBA_SQL_PROFILES``を参照しますが、SQLプロファイルの中身を確認するには``DBMSHSXP_SQL_PROFILE_ATTR``を使用します。XMLフォーマットで保持されているので、見やすいように展開します。

```sql:SQLプロファイルの設定参照
SELECT OUTLINE_HINT
FROM   DBMSHSXP_SQL_PROFILE_ATTR,
       xmltable('//hint' passing xmltype(comp_data) columns outline_hint varchar2(100) path 'text()')
WHERE  profile_name = 'TEST SQL PROFILE';

OUTLINE_HINT
----------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_TBL"@"SEL$1")
```


### 10gの場合

10gの場合は``DBMS_SQLTUNE.IMPORT_SQL_PROFILE``の古い方のインターフェイスしか使えません。``SQLPROF_ATTR``は、``VARCHAR2``のコレクションです。

```
PROCEDURE IMPORT_SQL_PROFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 PROFILE                        SQLPROF_ATTR            IN
（以下同じ)
```

このためスクリプトのプロファイル取得部分が以下のように変わります。ちょっとわかりにくですけど、``OTHER_XML``からヒントの部分のみを個別に取得してコレクションに代入しています。まぁ、こっちのやり方で**10g以上のすべてのバージョンに対応**できるんですけどね。ただ11gからは内部的にもXMLフォーマットで保持しているようなのと、あとわかりやすさを優先して説明してみました。

```sql:10gに対応する場合の変更点
outline_hint SYS.SQLPROF_ATTR;

select cast(collect(hint) as SYS.SQLPROF_ATTR)
into outline_hint
from (
    select * from (
        select other_xml from gv$sql_plan
        where sql_id = p_sql_id_plan and plan_hash_value = p_phv and 
              other_xml is not null
        union all
        select other_xml from dba_hist_sql_plan
        where sql_id = p_sql_id_plan and plan_hash_value = p_phv and other_xml is not null)
    where rownum = 1),
	xmltable('//hint' passing xmltype(other_xml) columns hint varchar2(500) path 'text()');
```

10gでは``DBMSHSXP_SQL_PROFILE_ATTR``も設定を``VARCHAR2``で持っているのでそのまま参照できます。

```sql:SQLプロファイル設定の参照(10g)
SQL> select ATTR_VALUE from DBMSHSXP_SQL_PROFILE_ATTR where profile_name = 'TEST SQL PROFILE' order by ATTR_NUMBER;

ATTR_VALUE
---------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_TBL"@"SEL$1")
```

### バグについて

11gの``FORCE_MATCH``オプションにはなかなか極悪なバグがあります。対象のSQLコードに``--``のコメントがあると``FORCE_MATCH``オプションが有効化されないというものです。さらに修正のためのパッチを当てたら全SQLプロファイルを作り直さなければならないという**呪い**付き。詳しくは、``ID 2112203.1``および``ID 1253696.1``を参照してください。

### おわりに

チューニングアドバイザを使用せず自分の思い通りの実行計画でSQLプロファイルを作成する方法でした。SQLプランマネジメント（SQLベースライン）でも同様のことができますが、それについてはまたの機会に。

また、チューニングアドバイザを使用して結果をアクセプトする本来の使用法に関しては、ここに良くまとまっています：＜<a href="http://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-38-2236622-ja.htmlurl">津島博士のパフォーマンス講座 第38回 SQLチューニングについて</a>＞

以上です。