태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Oracle 11g의 Serial Direct Path Read와 _very_large_object_threshold 파라미터

오라클/트러블슈팅 2010.11.02 11:29
아래 포스트를 통해서 Oracle 11g의 Serial Direct Path Read를 제어하는 방법을 소개한 바 있습니다. 위글의 핵심은 10949 진단 이벤트를 이용하면 Serial Direct Path Read를 비활성화할 수 있다는 것입니다.

최근에 Serial Direct Path Read를 제어하는 또 하나의 히든 파라미터를 알게 되었습니다. 바로 _VERY_LARGE_OBJECT_THRESHOLD 히든 파라미터입니다. 이 값이 "500"이면 세그먼트의 크기가 500MB 이상이면 10949 진단 이벤트에 무관하게 Serial Direct Path Read를 사용하라는 의미입니다. 테스트를 해보면 정확하게는 500MB가 아니고 그 근방의 값인듯 합니다. 어떤 문서에는 80%라고 하는데 좀 더 확인이 필요합니다. 하여간 요점은 지나치게 큰 테이블은 가능한 Serial Direct Path Read를 사용하라는 것입니다. 매우 합리적인 결정으로 보입니다.

간단한 테스트 사례를 통해 설명해보겠습니다.

1. 오라클 버전은 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.
4. 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. 아래에 그 결과가 있습니다. 10946 이벤트를 활성화했지만 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를 완전히 비활성화할 수 있지만, 이 기능은 기본적으로 좋은 기능입니다. Batch I/O 성능이 뛰어난 대부분의 스토리지에서 좋은 성능을 보여줄 것을 기대할 수 있습니다. 운영 환경에서 여러 팩터를 고려해서 적절히 제어하면 될 것입니다.
저작자 표시
신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바