Post
SQL 처리과정과 I/O 2
디스크 I/O가 SQL 성능을 결정한다.
자주 읽는 블록을 매번 디스크에서 읽는 것도 비효율적이기 때문에 DB Buffer Cache로 캐싱해 둔다.
Data block 읽을 때 Cache부터 탐색
V$SGA로 버퍼캐시 size 확인 select * from v$sga; or show sga;
1.버퍼 캐시에 있어서 Data를 읽는 경우를 논리적 I/O라고 한다.(Server Process에서 바로 DB Buffer Cache에 저장되어 있는 것을 읽음)
=>메모리 I/O와 읽는 횟수의 수치가 같다.
2.버퍼 캐시에 없어서 디스크를 가서 읽는다(Server Process에서 DataFile까지 가서 읽음)
이를 물리적 I/O라고 한다.
*Buffer Cache Hit Ratio : BCHR
BCHR= 캐시에서 곧바로 찾은 블록수 / 총 읽은 블록수 x 100
BCHR=(논리적I/O - 물리적 I/O) / 논리적I/O X 100
BCHR=물리적 I/O = 논리적I/O X (100 - BCHR)
ex) 디스크에서 100만개 버퍼캐시에서 150만개 읽었다. 이때 논리적 I/O는 250만개가 아닌 버퍼캐시에 있는 150만개이다.
디스크에서 곧바로 읽지 않고 버퍼 캐시에 먼저 저장 후 읽기 때문이다.
BCHR = (1-100만/150만)x100 = (1-2/3) x100 = 33.3%
Data를 입력 수정 삭제하지 않는 상황에서 조건절이 같으면 논리적 I/O는 항상 같다.
따라서 물리적 I/O는 BCHR에 의해 결정된다
BCHR은 시스템 상황에 따라 달라진다
결국 물리적 I/O는 시스템 상황에 의해 결정되는 통제 불가능한 외생변수 이다.(메모리를 증설해서 DB Buffer Cache를 늘리는 방법외에는)
따라서 SQL 성능을 높이기 위해서는 논리적 I/O를 줄여야 한다.
그럼 논리적 I/O를 줄이는 방법은?
논리적 I/O를 줄이는 방법은? SQL 튜닝이라고 하는데 위에 말한 것과 같이 논리적I/O를 줄여 물리적I/O도 같이 줄여지기 때문이다.
Row자체를 줄이는 방법도 있다.
SQL 수행 시 읽는 총 블록수를 논리적 I/O라고 한다.
예를 들어
자전거로 15km 거리를 간다고 가정하였을 때
자전거 15km가 바퀴 7500번 회전해야 가는 거리라고 한다면 이것이 논리적인 일량 논리적 I/O이다
다만, 바퀴 7500번을 회전해야 하는데 이것이 페달 750번을 밟으면 회전하기 때문에 이렇게 밟아야 갈 수 있다 하는 부분이 물리적 I/O이다. 이는 외부 변수로 달라지게 되는데
역풍을 만나면 늘어날 수 있으며 , 순풍을 만나면 줄어들 수 있다. 또한, 오르막, 내리막, 도로상태 등 외부 변수로 인해 일의 양이 줄어들 수 있게 된다.
*BCHR이 SQL 성능을 좌우하지만 , 높다고 해서 효율적인 SQL을 의미하지는 않는다
>같은 블록을 비효율적으로 반복해서 읽을수도 있기 때문이다. (순풍이 불어 목적지까지 빨리 갔지만 비효율적으로 멀리 돌아서 온 경우)
읽고자 하는 블록을 db 버퍼 캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O Call을 한다. 그 동안 Process는 대기 큐 (Wait Queue)에서 잠을 잔다.
-> 이 부분을 보면 어차피 자는거 한번에 많은 양을 요청해야 잠자는 횟수를 줄일 수 있다고 생각 가능하다.
->또한 대용량 테이블 스캔 시 (Full Scan) Multi block I/O를 크게 설정하는 것도 좋다
: 테이블 전체 블록 읽을 시 많이 담아야 유리하다. (db_file_multiblock_read_count) 128이 최대이다
alter session set db_file_multiblock_read_count = 128;
대부분 128로 설정되어 있을 것이다.
이는 Oracle I/O 단위가 8kb라서 그렇다. 8kbx128 = 1MB가 나온다
OS 레벨 I/O단위 최대가 1MB이기 때문에 이렇게 구성한다.
래치(Latch)
래치는 줄서기 개념으로 보면된다 (캐시 등 한 프로세스만 사용 가능하니 줄을 세운다)
DB Table에서 데이터를 찾는 방법은
Table Full Scan
Index Scan 2가지 뿐이다.
다시 언급하는데 SQL Tuning은 랜덤 I/O와의 전쟁이다.
DBMS가 제공하는 많은 기능들이 랜덤 I/O를 줄이는데에 있다.
*IOT, 클러스터,파티션,테이블 Prefetch, Batch i/o 등등이다.
Index 없이 탐색하면 모두 읽는다. 있다면, Range Scan으로 범위 탐색을 한다. (DBMS는 일반적으로 BTree 인덱스 사용 - Balanced Tree)
Index 탐색 시 수직탐색 수평탐색 방법이 있는데
수직은 Index Scan 시작 지점을 찾는 것이다 본격 탐색 전단계이다,
조건에 만족하는 첫번째 레코드를 찾는다.
=>Root부터 시작하고, Branch블록(하위 블록 주소값 가짐)
수평적 탐색에서 본격적 데이터 탐색이 이뤄진다. 인덱스 리프 블록(앞뒤 블록 주소 존재)을 수평적으로 스캔한다.
*결합 인덱스 구성 시 선택도 낮은 (중복이 덜한) 컬럼을 앞에 두고 결합 인덱스 생성해야 검사 횟수 줄여 성능 좋아진다.
*인덱스 컬럼 가공하면 스캔 시작점을 못찾아 Index Range Scan이 아닌 Index Full Scan 방식이 적용된다.
ex) select * from emp where birth_ymd betwwen ‘20010101’ and ‘20010505’
select * from emp where substr(birth_ymd,5,2)=‘05’
위 두개는 스캔시작 종료지점을 알 수 없어 Full Scan 들어간다
select * from emp where nvl(amount,0) <100
마찬가지로 가공안된 amount값으로 인덱스 만들었는데 nvl을 걸어 버리면 시작 지점 못찾는다.
또한 where 업체명 like ‘대한%’ 검색 시 대한으로 시작하는 경우 값이 특정구간에 모여 있으니 Range Scan이 되지만 ‘%대한%’인 것은 전체 구간에 흩어져 있어서 Range Scan 안된다.
where (전화번호 =yy or 고객명=xx) 수직적 탐색을 통해 어느 한 시작지점을 바로 찾을 수 없다.
위 내용을 Index Range Scan 하려면 select * from 고객 where 고객명=xx union all select * from 고객 where 전화번호=yy and(고객명 <> xx or 고객명 is null)
IN절은? where 전화번호 in (‘xx’,’yy’,’zz’) =>IN절은 SQL Optimizer가 IN-List Iterator방식을 사용하며 IN-List 개수만큼 Index Range Scan 반복 한다.
Index Range Scan하기 위한 가장 첫번째 조건은 인덱스 선두 컬럼이 조건절에 가공되지 않은 상태로 있는 것이다.
=>우선 선두 컬럼이 가공되지 않은 상태로 조건절에 있다면 Range Scan은 가능하며 , 이후 스캔 범위를 줄이는 것이 바람직하다.
댓글