はじめに
この記事は NTTテクノクロス Advent Calendar 2019 の２０日目の記事です。
こんにちはＮＴＴテクノクロスで最近はＤＢ関連のお仕事をしている田島です。
SQLチューニングについて
SQL言語の特徴として、取得したいカラムと、テーブルの関連性、条件を示すだけで、取得方法についてはDBMSが決めます。
DBMSが統計情報（テーブルの件数、カラムのカーディナリティ等）を考慮して、実行計画を作成します。
DBMSの想定どおりであればこの実行計画で性能が出ますが、想定と異なる場合に性能が出なくなります。
実行計画と実行値で差分の大きなところがDBMSの想定と異なるところとなり、その部分を見ていくことにより、チューニングができる可能性があります。
Oracleでは別の方法により実行計画を固定する方法等もありますが、ヒント句により変更する方法もあります。
今回はヒント句によるチューニングの確認方法について19cからの新機能を確認したいと思います。
ヒント使用状況レポートについて
18cまではヒント句の記述が誤っていたり有効に使用されているかは、実行計画が思ったとおりに変わったかでしか判断できないというわかりづらい状況でした。
19cから使用しているヒントが使用されているか、ヒント使用状況レポートを出力することができるようになったため、機能を確認してみます。
前提条件
使用するテーブルは以下のようにわかりやすいテーブルで確認してみます。
create table t1_tbl(id number primary key,data varchar2(100)) ;
create table t2_tbl(id number primary key,data varchar2(100)) ;
使用するSQLは単純にidで連結を使用したものについてヒント句を付けてどう実行計画がどう変わり、ヒント句が使用されているのか確認していきます。
select t1.id,t1.data,t2.data from t1_tbl t1,t2_tbl t2 where t1.id = t2.id;
レポートの出し方について今回は以下の方法で確認します。
explain plan for 実行するSQL;
select * from table(dbms_xplan.display(format => 'ALL'));
ヒント句を付ける前の状態では以下となりました。
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2690813551
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1_TBL | 1 | 65 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C007604 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2_TBL | 1 | 65 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
5 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100],
"T2"."DATA"[VARCHAR2,100]
2 - (#keys=0) "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100],
"T2".ROWID[ROWID,10]
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
3 - "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100]
4 - "T2".ROWID[ROWID,10]
5 - "T2"."DATA"[VARCHAR2,100]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
ヒント句(USE_NL)
では、ヒント句をつけて実行計画を変えてみます。
駆動表(t1)と内部表(t2)を変えてみたいと思います。
内部表をt1とするためにUSE_NLを使ってみます。
select /*+ USE_NL(t1) */ t1.id,t1.data,t2.data from t1_tbl t1,t2_tbl t2 where t1.id = t2.id;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2690813551
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1_TBL | 1 | 65 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C007604 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2_TBL | 1 | 65 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
5 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100],
"T2"."DATA"[VARCHAR2,100]
2 - (#keys=0) "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100],
"T2".ROWID[ROWID,10]
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
3 - "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100]
4 - "T2".ROWID[ROWID,10]
5 - "T2"."DATA"[VARCHAR2,100]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$1 / T1@SEL$1
U - USE_NL(t1)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
Hint Reportが出力されて確認してみますが、Unusedでヒント句がつかわれていませんね。
ヒント句(LEADING,USE_NL)
ではLEADINGもつけてみます。
select /*+ LEADING(t2 t1) USE_NL(t1) */ t1.id,t1.data,t2.data from t1_tbl t1,t2_tbl t2 where t1.id = t2.id;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2623727078
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2_TBL | 1 | 65 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C007603 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1_TBL | 1 | 65 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T2@SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "T2"."DATA"[VARCHAR2,100], "T1"."ID"[NUMBER,22],
"T1"."DATA"[VARCHAR2,100]
2 - (#keys=0) "T2"."DATA"[VARCHAR2,100], "T1".ROWID[ROWID,10],
"T1"."ID"[NUMBER,22]
3 - "T2"."ID"[NUMBER,22], "T2"."DATA"[VARCHAR2,100]
4 - "T1".ROWID[ROWID,10], "T1"."ID"[NUMBER,22]
5 - "T1"."DATA"[VARCHAR2,100]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$1
- LEADING(t2 t1)
4 - SEL$1 / T1@SEL$1
- USE_NL(t1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
無事にヒント句が使用されて、駆動表(t2)と内部表(t1)になっています。
ヒント句(USE_HASH)
では、テーブルの結合をハッシュ結合にしてみます。
select /*+ USE_HASH(t1 t2) */ t1.id,t1.data,t2.data from t1_tbl t1,t2_tbl t2 where t1.id = t2.id;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2338795136
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 130 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1_TBL | 1 | 65 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2_TBL | 1 | 65 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "T1"."ID"[NUMBER,22],
"T1"."DATA"[VARCHAR2,100], "T2"."DATA"[VARCHAR2,100]
2 - (rowset=256) "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100]
3 - (rowset=256) "T2"."ID"[NUMBER,22], "T2"."DATA"[VARCHAR2,100]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / T1@SEL$1
U - USE_HASH(t1 t2)
3 - SEL$1 / T2@SEL$1
- USE_HASH(t1 t2)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
同じヒント句がt1側で使用されていなく、t2側では使用されたと出力されています。
このヒント句は対象のテーブルを２つ記述するため、「1 - SEL$1」でヒント句が使用されたことを示す方がわかりやすいように思われますね。
念のため、「USE_HASH(t2)」で確認しましたがハッシュ結合にならず、ヒント句は使用されませんでした。
ヒント句(USE_MERGE)
ではついでにMERGE結合
select /*+ USE_MERGE(t1 t2) */ t1.id,t1.data,t2.data from t1_tbl t1,t2_tbl t2 where t1.id = t2.id;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3489417861
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 130 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 1 | 65 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1_TBL | 1 | 65 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1 | 65 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T2_TBL | 1 | 65 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
5 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
filter("T1"."ID"="T2"."ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100],
"T2"."DATA"[VARCHAR2,100]
2 - (#keys=1) "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100]
3 - "T1"."ID"[NUMBER,22], "T1"."DATA"[VARCHAR2,100]
4 - (#keys=1) "T2"."ID"[NUMBER,22], "T2"."DATA"[VARCHAR2,100]
5 - "T2"."ID"[NUMBER,22], "T2"."DATA"[VARCHAR2,100]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$1 / T1@SEL$1
U - USE_MERGE(t1 t2)
5 - SEL$1 / T2@SEL$1
- USE_MERGE(t1 t2)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
無事、MERGE結合になっています。
ヒントレポートはUSE_HASHの時と同様ですね。
おわりに
今回はSQLチューニングを実施するときに役立ちそうなヒントレポート機能の確認をしてみました。
Oracle Databaseの実行計画の精度がバージョンごとにどんどん上がり、SQLチューニングをする機会も減ってきているとは思いますが、実際のSQLチューニングをするときの参考になれば幸いです。
それでは引き続きNTTテクノクロス Advent Calendar 2019の２１日目もお楽しみください。