Tuesday, November 2, 2010

Oracle 11gのSerial Direct Path Readと_very_large_object_thresholdパラメータパラメータ

次のポストを通じてOracle 11gのSerial Direct Path Readを制御する方法を紹介したことがあります。

ポストの核心は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の性能が優れた多くのストレージで良い性能を見せてくれるのを期待できます。本番環境で色々なファクターを考えて適切に制御する必要があります。