ダイレクトパスリードとNSMTIOトレース [オプティマイザ]
この記事は、JPOUG Advent Calendar 2020 22日目の記事です。
21日目はyohei.azさんの記事『Tanel Poder の Linux Process Snapper』でした。
スマートスキャンをはじめとするExadataのオフロード機能を有効に使うためには、ダイレクトパスリードの発生条件を理解することが重要である（direct path readについて思うこと参照）。DPRの発生条件についての仕様は公開されておらず、また、Oracleのバージョンによって挙動が異なる。このため、有識者のブログ等でこういう条件ではこういう動きになったという断片的な情報は存在するものの、自分の目の前の環境でどういう動きになるのかを正確に知ることは難しい。
本稿では、ダイレクトパスリードの発生条件を確認する簡単な方法として、NSMTIOトレースを紹介する。私の手元の環境（OracleVM、19.3）で実際にトレースを取得して、挙動を確認した例を示す。あくまで例であり、環境が違えば異なる結果になる可能性があることはご留意頂きたい。
ダイレクトパスリードとは、フルスキャン（以後FTSと記す）の際に選択されるブロック読み取り方法であり、マルチブロックリードのようにバッファキャッシュを経由せず直接PGAへブロックを転送するため高速に処理を行うことができる。Exadataのスマートスキャンはダイレクトパスリードのコードの内部に実装されているため、スマートスキャン（正確にはExadataの各種オフロード機能）を使うにはダイレクトパスリードが前提となる。
ダイレクトパスリードにはパラレルとシリアルがある。パラレルダイレクトパスリードとは、parallelヒント等でパラレル化したフルテーブルスキャン（以下FTSと記す）するときに発生するダイレクトパスリードである。一方、シリアルダイレクトパスリードとは、パラレル化しない場合のFTSで発生するダイレクトパスリードである。
発生条件に関しては仕様が公開されておらず正確に把握することは難しい。FTS対象のオブジェクトサイズと以下のパラメータの大小関係、およびバッファキャッシュにオブジェクトがどれほど乗っているかが関係するといわれている。
STT(Small Table Threshold)・・・バッファキャッシュサイズの約2%
MTT(Middle Table Threshold)・・・バッファキャッシュサイズの約10%(STTの5倍）
VLOT(Very Large Object Threshold)・・・バッファキャッシュサイズの約500%(STTの250倍)
オブジェクトのサイズは、オブジェクト統計のBLOCKSが使われる。これは_direct_read_decision_statistics_drivenにより制御されており、falseにするとセグメントヘッダのブロックカウントを用いるようになる。11.2.0.2～からtrueになっている（参考文献[3]）。
ダイレクトパスリードは実行計画を取得してもわからないため、SQLトレースを取得しダイレクトパスリードなのかマルチブロックリードなのかを確認するのが確実である。しかし、なぜダイレクトパスリードが選択されたのかはSQLトレースだけではわからない。それを確認できるのがNSMTIOトレースである。データベースでトレース可能なコンポーネントのうち、KXDと呼ばれるExadata特有カーネルモジュールをoradebugで確認するとNSMTIOトレースを確認できる（参考文献[2])。
NSMTIOトレースの取得方法は簡単である。以下のようにセッションレベルでSQLトレースを取得するように指定すればよい（より正確な指定方法は、参考文献[1]参照）。
以下にselect * from dualでNSMTIOトレースを取得した例を示す。
上記トレースのNSMTIOで始まる★部分から、テーブルアクセス関数（qertbFetch）がNoDirectRead、つまりダイレクトパスが選択されずマルチブロックリードが選択されたであろうことがわかる。また、その選択にあたり、オブジェクトサイズが1ブロック(Obect's size: 1)であること、MTTが5660ブロックであること、STTおよびMTTとの大小関係、オブジェクト統計が有効であること等が関連していることが伺える。実際、この場合は、オブジェクトサイズがSTT(kcbstt 1132)より小さいことから、マルチブロックリードが選択される。
なお、複数のSQLを実行すると、NSMTIOトレースがどのSQLに紐づくか識別が難しいことがあるので、以下のようにSQLトレースとあわせて設定する方法もある。場合によって使い分ければよい。
・バージョン
・インスタンス（バッファキャッシュ）
・初期化パラメータ（STT）
上記から、STTは1,132ブロック、MTTはその5倍の5,660ブロック、VLOTはバッファキャッシュの500%程度、より正確にはSTT250倍の283,000ブロックあたりと推測できる。
・テスト用テーブルの作成
レコード数は作成したいテーブルサイズにあわせて適宜変更する。以下の４パターンで検証する。なお、(4)については環境の制約でdbms_statsでNUM_ROWSとBLOCKSを設定して挙動を確認した。
(1)STT < OBJECT SIZE
バッファキャッシュをクリアしてからSQLを実行すると、バッファキャッシュ（ローカルまたはキャッシュフュージョン）から読むコスト、ストレージによる削減要素（OLTP/EHCC圧縮）を確認して、その結果ダイレクトパスリードを選択しているらしいということがわかる。また、選択のロジックとしては、kcbismでSTTとの大小関係をチェックはしているが（islarge 0)、qertbFetchではMTTとVLOTとの大小関係しか見ていないようである。
一方、FTSを実行してバッファキャッシュにブロックを乗せた状態では、バッファキャッシュから読み込む挙動が確認された。
(2)STT < OBJECT SIZE < MTT
→基本的に(1)と同じ挙動が確認できた。オブジェクトサイズがSTTを超えることにより、ダイレクトパスに倒れる挙動を期待したが、本検証では明確な違いを確認できなかった。
(3)MTT < OBJECT SIZE < VLOT
★部分のkcbismでSTTとの大小関係をチェックしislarge 1となっている。挙動としては(1)と同じであった。
(4)VLOT < OBJECT SIZE
★部分のkcbivloでVLOTよりオブジェクトサイズが大きいためis_large 1となり、次のqertbFetchでダイレクトパスリードが選択されていることがわかる。Additional InfoでVLOTが283040ブロックであることがわかる。
(1)STT < OBJECT SIZE
kcbismでSTTより小さいことがチェックされ、qertbFetchでダイレクトパスリードが選択されずマルチブロックリードとなっていることがわかる。
(2)STT < OBJECT SIZE < MTT
STTよりオブジェクトが大きい場合は、qertbFetchでバッファキャッシュから読むコスト等がチェックされ、その結果、kcbdpcでDirectReadが選択されている。
バッファキャッシュにオブジェクトが乗っている状態では、kcbcmt1で何らかのバッファキャッシュ上のオブジェクトのブロック数が評価され、qertbFetchでNoDirectReadが選択されている様子がわかる。
(3)MTT < OBJECT SIZE < VLOT
MTTより大きい場合はkcbimdのチェックが追加されており、ここでMTTとの大小関係がチェックされている。kcbnbhがMTTなのだろう。ただし挙動は(2)同様である。
(4)VLOT < OBJECT SIZE
★部分のkcbivloでVLOTよりオブジェクトサイズが大きいためis_large 1となり、次のqertbFetchでダイレクトパスリードが選択されていることがわかる。VLOTの判定後のトレースの出方がシリアルの場合と同じなので、VLOTの場合はシリアルもパラレルも同じコードパスなのだろう。
_serial_direct_read（デフォルトauto）はシリアルダイレクトパスリードにおけるダイレクトパスの選択を制御することができる隠しパラメータである（セッションレベルで変更可能）。これをnever（ダイレクトパスリードを発生させない）、always（ダイレクトパスリードを発生させる）にそれぞれ指定した場合の挙動の違いをNSMTIOトレースで確認する。
(3)MTT < OBJECT SIZE < VLOT
"_serial_direct_read"=neverの場合、★部分でシリアルダイレクトパスリードが無効化され、マルチブロックリードが選択される挙動になっていることがわかる。
"_serial_direct_read"=alwaysの場合、★部分でシリアルダイレクトパスリードが有効化され、ダイレクトパスリードが選択される挙動になっていることがわかる。
今回はダイレクトパスリードの発生条件を確認するために、NSMTIOトレースの取得方法と、パラレル・シリアルダイレクトパスリードの検証結果を紹介した。
検証の結果、パラレルダイレクトパスについては、概ね常にダイレクトパスリードが発生する状態になることがわかった。バッファキャッシュに100%乗っている場合に、まれにバッファキャッシュから読み込まれる挙動も確認できた。一方、シリアルダイレクトパスについては、STT以下ではマルチブロックリード、MTT以上ではダイレクトパスリードになることがわかった。STT～VLOTまではダイレクトパスリードの場合と、マルチブロックリードの場合があることが確認された。バッファキャッシュ上の状態が関係していることは確かであるが、正確な挙動を確認までは至っていない。いずれのケースもVLOTより大きい場合はダイレクトパスリードになることが確認できた。課題は以下のqertbFetchのロジックの解明である。この中にバッファキャッシュの状態含めたコスト計算が含まれているはずで、この中のロジックをトレースすることができれば、より詳細にダイレクトパスリードの発生条件を明らかにすることができると考える。
qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
備忘まで、今回検証で確認できたダイレクトパスリードとSTT、MTT、VLOTの関係とNSMTIOトレースの特徴的な出力を下図に示す。繰り返しになるが、これで正しい挙動をとらえているとは思っていない。不完全ながら公開する意図は、トレースを取ればここまではわかるということを伝えることで、アップグレード時の挙動の変化を確認したり、オブジェクトサイズの境界値を監視することで性能問題を未然に防いだり、不幸にも性能トラブルに巻き込まれた際に迅速な解析が可能となると考える。DBAの生活の質の向上の一助になれば幸いである。
以上
[1]When bloggers get it wrong - part 2, Roger Macnicol
[2]Expert Oracle Exadata, Martin Bach他, pp.50-53
[3]Optimizer statistics-driven direct path read decision for full table scans, Tanel Poder
21日目はyohei.azさんの記事『Tanel Poder の Linux Process Snapper』でした。
１．はじめに
スマートスキャンをはじめとするExadataのオフロード機能を有効に使うためには、ダイレクトパスリードの発生条件を理解することが重要である（direct path readについて思うこと参照）。DPRの発生条件についての仕様は公開されておらず、また、Oracleのバージョンによって挙動が異なる。このため、有識者のブログ等でこういう条件ではこういう動きになったという断片的な情報は存在するものの、自分の目の前の環境でどういう動きになるのかを正確に知ることは難しい。
本稿では、ダイレクトパスリードの発生条件を確認する簡単な方法として、NSMTIOトレースを紹介する。私の手元の環境（OracleVM、19.3）で実際にトレースを取得して、挙動を確認した例を示す。あくまで例であり、環境が違えば異なる結果になる可能性があることはご留意頂きたい。
２．基本的な理解
ダイレクトパスリードとは、フルスキャン（以後FTSと記す）の際に選択されるブロック読み取り方法であり、マルチブロックリードのようにバッファキャッシュを経由せず直接PGAへブロックを転送するため高速に処理を行うことができる。Exadataのスマートスキャンはダイレクトパスリードのコードの内部に実装されているため、スマートスキャン（正確にはExadataの各種オフロード機能）を使うにはダイレクトパスリードが前提となる。
ダイレクトパスリードにはパラレルとシリアルがある。パラレルダイレクトパスリードとは、parallelヒント等でパラレル化したフルテーブルスキャン（以下FTSと記す）するときに発生するダイレクトパスリードである。一方、シリアルダイレクトパスリードとは、パラレル化しない場合のFTSで発生するダイレクトパスリードである。
発生条件に関しては仕様が公開されておらず正確に把握することは難しい。FTS対象のオブジェクトサイズと以下のパラメータの大小関係、およびバッファキャッシュにオブジェクトがどれほど乗っているかが関係するといわれている。
STT(Small Table Threshold)・・・バッファキャッシュサイズの約2%
MTT(Middle Table Threshold)・・・バッファキャッシュサイズの約10%(STTの5倍）
VLOT(Very Large Object Threshold)・・・バッファキャッシュサイズの約500%(STTの250倍)
オブジェクトのサイズは、オブジェクト統計のBLOCKSが使われる。これは_direct_read_decision_statistics_drivenにより制御されており、falseにするとセグメントヘッダのブロックカウントを用いるようになる。11.2.0.2～からtrueになっている（参考文献[3]）。
３．NSMTIOトレースについて
ダイレクトパスリードは実行計画を取得してもわからないため、SQLトレースを取得しダイレクトパスリードなのかマルチブロックリードなのかを確認するのが確実である。しかし、なぜダイレクトパスリードが選択されたのかはSQLトレースだけではわからない。それを確認できるのがNSMTIOトレースである。データベースでトレース可能なコンポーネントのうち、KXDと呼ばれるExadata特有カーネルモジュールをoradebugで確認するとNSMTIOトレースを確認できる（参考文献[2])。
SQL> oradebug doc component kxd KXD Exadata specific Kernel modules (kxd) KXDAM Exadata Disk Auto Manage (kxdam) KCFIS Exadata Predicate Push (kcfis) NSMTIO Trace Non Smart I/O (nsmtio) ★ KXDBIO Exadata Block level Intelligent Operations (kxdbio) KXDRS Exadata Resilvering Layer (kxdrs) KXDOFL Exadata Offload (kxdofl) KXDMISC Exadata Misc (kxdmisc) KXDCM Exadata Metrics Fixed Table Callbacks (kxdcm) KXDBC Exadata Backup Compression for Backup Appliance (kxdbc)
NSMTIOトレースの取得方法は簡単である。以下のようにセッションレベルでSQLトレースを取得するように指定すればよい（より正確な指定方法は、参考文献[1]参照）。
alter session set events 'trace[nsmtio]'; ＜任意のSQL文＞ alter session set events 'trace[nsmtio] off';
以下にselect * from dualでNSMTIOトレースを取得した例を示す。
SQL> alter session set tracefile_identifier='nsmtio'; Session altered. SQL> select value from v$diag_info where name like 'Default%'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_17198_nsmtio.trc SQL> alter session set events 'trace[nsmtio]'; Session altered. SQL> select * from dual; D - X SQL> alter session set events 'trace[nsmtio] off'; Session altered. SQL> !cat /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_17198_nsmtio.trc NSMTIO: kcbism: islarge 0 next 0 nblks 1 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 ★NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1 (blocks), Threshold: MTT(5660 blocks), _object_statistics: enabled, Sage: enabled, Direct Read for serial qry: enabled(:::::::auto DR::), Ascending SCN table scan: FALSE flashback_table_scan: FALSE, Row Versions Query: FALSE SqlId: a5ks9fhw2v9s1, plan_hash_value: 272002086, Object#: 143, Parition#: 0 DW_scan: disabled NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 0, invalid table sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted: -1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks: -1, isQesSageEnabled: FALSE
上記トレースのNSMTIOで始まる★部分から、テーブルアクセス関数（qertbFetch）がNoDirectRead、つまりダイレクトパスが選択されずマルチブロックリードが選択されたであろうことがわかる。また、その選択にあたり、オブジェクトサイズが1ブロック(Obect's size: 1)であること、MTTが5660ブロックであること、STTおよびMTTとの大小関係、オブジェクト統計が有効であること等が関連していることが伺える。実際、この場合は、オブジェクトサイズがSTT(kcbstt 1132)より小さいことから、マルチブロックリードが選択される。
なお、複数のSQLを実行すると、NSMTIOトレースがどのSQLに紐づくか識別が難しいことがあるので、以下のようにSQLトレースとあわせて設定する方法もある。場合によって使い分ければよい。
alter session set events 'trace[nsmtio]:sql_trace level 8'; ＜任意のSQL文＞ alter session set events 'trace[nsmtio]:sql_trace off';
４．検証
４－１．検証環境と検証内容
・バージョン
SQL> select BANNER_FULL from v$version; BANNER_FULL -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
・インスタンス（バッファキャッシュ）
SQL> select * from v$sgainfo NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Fixed SGA Size 9140336 No 0 Redo Buffers 3440640 No 0 Buffer Cache Size 524288000 Yes 0★ In-Memory Area Size 0 No 0 Shared Pool Size 297795584 Yes 0 Large Pool Size 4194304 Yes 0 Java Pool Size 0 Yes 0 Streams Pool Size 0 Yes 0 Shared IO Pool Size 37748736 Yes 0 Data Transfer Cache Size 0 Yes 0 Granule Size 4194304 No 0 Maximum SGA Size 838858864 No 0 Startup overhead in Shared Pool 198150216 No 0 Free SGA Memory Available 0 0
・初期化パラメータ（STT）
SQL> SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value", 2 b.ksppstvl "Session Value", 3 c.ksppstvl "Instance Value" 4 FROM x$ksppi a, 5 x$ksppcv b, 6 x$ksppsv c 7 WHERE a.indx = b.indx 8 AND a.indx = c.indx 9 AND a.ksppinm in ('_small_table_threshold', '_very_large_object_threshold') 10 / Parameter Default Value Session Value Instance Value ----------------------------------- --------------- --------------- --------------- _small_table_threshold TRUE 1132 1132 _very_large_object_threshold TRUE 500 500
上記から、STTは1,132ブロック、MTTはその5倍の5,660ブロック、VLOTはバッファキャッシュの500%程度、より正確にはSTT250倍の283,000ブロックあたりと推測できる。
・テスト用テーブルの作成
drop table scott.t1 cascade constraints; create table scott.t1 (id, val, padding1 , padding2 , padding3 , padding4 , padding5 ) as select rownum id, trunc(100 * dbms_random.normal) val, rpad('x',100) padding1, rpad('x',100) padding2, rpad('x',100) padding3, rpad('x',100) padding4, rpad('x',100) padding5 from dual connect by level <= 10000; exec dbms_stats.gather_table_stats('SCOTT','T1'); col segment_name for a20 select segment_name, bytes/1024/1024 mb from user_segments where segment_name='T1'; select num_rows, blocks from dba_tables where owner='SCOTT' and table_name='T1';
４－２．検証項目
レコード数は作成したいテーブルサイズにあわせて適宜変更する。以下の４パターンで検証する。なお、(4)については環境の制約でdbms_statsでNUM_ROWSとBLOCKSを設定して挙動を確認した。
NUM_ROWS BLOCKS
---------- ----------
(1) 10000 791 ・・・STT < OBJECT SIZE
(2) 20000 1573 ・・・STT < OBJECT SIZE < MTT
(3) 80000 6260 ・・・MTT < OBJECT SIZE < VLOT
(4) 1000000 300000 ・・・VLOT < OBJECT SIZE *
*)
BEGIN
DBMS_STATS.SET_TABLE_STATS(
ownname => 'SCOTT'
, tabname => 'T1'
, numrows => 1000000
, numblks => 300000 );
END;
/
５．検証結果
５－１．パラレルダイレクトパスリード
(1)STT < OBJECT SIZE
バッファキャッシュをクリアしてからSQLを実行すると、バッファキャッシュ（ローカルまたはキャッシュフュージョン）から読むコスト、ストレージによる削減要素（OLTP/EHCC圧縮）を確認して、その結果ダイレクトパスリードを選択しているらしいということがわかる。また、選択のロジックとしては、kcbismでSTTとの大小関係をチェックはしているが（islarge 0)、qertbFetchではMTTとVLOTとの大小関係しか見ていないようである。
NSMTIO: kcbism: islarge 0 next 0 nblks 791 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: NSMTIO:kkfdtsc:DirectRead: NSMTIO: kcbzib: Cache Scan triggered for tsn = 5, rdba=0x030105ba, fn = 12, kobjd = 81289, block cnt = 0, nonconUE pre-warm = FALSE, prefetch = FALSE NSMTIO: kcbism: islarge 0 next 0 nblks 769 type 2, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcb8 kcbnwp 1 NSMTIO: kxfrCancelDR: objn:0x13d89 flg:0x4201 size:769 table:small cache:56608 affpct:80 NSMTIO: kcbivlo: nblks 791 vlot 500 pnb 56608 kcbisdbfc 0 is_large 0 NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)★ NSMTIO: kcbdpc:DirectRead: tsn: 5, objd: 81289, objn: 81289★
一方、FTSを実行してバッファキャッシュにブロックを乗せた状態では、バッファキャッシュから読み込む挙動が確認された。
NSMTIO: kcbdpc:NoDirectRead:[CACHE_READ]: tsn: 5, objd: 81292, objn: 81292
(2)STT < OBJECT SIZE < MTT
→基本的に(1)と同じ挙動が確認できた。オブジェクトサイズがSTTを超えることにより、ダイレクトパスに倒れる挙動を期待したが、本検証では明確な違いを確認できなかった。
(3)MTT < OBJECT SIZE < VLOT
★部分のkcbismでSTTとの大小関係をチェックしislarge 1となっている。挙動としては(1)と同じであった。
NSMTIO: kcbism: islarge 0 next 0 nblks 12 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbn kcbnwp 1 NSMTIO: kcbism: islarge 1 next 0 nblks 6260 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kc08 kcbnwp 1★ NSMTIO: NSMTIO:kkfdtsc:DirectRead: NSMTIO: kcbzib: Cache Scan triggered for tsn = 5, rdba=0x0302aeba, fn = 12, kobjd = 81291, block cnt = 0, nonconUE pre-warm = FALSE, prefetch = FALSE NSMTIO: kcbism: islarge 1 next 0 nblks 6154 type 2, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kc08 kcbnwp 1 NSMTIO: kxfrCancelDR: objn:0x13d8b flg:0x4601 size:6154 table:large cache:56608 affpct:80 NSMTIO: kcbivlo: nblks 6260 vlot 500 pnb 56608 kcbisdbfc 0 is_large 0 NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) NSMTIO: kcbdpc:DirectRead: tsn: 5, objd: 81291, objn: 81291
(4)VLOT < OBJECT SIZE
★部分のkcbivloでVLOTよりオブジェクトサイズが大きいためis_large 1となり、次のqertbFetchでダイレクトパスリードが選択されていることがわかる。Additional InfoでVLOTが283040ブロックであることがわかる。
NSMTIO: kcbism: islarge 1 next 0 nblks 300000 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: NSMTIO:kkfdtsc:DirectRead: Additional Information: Cache_Attribute_Set:No, SCN_Ascending_Scan: No, Event_10354_Set: No, SqlID: dg16qmpsbha21, Id: 0 NSMTIO: kcbism: islarge 1 next 0 nblks 6154 type 2, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: kxfrCancelDR: objn:0x13dc5 flg:0x4601 size:6154 table:large cache:56608 affpct:80 #insts:1 clu-cached:no arch:-1 direct-read:yes SmartIO:yes affinitized:no dw_scan:no very_large_thr:500 NSMTIO: kcbivlo: nblks 300000 vlot 500 pnb 56608 kcbisdbfc 0 is_large 1★ NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT]★ NSMTIO: Additional Info: VLOT=283040★
５－２．シリアルダイレクトパスリード
(1)STT < OBJECT SIZE
kcbismでSTTより小さいことがチェックされ、qertbFetchでダイレクトパスリードが選択されずマルチブロックリードとなっていることがわかる。
NSMTIO: kcbism: islarge 0 next 0 nblks 791 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcb2 kcbnwp 1 NSMTIO: kcbism: islarge 0 next 0 nblks 791 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcbnbh bnwp 1 NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 791 (blocks), Threshold: MTT(5807 blocks),★
(2)STT < OBJECT SIZE < MTT
STTよりオブジェクトが大きい場合は、qertbFetchでバッファキャッシュから読むコスト等がチェックされ、その結果、kcbdpcでDirectReadが選択されている。
NSMTIO: kcbism: islarge 1 next 0 nblks 1573 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcbnbh 58072 kcbnwp 1 NSMTIO: kcbism: islarge 1 next 0 nblks 1573 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcbnbh 58072 kcbnwp 1 NSMTIO: kcbimd: nblks 1573 kcbstt 1161 kcbnbh 5807 kcbisdbfc 3 is_medium 0 NSMTIO: kcbcmt1: scann age_diff adjts last_ts nbuf nblk has_val kcbisdbfc 0 15374 0 58072 1573 0 0 NSMTIO: kcbivlo: nblks 1573 vlot 500 pnb 58072 kcbisdbfc 0 is_large 0 NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)★ NSMTIO: kcbdpc:DirectRead: tsn: 5, objd: 81221, objn: 81221★
バッファキャッシュにオブジェクトが乗っている状態では、kcbcmt1で何らかのバッファキャッシュ上のオブジェクトのブロック数が評価され、qertbFetchでNoDirectReadが選択されている様子がわかる。
NSMTIO: kcbism: islarge 1 next 0 nblks 1572 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcbnbh 58072 kcbnwp 1 NSMTIO: kcbism: islarge 1 next 0 nblks 1572 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcbnbh 58072 kcbnwp 1 NSMTIO: kcbimd: nblks 1572 kcbstt 1161 kcbnbh 5807 kcbisdbfc 3 is_medium 0 NSMTIO: kcbcmt1: hit age_diff adjts last_ts nbuf nblk has_val kcbisdbfc cache_it 1538 43216 41678 58072 1572 1 0 1★ NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1572 (blocks), Threshold: MTT(5807 blocks),★
(3)MTT < OBJECT SIZE < VLOT
MTTより大きい場合はkcbimdのチェックが追加されており、ここでMTTとの大小関係がチェックされている。kcbnbhがMTTなのだろう。ただし挙動は(2)同様である。
NSMTIO: kcbism: islarge 1 next 0 nblks 6260 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcbnbh 58072 kcbnwp 1 NSMTIO: kcbism: islarge 1 next 0 nblks 6260 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1161 keep_nb 0 kcbnbh 58072 kcbnwp 1 NSMTIO: kcbimd: nblks 6260 kcbstt 1161 kcbnbh 5807 kcbisdbfc 3 is_medium 0★ NSMTIO: kcbivlo: nblks 6260 vlot 500 pnb 58072 kcbisdbfc 0 is_large 0 NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) NSMTIO: kcbdpc:DirectRead: tsn: 5, objd: 81239, objn: 81239 kcbdpc: kx 8 kc 8 lhs 4 rhs NSMTIO: Additional Info: VLOT=290360
(4)VLOT < OBJECT SIZE
★部分のkcbivloでVLOTよりオブジェクトサイズが大きいためis_large 1となり、次のqertbFetchでダイレクトパスリードが選択されていることがわかる。VLOTの判定後のトレースの出方がシリアルの場合と同じなので、VLOTの場合はシリアルもパラレルも同じコードパスなのだろう。
NSMTIO: kcbism: islarge 1 next 0 nblks 300000 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: kcbism: islarge 1 next 0 nblks 300000 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: kcbimd: nblks 300000 kcbstt 1132 kcbnbh 5660 kcbisdbfc 3 is_medium 0 NSMTIO: kcbivlo: nblks 300000 vlot 500 pnb 56608 kcbisdbfc 0 is_large 1★ NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT]★ NSMTIO: Additional Info: VLOT=283040
５－３．隠しパラメータによる制御の影響
_serial_direct_read（デフォルトauto）はシリアルダイレクトパスリードにおけるダイレクトパスの選択を制御することができる隠しパラメータである（セッションレベルで変更可能）。これをnever（ダイレクトパスリードを発生させない）、always（ダイレクトパスリードを発生させる）にそれぞれ指定した場合の挙動の違いをNSMTIOトレースで確認する。
(3)MTT < OBJECT SIZE < VLOT
"_serial_direct_read"=neverの場合、★部分でシリアルダイレクトパスリードが無効化され、マルチブロックリードが選択される挙動になっていることがわかる。
NSMTIO: kcbism: islarge 1 next 0 nblks 6260 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: kcbism: islarge 1 next 0 nblks 6260 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: kcbimd: nblks 6260 kcbstt 1132 kcbnbh 5660 kcbisdbfc 3 is_medium 0 NSMTIO: qertbFetch:NoDirectRead:[_SERIAL_DIRECT_READ Disabled]:★ NSMTIO: Additional Info: VLOT=283040
"_serial_direct_read"=alwaysの場合、★部分でシリアルダイレクトパスリードが有効化され、ダイレクトパスリードが選択される挙動になっていることがわかる。
NSMTIO: kcbism: islarge 1 next 0 nblks 6260 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: kcbism: islarge 1 next 0 nblks 6260 type 2, bpid 3, kcbisdbfc 0 kcbnhl 1024 kcbstt 1132 keep_nb 0 kcbnbh 56608 kcbnwp 1 NSMTIO: kcbimd: nblks 6260 kcbstt 1132 kcbnbh 5660 kcbisdbfc 3 is_medium 0 NSMTIO: qertbFetch:DirectRead:[_SERIAL_DIRECT_READ Always]: Additional Info: Object# = 81277, Object_Size = 6260 blocks★ NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 0, invalid table sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted: -1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks: -1, isQesSageEnabled: FALSE
６．まとめ
今回はダイレクトパスリードの発生条件を確認するために、NSMTIOトレースの取得方法と、パラレル・シリアルダイレクトパスリードの検証結果を紹介した。
検証の結果、パラレルダイレクトパスについては、概ね常にダイレクトパスリードが発生する状態になることがわかった。バッファキャッシュに100%乗っている場合に、まれにバッファキャッシュから読み込まれる挙動も確認できた。一方、シリアルダイレクトパスについては、STT以下ではマルチブロックリード、MTT以上ではダイレクトパスリードになることがわかった。STT～VLOTまではダイレクトパスリードの場合と、マルチブロックリードの場合があることが確認された。バッファキャッシュ上の状態が関係していることは確かであるが、正確な挙動を確認までは至っていない。いずれのケースもVLOTより大きい場合はダイレクトパスリードになることが確認できた。課題は以下のqertbFetchのロジックの解明である。この中にバッファキャッシュの状態含めたコスト計算が含まれているはずで、この中のロジックをトレースすることができれば、より詳細にダイレクトパスリードの発生条件を明らかにすることができると考える。
qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
備忘まで、今回検証で確認できたダイレクトパスリードとSTT、MTT、VLOTの関係とNSMTIOトレースの特徴的な出力を下図に示す。繰り返しになるが、これで正しい挙動をとらえているとは思っていない。不完全ながら公開する意図は、トレースを取ればここまではわかるということを伝えることで、アップグレード時の挙動の変化を確認したり、オブジェクトサイズの境界値を監視することで性能問題を未然に防いだり、不幸にも性能トラブルに巻き込まれた際に迅速な解析が可能となると考える。DBAの生活の質の向上の一助になれば幸いである。
以上
◆参考文献：
[1]When bloggers get it wrong - part 2, Roger Macnicol
[2]Expert Oracle Exadata, Martin Bach他, pp.50-53
[3]Optimizer statistics-driven direct path read decision for full table scans, Tanel Poder
2020-12-22 00:00
