태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'10949 event'에 해당되는 글 2건

  1. 2010.11.02 Oracle 11g의 Serial Direct Path Read와 _very_large_object_threshold 파라미터
  2. 2009.07.21 Serial Full Table Scan에 대한 direct path read 비활성화하기 - Oracle 11g (9)

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 성능이 뛰어난 대부분의 스토리지에서 좋은 성능을 보여줄 것을 기대할 수 있습니다. 운영 환경에서 여러 팩터를 고려해서 적절히 제어하면 될 것입니다.
Trackbacks 0 : Comments 0

Write a comment


Serial Full Table Scan에 대한 direct path read 비활성화하기 - Oracle 11g

오라클 2009.07.21 14:18
아래 Blog Post를 보시면 Oracle 11g에서는 드디어, Serial Full Table Scan에 대해서도 Direct Path Read가 사용된다는 것을 알 수 있습니다. 기본적으로 좋은 기능입니다. 하지만 내가 원하는 것은 이런 기능들을 마음대로 제어하는 것입니다. Oracle을 내맘대로 다루겠다!

그래서 시간을 내어서 이 기능을 비활성화하는 방법을 연구했습니다. 불행하게도 파라미터나 힌트 등에서는 제어할 수 있는 방법을 찾지 못했지만, 진단 이벤트를 통해서 제어할 수 있습니다. Oracle 11g에서 새롭게 추가된 10949 이벤트입니다.

UKJA@ukja116> @oerr 10949
10949                                                                           
 "Disable autotune direct path read for full table scan"                        
// *Cause:                                                                      
// *Action:  Disable autotune direct path read for serial full table scan.      
//                                                                              
간단한 예를 통해서 이 이벤트가 어떻게 동작하는지 보겠습니다.

1.충분히 큰 크기의 테이블을 만듭니다. 최소 크기는 5 * _small_table_threshold입니다.

UKJA@ukja116> col value new_value sth
UKJA@ukja116> @para small_table
old   9: and i.ksppinm like '%&1%'
new   9: and i.ksppinm like '%small_table%'

NAME                           VALUE                IS_DEFAUL SES_MODIFI        
------------------------------ -------------------- --------- ----------        
SYS_MODIFI                                                                      
----------                                                                      
DESCRIPTION                                                                     
--------------------------------------------------------------------------------
_small_table_threshold         637                  TRUE      true              
deferred                                                                        
threshold level of table size for direct reads                                  
                                                                                
Elapsed: 00:00:00.04
UKJA@ukja116> 
UKJA@ukja116> create table t1(c1 number, c2 char(2000), c3 char(2000), c4 char(2000));

Table created.

Elapsed: 00:00:00.21
UKJA@ukja116> insert into t1
  2  select level, 'x', 'x', 'x'
  3  from dual connect by level <= 10 + 5*&sth;
old   3: from dual connect by level <= 10 + 5*&sth
new   3: from dual connect by level <= 10 + 5*637

3195 rows created.

Elapsed: 00:00:01.14
UKJA@ukja116> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja116> @gather t1
UKJA@ukja116> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68
2. 10046 이벤트(레벨 8)를 이용해 10940 이벤트가 비활성화된 경우(Default)와 10949 이벤트가 활성화된 경우를 비교해 봅니다.
-- case#1
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
select count(*) from t1;

-- case#2
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
alter session set events '10949 trace name context forever, level 1';
select count(*) from t1;
3. Case #1에서는 direct path read 대기 이벤트가, Case #2에서는 db file scattered read 대기 이벤트가 목격됩니다. 즉, 10949 이벤트를 활성화하면 Serial Full Table Scan에 대한 Direct Path Read가 비활성화되는 것을 확인할 수 있습니다.
SQL ID : 5bc0v4my7dvr5
select count(*) 
from
 t1

-- Case #1
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         3        0.02          0.04
  db file scattered read                          1        0.02          0.02
  direct path read                              231        0.29          1.67
  SQL*Net message from client                     2        0.03          0.03


-- Case #2
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                        213        0.26          1.77
  SQL*Net message from client                     2        0.00          0.00
예상치 못한 성능 문제때문에 이 기능을 끄고 싶다면, 10949 이벤트를 꼭 기억해주세요!

