http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/
の翻訳です。基本的に意訳です。意味をとりやすくするために追加した箇所については、《…》で囲っています。
本文書に関する指摘については、原文著者のTanel Poderさんではなく、渡部まで御連絡お願いいたします。
日本語訳：渡部 亮太 (WR at Csus4 dot net)
履歴
- 2013-01-21: 初稿up
Oracle geek、technology enthusiastsの皆さん、こんにちは！久しぶりですね 😉
Oracleのfull table scanとdirect path readに関するHacking Sessionで、私はdirect path readの決定がオプティマイザによって行われるのではないこと、それぞれの実行中、1つのセグメント（パーティション）が別々にスキャンされることを説明した。また、_small_table_thresholdパラメータとX$KCBOQH.NUM_BUF （任意のセグメントのバッファが現在どれだけキャッシュされているか追跡する《X$表》）が、direct path read を行うかどうかを決定するために、どのように使用されるかを説明した。
あなたが_small_table_thresholdパラメータが何であるか、direct path read決定にどのように関連するかを知らない場合は、Alex Fatkulinによるこのポストを 読むことを薦めます。
上記の言及した値に加えて、そのセグメント（のHWMより下）にスキャンしなければならないブロックがいくつあるかを、Oracleは知っている必要があります。これは、従来、（通常のバッファリングされるシングルブロック読み込み "db file sequential read"を使って）最初のセグメント?ヘッダー?ブロックを読み取ることによって行われていました。
Oracle Database 11.2.0.2から、少し動作が変わっています。セグメント?ヘッダーから抽出された実際のセグメントのブロック数の代わりに、Oracleは実際にTAB$.BLKCNT（dba_tables.blocks) またはTABPART$.BLKCNT、IND$.LEAFCNTなどからこの数値を取得して、direct path readを決定します。
これを制御する新しいパラメータ_direct_read_decision_statistics_drivenがあります：
SQL> @pd direct_read_decision
Show all parameters and session values from x$ksppi/x$ksppcv...
NAME?????????????????????????????????????????????? VALUE??? DESCRIPTION
-------------------------------------------------- -------- ----------------------------------------------------------
_direct_read_decision_statistics_driven??????????? TRUE???? enable direct read decision based on optimizer statistics- このパラメータがFALSEの場合、direct path readの決定は、セグメント?ヘッダーのブロック数（実際のブロック数）に基づいて行われます。
- TRUEの場合（11.2.0.2+ のデフォルト）、direct path readの決定は、いずれかの元表（TAB$、IND$）に格納されているブロック数 – オプティマイザ統計に基づいて行われます。
?
ブロック数はデータ?ディクショナリのオプティマイザ統計から取られていても、オプティマイザが、伝統的な意味におけるdirect path readパスの決定をしたのではない（コストベースの意思決定ではない）ことに注意してください。
（訳注：CBOによる見積もりコストが小さいため、direct path readが選択されているわけではないことが言いたいはず）
私のテスト用データベースからの例を示します。
SQL> CREATE TABLE t AS SELECT * FROM dba_source;
Table created.
SQL> @gts t
Gather Table Statistics for table t...
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT blocks FROM user_tables WHERE table_name = 'T';
BLOCKS
----------
10704
SQL>
テーブルは（HWMまで）10704ブロックを使用しています。
SQL> SELECT name,block_size,buffers FROM v$buffer_pool;
NAME BLOCK_SIZE BUFFERS
-------------------- ---------- ----------
DEFAULT 8192 9424
テーブル（10704ブロック）は、バッファ?キャッシュ全体（9424バッファ）よりも大きいサイズになっています。そして、この値は、インスタンスの_small_table_thresholdの値 168よりもずっと大きい。（このパラメータについてのhacking session full table scanとdirect pathの読み取り を見て欲しい）
このため、SELECT COUNT（*） FROM tを実行するといつでも、direct path read が現れます。
SQL> @snapper ash,stats=sw,sinclude=table.scan|gets 5 1 99 Sampling SID 99 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v3.54 by Tanel Poder ( http://blog.tanelpoder.com ) ---------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH ---------------------------------------------------------------------------------------------------------------------------------------- 99, SYS , STAT, db block gets , 1, .2, 99, SYS , STAT, db block gets from cache , 1, .2, 99, SYS , STAT, consistent gets , 11867, 2.37k, 99, SYS , STAT, consistent gets from cache , 7, 1.4, 99, SYS , STAT, consistent gets from cache (fastpath) , 6, 1.2, 99, SYS , STAT, consistent gets - examination , 1, .2, 99, SYS , STAT, consistent gets direct , 11860, 2.37k,99, SYS , STAT, no work - consistent read gets , 11859, 2.37k, 99, SYS , STAT, cleanouts only - consistent read gets , 1, .2, 99, SYS , STAT, table scans (long tables) , 1, .2, 99, SYS , STAT, table scans (direct read) , 1, .2,99, SYS , STAT, table scan rows gotten , 739834, 147.97k, 99, SYS , STAT, table scan blocks gotten , 11860, 2.37k, 99, SYS , TIME, parse time elapsed , 46, 9.2us, .0%, | | 99, SYS , TIME, DB CPU , 79988, 16ms, 1.6%, |@ | 99, SYS , TIME, sql execute elapsed time , 254990, 51ms, 5.1%, |@ | 99, SYS , TIME, DB time , 255375, 51.08ms, 5.1%, |@ | 99, SYS , WAIT, enq: KO - fast object checkpoint , 174947, 34.99ms, 3.5%, |@ | 99, SYS , WAIT, direct path read , 1280, 256us, .0%, | |99, SYS , WAIT, SQL*Net message to client , 9, 1.8us, .0%, | | 99, SYS , WAIT, SQL*Net message from client , 4672912, 934.58ms, 93.5%, |@@@@@@@@@@| 99, SYS , WAIT, events in waitclass Other , 6, 1.2us, .0%, | | -- End of Stats snap 1, end=2012-09-02 20:03:55, seconds=5 --------------------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS --------------------------------------------------------------------------------- 2% | 88r4qn9mwhcf5 | enq: KO - fast object checkpoint | Application 2% | 88r4qn9mwhcf5 | ON CPU | ON CPU -- End of ASH snap 1, end=2012-09-02 20:03:55, seconds=5, samples_taken=43
では、_small_table_thresholdの値よりも小さい5ブロックだけがあるかのように、
テーブルの統計情報をfakeしてみます。
SQL> EXEC DBMS_STATS.SET_TABLE_STATS(user,'T',numblks=>5);
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM t /* attempt 2 */;
COUNT(*)
----------
1000
direct path readはなくなります – ほら、通常のバッファ読み出しが実行されています！
SQL> @snapper ash,stats=sw,sinclude=table.scan|gets 5 1 99 Sampling SID 99 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v3.54 by Tanel Poder ( http://blog.tanelpoder.com ) ---------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH ---------------------------------------------------------------------------------------------------------------------------------------- 99, SYS , STAT, db block gets , 1, .17, 99, SYS , STAT, db block gets from cache , 1, .17, 99, SYS , STAT, consistent gets , 11865, 1.98k, 99, SYS , STAT, consistent gets from cache , 11865, 1.98k,99, SYS , STAT, consistent gets from cache (fastpath) , 11528, 1.92k, 99, SYS , STAT, consistent gets - examination , 1, .17, 99, SYS , STAT, no work - consistent read gets , 11851, 1.98k, 99, SYS , STAT, cleanouts only - consistent read gets , 1, .17, 99, SYS , STAT, table scans (long tables) , 1, .17, 99, SYS , STAT, table scan rows gotten , 738834, 123.14k, 99, SYS , STAT, table scan blocks gotten , 11852, 1.98k, 99, SYS , TIME, parse time elapsed , 84, 14us, .0%, | | 99, SYS , TIME, DB CPU , 109983, 18.33ms, 1.8%, |@ | 99, SYS , TIME, sql execute elapsed time , 116709, 19.45ms, 1.9%, |@ | 99, SYS , TIME, DB time , 117102, 19.52ms, 2.0%, |@ | 99, SYS , WAIT, db file scattered read , 63956, 10.66ms, 1.1%, |@ |99, SYS , WAIT, SQL*Net message to client , 8, 1.33us, .0%, | | 99, SYS , WAIT, SQL*Net message from client , 5119722, 853.29ms, 85.3%, |@@@@@@@@@ | -- End of Stats snap 1, end=2012-09-02 20:06:19, seconds=6 --------------------------------------------------------------------------------- Active% | SQL_ID | EVENT | WAIT_CLASS --------------------------------------------------------------------------------- 2% | 07sgczqj432mr | db file scattered read | User I/O -- End of ASH snap 1, end=2012-09-02 20:06:19, seconds=5, samples_taken=46
（"attempt 2"というコメントをつけて）意図的に、新しいカーソルをコンパイルするハード解析を強制していることに注意してください。_direct_read_decision_statistics_drivenパラメータは、オプティマイザ環境の一部ではありません。よって、パラメータを変更しても自動的に新しい子カーソルは、作成されません。（ちなみに、_small_table_thresholdと_serial_direct_readパラメータにも同じことが言えます）しかし、SQLテキストを変更すれば、とにかく全く新しい（親と子）カーソルはコンパイルされます。
ですが、ちょっと待ってほしい。なぜOracleにオプティマイザ統計から新しいブロックカウント値を読み出させるために、新しいカーソルをコンパイルする必要があるのでしょうか？！
direct path readの決定は、オプティマイザによって行われるのではなく、 クエリ実行中に（個々のパーティションを含む）あるセグメントがスキャンされるたびに、毎実行ランタイムで決定されると、この記事の冒頭（や他の多くの箇所）で述べている。これ — direct pathの決定がセグメント?ヘッダーに記録された実際に存在する現在のブロック数に基づいて行われる — は、（11.2.0.1までの）古いOracleバージョンに当てはまります。
よって、_small_table_threshold計算しきい値を超えるような数ブロックのセグメント拡張により、急に決定が変更される場合があります。パフォーマンスの安定性の理由などのため、この動作は変更されたように思われます。
11.2.0.2での私のテストで示される限りにおいては、新しい統計駆動のdirect path readの決定ロジックを使用している場合、たとえオプティマイザ統計におけるセグメントのブロック数がかわったとしても、個々のセグメントのブロック数が、コンパイルされたカーソルのどこかに格納されて、次の実行時に再利用されている《ことがわかっている》。あなたは新しい統計を収集していないかぎり、- バッファ?キャッシュ?サイズ（と既にキャッシュされたブロック数）を変更しないかぎり- これはやや良好な安定性をもたらすかもしれません 。（たとえば、他の、インデックス?ベースのアクセスに起因して）セグメントのキャッシュされたブロック数が変化した場合、実行時にdirect pathの決定が?変わる可能性があります。ブロック数はカーソルに格納されていますが、決定に影響を与えるその他の要因（バッファ?キャッシュ?サイズ、キャッシュされたブロック数）が、《direct path readを使用する決定を》変更する可能性があるためです。
スマートスキャンの動作は、セグメントのフルスキャンにdirect path read IOメソッドが選ばれるかどうかに完全に依存しているため、このトピックは特にExadataに関連しています。決定に関与する複数の動的な部分と要因があるため、これについて実験するときは、（間違った結論を導かないよう）かなり慎重かつ徹底的であることが必要になります。：
- ブロック数は、オプティマイザ統計またはセグメント?ヘッダーいずれから取得されるか
- セグメント?ヘッダーおよび/またはオプティマイザ統計のブロック数を変更するか
- バッファ?キャッシュ?サイズを変更するか（もちろん_small_table_threshold）も）
- セグメントのキャッシュされたブロックの量を変更するか
- パラレル実行か、シリアル実行か
- バッファリングされるパラレルフルスキャンが許されているか（11.2のメモリ内のPX機能）
- 新しい子カーソルが作成されたか、古いカーソルを再利用したか
- など