Oracle SQLチューニング Season2（第18回） 第4章 SQL TuningとHINTの関係（1/8）

　今回のSQLチューニング 2nd Season（第18回）は新シリーズがスタートします。
　第4章では全8回シリーズで「SQL TuningとHINTの関係」についてお届けしていきます。
　まず第1回目となる今回は「HINTの使用規則」「HINTの使用目的」について解説します。

4.1　HINTとは？

　SQLの実行計画を策定する主体となるのがオプティマイザです。
　オプティマイザが実行計画を樹立する時、テーブルとインデックスが持つ統計情報を参照します。
　しかし、その数が多かったり、SQL内にインラインビュー、サブクエリなどが多数使われている様な場合には、誤ったコスト計算で効率的ではない実行計画が生成される場合も少なくなく、常に効率的な実行計画が生成されません。

　このように、SQLの作成意図とは違う形でオプティマイザの誤ったコスト計算による性能問題が発生する場合、最も効率的に対処できる方法としてSQLに対してHINTを適用します。

　HINTは、非効率的に策定された実行計画をSQL作成者またはチューナーが能動的に間違った実行計画を修正するために最も効率的で強力な方法であると言えます。

　また、HINTを適用してもSQL結果（抽出されるデータ）に影響を与えないため、データ整合性に対する負担軽減も可能です。

　したがって、性能問題が発生したSQLの改善方法としてHINTを多用します。
　ただし、HINTには使用規則があり、これを遵守しなければなりません。
　間違えた使い方をすると、本来は性能問題を改善するために使用されるはずのHINT自体が、むしろ大きな問題を引き起こす原因となる可能性があるため、十分に理解した上で使用しなければなりません。

4.1.1　HINTの使用規則

　［図4-1］の様に、SQL Blockの最初のキーワード（SELECT, INSERT, UPDATE, DELETE, MERGE）の直後に追加する必要があります。又、HINTを使用する場合には、1つのHINTコメントが必要となり、1つのHINTコメントの中には複数のHINTを含めることができます。

［図4-1］ヒント構文の適用位置

［図4-1］のヒントコメントは “/*+ */” で、適用されたヒントは合計3つです。
・　ヒントコメント: /*+ ~ */ (複数行のヒント、–+は1行のヒントコメントです)
・　ヒント : ヒント1) LEADING(A) , ヒント2) USE_NL(A B) , ヒント3) INDEX(A IDX_01)

　HINTは通常、最初に処理するテーブルの指定（以降、Driving Table）や結合する順序を決めたり、テーブル間の結合方法、そしてデータアクセスタイプを決定する際によく使われます。

　ジョイン順序やジョイン方法、データアクセスタイプを誘導するためのHINTは、特定のテーブルを指定して使用することになります。
　このようにFROM節にテーブル名だけがある場合、HINTはテーブル名を使用して作成の上、テーブル別名（Table Alias）も使用します。
　また、HINT構文を作成するときには、必ずテーブル別名を使用しなければなりません。

［表4-1］テーブル名またはテーブル別名のヒント例

　SQLを作成する際、そのSQLがどのような業務を担うのか？誰が作成したものか？などを知ることができるようにプログラムレベルやSQLレベルに識別子を付与する場合があります。

　通常、このような場合では、DBMS_APPLICATION_INFOパッケージを利用してModule / Action名を指定したり、SQLにコメントを適用する方法を使いますが、最近ではプログラム作成時、一般的にDBMS_APPLICATION_INFOパッケージを利用した方法よりSQLにコメントを適用することを好みます。
　DBMS_APPLICATION_INFOパッケージを使って設定する方法は、SQLにコメントを適用する方法と比べて二つの非効率性が存在します。  

　まず、DBMS_APPLICATION_INFOパッケージを使う方法は、単位SQLの組み合わせで構成された業務を指している事が多く、指定した識別子（Module）の下に多数のSQLが存在する可能性があり、性能問題が発生しているSQLを探さなければならない煩わしさがあります。
　つまり、多数のSQLのうち問題のSQLをすぐに見つけることが難しく、不必要に時間がかかることがあります。
　2つ目に、識別子を設定するためにDBMS_APPLICATION_INFOパッケージを実行しなければならない負担があります。
　同時ユーザーが多く、照会が頻繁な画面で使用する場合には、DBサーバーのCPU使用率の増加を引き起こす可能性があるためです。

