2022.05.30
11g – Serial direct read 動作原理
11g – Serial direct read 動作原理
㈱エクセンコンサルティング本部/DBコンサルティングチームイム・ギョンソク
概要
Oracle 11gで最初に導入されたSerial direct readは、大量のデータを処理するエクサデータで非常に重要な機能です。
スマートスキャンを行うためには、必ずテーブルフルスキャンとdirect path readが先行しなければなりません。
しかし、 serial direct read の動作はオプティマイザで環境に影響を受けないため、ヒントを適用して制御することができず、意図通りに動作しない、または途中で変更されることもあります。
それでも、それはランダムに動作するわけではなく、いくつかの規則があります。 私は、serial direct readがどのような規則によって決定され、変更されるかをいくつかのケースで詳しく紹介したいと思います。
Serial direct readとは？
DW環境では、大量のデータを照会することが頻繁に発生します。
したがって、テーブルやインデックスに対するフルスキャンを避けることはできません。
Oracleでは、フルスキャンは、複数のブロックを一度にバッファキャッシュに読み込むマルチブロックI / Oを介して処理されます。
マルチブロックI / Oは、サイズが制限されたバッファキャッシュを介して行われるため、内部的に複雑なプロセスが必要であり、バッファキャッシュにかなりの負担となる可能性があります。 ブロックを読むフリーリストを検索してバッファを変更するたびにラッチを取得し、cache buffers chains latch、cache buffers lru chain latch などのイベントが発生する可能性があります。 これらのブロックは、LRUリストの末尾にあるが他のセッションが必要とするキャッシュされたブロックをage outし、既に変更されたブロックに対して大量のCRコピーを生成することになります。
したがって、これらの理由から、一般にテーブルをフルスキャンするときにバッファキャッシュを経ないPQ（Parallel Query）が多用されています。 PQスレーブセッションはdirect path readを介してPGAにデータを直接読み込むため、バッファキャッシュへの負担を軽減し、パフォーマンスにも効果的です。
しかし、過度のPQ使用は、CPU、MEMORY使用率の増加、および頻繁なチェックポイントのために、むしろシステム負荷を増大する可能性があります。
Oracleは、Parallelオプションを使用せずにdirect readが可能なSDPR（serial direct path read）を11gで初めて採用しました。 SDPRは、テーブルのフルスキャンやインデックスの高速フルスキャン時にシングルモードでバッファキャッシュを経ずにブロックを直接読み込むことを言います。 実際、これらの機能はOracle 10Gにもありましたが、本格的に適用されたのは11gからになります。
特に、１１ｇｒ２以降では、いくつかのパラメータを介して適切な制御も可能となりました。
serial direct read は、隠しパラメータ _serial_direct_read と定義します。 10Gではデフォルト値がfalseでしたが、11gr2からautoに変更され、Oracleが適切にdirect readを適用できるようになります。
_serial_direct_read オプションは true、false、auto、always、never で大きく 5 つで定義できます。 デュアルtrue、alwaysはフルスキャンするすべてのセグメントを常にSDPRで処理し、逆にneverはいずれの場合も処理しません。 しかし、falseの場合はneverと同じ意味に見えますが、むしろautoと同様に動作します。
NAME VALUE DESCRIPTION
———————————————————–
_serial_direct_read AUTO enable direct read in serial
どのように動作しますか？
データベースに存在するすべてのテーブルがSDPRの対象となるわけではありません。
Oracleは、SDPR対応テーブルの選択基準をHiddenパラメータ_small_table_thresholdに定義しました。 デフォルト値は
_db_block_buffers の2%としてこれより小さいと、Oracleが小さなテーブルとして認識するという意味です。
10gでは、テーブルをフルスキャンするとき、小さなテーブルはLRUリストの真ん中に位置し、大きなテーブルはLRUリストの一番端に位置するため、age outになりやすく、physical readが発生する確率が高いことを意味しました。 ただし、11gr2では、対応するパラメータはdirect readが可能なテーブルサイズの最小しきい値として定義されます。
NAME DESCRIPTION
————————- ————————————————–
_small_table_threshold lower threshold level of table size for direct reads
テーブルをフルスキャンすると、まずテーブルのセグメントヘッダブロックを読み、HWM以下を占めるブロックがいくつなのかを照会します。
ブロックの数が_small_table_thresholdより大きい場合はdirect path readで処理され、それより小さい場合はバッファキャッシュを介したマルチブロックI / Oによってdb file scattered readとして処理されます。
11gR2以前は、テーブルのサイズが_small_table_threshold設定値の5倍以上でなければなりませんでした。しかし、11gR2以降は、_small_table_threshold値とテーブルのサイズがほぼ同じになる瞬間に発生します。
また、テーブルの全ブロック数と比較してバッファキャッシュにキャッシュされているテーブルのブロックまたはダーティブロックが占める割合によっても異なります。
特定のテーブルが占めるブロック全体の50％以上がバッファキャッシュにキャッシュされている場合、または25％以上がダーティ状態でキャッシュされている場合、テーブルのブロック全体が_small_table_thresholdより大きくてもSDPRは発生しません。
これは、SDPRがオプティマイザによるものではなく、実際のセグメントブロックを照会するランタイム時に決定されるためです。したがって、テーブルのサイズ、バッファキャッシュのサイズ、バッファキャッシュにキャッシュされたテーブルのブロック数を考慮して、SDPRの可否はいつでも変更でき、より安定した性能を保証できます。
11gR2以降には、これとは異なる一つの方式が追加されます。
ＳＤＰＲの可否を実際のテーブルのブロック数を照会して決定したことをテーブルの統計情報に格納されたブロック数（ｄｂａ＿ｔａｂｌｅｓ．ｂｌｏｃｋｓ）で決定するものです。
これはHiddenパラメータ_direct_read_decision_statistics_drivenで制御でき、デフォルトは統計情報のブロックを参照する場合、統計情報が変わらない限り、テーブルのサイズに変化があってもSDPR決定に影響を与えることはありません。
したがって、統計情報を活用してＳＤＰＲをより柔軟に適用することができます。
しかし、バッファキャッシュにキャッシュされたテーブルのブロック数や状態によっては依然として影響を受けます。
serial direct read 登場背景にはエクサデータと密接な関連があります。
おそらく、エクサデータの主な機能であるスマートスキャンがテーブルをdirect readでフルスキャンするときに可能だからです。
SSDフラッシュディスクの活用が増加するにつれて、フラッシュディスクを介した直接読み取りはI / O速度をさらに一歩進めました。 したがって、serial direct read を適切に使用すると、パフォーマンスに大きく役立つことがあります。
- 表サイズの変化によるシリアル・リード。
_small_table_thresholdを基準にSDPRが決定されるテーブルの最小サイズはどのように決まるかどうかテストで見てみましょう。
1つの注意は、Oracle 11gR2の場合、正確なテストのためにテーブル統計情報を生成しないか、または_direct_read_decision_statistics_driven = falseにして統計情報ブロックでSDPRを決定しないようにする必要があります。
テストに使用されるテーブルは、1つのブロックに1つの行のみを格納するように作成し、テーブルサイズの変化に応じてSDPRの発生の有無を測定できるプロシージャを作成します。
— Oracleバージョンの確認
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production
— テストテーブルの作成。
SQL> create table sdpr_test (col varchar2(100)) pctused 1 pctfree 99 tablespace users ;
— テーブルのサイズを測定するためのプロシージャの作成。
create or replace procedure SDPR_PRO( p_start in number default 0, p_step in number,
p_stop in number default null
) is
b_prd number; b_prd1 number; b_prd2 number; b_blocks number:=0;
b_start number:=p_start; b_cnt number:=0 ; b_result number;
begin
dbms_output.enable(buffer_size=>10000); execute immediate ‘truncate table sdpr_test’;
select value into b_prd1
from v$sesstat st, v$statname sn where st.statistic#=sn.statistic#
and sn.name = ‘physical reads direct’ and st.sid = userenv(‘sid’); loop
insert /*+ append */ into sdpr_test select rpad(‘*’, 100, ‘*’)
from dual
connect by level <= p_step + b_start; commit;
b_blocks:=b_blocks + p_step + b_start; b_start:=0;
execute immediate ‘alter system flush buffer_cache’;
select /*+ full(sdpr_test) */ count(*) into b_cnt from sdpr_test; select value into b_prd2
from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = ‘physical reads direct’ and st.sid = userenv(‘sid’);
b_prd:=b_prd2 – b_prd1 ;
exit when (b_prd > 0 or b_blocks > nvl(p_stop, b_blocks)); end loop;
b_result:=b_blocks-p_step;
dbms_output.put_line(chr(10)||’Block count for SDPR: ‘||b_result||’ blocks’); end;
/
— buffer.sql
accept obj prompt’enter the object name :’ SELECT o.owner ,
o.object_name ,
decode( state , 0 , ‘free’ , 1 , ‘xcur’ , 2 , ‘scur’ , 3 , ‘cr’ , 4 ,
‘read’ , 5 , ‘mrec’ , 6 , ‘irec’ , 7 , ‘write’ , 8 , ‘pi’ ) state , decode( bitand( flag , 1 ) , 0 , ‘N’ , ‘Y’ ) dirty ,
decode( bitand( flag , 16 ) , 0 , ‘N’ , ‘Y’ ) temp ,
decode( bitand( flag , 1536 ) , 0 , ‘N’ , ‘Y’ ) ping,
decode( bitand( flag , 16384 ) , 0 , ‘N’ , ‘Y’ ) stale,
decode( bitand( flag , 65536 ) , 0 , ‘N’ , ‘Y’ ) direct, COUNT( * ) blocks
FROM sys.xm$bh b ,
dba_objects o
WHERE b.obj = o.data_object_id AND b.ts# >= 0
AND o.object_name = UPPER( LTRIM( RTRIM( ‘&obj’ ) ) ) AND state = 1
GROUP BY o.owner ,
o.object_name , state ,
decode( bitand( flag , 1 ) , 0 , ‘N’ , ‘Y’ ) ,
decode( bitand( flag , 16 ) , 0 , ‘N’ , ‘Y’ ) ,
decode( bitand( flag , 1536 ) , 0 , ‘N’ , ‘Y’ ) ,
decode( bitand( flag , 16384 ) , 0 , ‘N’ , ‘Y’ ) ,
decode( bitand( flag , 65536 ) , 0 , ‘N’ , ‘Y’ ) , blsiz ;
— _ small_table_threshold を 1000 に設定.
SQL> alter session set “_small_table_threshold”=1000 ;
— sdpr_proプロシージャの実行
SQL> exec sdpr_pro(p_start=>800,p_step=>1,p_stop=>1500); Block count for SDPR: 976 blocks
— バッファキャッシュ状態照会.
SQL> @buffer
enter the object name :sdpr_test
| OWNER OBJECT_NAM STATE | DIRTY | TEMP | PING | STALE | DIREC | BLOCKS |
| ——– ———- —– | —– | —– | —– | —– | —— | —— |
| SYS SDPR_TEST xcur | N | N | N | N | N | 1 |
_small_table_threshold を 1000 とし、SDPR_TEST テーブルのブロック数が 800 ブロックから１ブロックずつ増加して1500ブロックまで大きくなるようにプロシージャを実行した結果、テーブルのサイズが976ブロックを1ブロック超過する977ブロックになる瞬間、最初にdirect readが発生します。
これは_small_table_thresholdが1000の場合、テーブルサイズの最小しきい値は976ブロックであることを意味します。 したがって、HWMまでのサイズが976ブロックを超えるテーブルをフルスキャンする場合、direct path readで実行できるという意味です。
テストセッションに10046トレースをかけてもう少し詳しく見てみると、一番最初にdb file sequential readイベントを待機しながらfile＃= 4、block＃= 17098ブロックを読み取ることを確認できます。該当ブロックは、ＳＤＰＲ＿ＴＥＳＴテーブルのセグメントヘッダブロックで、これを通じてテーブルが占める実際のＨＷＭ以下までのブロック数を問い合わせます。
テーブルの実際のブロック数を_small_table_threshold値と比較してSDPRを実行することが決定された場合、次にダーティ状態のブロックをディスクに反映するチェックポイントが作成され、enq：KO – fast object checkpointイベントを待機します。
チェックポイントはSDPR_TESTテーブルに限り非常に短時間で行われ、チェックポイントが終了するとdirect path readでテーブルをフルスキャンすることになります。
実際のバッファキャッシュ状態をx $ bhビューで照会すると、SDPR_TESTテーブルの全ブロックのうちセグメントヘッダに対応するブロック1つだけがバッファキャッシュに存在することが分かります。
— 10046セッショントレース
WAIT #140630657766376: nam=’db file sequential read’ ela= 9 file#=4 block#=17098 blocks=1 obj#=80219 tim=1362977906994976
WAIT #140630657766376: nam=’db file sequential read’ ela= 6 file#=3 block#=192 blocks=1 obj#=0 tim=1362977906995033
WAIT #140630657766376: nam=’reliable message’ ela= 71 channel context=2736958984 channel handle=2736907704 broadcast message=2738048368 obj#=0 tim=1362977906995246
WAIT #140630657766376: nam=’enq: KO – fast object checkpoint’ ela= 483 name|mode=1263468550 2=65562 0=1 obj#=0 tim=1362977906995753
WAIT #140630657766376: nam=’asynch descriptor resize’ ela= 0 outstanding #aio=0 current aio limit=412 new aio limit=442 obj#=0 tim=1362977906995790
WAIT #140630657766376: nam=’direct path read’ ela= 24 file number=4 first dba=17099 block cnt=5 obj#=80219 tim=1362977906995941
WAIT #140630657766376: nam=’direct path read’ ela= 39 file number=4 first dba=16080 block cnt=8 obj#=80219 tim=1362977906996015
WAIT #140630657766376: nam=’direct path read’ ela= 96 file number=4 first dba=16089 block cnt=15 obj#=80219 tim=1362977906996142
WAIT #140630657766376: nam=’direct path read’ ela= 43 file number=4 first dba=16105 block cnt=15 obj#=80219 tim=1362977906996213
WAIT #140630657766376: nam=’direct path read’ ela= 9 file number=4 first dba=16121 block cnt=7 obj#=80219 tim=1362977906996261
WAIT #140630657766376: nam=’direct path read’ ela= 11 file number=4 first dba=17024 block cnt=8 obj#=80219 tim=1362977906996297
— バッファキャッシュ状態の照会。
SQL> SELECT o.owner ,
- o.object_name ,
| 4 blsiz , 5 dbarfil , 6 dbablk 7 FROM sys.xm$bh b , 8 dba_objects o 9 WHERE b.obj = o.data_object_id 10 AND b.ts# >= 0 11 AND o.object_name = ‘SDPR_TEST’ 12 AND state = 1; |
decode( state , 0 , ‘free’ , 1 , ‘xcur’ , 2 , ‘scur’ , 3 , ‘cr’ , 4 , ‘read’ , 5 , ‘mrec’ , 6 , ‘irec’ , 7 , ‘write’ , 8 , ‘pi’ ) state ,
OWNER OBJECT_NAM STATE BLSIZ DBARFIL DBABLK
——————————————————-
SYS SDPR_TEST xcur 8192 4 17098
— SDPR_TEST表のセグメントヘッダーブロック照会。
SQL> select segment_name, header_file, header_block
2 from dba_segments
3 where segment_name = ‘SDPR_TEST’;
| SEGMENT_NAME | HEADER_FILE | HEADER_BLOCK |
| ————— | ————- | ————- |
| SDPR_TEST | 4 | 17098 |
（2）テーブル内のキャッシュされたブロック数の変化に応じたシリアルダイレクトリード。
SDPR_TESTテーブルが977ブロック以上大きくなっても常にSDPRで行われるわけではありません。
SDPR_TESTテーブルが現在のバッファキャッシュにどれだけキャッシュされているかによって、SDPRかどうかは異なります。
テーブルの全ブロックの50％以上がバッファキャッシュにすでにキャッシュされている場合、SDPRは発生しなくなります。
テーブル全体ブロックをディスクから直接読み出すよりもキャッシュされているブロックを読み取る方が有利だと判断しています。
それでは、SDPR_TESTテーブルがバッファキャッシュにどの程度キャッシュされたときにSDPRが発生しないかをテストしてみましょう。
まず、バッファキャッシュにブロックをキャッシュするためのプロシージャを生成します。 SDPR_TESTテーブル300ブロックをあらかじめキャッシュした後、キャッシュされたブロックが1つずつ増えるようにプロシージャを実行すると、ブロックがキャッシュされたブロックが数個あるときにSDPRが停止することを確認してみましょう。
–インデックスの作成。
SQL> create index idx_sdpr_test on sdpr_test (1) tablespace users;
— プロシージャの作成。
create or replace procedure cached_sdpr_pro( p_start in number default 0,
p_step in number default 1
) is
b_v varchar2(100); b_trsh number:=0;
b_prd number; b_prd1 number; b_prd2 number; b_cnt number:=0;
b_start number:=p_start; cursor b_cur is
select /*+ index(sdpr_test idx_sdpr_test) */ * from sdpr_test ; begin
dbms_output.enable(buffer_size=>10000);
execute immediate ‘alter system flush buffer_cache’;
select value into b_prd1
from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = ‘physical reads direct’ and st.sid = userenv(‘sid’);
open b_cur;
loop
for i in 1 .. p_step+b_start loop fetch b_cur into b_v;
end loop; b_trsh:=b_trsh+p_step+b_start; b_start:=0;
select /*+ full(sdpr_test) */ count(*) into b_cnt from sdpr_test ; select value into b_prd2
from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = ‘physical reads direct’ and st.sid = userenv(‘sid’); b_cnt:=b_prd2 – b_prd1 ;
exit when b_cnt=b_prd or b_cur%notfound ; b_prd:=b_cnt;
end loop; close b_cur;
dbms_output.put_line(chr(10)||’Block count for impossible sdpr : ‘||b_trsh||’ blocks’);
end;
/
— _ small_table_threshold を 1000 に設定.
SQL> alter session set “_small_table_threshold”=1000 ;
— dynamic samplingを無効にする
SQL> alter session set optimizer_dynamic_sampling=0;
— プロシージャの実行
SQL> exec cached_sdpr_pro(p_start=>300, p_step=>1);
Block count for impossible sdpr : 497 blocks
プロシージャ実行結果 SDPR_TEST テーブル全体ブロックのうち 50% (497/977*100 = 50.8 ) である 497 個のブロックがキャッシュされる時点で、これ以上 SDPR は発生しません。 SDPR_TESTテーブルをフルスキャンするとき、バッファキャッシュにすでにキャッシュされたブロック数が497個以上存在する場合、direct path readではなくバッファキャッシュを経由するdb file scattered read で処理されます。
ここでの注意点は、テストに使用されたテーブルには統計情報が存在しないため、プロシージャを初めて実行するときにダイナミックサンプリングが発生する可能性があることです。
ダイナミックサンプリングが実行されると、テーブルをサンプリングする過程でブロックの一部がバッファキャッシュにあらかじめキャッシュされたままプロシージャが実行され、497より小さい結果値が出ることがあります。
したがって、正確な結果値を得るためにプロシージャをもう一度実行するか、dynamic samplingにならないようにoptimizer_dynamic_samplingパラメータレベルを ‘0’に変更する必要があります。
(3) キャッシュされたダーティブロック数変化によるシリアルダイレクトリードの決定
テーブルのブロックの25％以上がダーティブロック状態でキャッシュされていても、SDPRは発生しません。
前述のように、direct readはオブジェクト単位のチェックポイントを伴うため、テーブル内のすべてのダーティブロックはチェックポイントの対象になります。
したがって、２５％以上存在するダーティブロックに対してチェックポイントを適用した後、ディスクから再びブロックを読み出すよりも、既にキャッシュされている状態のブロックを読み取ることが有利であると判断することができます。
それでは、実際にいくつかのダーティブロックがキャッシュされるSDPRがもはや発生しないかどうかをテストしましょう。
ダーティブロックがバッファキャッシュにキャッシュされるようにプロシージャを作成した後、どの時点でSDPRが停止するかをプロシージャを実行した結果値を確認してみましょう。
– ダーティブロックを生成するためのプロシージャの生成。
create or replace procedure dirty_sdpr_pro ( p_start in number default 0,
p_step in number,
p_stop in number default null
) is
b_trsh number:=0; b_prd number:=0; b_prd1 number:=0; b_prd2 number:=0; b_cnt number:=0;
b_start number:=p_start; begin
dbms_output.enable(buffer_size=>10000);
execute immediate ‘alter system flush buffer_cache’;
select value into b_prd1
from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = ‘physical reads direct’ and st.sid = userenv(‘sid’);
loop
b_trsh:=b_trsh+p_step+b_start;
update sdpr_test set col=col where rownum <= b_trsh; commit;
b_start:=0;
select /*+ full(sdpr_test) */ count(*) into b_cnt from sdpr_test ; select value into b_prd2
from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = ‘physical reads direct’ and st.sid = userenv(‘sid’);
b_cnt:= b_prd2 – b_prd1 ;
exit when b_cnt=b_prd or b_trsh > nvl(p_stop, b_trsh); b_prd:=b_cnt;
end loop;
dbms_output.put_line(chr(10)||’Block count for impossible sdpr : ‘||b_trsh||’ blocks’);
end;
/
— _ small_table_threshold を 1000 に設定します。
SQL> alter session set “_small_table_threshold”=1000 ; SQL> alter session set optimizer_dynamic_sampling=0 ;
— プロシージャの実行
SQL> exec dirty_sdpr_pro(p_start=>50, p_step=>1, p_stop=>1500) ; Block count for impossible sdpr : 245 blocks
SQL> @buffer
enter the object name :sdpr_test
OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS
——- ———– —— ——- —— —— —— ——- ——
| SYS | SDPR_TEST | xcur | Y | N | N | N | N | 245 |
| SYS | SDPR_TEST | xcur | N | N | N | N | N | 733 |
プロシージャの実行結果、SDPR_TESTテーブル全体ブロックの25％（245/977 * 100 = 25）の245
のダーティブロックがキャッシュされた時点で、もはやSDPRは発生しません。
x$bhビューを照会してみると、SDPR_TESTテーブルのダーティブロックが正確に245個キャッシュされる時点でdirect path readが停止し、全体977ブロックのうち残りの733ブロックがマルチブロックi/oによってキャッシュされたことを確認することができます。
(4) 統計情報 と serial direct read
Oracle 11gR2以降は、オプティマイザ統計情報に保存されているtab$blkcnt、tabpart$.blkcnt、
ind$.leafcnt を参照して serial direct read かどうかが決定されます。
統計情報を適用してSDPRを決定するには、_direct_read_decision_statistics_driven隠しパラメータがtrueでなければなりません。
このパラメータは11gr2で新しく追加され、デフォルト値はtrueです。 これは11gr2以降は基本的に統計情報を参照してSDPRを決定するという意味があります。 統計情報に格納されたテーブルの blocks が _small_table_threshold 値より大きい場合は SDPR で処理され、小さい場合はバッファキャッシュを経由するマルチブロック I/O として処理されます。
NAME VALUE DESCRIPTION
——————————————- ———– ————————
_direct_read_decision_statistics_driven TRUE enable direct read
decision based on optimizer statistics
統計情報が存在するテーブルの場合、テーブルの全ブロック数に大きな変化があっても、SDPRは途中で変更されません。
SDPRの判断基準は、もはやテーブルの実際のブロック数によっては適用されないからです。
しかし、テーブルの統計情報が新たに更新されたり、バッファキャッシュにキャッシュされたテーブルブロック数とダーティブロックの数に変化が生じた場合、ＳＤＰＲの可否はいつでも変更することができます。
これは、前述のように、SDPR決定がオプティマイズされ、環境ではなくランタイム時に決定されるためです。
テーブル統計情報のブロックを_small_table_threshold値より大きく設定して、テーブルをフルスキャンするときに常にSDPRで実行し、テーブルのサイズ、バッファキャッシュの状態に応じてSDPRがどのように適用されるかをテストしてみましょう。
— _small_table_threshold を 1000 に設定します。
SQL> alter session set “_small_table_threshold” = 1000 ;
— SDPR_TEST テーブル統計情報の blocks を _small_table_threshold より大きい 1100 に設定
SQL>EXECDBMS_STATS.SET_TABLE_STATS(ownname=>’SYS’,tabname=>’SDPR_TEST’,numblks=> 1100,no_invalidate=>false);
— _sdpr_proプロシージャの実行。
SQL> exec sdpr_pro(p_start=>0,p_step=>1,p_stop=>1500); Block count for SDPR: 0 blocks
SQL> @buffer
enter the object name :sdpr_test
OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS
—— ———— ——- —— —– —– —– —— ——- SYS SDPR_TEST xcur N N N N N 1
SQL> select statistic_name, value
- from v$segment_statistics
- where owner=user
- and object_name=’SDPR_TEST’
- and statistic_name=’physical reads direct’;
STATISTIC_NAME VALUE
————————————————— ———-
physical reads direct 1
テーブル統計情報の blocks を small_table_threshold より大きい 1100 とし、テーブルサイズが 0 から 1 ブロックずつ増加して 1500 ブロックになるまで sdpr_pro プロシージャを実行すると、0 ブロックを超過する時点である実際のテーブルサイズが 1 ブロック以上 になるとSDPRが最初に発生します。
統計情報の blocks が _small_table_threshold 値より大きいので 1 ブロックを読み取るときも SDPR で処理されることがあります。
逆に、テーブル統計情報の blocks が _small_table_threshold 値より小さい場合、テーブルがどんなに大きくても SDPR は発生しません。
したがって、データの変化量が大きいテーブルほど、現在の状態に合わせて統計情報を生成することが重要です。
今回は、バッファキャッシュにキャッシュされたテーブルのブロック数とダータブロックの数が変更された場合、SDPRがどのように処理されるかを見てみましょう。
— テーブル 2000 ブロックの生成。
SQL> truncate table sdpr_test;
SQL> insert /*+ append */ into sdpr_test 2 select rpad(‘*’, 100, ‘*’)
- from dual
- connect by level <= 2000; 2000 rows created.
— 統計情報の生成
SQL> exec dbms_stats.gather_table_stats(ownname=>’SYS’, tabname=>’SDPR_TEST’, method_opt=>’FOR ALL COLUMNS SIZE 1′, no_invalidate=>false);
SQL> select owner,table_name,blocks,num_rows
- from dba_tables
- where table_name = ‘SDPR_TEST’;
| OWNER | TABLE_NAME | BLOCKS NUM_ROWS |
| ——– | ————————— | ———- ——— |
| SYS | SDPR_TEST | 2040 2000 |
— テーブルキャッシュプロシージャを実行します。
SQL> alter session set “_small_table_threshold” = 1000 ;
SQL> exec cached_sdpr_pro(p_start=>300, p_step=>1); Block count for impossible sdpr : 1990 blocks
SQL> @buffer
enter the object name :sdpr_test
OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS
——————- ——- —– —— —– —— —— ——- SYS SDPR_TEST xcur N N N N N 2001
— ダーティブロック生成プロシージャの実行。
SQL> exec dirty_sdpr_pro(p_start=>50, p_step=>1, p_stop=>1500) ; Block count for impossible sdpr : 990 blocks
SQL> @buffer
enter the object name :sdpr_test
OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS
——————- ——- —– —— —– —— —— ——- SYS SDPR_TEST xcur Y N N N N 990
SYS SDPR_TEST xcur N N N N N 1011
SDPR_TESTテーブルにブロック2000個を作成して統計情報を収集すると、統計情報がないときとは若干異なる結果が表示されます。 統計情報がない場合は、バッファキャッシュにSDPR_TESTテーブルブロックが50%以上キャッシュされたり、ダーティブロックが25%以上キャッシュされた場合、SDPRが停止しました。 もちろん、バッファキャッシュとテーブルのサイズによっては若干の違いがありますが、それでもほぼ同じレベルで停止しました。 しかし、統計情報を参照する場合、テーブルのキャッシュされたブロック数がテーブルのサイズとほぼ同じ１００％のときとダーティブロックが５０％のときにＳＤＰＲが停止するということになります。これは統計情報が存在しない時のキャッシュ比率と比較すると、それぞれ2倍ずつ増加したものと見られます。 この時、統計情報のテーブルblocksを任意に1100に小さくするとどうなるでしょうか。
— テーブル統計情報のブロックを1100に下げます。
EXEC DBMS_STATS.SET_TABLE_STATS (ownname=>’SYS’, tabname=> ‘SDPR_TEST’,
numblks=>1100 , no_invalidate=>false) ;
SQL> select owner,table_name,blocks,num_rows
- from dba_tables
- where table_name = ‘SDPR_TEST’;
OWNER TABLE_NAME BLOCKS NUM_ROWS
——– —————– ——— ———- SYS SDPR_TEST 1100 2000
SQL> exec cached_sdpr_pro(p_start=>300, p_step=>1); Block count for impossible sdpr : 1073 blocks
SQL> exec dirty_sdpr_pro(p_start=>50, p_step=>1, p_stop=>1500) ;
Block count for impossible sdpr : 497 blocks
テスト結果、実際のテーブルのサイズは2000ブロック以上ですが、統計情報のブロックを1100に下げたところ、1070個のブロックがキャッシュされるときまたは497個のダーティブロックがキャッシュされるときに停止しました。
これは、テーブルキャッシュの有無を実際のテーブルのサイズではなく統計情報のブロックを基準に決定することが分かります。
テーブル統計情報の blocks が 1100 なので、キャッシュされたテーブルブロック数が統計情報
blocks の 100% に近い 1070 ブロック、ダーティブロックが blocks の 50% が 497 ブロックで SDPR止まりました。
統計情報のないテーブルをフルスキャンするときは、カーソルを実行するたびにテーブルの実際のブロック数をセグメントヘッダブロックで照会します。
ただし、統計情報を適用するときは、tab$blkcnt、tabpart$.blkcnt、ind$.leafcntの値をカーソルのどこかに保存し、実行するたびに保存された値を参照してSDPRかどうかを決定します。
したがって、カーソルが無効になったり、統計情報を新しく作成したり、キャッシュとダーティブロックの比率が変わった場合、SDPRの可否もランタイム時にいつでも変わることになります。
SQL> truncate table sdpr_test;
SQL> analyze table sdpr_test delete statistics ; SQL> alter system flush shared_pool ;
— テーブル500ブロックの生成。
SQL> insert /*+ append */ into sdpr_test 2 select rpad(‘*’, 100, ‘*’)
- from dual
- connect by level <= 500;
500 rows created.
— sdpr_testテーブル統計情報の削除。
SQL> alter table sdpr_test delete statistics;
— test-1 回カーソル実行。
SQL> alter session set “_small_table_threshold” = 1000 ;
SQL> select /*+ full(sdpr_test) test-1 */ count(*) from sdpr_test; COUNT(*)
———-
500
— v$segment_statistics 照会。
SQL> select statistic_name, value
- from v$segment_statistics
- where owner=user
- and object_name=’SDPR_TEST’
- and statistic_name=’physical reads direct’;
STATISTIC_NAME VALUE
—————————— ———-
physical reads direct 0
–テーブルの統計情報 blocks を 1100 に設定 ( no_invalidate オプションの削除) SQL> EXEC DBMS_STATS.SET_TABLE_STATS(ownname=>’SYS’,
tabname=>’SDPR_TEST’,numblks=>1100);
— カーソル test-1 再実行.
SQL> select /*+ full(sdpr_test) test-1 */ count(*) from sdpr_test;
— v$segment_statistics 照会。STATISTIC_NAME VALUE
——————————— ———
physical reads direct 0
–カーソルtest-2の実行.
SQL> select /*+ full(sdpr_test) test-2 */ count(*) from sdpr_test; COUNT(*)
———-
1000
— v$segment_statistics 照会. STATISTIC_NAME VALUE
————————— ——–
physical reads direct 2000
SDPR_TESTテーブルに500ブロックを作成した後に統計情報を削除した状態でテーブルをフルスキャンすると、実際のブロック数が_small_table_thresholdより少ないため、SDPRは発生しません。 統計情報
のブロックを 1100 に設定し、同じ SQL を再実行する場合も同様に SDPR
は行われません。
統計情報ブロックが1100なので、当然SDPRで処理されるべきですが、そうでない理由は統計情報をno_invalidateで生成したからです。
no_invalidateオプションを減算すると、デフォルト値がautoモードで統計情報が生成され、一定時間が経過してからカーソルが無効になります。
したがって、最初に実行されたtest-1カーソルは、再実行したときもカーソルがまだinvalidされていないため、SDPRでは実行されませんが、統計情報作成後に最初に実行されるtest-2カーソルは統計情報ブロックを適用してSDPRで実行されます。
したがって、test-2カーソルは無効でないか、バッファキャッシュにテーブルブロックのキャッシュとダーティブロックの状態が変わらない限り継続的にSDPRで実行されます。
[表-1]は、先に説明した serial direct read かどうかを決定する判別基準を表にまとめたものです。
| 分類 | SDPR決定基準 | SDPR停止 (ブロックキャッシュ) | SDPR停止 （ダーティブロックキャッシュ） |
| 統計情報あり | 統計情報 blocks ( tab$blkcnt, tabpart$.blkcnt, ind$.leafcnt) | 統計情報ブロック対比 100％キャッシュ | 統計情報ブロック対比 50％キャッシュ |
| 統計情報なし | セグメント物理ブロック数（セグメント ヘッダブロック参照） | セグメントブロック数対比 50％キャッシュ | セグメントブロック数対比 25％キャッシュ |
結論
これまでSDPR（serial direct read）がどのように動作するかをいくつかの事例で見てきました。 しかし、場合によっては多様で複雑に感じることもあるでしょう。
関連するパラメータを変更せず、統計情報だけを適切に生成してOracleが勝手に実行するようにしておけば良いのですが、何でも不足したり過度にすれば良くないように不要なdirect readが過度に発生したり、逆にそうでなければSDPRの動作方式を知っていれば 上記のパラメータや統計情報などを活用して適切なチューニングも可能であると考えます。
参考文献
http://afatkulin.blogspot.co.uk , http://blog.tanelpoder.com , http://docs.oracle.com
