태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

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 이벤트를 꼭 기억해주세요!

신고
Trackback 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

티스토리 툴바