옵티마이저 힌트
SQL 옵티마이저는 순식간에 엄청나게 많은 연산을 한다. 그 과정에 옵티마이저가 사용하는 정보는 다음과 같다.
-테이블, 컬럼, 인덱스 구조에 관한 기본 정보
-오브텍트 통계: 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
-시스템 통계: CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
-옵티마이저 관련 파라미터
주의사항
1. 힌트 안에 인자를 나열할 땐 ','(콤마)를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안된다.
/*+ INDEX(A A_X01) INDEX(B, B_X03) */ -> 모두 유효
/*+ INDEX(C), FULL(D) */ -> 첫번째 힌트만 유효
2. 테이블을 지정할 때 스키명까지 명시하면 안 된다
SELECT /*+ FULL(SCOTT.EMP) */ -> 무효
FROM EMP
3. FROM 절 테이블명 옆에 ALIAS를 지정했다면 힌트에서도 반드시 ALIAS를 사용해야 한다
FROM 절에 ALIAS를 지정했는데 힌트에는 아래와 같이 테이블명을 사용하면 그 힌트는 무시된다
SELECT /*+ FULL(EMP) */ ->무효
FROM EMP E
SQL 공유 및 재사용
소프트 파싱 vs 하드 파싱
라이브러리 캐시
SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간
사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인한다
SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 소프트 파싱(Soft Parsing)이라 하고
실퍂해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 하드 파싱(Hard Parsing)이라고 한다
DB에서 이루어지는 처리 과정은 대부분 I/O 작업에 집중하는 반면
하드 파싱은 CPU를 많이 소비하는 몇 안되는 작업 중 하나이다. 따라서 내부 프로시저를 한 번만 사용하고 버리는 것은 매우 비효율적
바인드 변수의 중요성
1. 이름없는 SQL 문제
SQL텍스트 전체가 이름 역할을 한다.
따라서 SQL이 변하면 SQL ID도 변한다
2. 공유 가능 SQL
로그인 프로그램을 작성할 때에 프로시저를 여러 개 생성할 것이 아니라
아래처럼 로그인ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 마땅하다
create procedure LOGIN (login_id in varchar2)
-> SQL에 대한 하드파싱은 최초 한 번만 일어나고, 캐싱된 SQL을 100만 고객이 공유하면서 재사용
데이터 저장 구조 및 I/O 메커니즘
- SQL이 느린 이유: OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문이다. interrupt 없이 열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 변환하고 잠시 수면 상태(waiting)에서 I/O가 완료되기를 기다린다. 열심히 일해야 할 프로세스가 잠을 자고 있으니 I/O가 많으면 성능이 느릴 수밖에 없다.
- 스토리지 성능이 빨라지고 있지만 여전히 우리 기대에는 못 미친다. 전반적으로 I/O 튜닝이 안 된 시스템이라면 수많은 프로세스에 의해 동시다발적으로 발생하는 I/O call 때문에 디스크 경합이 심해지고 그만큼 대기 시간도 늘어난다. 디스크 I/O가 SQL 성능을 좌우한다고 해도 과언이 아니다.
데이터베이스 저장 구조
-블록: 데이터를 읽고 쓰는 단위 (DBMS가 데이터를 읽고 쓰는 단위/테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다)
-익스텐트: 공간을 확장하는 단위. 연속된 블록 집합
-세그먼트: 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
-테이블스페이스: 세그먼트를 담는 콘테이너
-데이터파일: 디스크 상의 물리적인 OS 파일
오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기 위해 8KB를 읽는 셈이다.
시퀀셜 액세스 vs 랜덤 액세스
시퀀셜 액세스
논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다.
오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다.
읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을
순서대로 읽으면, 그것이 곧 Full Table Scan이다.
랜덤 액세스
논리적, 물리적인 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식
논리적 I/O vs 물리적 I/O
라이브러리 캐시
SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'
DB버퍼 캐시
'데이터 캐시'로 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다
서버 프로세스와 데이터파일 사이에 버퍼캐시가 있으므로 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다
버퍼캐시는 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다
논리적 I/O
SQL을 수행하면서 읽은 총 블록 I/O
아무리 여러 번 실행해도 매번 읽는 블록 수는 같다
논리적 I/O 횟수는 일반적으로 DB 버퍼캐시에서 블록을 읽은 횟수와 일치한다.
논리적 I/O가 메모리 I/O와 같은 개념은 아니지만 결과적으로 수치는 같다
물리적 I/O
DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O
데이터 입력이나 삭제가 없어도 물리적 I/O는 SQL을 실행할 때마다 다르다
첫 번째 실행할 때보다 두번째 실행할 때 줄어들고, 한참 후에 다시 실행하면
반대로 물리적 I/O가 늘어난다. DB 버퍼캐시가 다른 테이블 블록으로 채워지기 때문이다.
버퍼캐시 히트율 BCHR
= (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) X 100
= ((논리적 I/O - 물리적 I/O) / 논리적 I/O) X 100
= (1-(물리적 I/O) / (논리적 I/O)) X 100
*BCHR에는 주의해야 할 함정이 있다.
BCHR이 SQL 성능을 좌우하지만 BCHR이 높다고 해서 효율적인 SQL을 의미하지 않는다
논리적 I/O는 일정하므로 물리적 I/O는 BCHR에 의해 결정된다.
BCHR은 시스템 상황에 따라 달라지므로 물리적 I/O는 결국 시스템 상황에 의해 결정되는 통제 불가능한 외생변수다.
물리적 I/O = 논리적 I/O X (100%-BCHR)
따라서 SQL 성능을 높이기 위해서 할 수 있는 일은 논리적 I/O를 줄이는 일뿐이다.
논리적 I/O는 항상 일정하게 발생하지만 SQL 튜닝을 통해 줄일 수 있는 통제 가능한 내생변수다.
논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다