LoginSignup
2
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@tlokweng

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

Last updated at Posted at 2018-09-17

はじめに

ここでは、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
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE

テストデータ

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

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ヒント加えて同じ検索を行い、テーブルフルスキャンに実行計画を変更します。

ヒントなし(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> 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> 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> 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に突っ込めば完成。

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> 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> 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も検索するようにしておきます。

スクリプト(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> @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プロファイルの設定参照
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フォーマットで保持しているようなのと、あとわかりやすさを優先して説明してみました。

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プロファイル設定の参照(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ベースライン）でも同様のことができますが、それについてはまたの機会に。

また、チューニングアドバイザを使用して結果をアクセプトする本来の使用法に関しては、ここに良くまとまっています：＜津島博士のパフォーマンス講座 第38回 SQLチューニングについて＞

以上です。

2
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?