ポストの核心は10949診断イベントを利用してSerial Direct Path Readを不活性化することが可能だということです。
最近Serial Direct Path Readを制御するもう1つの隠しパラメータを知るようになりました。_VERY_LARGE_OBJECT_THRESHOLD隠しパラメータです。例えば、このパラメータの値が「500」ならば、セグメントのサイズが500MNB以上だったら10949診断イベントとは無関係にSerail Direct Path Readが使用されるようになります。これが意味するのは大きすぎるテーブルは可能なかぎりSerial Direct Path Readを使用しろということです。とても合理的な決定だと思います。
簡単なテストケースで説明してみます。
1. Oracleのバージョンは11.2.0.1です。
SQL> select * from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. _VERY_LARGE_OBJECT_THRESHOLDパラメータの値は500(MB)です。
SQL> @para very_large_object
SQL> set echo off
old 9: and i.ksppinm like '%&1%'
new 9: and i.ksppinm like '%very_large_object%'
NAME VALUE IS_DEFAUL SES_MODIFI SYS_MODIFI
------------------------------ -------------------- --------- ---------- ----------
DESCRIPTION
-------------------------------------------------------------------------------------
_very_large_object_threshold 500 TRUE true deferred
upper threshold level of object size for direct reads
3. 約104MBのサイズのテーブルT_VLOTを作り、セッションレベルで94MBを_VERY_LARGE_OBJECT_THRESHOLDパラメータの値で指定します。
SQL> create table t_vlot
2 as
3 select
4 rpad('x',2000) as c1,
5 rpad('x',2000) as c2,
6 rpad('x',2000) as c3,
7 rpad('x',2000) as c4
8 from dual
9 connect by level <= 6500
10 ;
Table created.
SQL> col tsize new_value tsize
SQL> select trunc(blocks*8*1024/1024/1024) - 10 as tsize
2 from dba_segments
3 where owner = user and segment_name = 'T_VLOT'
4 ;
TSIZE
----------
94
SQL>
SQL> alter session set "_very_large_object_threshold" = &tsize;
old 1: alter session set "_very_large_object_threshold" = &tsize
new 1: alter session set "_very_large_object_threshold" = 94
Session altered.
10949診断イベントを活性化し、T_VLOTテーブルに対してTable Full Scanを行ないます。そして10046診断イベントを通じて待機イベントを分析します。
SQL> -- even when 10949 is enabled
SQL> alter session set events '10949 trace name context forever, level 1';
Session altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);
PL/SQL procedure successfully completed.
SQL> select count(*) from t_vlot;
COUNT(*)
----------
6500
SQL> exec tpack.end_diag_trace(userenv('sid'), 10046);
PL/SQL procedure successfully completed.
SQL> select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));
5. 次にその結果があります。10949イベントが活性化されていますが、direct path read待機イベントが現れます。Serial Direct Path Readが動作したという意味です。
SQL ID: 1n87ukuyyv5h2
Plan Hash: 2969598161
select count(*)
from
t_vlot
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.09 2.86 13000 13004 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.09 2.86 13001 13005 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 97
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=13004 pr=13000 pw=0 time=0 us)
6500 TABLE ACCESS FULL T_VLOT (cr=13004 pr=13000 pw=0 time=60657 us cost=3575 size=0 card=6473)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
SQL*Net message to client 2 0.00 0.00
direct path read 412 0.04 2.73
asynch descriptor resize 1 0.00 0.00
SQL*Net message from client 2 0.00 0.00
たとえ診断イベントと隠しパラメータを通じてSerial Direct Path Readを完全に不活性化できますが、この機能は基本的に良い機能です。バッチI/Oの性能が優れた多くのストレージで良い性能を見せてくれるのを期待できます。本番環境で色々なファクターを考えて適切に制御する必要があります。