2024.12.10
SQLチューニング 2nd Season（第24回）第4章 SQL TuningとHINTの関係 （7/8）
今回のSQLチューニングブログ 2nd Season（第24回）は、「SQL Tuning と HINTの関係」の第7回目です。
～ PARAMETER 制御ヒント・SQL PERFORMANCEに関するヒント ～ について解説していきます。
それでは早速、はじめていきましょう。
4.2.10 PARAMETER 制御ヒント
SQL実行計画に影響を与える要素はいくつかあります。
その中で、オプティマイザのパラメータ設定値も重要な要素の一つです。
これは、SQLの性能改善方法のうち、オプティマイザに関連するパラメータ設定値を変更することも一つの方法であることを意味します。しかし、パラメータ設定値を変更することはリスクが伴います。
変更が完了した瞬間、DBサーバー全体に影響を与え、既存のSQL実行計画にどのような変更が発生するか誰も保証できない問題が発生する可能性があるからです。
つまり、問題がなかったSQLがパラメータの変更によって実行計画が変更される可能性があり、この変更された実行計画によって性能問題を引き起こすSQLになる可能性もあります。
従って、オプティマイザに関連するパラメータの変更は、SQLの全検査を前提としない限り、効果的な方法とは言えないかもしれません。しかし、Oracle10.2.0.1バージョン以降、SQL単位でオプティマイザに関連するパラメータの変更が可能になりました。
これは、DBサーバー全体に影響を与える可能性のあるパラメータ変更を該当するSQLレベルのみにパラメータ変更値の適用が可能であることを意味します。従って、上記で説明したリスクを持つ必要がありません。
■ OPT_PARAM
使用バージョン：10.2.0.1〜。
使用方法: /*+ OPT_PARAM('Parameter_name','Value') */ /*+ OPT_PARAM('Parameter_name','Value')
ヒント意味：オプティマイザ関連パラメータをSQLレベルで制御できるようにするヒント
以前のバージョンでは、SessionレベルやSystemレベルに限定してオプティマイザのパラメータを制御することができましたが、Oracle10.2.0.1バージョンに新しく追加されたヒントであるOPT_PARAMヒントを使うことで、SQLレベルでのパラメータ制御ができるようになりました。
使用例：
SELECT /*+ INDEX_COMBINE(T1 IDX02_HINT_T3 IDX03_HINT_T3) */
*
FROM HINT_T3 T1
WHERE ORDDATE BETWEEN TO_DATE('2013-01-01','YYYY-MM-DD')
AND TO_DATE('2015-12-31','YYYY-MM-DD')
AND CUST_NO BETWEEN '1' AND '10';
----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |00:00:00.01 | 9 |
|* 1 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 1 | 1 |00:00:00.01 | 9 |
|* 2 | INDEX RANGE SCAN | IDX03_HINT_T3 | 1095 | 1095 |00:00:00.01 | 5 |
----------------------------------------------------------------------------------------------
上記のSQLのようにB*Tree Index Combinationを誘導する結果となりました。
これは、Index Range Scanで実行される場合や、Index Skip ScanヒントをSQLに追加した場合、Index Range Scanで実行された場合などのSQLチューニング実施時においてよく経験することです。
このような場合、SQLにヒントを適用したが、オプティマイザ関連パラメータの設定値が該当機能を使用できないように設定されているためです。
上記の使用例SQLに適用されたB*Tree Index Combination関連のパラメータ設定値を照会した場合、下記のようになります。
select a.ksppinm, b.ksppstvl
from x$ksppi a, x$ksppsv b
where a.indx=b.indx
and a.ksppinm like '_b_tree_bitmap_plans';
KSPPINM KSPPSTVL
-------------------- ---------------
_b_tree_bitmap_plans FALSE
該当パラメータの設定値がFALSEに設定されていることで、ヒントのようなSQLが実行されませんでした。
この時には、前述したOPT_PARAMヒントを使うことができます。
SELECT /*+ OPT_PARAM('_b_tree_bitmap_plans','TRUE') INDEX_COMBINE(T1 IDX02_HINT_T3 IDX03_HINT_T3) */
*
FROM HINT_T3 T1
WHERE ORDDATE BETWEEN TO_DATE('2013-01-01','YYYY-MM-DD')
AND TO_DATE('2015-12-31','YYYY-MM-DD')
AND CUST_NO BETWEEN '1' AND '10';
------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |00:00:00.01 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID | HINT_T3 | 1 |00:00:00.01 | 9 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 |00:00:00.01 | 8 |
| 3 | BITMAP AND | | 1 |00:00:00.01 | 8 |
| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 |00:00:00.01 | 3 |
| 5 | SORT ORDER BY | | 50 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | IDX02_HINT_T3 | 50 |00:00:00.01 | 3 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | 1 |00:00:00.01 | 5 |
| 8 | SORT ORDER BY | | 1095 |00:00:00.01 | 5 |
|* 9 | INDEX RANGE SCAN | IDX03_HINT_T3 | 1095 |00:00:00.01 | 5 |
------------------------------------------------------------------------------------------
4.2.11 SQL PERFORMANCEに関するヒント
■ MONITOR
使用バージョン：11.1.0.6〜。
使用方法: /*+ MONITOR */ (Inverse: NO_MONITOR)
ヒント意味：Real-Time SQL Monitoring機能を使用できるようにするヒント
Oracle11gから提供された Real-Time SQL Monitoring機能は、”_sqlmon_threshold”隠しパラメータ設定値より大きい遅いSQLの実行履歴をV$SQL_MONITOR、V$SQL_PLAN_MONITORビューに残して、パフォーマンス問題を引き起こす可能性のあるSQLをDBMS_SQLTUNE.REPORT_SQL_MONITOR関数を利用して情報を照会できるようになりました。
ところで、Real-Time SQL Monitoring機能によって実行履歴が残らないが、強制的にSQLの実行履歴を残したい場合は、SQLにMONITORヒントを付与すれば残ります。反対の場合はNO_MONITORヒントを使うことができます。
Note. Real-Time SQL Monitoring機能を使うためには、以下のパラメータが設定されている必要があります。
- パラメータ設定1: STATISTICS_LEVEL = ALL or TYPICAL (Default Value)
- パラメータ設定2: CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC + TUNING (Default Value)
使用例:
1. Monitoring対象SQL登録
select /*+ monitor */
t1.cust_no,
count(*),
min(orddate),
max(orddate),
max(ord_no)
from hint_t1 t1,
hint_t3 t3
where to_char(t1.cust_no)=to_char(t3.cust_no)
group by t1.cust_no;
2-1. Select文のMonitoring結果確認
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id => 'f48q812tpww1w' ,
type => 'TEXT',
report_level => 'ALL') AS report
FROM dual;
2-2. PL/SQL Blockで結果確認
declare
v_output clob;
begin
v_output := DBMS_SQLTUNE.report_sql_monitor(
sql_id => 'f48q812tpww1w',
type => 'TEXT',
report_level => 'ALL') ;
dbms_output.put_line(v_output);
end;
3. 抽出結果
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor */ t1.cust_no, count(*), min(orddate), max(orddate), max(ord_no) from hint_t1 t1, hint_t3 t3 where to_char(t1.cust_no)=to_char(t3.cust_no) group by t1.cust_no
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : T_TEST (12:18583)
SQL ID : 5avavwr5vctmu
SQL Execution ID : 16777216
Execution Started : 01/02/2015 08:14:32
First Refresh Time : 01/02/2015 08:14:32
Last Refresh Time : 01/02/2015 08:15:09
Duration : 37s
Module/Action : T_TEST@QA228:12/Trial Until 2015-06-30
Service : SYS$USERS
Program : LitePlus.exe
Fetch Calls : 1346
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 27 | 27 | 0.08 | 1346 | 10928 | 697 | 85MB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=3290747011)
=========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 13 | +25 | 1 | 33625 | | | | | |
| 1 | HASH GROUP BY | | 50000 | 11550 | 13 | +25 | 1 | 33625 | | | 6M | | |
| 2 | HASH JOIN | | 50000 | 9870 | 26 | +1 | 1 | 33625 | | | 8M | 7.69 | Cpu (2) |
| 3 | TABLE ACCESS FULL | HINT_T1 | 100K | 120 | 1 | +2 | 1 | 100K | 29 | 3MB | | | |
| 4 | VIEW | VW_GBC_5 | 50000 | 9290 | 1 | +25 | 1 | 50000 | | | | | |
| 5 | HASH GROUP BY | | 50000 | 9290 | 24 | +2 | 1 | 50000 | | | 5M | 92.31 | Cpu (24) |
| 6 | TABLE ACCESS FULL | HINT_T3 | 3M | 2961 | 24 | +2 | 1 | 3M | 668 | 82MB | | | |
=========================================================================================================================================================
■ RESULT_CACHE
使用バージョン：11.1.0.6〜。
使用方法: /*+ RESULT_CACHE */ (Inverse: NO_RESULT_CACHE)
ヒント意味：Result Cache Memory機能を使用できるようにするヒントです。
同じデータを繰り返し照会するSQLに対する応答速度を改善するため、Oracle11gでは、メモリ領域にResult Cacheを利用する機能を新たに発表しました。
Result CacheはShared PoolにResult Cache Memoryと呼ばれる領域にSQLおよびPL/SQL Functionの結果を保存し、その後、同じ照会時にBuffer Cacheを探索する一連の過程なしにResult Cacheに保存された結果データをそのまま活用するものです。
多くのデータを対象に集計データを抽出するSQLが繰り返し実行される場合、最初の実行時に結果をResult Cache Memoryに保存し、以降の照会からはSQLを実行せずにResult Cache Memoryに保存された結果をそのまま使用することができます。そして、SQL内で繰り返し使用される特定のSQL Blockの抽出件数に比べて多くのI/O処理量と長い応答時間を要する場合にResult Cache機能を使用すると、当該SQLの性能だけでなく、DBサーバーの負荷量も減少することになります。
Result Cache Memory は、SQL 照会結果を保存する SQL Query Result Cache と PL/SQL Function の結果を保存する PL/SQL Function Result Cache で構成されます。
Result Cacheに保存されるSQLの照会結果は、各インスタンスごとに保存され、すべてのセッションに共有されます。しかし、Result Cacheを使用するSQLのオブジェクトに変更が発生した場合、Invalid状態になります。
SQL Query Result Cacheの使用はRESULT_CACHE_MODEパラメータによって制御が可能です。
このパラメータはMANUALとFORCEという値を持つことができますが、MANUALの場合、Result Cacheを使うためには必ずSQLにRESULT_CACHEヒントを使わなければなりません。一方、FORCEの場合はすべての結果を保存するため、NO_RESULT_CACHEというヒントを使用してこれを回避することができます。
SQLに /*+ RESULT_CACHE */ ヒントを使用すると、まずResult Cache Memoryを探索して、抽出したいデータがResult Cache Memoryに存在する場合、すぐに結果を取得し、そうでなければ、そのSQLを実行した結果をResult Cache Memoryに保存することになります。このプロセスは、InvalidされたResult Cacheにも同じように適用されます。
もし、この機能を使用しない場合には、RESULT_CACHE_MAX_SIZEパラメータの設定値を0に設定変更を実施後、インスタンスを再起動してください。
使用例:
[Result Cache初回実行時のSQLとXPLAN ]SQLとXPLAN ]
SELECT /*+ result_cache */
*
FROM (
SELECT t1.cust_no ,
COUNT( * ) ,
MIN( orddate ) ,
MAX( orddate ) ,
MAX( ord_no )
FROM hint_t1 t1 ,
hint_t3 t3
WHERE t1.cust_no=t3.cust_no
GROUP BY t1.cust_no
ORDER BY 2 DESC
)
WHERE ROWNUM <= 10 ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:44.41 | 10727 | 10507 |
| 1 | RESULT CACHE | g0fkt8ac018571170x988a82ym |00:00:44.41 | 10727 | 10507 |
|* 2 | COUNT STOPKEY | |00:00:44.41 | 10727 | 10507 |
| 3 | VIEW | |00:00:44.41 | 10727 | 10507 |
|* 4 | SORT ORDER BY STOPKEY | |00:00:44.41 | 10727 | 10507 |
| 5 | HASH GROUP BY | |00:00:44.31 | 10727 | 10507 |
|* 6 | HASH JOIN | |00:00:37.45 | 10727 | 10507 |
| 7 | INDEX FAST FULL SCAN| IDX01_HINT_T1 |00:00:00.30 | 216 | 0 |
| 8 | TABLE ACCESS FULL | HINT_T3 |00:00:07.61 | 10511 | 10507 |
-------------------------------------------------------------------------------------------------
[ Result Cache 반복 수행 시 SQL 및 XPLAN ]
select /*+ result_cache */
t1.cust_no,
count(*),
min(orddate),
max(orddate),
max(ord_no)
from hint_t1 t1,
hint_t3 t3
where t1.cust_no=t3.cust_no
group by t1.cust_no ;
---------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 |00:00:00.01 |
| 1 | RESULT CACHE | g0fkt8ac018571170x988a82ym | 10 |00:00:00.01 |
|* 2 | COUNT STOPKEY | | 0 |00:00:00.01 |
| 3 | VIEW | | 0 |00:00:00.01 |
|* 4 | SORT ORDER BY STOPKEY | | 0 |00:00:00.01 |
| 5 | HASH GROUP BY | | 0 |00:00:00.01 |
|* 6 | HASH JOIN | | 0 |00:00:00.01 |
| 7 | INDEX FAST FULL SCAN| IDX01_HINT_T1 | 0 |00:00:00.01 |
| 8 | TABLE ACCESS FULL | HINT_T3 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------
初回実行時、約44秒の応答時間がかかり、Disk I/O発生量が1万ブロック以上発生しました。
繰り返し実行時した場合には、最初に実行した10件の保存結果だけを抽出すればよいので、応答時間やI/Oにはほとんど負荷をかけることなく処理されたことを確認することができます。
4.2.12 QUERY OPTIMIZINGに関するヒント
■ OPTIMIZER_FEATURES_ENABLE
使用バージョン：10.1.0.3〜。
使用方法: /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0.8') */ /*+ OPTIMIZER_FEATURES_ENABLE
ヒント意味: ヒントに記載されたOptimizer VersionでSQLが実行できるように誘導するヒント。
Oracle製品は、バージョンがアップグレードされるたびに、オプティマイザの動作に大きな変化が起こります。
例えば、Oracle9iでは Bind Peekingアルゴリズムが追加され、より現実的な値に基づいた実行計画の生成が可能になった点や、Oracle10gでは、Cost Based Subquery unnesting、View Merging機能が追加され、Query Transformationがより現実的になった場合があります。しかし、特定の状況では、このようなオプティマイザの動作方法の変更が既存のSQLの性能を低下させる原因になる場合があります。
この時、パフォーマンス問題が発生したSQLを変更したり、関連するオラクルパラメータを再調整して解決するのが一般的です。しかし、性能問題の正確な原因を把握することが難しく、個々のSQLの変更やパラメータの再調整が不可能な場合があります。この場合、OPTIMIZER_FEATURES_ENABLEパラメータの設定値を性能問題が発生しなかった以前のバージョンに変更して性能問題を解決することができます。例えば、Oracle 9.2.0.8バージョンを使用する環境では問題がなかったSQLが10.2.0.3にアップグレードして性能問題が発生した場合には、次のようにオプティマイザの動作方式を以前のバージョンに変更することができます。
・ Oracle 10.1.0.3以前のバージョン：パラメータ設定値の変更
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE = "9.2.0.8"；
・ Oracle 10.1.0.3以降のバージョン：SQLにヒントを付与
/*+ OPTIMIZER_FEATURES_ENABLE('9.2.0.8') */ /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0.8')
■ GATHER_PLAN_STATISTICS
使用バージョン：10.1.0.3〜。
使用方法: /*+ GATHER_PLAN_STATISTICS */ /*+ GATHER_PLAN_STATISTICS
ヒント意味: SQL実行時に'Row Source Operation'情報を有効にするヒント
GATHER_PLAN_STATISTICSヒントは、SQL実行時にRow Source Operation情報を収集するためのヒントです。GATHER_PLAN_STATISTICSヒントを介して収集された情報は、DBMS_XPLANパッケージのDISPLAY_CURSORプロシージャを利用して確認することができます。
SQL実行時に予測計画（Explain Plan）によるロー数と実際の実行（Row Source Operation）によるロー数を比較することができるので、SQLの性能改善のための情報として有用です。
但し、特定のバージョンやOS環境下では、STATISTICS_LEVELパラメータの設定値をALLに設定しておかないと、正確な情報が表示されない場合があるので注意してください。
・ E-Rows: 予測計画(Explain Plan)によるRow数
・ A-Rows: 実際の実行(Row Source Operation)によるRow数
・ A-Time: 実際の実行(Row Source Operation)時のElapsed Time.
Note. E-RowsとA-Rowsの差が大きい場合は、オプティマイザによるコスト計算に問題（例えば、統計情報異常）があることを意味することができるので、統計情報の再集計などの措置が必要な場合があります。
■ QB_NAME
使用バージョン：10.1.0.3〜。
使用方法: /*+ QB_NAME(Query Block Name指定) */ /*+ QB_NAME(Query Block Name指定)
ヒントの意味：SQLのQuery Blockを指定するヒント
Oracleは、インラインビュー、サブクエリを持つ特定のSQLに対してSQL Text （SELECT … FROM … WHERE）を複数の実行単位に分けて、Query Block名を指定した後に実行しますが、このようなSQLのQuery Blockをうまく活用すれば、性能問題の解決やSQL構文がどのように実行するかを把握するのに役立ちます。
Oracle10gより前のバージョンでは、OracleがQuery Blockに任意に付与した識別子（名前）しか使用することができませんでしたが、Oracle10gからは新たに QB_NAME という便利なヒントが追加されました。
これを使用することで、ユーザーが直接SQLのQuery Blockに識別子を指定することができるようになりました。
次のSQLは、サブクエリにT1_SUBQUERY、T3_SUBQUERYというそれぞれQuery Block名を指定した例です。
使用例:
select t1.*, t2.*, t3.*
from t1, t2, t3
where t1.c1 = t2.c1
and t2.c1 = t3.c1
and exists (
select /*+ QB_NAME(T1_SUBQUERY) */ ---> Query Block名指定
'x'
from t1 t1_1
where t1.c1 = t1_1.c1
and t1_1.c2 in ( 'A','B','C' )
)
and exists (
select /*+ QB_NAME(T3_SUBQUERY) */ ---> Query Block名指定
'x'
from t3 t3_1
where t3.c1 = t3_1.c1
and t3_1.c4 in ('a', 'b', 'c')
);
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1332 | 54612 | 341 (3)| 00:00:05 |
|* 1 | HASH JOIN RIGHT SEMI | | 1332 | 54612 | 341 (3)| 00:00:05 |
|* 2 | TABLE ACCESS FULL | T3 | 11538 | 80766 | 84 (2)| 00:00:02 |
|* 3 | HASH JOIN | | 11539 | 383K| 256 (4)| 00:00:04 |
|* 4 | HASH JOIN | | 11539 | 247K| 172 (5)| 00:00:03 |
|* 5 | HASH JOIN RIGHT SEMI| | 11539 | 135K| 115 (5)| 00:00:02 |
|* 6 | TABLE ACCESS FULL | T1 | 11538 | 69228 | 58 (6)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 100K| 585K| 56 (2)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T2 | 100K| 976K| 56 (2)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T3 | 100K| 1171K| 84 (2)| 00:00:02 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C7C9B628
2 - SEL$C7C9B628 / T3_1@T3_SUBQUERY ---> /*+ QB_NAME(T3_SUBQUERY) */
6 - SEL$C7C9B628 / T1_1@T1_SUBQUERY ---> /*+ QB_NAME(T1_SUBQUERY) */
7 - SEL$C7C9B628 / T1@SEL$1
8 - SEL$C7C9B628 / T2@SEL$1
9 - SEL$C7C9B628 / T3@SEL$1
QB_NAME ヒントは次のような場合に使用すると効率的です。
・ SQL内で同一テーブルが複数回実行される場合に便利です。(性能分析が容易)
・ 特定のQuery Blockを制御したいときに便利です。
SQLチューニング 2nd Season（第24回）終
次回のSQLチューニングブログは・・・
SQLチューニングブログ 2nd Season（第4章）
「SQL Tuning と HINTの関係」（8/8）
～ HINTの適用と注意事項 ～
次回はこのテーマについての 最終回 です。
私たちは 日本のITインフラ における
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。
日本エクセムのデータベースソリューション
日本エクセムの最新情報は公式SNSでも配信中
SQLチューニングブログについてのお問い合わせは
