by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

db file scattered read II




1. db file scattered read 대기이벤트

1. 멀티 블록 IO를 수행할때마다 물리적인 IO가 끝날때까지 발생한다.
2. P1=file#, P2=시작 block#, P3=읽는 block수

2. 멀티 블록 IO

1. Full Table Scan, Index Full Scan을 수행하는 경우 성능보장을 위해 가능한 여러개의 블록을 한번에 읽는 방식
2. DB_FILE_MULTIBLOCK_READ_COUNT(MBRC) 파라메터로 지정

SYS@TEST3 >show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

SYS@TEST3 >alter system set db_file_multiblock_read_count=10000000;

System altered.

SYS@TEST3 >show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128
=> 128이 한번에 읽을 수 있는 최대 블록수

3. Full Table Scan, Index Full Scan중 Single Block IO를 수행하는 경우 (db file scattered read가 아닌 db file sequential read 대기가 발생)
3-1) 익스텐트 경계에 도달한 경우 : 멀티 블록 IO = 8

  • 익스텐트 9 블록 : 멀티 블록 IO 1회(8개 블록) + 싱글 불록 IO 1회(1개 블록)
  • 익스텐트 10 블록 : 멀티 블록 IO 1회(8개 블록) + 멀티 불록 IO 1회(2개 블록)

3-2) 스캔 도중에 캐쉬된 블록이 있을 경우

  • 읽을 블록 : 8개, 세번째 블록 캐쉬되어 있는 경우 : 멀티 블록 IO 1회 (앞2개 블록) + Logical IO 1회 + 멀티 블록 IO 1회 (뒤5개 블록)

3-3) chained row가 있는 경우 : FTS수중하다가 chained row를 만나면 나머지 row를 읽기 위해 추가적인 싱글 블록 IO를 수행

3. db file scattered read 대기 해결책

1. 어플리케이션 레이어 : SQL튜닝 ( SQL문의 특성을 고려하여 FTS가 유리한지 인덱스범위스캔이 유리한지 판단한다.)
2. 오라클 메모리 레이어

  • 버퍼 캐시의 크기를 적절히 조정
  • 다중 버퍼 풀 사용 :
    • 자주 액세스되는 객체를 메모리에 상주시킴으로써 물리적인 IO를 최소화한다.
    • 휘발성 데이터는 빠른 속도로 메모리에서 재활용한다.
    • 각 버퍼마다 별도의 cache buffers lru chain 래치를 사용하기 때문에 래치 경합을 감소시킨다.
  • 다중 버퍼 풀 사용에 따른 db file scattered read 대기 테스트
    • 다른 4개의 세션에서 recycle버퍼풀을 사용할때 default 버퍼풀을 사용하는 세션1의 db file scattered read 대기 시간이 다른 세션에 비해 낮아졌다.
    • 교재는 recycle버퍼풀을 사용한 다른 4개 세션의 db file scattered read 대기 시간이 줄어들었다고 하나, 실제 테스트 결과 대기 시간이 줄지는 않았다.
CASE1

SQL> select table_name, blocks*8192/1024/1024 from dba_tables where owner = 'LKWTEST' and table_name like 'HISTORY%';

TABLE_NAME                     BLOCKS*8192/1024/1024
------------------------------ ---------------------
HISTORY5                                  102.789063
HISTORY4                                  107.765625
HISTORY3                                  102.789063
HISTORY2                                  102.789063
HISTORY1                                  86.8515625

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0

SQL> alter system set db_cache_size=32M;

System altered.

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 32M


SQL> select table_name, buffer_pool from dba_tables where owner = 'LKWTEST';

TABLE_NAME                     BUFFER_
------------------------------ -------
HISTORY1                       DEFAULT
HISTORY2                       DEFAULT
HISTORY3                       DEFAULT
HISTORY4                       DEFAULT
HISTORY5                       DEFAULT

--fts 프로시저 생성
CREATE OR REPLACE PROCEDURE LKWTEST.fts_history1
IS
BEGIN
for idx in 1 .. 10 loop
    execute immediate 'truncate table lkwtest.history1_temp';    
    execute immediate 'insert into lkwtest.history1_temp select * from lkwtest.history1';
    commit;    
end loop;   
END ;
/

-- 세션1 history1 테이블 10번 FTS
SQL> exec lkwtest.fts_history1;

PL/SQL procedure successfully completed.

SQL> @lkw_sess_event.sql

EVENT                                                            TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
db file scattered read                                                  3793          77


-- 세션2 history2 테이블 10번 FTS
SQL> exec lkwtest.fts_history1;

PL/SQL procedure successfully completed.

SQL> @lkw_sess_event.sql

EVENT                                                            TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
db file scattered read                                                  4257          95


CASE2
SQL> alter system set db_cache_size=16M;

System altered.

SQL> alter system set db_recycle_cache_size=16M;

System altered.

SQL> ALTER TABLE lkwtest.history2 storage(BUFFER_POOL RECYCLE);

Table altered.

SQL> select table_name, buffer_pool from dba_tables where owner = 'LKWTEST';

TABLE_NAME                     BUFFER_
------------------------------ -------
HISTORY1                       DEFAULT
HISTORY2                       RECYCLE
HISTORY3                       RECYCLE
HISTORY4                       RECYCLE
HISTORY5                       RECYCLE

-- 세션1 history1 테이블 10번 FTS
SQL> exec lkwtest.fts_history1;

PL/SQL procedure successfully completed.

SQL> @lkw_sess_event.sql

EVENT                                                            TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
db file scattered read                                                  5306          45

-- 세션2 history2 테이블 10번 FTS
SQL> exec lkwtest.fts_history2;

PL/SQL procedure successfully completed.

SQL> @lkw_sess_event.sql

EVENT                                                            TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
db file scattered read                                                  7581          96

  • 세션 단위로 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터 값을 높게 설정한다.
  • 큰 크기의 블록을 사용
    • 한 블록이 포함하는 로우수가 증가하므로 같은 크기의 테이블을 구성하는데 적은 수의 블록을 사용하게 되고, 그 만큼 멀티 블록 IO의 횟수가 줄어든다.
    • Row chaining, Row migration이 발생할 확률이 낮아져, 부가적인 IO가 줄어들게 된다.

3. 오라클 세그먼트 레이어 : 파티셔닝등으로 FTS범위를 줄일 수 있는 방법 검토
4. OS/디바이스 레이어 : v$filestat 뷰를 이용하여 데이터파일별로 멀티블록IO, 싱글블록IO의 횟수 및 시간을 체크한 후, 평균수행시간이 높게 나온 데이터파일이 존재하는 IO시스템의 성능을 개선한다.

SQL> select f.file#, f.name, 
     s.phyrds, s.phyblkrd, s.readtim,  -- 전체 읽기 작업 정보 
      s.singleblkrds,  s.singleblkrdtim,  -- Single block IO
    (s.phyblkrd - s.singleblkrds) as multiblkrd,   -- Multi block IO 회수
     (s.readtim - s.singleblkrdtim)  as multiblkrdtim,  -- Multi block IO 시간
     round(s.singleblkrdtim/decode(s.singleblkrds,0,1,s.singleblkrds),3) as singeblk_avgtim, -- Single block IO 평균대기시간(cs)
    round((s.readtim-s.singleblkrdtim)/(s.phyblkrd-s.singleblkrds),3) as multiblk_avgtim -- Multi block IO 평균대기시간(cs)
from v$filestat s, v$datafile f 
where s.file# = f.file#;

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.