2022.10.19
SQLチューニング（第37回）「カラム変形によるSQL性能問題の理解する」（1／2回）
今回から、２回にわたって、「カラム変形によるSQL性能問題の理解する」と題して、カラムに関する情報をお届けします。早速、内容に入っていきましょう。
10.1 照会カラムと変数のデータ タイプを合わせよう
疑問
“照会条件に適合したインデックスがあるのにも関わらず、インデックス スキャンで処理されないでFull Table Scanをする場合がありますが、なぜこういう現象が発生するのですか?“このような現象に対する多くの原因は、Where節に使われたカラムと照会変数値のデータ タイプが(カラム:VARCHAR2,条件:NUMBER)違うので発生する場合が多くあります。 時にはこの場合を除いても、間違って生成された統計情報によるOptimizerの判断エラーや、DBサーバーのパラメーター設定中のBind Peekingによって発生する場合もありえます。
しかしBind Peeking機能がFALSEに設定されていて、統計情報も最近生成されていて、Where節に使われたカラムに対してインデックスが存在して、該当カラムのデータ分布図までも選んだ場合、インデックス使用が効率的なのにFull Table Scanで実行されているならば、SQLを問い合わせる変数値とカラムのデータ タイプが不一致なことではないか疑って見る必要があります。
[例題1] SQLはREGISTERNO =:B1条件で照会されますが、REGISTERNOカラムに対するEQUAL照会はインデックス使用が常に効率的です。 ところでインデックス生成後SQLの実行計画を確認すると、予想とは違ってインデックス スキャンでないFull Table Scanと解釈されています。
[例題1]
SELECT registeryn
FROM tb_crm_register
WHERE registerno = :b1
SELECT STATEMENT - ALL_ROWS- Cost Estimate:3352
TABLE ACCESS FULL :CUSR.TB_CRM_REGISTER
それならばなぜ、Where節の照会条件に対してインデックス スキャンと解釈することが最も効率的な方法にもかかわらず、Optimizerはいかなる理由でFull Table Scanで実行するように判断するのでしょうか? その理由を知るために私たちはFull Table Scanがどの場合に実行されることができるかを先に知らなければなりません。 以下の三つがSQLがFull Table Scanで実行するのに影響を及ぼす要素となります。
・ 誤った統計情報によってOptimizerがFull Table Scanが有利だと判断した場合
・ インデックスが存在しない場合
・ カラム変形による場合
上記で言及した要素に対して直接確認し、[例題1] SQLがFull Table Scanを実行した原因が何か調べて見ましょう。 先に、Full Table Scanが発生する場合の中の一つはテーブルの統計情報が正常ではないで生成されている時です。
例えば、データが存在しないテーブルを生成した後すぐに統計情報を収集したとすれば、この時、統計情報に存在するテーブルの総データ件数は0件でしょう。 そして統計情報収集以後に該当テーブルに多くのデータが入力され、統計情報を更新しなかったとすれば、Optimizerに誤った情報を提供してWhere節条件に適合したインデックスがあってもFull Table Scanで実行します。
[例題1] SQLのFull Table Scanの原因がTB_CRM_REGISTERテーブルの統計情報が不正確で発生したことか確認するために該当テーブルの統計情報を確認して見ましょう。
[WHERE節カラムの統計情報]
COLUMN_NAME DATA_TYPE LEN NN DISTINCT DENSITY NUM_NULLS
-------------- ---------- ----- -- ---------- ------------ ----------
REGISTERNO VARCHAR2 8 N 612402 0.000001669 0
[テーブル統計情報一部]
AVG
ROW
TABLE NAME TABLESPACE NAME NUM_ROWS LEN BLOCKS
--------------------- ---------------- ---------- ------ ---------
TB_CRM_REGISTER(CUSR) TS_DATA_01 612402 121 12288
実際のテーブルのデータ件数を問い合わせた結果値と、テーブル統計情報のNUM_ROWS件数を比較してみるとほとんど一致しました。 したがって統計情報は正常収集されていました。 したがってFull Table Scanが発生した原因が不正確な統計情報によったことではないと推測されます。
それならWhere節に使われたカラムに対してインデックスが存在するのかを確認して見ましょう。
[インデックス構成情報]
INDEX_NAME TYPE U COLUMN LIST
--------------- ---- - -----------
IDX_REGISTER1 NORM N REGISTERNO
インデックス構成情報で見るようにREGISTERNOカラムにインデックスが存在します。 そしてREGISTERNOカラムのNUM_DISTINCT値とTB_CRM_REGISTERテーブルの全体件数が同じことと見てREGISTERNO =:B1条件から抽出されるデータは平均1件だと予想されるので非常に効率的です。これを計算する方法は下記のとおりとなります。
TABLE全体件数(NUM_ROWS)/ COLUMN NDV = 612402 / 612402 = 1今まで確認した内容を整理すれば、Where節の条件で照会されるREGISTERNOカラムに対して効率的なインデックスが存在して、TB_CRM_REGISTERテーブルに対する統計情報も正常収集されています。 そしてREGISTERNOカラムを持ってEQUAL照会をする場合、平均1件抽出されると予想される効率的なインデックスも存在します。 したがって最後に私たちが確認してみなければならないことは:B1のデータ タイプとカラムのデータ タイプが違って内部的なカラム変形が発生するのかどうかを点検することになります。
10g以上ではV$SQL_BIND_CAPTUREとDBA_HIST_SQLBINDビューを利用してSQLが実行される時使ったBind値に対する情報を確認できますし、値だけでなくデータ タイプも知ることが出来ます。 該当ビューを問い合わせてカラムの変数値が持つデータ タイプを確認して見ましょう。
SELECT name,
last_captured,
datatype,
value_string
FROM V$SQL_BIND_CAPTURE
WHERE hash_value = :v_hash_value ;
NAME LAST_CAPTURED DATATYPE VALUE_STRING
---------- ------------- ---------- ------------
:B1 10/03/26 2 10516940
:B1 10/04/06 2 10521217
:B1 10/03/31 2 10455816
:B1 10/03/24 2 10515642
V $ SQL_BIND_CAPTUREの列のうち、DATATYPEの値が2の場合は、変数のデータ型がNUMBERであることを意味しますので、：B1のデータ型はNUMBER型（DATATYPEが1の場合はCHARです）です。 そして、列情報を確認すると、REGISTERNO列のデータ型はVARCHAR2（8）です。 まとめてみると、REGISTERNO列のデータ型はVARCHAR2（8）であり、プログラムで照会する：B1のデータ型はNUMBERです。
これでインデックスを使用する必要があるにもかかわらず、Full Table Scanとして解釈された理由が明らかになりました。 その理由は、Bind変数とカラムのデータ型が異なる場合、Oracleは片方を型変換後に比較することになります。 このとき、型変換優先順位によって変数側のデータ型を型変換せずに、カラム側のデータ型を変形してインデックス列に対する変形が発生したのです。 このため、インデックスを使用できず、Full Table Scanをすることになったと判断できます。
カラム REGISTERNO = VARCHAR2(8)
変数-:B1 = NUMBER
WHERE registerno = :b1 ---> WHERE TO_NUMBER(registerno) = :b1
上記のようにカラムと変数のデータ タイプが違って内部的にカラム変形が発生して性能問題が発生する場合には、SQLで使われるBind変数とカラムのデータ タイプを同一になるように照会条件を変更しなければなりません。 また、プログラムで変数を定義した部分でデータ タイプをカラムと同一の型に合わせれば性能問題を解決することができます。
SELECT registeryn
FROM TB_CRM_REGISTER
--WHERE registerno =:b1? 照会条件変更前
WHERE registerno = TO_CHAR(:b1);? 照会条件変更後
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 1 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 1 4 0 1
Rows Row Source Operation
------ ------------------------------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TB_CRM_REGISTER (cr=4 pr=1 pw=0 time=0 us …)
1 INDEX UNIQUE SCAN IDX_REGISTER1 (cr=3 pr=1 pw=0 time=0 us …)
ここで“変数とカラムのデータ タイプが違う場合、常に性能問題が発生するのか?”という疑問が発生します。 この部分に対してはテストを通じて確認して見ましょう。 テストでは紙面関係上すべてのデータ タイプに対してテストを実行できないので、[例題1]で問題になっているVARCHAR2(8)タイプとNUMBERタイプに対してだけ検証しましたので残りに対しては別にテストを実行してみるように願います。
Script. テスト データ生成用
< T1 >
■生成要件
- T1テーブルの全体件数は1,000,000ロー
- C1はNumberタイプでありUnique値を持つ。
- C2のデータ タイプは文字型であり、YYYYMMDDフォーマットを持つ日データである。
■テーブル生成
create table t1
as
SELECT 1000000-( LEVEL -1) c1, TO_CHAR(SYSDATE-( LEVEL -1),'yyyymmdd') c2
from DUAL
CONNECT BY LEVEL <= 1000000 ;
■各カラムにインデックス生成および統計情報収集
CREATE INDEX idx_t1_01 ON t1(c1);
CREATE INDEX idx_t1_02 ON t1(c2);
begin
dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',
TABNAME=>'T1',
ESTIMATE_PERCENT=>99,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
GRANULARITY=>'ALL',
CASCADE=>TRUE,
NO_INVALIDATE=>FALSE) ;
end;
/
テスト[1]. VARCHAR2(8)タイプであるC2を問い合わせる変数のタイプがNUMBERである時場合
・ カラム データ タイプ? VARCHAR2(8)
・ 変数データ タイプ- NUMBER
SELECT *
FROM T1
WHERE c2 = 20110124 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 598 (4)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 598 (4)| 00:00:08 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("C2")=20110124) --->内部でカラム変形が発生してインデックス
使用をできない。
テスト[2]. NUMBERタイプであるC1を問い合わせる変数のタイプがVARCHAR2である場合
・ カラム データ タイプ- number
・ 変数データ タイプ- varchar2(8)
SELECT *
FROM T1
WHERE c1 = '111111' ;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 13 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_01 | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=111111) --->カラム変形なくてインデックス スキャンが正常実行される。
VARCHAR2(8)タイプを持つカラムに対してNUMBERタイプで問い合わせる場合、内部的にカラムに対してVARCHAR2をNUMBERタイプで変形して性能問題が発生しますが、反対の場合は発生しません。
前のテストを通じてカラムと変数のデータ タイプが違う場合、性能問題が発生することも、発生しないこともあります。 しかし全体プログラムのうちきわめて一部でもデータ タイプによる性能問題が発生するならばDBサーバーの性能に悪影響を与えることになります。
このようなデータ タイプの不一致による性能問題はテーブル結合時、各テーブルの結合キーで使われるカラムのデータ タイプが互いに相異なった場合にも発生する可能性があるので、テーブルを設計する時結合カラムのデータ タイプがそれぞれ違わないように特別の注意を注がなければなりません。
いかがでしたでしょうか？カラムに関する話は次回に続きます。ご期待ください。では See You ^^
