- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3902010&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
블록단위 I/O
- 오라클의 I/O는 Block단위로 이루어 진다.
- 하나의 컬럼만을 읽으려고 해도 레코드가 속한 블록 전체를 읽게 됨
- 하나의 블록을 액세스해 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어 들인다면 설령 무거운 디스크 I/O를 수반하더라도 비효율은 없으며 이를 Sequential 액세스라고 한다.
- 레코드 하나를 읽으려고 블록을 통째로 액세스한다면 그것은 메모리 버퍼에서 읽더라도 비효율이 존재 하고 이를 Random액세스라고 한다.
- SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수이다.
- 블록 단위 I/O는 버퍼 캐시와 데이터 파일 I/O모두에 적용된다.
- 메모리 버퍼 캐시에서 블록을 읽고 쓸 때
- 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때 (Direct Path I/O)
- 데이터파일에서 DB 버퍼 캐시로 블록을 적재할 때 : Single Block Read 또는 Multiblock Read 방식을 사용
- 버퍼 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때 : Dirty 버퍼를 주기적으로 데이터파일에 기록하는 것을 말하며, DBWR 프로세스에 의해 수행된다. 성능향상을 위해 한 번에 여러 블록씩 처리한다.
- 오라클 Dictionary Cache는 로우 단위로 I/O를 수행한다. 이로 인해 '로우 캐시'라고 부르기도 한다.
- 오라클에서 허용하는 블록 크기는 2k, 4k, 8k, 16k, 32k, 64k이다. 데이터베이스를 생성할 때 DB_BLOCK_SIZE를 지정하며, 다른 크기의 블록을 동시에 사용하려면 각각 별도의 Tablespace 와 버퍼 Pool을 구성해 주어야 한다. System Table Space는 Default로 8K이다.
(1) Sequential VS. Random 액세스
- Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식
- 인덱스 리프 블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결돼 있고, 이 포인터를 따라 스캔하는 것을 Sequential 액세스 방식이다.
- 테이블 레코드 간에는 포인터로 연결되어 있지 않지만 테이블을 스캔할 때는 물리적으로 저장된 순서대로 읽어 나가므로 이것 또한 Sequential 액세스 방식이다.
- Sequential 액세스 성능을 향상시키려고 오라클 내부적으로 Multiblock I/O, 인덱스 Prefetch 같은 기능을 사용한다.
- Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근 하는 방식
- ①,②,③,④,⑤,⑥이 Random Acess에 해당하며, ①,②,③번 액세스는 인덱스 깊이에 따라 1~3블록 정도 읽는 것이므로 대개 성능에 영향을 미치지 않고, ,④, ⑥번 액세스가 성능 저하를 일으킨다.
- NL 조인에서 Inner 테이블 액세스를 위해 사용되는 인덱스는 ①,②,③번까지도 성능에 지대한 영향을 미칠 수 있다.
- Random 액세스 성능을 향상시키려고 오라클 내부적으로 버퍼 Pinning, 테이블 Prefetch 같은 기능을 사용
- 블록단위 I/O를 하더라도 한번 액세스할 때 Sequential 방식으로 그 안에 저장된 모든 레코드를 읽으면 비효율이 없다고 할 수 있으나, 하나의 레코드를 읽으려고 한 블록씩 읽는다면 매우 비효율적이다.
- Sequential 액세스 효율은 Selectivity에 의해 결정된다. 즉, 같은 결과 건수를 내면서 얼마나 적은 레코드를 읽느냐로 효율성을 판단 할 수 있다. (100개를 읽었는데 그중 99개를 버리고 1개를 취한다면 Random 액세스 보다 나을게 없다)
- I/O 튜닝의 핵심 원리
- Sequential 액세스의 선택도를 높인다.
- Random 액세스 발생량을 줄인다.
(2) Sequential 액세스 선택도 높이기SQL>drop table t; 테이블이 삭제되었습니다. SQL>create table t 2 as 3 select * from all_objects 4 order by dbms_random.value; 테이블이 생성되었습니다. SQL>select count(*) from t; COUNT(*) ---------- 13225 SQL>select count(*) from t 2 where owner like 'SYS%'; COUNT(*) ---------- 7315 Rows Row Source Operation -- --- ---- ----- ------ ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=177 pr=0 pw=0 time=5790 us) 7315 TABLE ACCESS FULL T (cr=177 pr=0 pw=0 time=22003 us)
- 7315개 레코드를 선택하기 위해 13225개 레코드를 스캔 했으므로 선택도는 55%로 이다. 읽은 블록의 개수는 177개 이다.
SQL>select count(*) from t 2 where owner like 'SYS%' 3 and object_name = 'ALL_OBJECTS'; count(*) ---------- 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=177 pr=0 pw=0 time=1872 us) 1 TABLE ACCESS FULL T (cr=177 pr=0 pw=0 time=1850 us)
- 1개의 레코드를 선택하려고 13,225개 레코드를 스캔 했으므로 선택도는 0.007%이다. 선택도가 매우 낮으므로 테이블 Full Scan 비효율이 크다. 읽은 블록은 177똑같다.
SQL>create index t_idx on t (owner, object_name); SQL>select /*+ index(t t_idx) */ count(*) from t 2 where owner like 'SYS%' 3 and object_name = 'ALL_OBJECTS'; count(*) ---------- 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=60 pr=0 pw=0 time=4844 us) 4352 TABLE ACCESS SAMPLE T (cr=60 pr=0 pw=0 time=13115 us)
- 참조하는 컬럼이 모두 인덱스에 있으므로 인덱스만 스캔하고 결과를 낼 수 있다. 인덱스를 스캔하면서, 60개의 블록을 읽고 1개 레코드를 얻었다.
- 인덱스 선두 컬럼이 '='조건이 아니므로 owner like 'SYS%' 조건에 해당하는 로우가 읽어야 할 대상 범위지만, 다행히 스캔 시작 지점은 owner='SYS' 조건과 object_name >= 'ALL_OBJECTS' 조건에 의해 결정된다.
SQL>select /*+ index(t t_idx) */ count(*) from t 2 where owner like 'SYS%' 3 and ((owner = 'SYS' and object_name >= 'ALL_OBJECTS' ) or (owner >'SYS')); count(*) ---------- 7213
- 1/7213 * 100 = 0.01%의 선택도 이다. 테이블 뿐만 아니라 인덱스를 Seqeuntial 액세스 방식으로 스캔할 때도 비효율이 있는 것을 알 수 있다.
- 인덱스 스캔의 효율은조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 영향을 받는다.
SQL>drop index t_idx; 인덱스가 삭제되었습니다. SQL>create index t_idx on t (object_name, owner); 인덱스가 생성되었습니다. SQL>select /*+ index(t t_idx) */ count(*) from t 2 where owner like 'SYS%' 3 and object_name = 'ALL_OBJECTS'; count(*) ---------- 1 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=50 us) 1 INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=27 us)(object id 74891)
- Index를 변경하여 I/O가 줄어 들었다. 두개의 CR 블록 읽기가 발생했다. 인덱스 루트 블록과 하나의 리프 블록만 읽었기 때문이다.
- 선택도가 100%이므로 가장 효율적인 방식으로 Sequential 액세스를 수행하였다.
(3) Random 액세스 발생량 줄이기
- 클러스터링 팩터가 좋을수록 버퍼 Pinning에 의한 블록 I/O감소 효과는 더 커진다.
문서에 대하여
- 최초작성자 : [미녀씨]
- 최초작성일 : 2010년 01월 15일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3902010&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.