　このような理由から、プログラムやSQLに識別子を付与する方法としてはSQLにコメントを追加する方法を主に使います。
　しかし、使用する上で注意すべき点が存在します。
　それは、SQLにヒントを追加する時にヒント構文の効力が発生しない場合がある点です。
　［表4-2］を見れば分かるように、SQLに識別子をヒントコメントで作成した場合、ヒント構文の適用位置によっては、ヒント構文が単純コメントとして解釈されてしまい、正常なヒント適用ができない場合があるのです。

［表4-2］SQL説明コメントと正しいヒントの適用例

Script.正しいヒント適用位置

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;

CREATE TABLE T1
AS
SELECT LEVEL AS C1, LEVEL+100 AS C2
FROM DUAL
CONNECT BY LEVEL <= 10000 ;

CREATE TABLE T2
AS
SELECT LEVEL AS C1, LEVEL+100 AS C2
FROM DUAL
CONNECT BY LEVEL <= 10000 ;

CREATE INDEX T1_IDX_01 ON T1 (C1) ;
CREATE INDEX T2_IDX_01 ON T2 (C2) ;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS( 'MAXGAUGE' , 'T1' ) ;
EXECUTE DBMS_STATS.GATHER_TABLE_STATS( 'MAXGAUGE' , 'T2' ) ;
テスト[1]：誤って適用されたヒント (X)


SELECT /*+ SQL説明 */ 
       /*+ FULL(T1)*/
         T1.C1、
         T2.C1
   FROM T1, T2
 WHERE T1.C1 = :B1
   AND T1.C2 = T2.C2

SELECT STATEMENT ALL_ROWS-Cost : 3
 TABLE ACCESS BY INDEX ROWID MAXGAUGE.T2(2)
  NESTED LOOPS  
   TABLE ACCESS BY INDEX ROWID MAXGAUGE.T1(1)
    INDEX RANGE SCAN MAXGAUGE.T1_IDX_01 (C1) ("T1"."C1"=TO_NUMBER(:B1))
   INDEX RANGE SCAN MAXGAUGE.T2_IDX_01 (C2) ("T1"."C2"="T2"."C2")

　［テスト1］のSQLで使用したT1テーブルのC1カラムは効率的なカラムでインデックスが生成されており、T1とT2テーブルの結合はT1　→ T2 の順に実行され、T1はインデックススキャンで実行しなければ効率的なSQLです。
　ところで、ヒントが正常に実行するかテストするためにSQLに/*+ FULL(T1) */ヒントを適用しましたが、SQL実行はインデックスを通じたデータアクセスが発生しました。それは、ヒントが効果を発揮することができなかったのです。

テスト[2]：正しく適用されたヒント (⚪︎)

SELECT /*+ FULL(T1) */ /*+ SELECT /*+ FULL(T1) */
       /*+ SQL説明 */
         T1.C1、
         T2.C1
   FROM T1, T2
  WHERE T1.C1 = :B1
    AND T1.C2 = T2.C2

SELECT STATEMENT ALL_ROWS-Cost : 11
 TABLE ACCESS BY INDEX ROWID MAXGAUGE.T2(2)
  NESTED LOOPS  
   TABLE ACCESS FULL MAXGAUGE.T1(1) ("T1"."C1"=TO_NUMBER(:B1))
   INDEX RANGE SCAN MAXGAUGE.T2_IDX_01 (C2) ("T1"."C2"="T2"."C2")

　［テスト2］のようにヒントの適用位置を変更すると、T1テーブルのデータアクセスをインデックスではなくTABLE FULL SCANで実行しました。この結果はSQLに適用したヒントが効果を発揮したと言えると思います。
　下の［テスト3］はSQL説明をヒント構文で適用せず、一般的なコメント構文で処理して正常にヒントが適用されたことが確認できます。

テスト[3]：正しく適用されたヒント (⚪︎)

SELECT /* SQL説明 */ 
       /*+ FULL(T1)*/
         T1.C1、
         T2.C1
   FROM T1, T2
  WHERE T1.C1 = :B1
    AND T1.C2 = T2.C2

SELECT STATEMENT ALL_ROWS-Cost : 11
 TABLE ACCESS BY INDEX ROWID MAXGAUGE.T2(2)
  NESTED LOOPS
   TABLE ACCESS FULL MAXGAUGE.T1(1) ("T1"."C1"=TO_NUMBER(:B1))
   INDEX RANGE SCAN MAXGAUGE.T2_IDX_01 (C2) ("T1"."C2"="T2"."C2")

4.1.2 HINTの使用目的

4.2.1.1 統計情報による実行計画異常