Trackbacks 0 : Comments 9
  1. 욱짜 2009.07.21 17:43 신고 Modify/Delete Reply

    높임말 한번 사용해봤습니다. ^^;

    Optimizer가 결정하는 것은 Table Full Scan을 한다(즉 Operation으로 보면 TABLE ACCESS FULL이 된다)까지입니다. Table Full Scan을 할 때 direct path I/O를 하느냐, conventional path I/O를 하느냐는 Optimizer 이후, 즉 실제 실행할 때의 단계로 보는게 맞을 것 같습니다.
    (Direct Path I/O를 선택할 때와 Conventional Path I/O를 선택할 때에 통계 정보의 변화가 없다면 Cost는 동일할 겁니다)

    아마 제 예상이 맞다면 11g에서는 원래 direct path read를 하던 Query가 Shared Pool에서 Age Out이 되지 않아도, 어느 시점에 갑자기 conventional path I/O를 하는 경우가 있을 겁니다. 그 반대의 경우도 있을 거구요. 즉, 실행 계획은 동일하지만 실제 실행할 때 여러 가지 요소(그중 하나는 해당 Table에 대해 현재 Buffer Cache에 적재되어 있는 블록의 수)를 고려하여 읽는 방식을 결정하게 될 겁니다.

  2. extremedb 2009.07.21 17:52 신고 Modify/Delete Reply

    갑자기 글에 높임말을 사용하시니 이상 하네요^^.
    11g 에서 수행되는 Direct I/O 는 CBO 가 맞습니다.
    다양한 통계를 이용하여 Cost 를 구하는 것은 CBO 밖에 없습니다.
    다만 OPTIMIZING 전략이 다르다고 할수 있는것은 2가지 입니다.
    첫번째 최초에 Rowsorce 가 생성되고 난후에 해당 모듈을 call 한다는것과
    두번째 FETCH 단위로 CBO 의 해당 모듈을 CALL 한다고 보아야 할겁니다.

  3. extremedb 2009.07.21 17:59 신고 Modify/Delete Reply

    욱짜님의 말씀에 동의 합니다.
    다만 저는 11g 에서 수행되는 Direct I/O 가CBO 의 판단이라는 것을 말하고 싶은것 입니다.
    CBO 는 말 그대로 COST 로서 엑세스 방법이나 조인방법을 판단 하는것입니다.
    즉 COST 를 구하여 Direct I/O 를 할지 말지를 결정하게 된다는 것입니다.
    물론 이것이 아직까지 PLAN 이나 10053 에 나타나지는 않습니다. 하지만 분명히 실제로는 COST(수행시간) 에 영향을 미치므로 앞으로 수정되어야 하지 않나 싶습니다.
    참 그리고 10949 이벤트를 찾아내시다니 대단 하십니다.

  4. 욱짜 2009.07.22 12:22 신고 Modify/Delete Reply

    Cost라는 용어가 보편적이라서 용어의 혼란이 있을 수 있을 것 같습니다.

    Direct Path Read와 Conventiona Path Read를 결정하는 과정에서 Cost를 분명히 고려할 겁니다. 세그먼트의 블록의 수, Buffer Cache에 남아있는 블록의 수, 기타 블로그에 남기신 것과 같은 다향한 요소들을 고려할 것이구요. 단, 여기서의 Cost가 우리가 흔히 Cost Based Optimizer라고 부를 때의 Cost는 아니고, 일반적인 용어로서의 Cost라고 보는 것이 맞을 것 같습니다. Cost Based Analysis Module이라는 이름에서 Cost는 CBO의 Cost가 아니라 말 그대로 I/O 비용을 적절히 고려하겠다는 의미라고 보면 정확할 거 같아요.

    하여간 이제 본격적으로 11g와 관련된 이슈들을 논의할 시점이 도래하고 있네요!

  5. extremedb 2009.07.22 15:25 신고 Modify/Delete Reply

    동욱님 말씀대로 그런면이 있습니다.
    제가 CBO 혹은 CBO 에 준한다고 말씀드린것은
    Direct I/O 시 하나의 블럭을 읽는 속도을
    Cost Factor 로 사용하고 있다는 점 때문입니다.
    물론 이것은 개인적인 추정입니다.
    Direct I/O 시 하나의 블럭을 읽는 속도는 Dreadtim 으로 오라클에서 명명 하였으면 좋겟습니다. Sreadtim, Mreadtim 처럼 일관성 있게 말이죠.

  6. 욱짜 2009.07.24 14:33 신고 Modify/Delete Reply

    Chris Antognini가 이 주제에 대해 흥미로운 사실을 소개하고 있습니다.

    http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/

    11g에서는 Big DML이후에 각별히 신경쓸 필요가 있겠군요.

  7. extremedb 2009.07.24 21:16 신고 Modify/Delete Reply

    그렇군요.
    제가 테스트를 진행할때도 비슷한 경험을 했었습니다.

  8. 욱짜 2009.07.29 17:40 신고 Modify/Delete Reply

    Chris Antognini의 테스트 결과에 대해 제 해석을 추가했습니다. 관심있으신 분들은 읽어 보세요.

    Chris Antognini - http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/
    Dion Cho - http://dioncho.wordpress.com/2009/07/29/serial-direct-path-read-and-block-cleanout11g/

  9. KELLEYMeadows34 2011.11.03 15:21 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment