1. 멀티 블록 IO를 수행할때마다 물리적인 IO가 끝날때까지 발생한다.
2. P1=file#, P2=시작 block#, P3=읽는 block수
1. Full Table Scan, Index Full Scan을 수행하는 경우 성능보장을 위해 가능한 여러개의 블록을 한번에 읽는 방식
2. DB_FILE_MULTIBLOCK_READ_COUNT(MBRC) 파라메터로 지정
| {code} SYS@TEST3 >show parameter db_file_multiblock_read_count |
NAME TYPE VALUE
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
|
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를 수행
h2. 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
SQL> show parameter db_cache_size
NAME TYPE VALUE
SQL> alter system set db_cache_size=32M;
System altered.
SQL> show parameter db_cache_size
NAME TYPE VALUE
SQL> select table_name, buffer_pool from dba_tables where owner = 'LKWTEST';
TABLE_NAME BUFFER_
--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
-- 세션2 history2 테이블 10번 FTS
SQL> exec lkwtest.fts_history1;
PL/SQL procedure successfully completed.
SQL> @lkw_sess_event.sql
EVENT TOTAL_WAITS TIME_WAITED
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_
-- 세션1 history1 테이블 10번 FTS
SQL> exec lkwtest.fts_history1;
PL/SQL procedure successfully completed.
SQL> @lkw_sess_event.sql
EVENT TOTAL_WAITS TIME_WAITED
-- 세션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_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#;
|
h2. 문서에 대하여
* 최초작성자 : [~kwlee55]
* 최초작성일 : 2011년 01월 06일
* 이 문서는 [오라클클럽|http://www.gurubee.net] [코어 오라클 데이터베이스 스터디|5차 코어 오라클 데이터베이스 스터디] 모임에서 작성하였습니다.
* {color:blue}{*}이 문서의 내용은 (주)엑셈에서 출간한 'PRACTICAL OWI IN ORACLE 10G'와 'Advanced OWI in Oracle 10g'를 참고하였습니다.*{color}