　統計情報(Table/Index/Column)は、SQL実行時、クエリ最適化時に参照されるデータで、実際の情報と異なる場合、非効率的な実行計画を策定するのに大きな影響を与えます。
　このような統計情報異常によるSQLの非効率的な実行は、全体的なDBサーバーの性能低下の要因になるので注意しなければなりません。

　統計情報によるSQLの性能問題は、一般的に性能問題を引き起こすオブジェクトの統計情報を再作成すれば解決されることが多いです。
　しかし、業務量が多い時間帯にサイズが大きいオブジェクトの統計情報を再作成するのは危険な方法です。
　統計情報の生成自体に時間がかかり、DBサーバーの多くのリソースを使用し、生成中に他のパフォーマンス問題を引き起こす危険性があるためです。
　このような場合、オブジェクトの統計情報を操作して既存の統計情報を変更し、SQLカーソルをDisableさせて性能問題を解決することもできますが、この方法も適用するのに時間がかかり、他のSQLが性能問題を引き起こす危険性があり、簡単に適用しにくい改善方法です。

　従って、SQLの変更が可能な状況(緊急反映が可能な場合)であれば、SQLの性能問題を解決する方法としてヒントを検討してみてください。

4.2.1.2 Bind Peekingによる実行計画異常

　“_optim_peek_user_binds”パラメータの設定値によって、バインド変数があるSQLに対する最初の解析(以降、ハードパーシング)に違いがあります。Falseの場合は一般的なハードパーシングで実行されますが、Trueの場合はオプティマイザがSQLを解釈する時、バインドカラムのHistogramを参照し、SQLのコストを計算して実行計画を立てます。
　一見すると、”_optim_peek_user_binds”の設定値をTrueに設定するのが効率的に見えます。
パラメータの意図としては良いですが、予期せぬ性能問題が発生する場合がしばしば発生してしまうため、推奨値はFalseとします。

　例えば、バインド変数で照会されるカラムが日付カラムで、インデックスが存在し、99.9%は日単位で照会が行われ、平均抽出されるロー数は100件以内であると仮定しましょう。
　そして、テーブルデータ件数は1億件を超えるとすると、通常照会されるパターンでSQLが解釈される時は、インデックススキャンで実行するでしょう。
　ところが、そのSQLのCursor情報がShared PoolでAging Outされ、ハードパーシングをする時点で、普段は照会されない1年の日付範囲でSQLを実行した場合、オプティマイザはインデックススキャンではなく、TABLE FULL SCANで実行します。そして、その後照会される同じSQLは、普段と同じように1日照会をしても、1億件以上のテーブルをTable Full Scanで実行することになります。

　このように”_optim_peek_user_binds”パラメータの設定値をTrueに設定すると、SQLの実行が異常な場合があり、DBサーバーのパフォーマンスに大きな悪影響を及ぼす可能性があります。

　この場合、パラメータの設定値をFalseに変更する必要がありますが、すぐに反映するのが難しい場合は、ヒントを代替として使用することができます。

4.2.1.3 インデックス構成変更による実行計画異常

　オプティマイザがSQLのコスト計算時に重要な情報の1つがインデックス構成情報です。
　ところが、既存のテーブルにインデックスが追加生成されたり、既存のインデックスが削除されるなど、インデックス構成情報が変更される場合、SQLの実行計画に異常が発生する可能性があり、これにより性能問題を引き起こす可能性があります。このような場合、SQLの性能問題を解決する方法としてヒントを使うことができます。

4.2.1.4 オプティマイザが非効率実行計画を策定する場合

　一般的にSQLにヒントを使用する場合は、作成者が意図していない実行計画で実行され、パフォーマンスの問題を引き起こす場合です。
　このような場合、オプティマイザがSQLのCost計算時に誤って実行したもので、SQL作成者の介入が必要です。
　SQLの非効率的な実行計画による性能問題を解決するためには、通常、SQLを再作成することが望ましいが、SQLの実行計画を制御するヒントを適用して解決することもできます。

　他のDBMSの場合でも、Oracleが提供するヒントのようなキーワードを提供しているが、Oracleのように実行計画を自由に誘導したり、SQL作成者の意図通りに実行するように制御することがOracleに比べて強力ではありません。 
　その分、Oracleが提供するヒントは、SQLの性能問題を改善する方法として使用できる強力な武器の1つだと思います。

次回のSQLチューニングブログは

SQLチューニングブログ 2nd Season （第4章）
「SQL Tuning と HINTの関係」（2/8）

HINTの種類と使い方について