1.1 SQL 파싱과 최적화
.SQL이란?
- 구조적 질의 언어; 즉, 구조적이고 집합적이고 선언적인 질의 언어.
하지만 원하는 결과집합을 만드는 과정은 절차적인 과정, 즉 '프로시저'가 필요하다.
(프로시저: SQL 서버에서 제공하는 프로그래밍 기능으로, 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램)
프로시저를 만들어 내는 DBMS 내부 엔진이 바로 'SQL 옵티마이저'이다.
(SQL 옵티마이저: 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로 선택해주는 DBMS 핵심 엔진)
SQL 최적화란?
DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정.
1) 사용자로부터 SQL 전달받음
2) SQL 파서가 파싱 진행
- 파싱 트리 생성: SQL문 이루는 개별 구성요소 분석해서 파싱트리 생성
- 문법적 오류 없는지 확인
- 의미상 오류 없는지 확인
3) SQL 최적화: SQL 옵티마이저가 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 가장 효율적인 하나를 선택
- 사용자로부터 전달받은 쿼리문 수행 위한 실행계획들 찾아낸다.
- 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계정보 이용해 각 실행계획의 예상비용 산정
- 최저비용 나타내는 실행계획 선택
4) 로우 소스 생성기가 SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅.
SQL 옵티마이저의 실행경로 선택 근거?
- '비용': 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간 표현한 값.
- '실행계획' : SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한 것.
(= SQL 실행경로 미리보기)
- 이때, SQL 옵티마이저는 항상 최선의 선택 보여주지 않을 수 있기 때문에, '옵티마이저 힌트' 이용해 데이터 액세스 경로 바꿀 수 있다.
옵티마이저 힌트란?
사용자가 옵티마이저에게 원하는 방향으로 실행계획 세우도록 힌트를 주는 방법
- 사용법:
1) 주석 기호에 '+'를 붙인다.
SELECT /*+ INDEX(A 고객_PK) */ 고객명, 연착처, 주소
FROM 고객 A
WHERE 고객_ID = '01';2) 힌트 안 인자 나열시 ',' 사용 가능하지만, 힌트와 힌트 사이에는 사용하면 안된다.
/*+ INDEX(A A_1) INDEX(B, B_1) */ (o)
/*+ INDEX(C), FULL(D) */ (X)
/*+ INDEX(A A_1) INDEX(B, B_1) */ --> (O)
/*+ INDEX(C), FULL(D) */ (X) --> (X)3) 테이블 지정시 스키마명까지 명시하면 안된다.
SELECT /*+ FULL(SCOTT.EMP) */ --> (X)
FROM EMP4) FROM 절 테이블명 옆에 ALIAS 지정했다면, 힌드에도 ALIAS 사용해야 한다.
SELECT /*+ FULL(EMP) */ --> (X)
FROM EMP E
1.2. SQL 공유 및 재사용
소프트 파싱 vs 하드 파싱
- 소프트 파싱 : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
- 하드 파싱 : SQL을 캐시에서 찾는데 실패해 최적화 및 로우소스 생성 단계까지 모두 거치는 것.
( 라이브러리 캐시 : SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간으로, SGA 구성요소이다)
(SGA : 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간)
하나의 쿼리 수행하는데에 있어 후보가 될만한 많은 실행경로 도출하고, 딕셔너리와 통계정보 읽어 각각에 대한 효율성을 판단해야 하는 과정은 하드할 수 밖에 없으! 따라서 라이브러리 캐시가 필요하다.
바인드 변수의 필요성
실행되는 SQL문은 이름이 없고 전체 SQL 텍스트가 이름 역할을 한다.
이때, SQL 문장이 조금만 달라져도 다른 SQL로 취급이 되어 하드파싱을 수행하게 된다.
따라서, 하드 파싱을 줄이기 위해 '바인드 변수'를 사용하여 파라미터 주입 방식으로 SQL을 작성해야 한다!
(바인드 변수: SQL 문에서 value 값을 '?'로 표시해두고 파라미터 주입을 통해 값을 전달하는 식의 변수)
=> 즉, 바인드 변수를 사용한다면 쿼리 자체에 변수를 사용하고 있어서 공유 영역에 저장되어 있는 쿼리와 무조건 동일하다. 따라서 소프트 파싱이 가능한 것이다. 바인드 변수를 사용하지 않는다면, 쿼리 수행 횟수만큼 매번 하드 파싱 해야하기 때문에 이에 대한 부하가 발생한다.
1.3. 데이터 저장 구조 및 I/O 매커니즘
SQL이 느린 이유?
I/O 때문 (I/O를 처리하는동안 프로세스는 잠을 자고 있다)
(프로세스: 실행중인 프로그램으로, 생성 -> 준비 / 실행 / 대기 -> 종료 와 같은 생명주기 갖는다)
I/O가 처리하는 동안 CPU를 OS에 반환하고 프로세스는 수면 상태에서 I/O가 종료되길 기다리기 때문에, I/O가 많으면 성능이 느릴 수 밖에 없다.
데이터베이스 저장 구조
- 테이블스페이스 : 세크먼트를 담는 콘테이너로, 여러 개의 데이터파일로 구성된다.
- 데이터 파일 : 디스크 상의 물리적인 OS 파일로 익스텐트와 데이터 블록을 저장하는 파일.
- 세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트
- 익스텐트 : 공간을 확장하는 단위로, 세그먼트 공간이 부족할 경우 익스텐트를 추가로 할당받는다.
- 데이터 블록(=페이지) : 사용자가 입력한 레코드를 실제로 저장하는 공간으로, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드이다.
블록 I/O
DBMS가 데이터를 읽고 쓰는 단위 => '블록'
따라서 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽으며, 테이블 뿐 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.
테이블 또는 인덱스 블록을 읽는 방법:
1) 시퀀셜 엑세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식.
인덱스 리프 블록은 앞뒤를 가리키는 주솟값 통해 논리적으로 서로 연결되어 있기 때문에, 이ㅣ 주솟값을 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이다. (Full Table Scan)
2) 랜덤 액세스 : 논리적, 물리적인 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식.
DB 버퍼캐시란?
- 라이브러리 캐시는 SQL과 실행게획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드캐시'인 반면,
- DB 버퍼캐시는 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는데에 목적이 있는 '데이터 캐시'
데이터 블록 읽을 떄는 항상 버퍼캐시부터 탐색한다.
논리적I/O vs 물리적I/O
1) 논리적 블록 I/O : SQL을 처리하는 과정에 발생하 총 블록 I/O. (일반적을 메모리상의 버퍼 캐시를 경유하므로 메모리 I/O = 논리적 I/O)
* SQL을 수행하면서 읽은 총 블록 I/O = 논리적 I/O
= DB 버퍼캐시에서 블록을 읽은 횟수 (모든 블록은 DB 버퍼캐시를 경유해서 읽기 떄문)
= 메모리 I/O
2) 물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O. (SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스 하므로 논리적 블록 I/O 중 일부를 물리적으로 I/O 한다)
물리적 I/O = DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O
버퍼캐시 히트율: 버퍼캐시 효율을 측정하는데 사용하는 지표
BHCR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) x 100
= ( (논리적 I/O - 물리적 I/O) / 논리적 I/O ) x 100
= ( 1- (물리적 I/O) / (논리적 I/0) ) x 100- 즉, 읽은 전체 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율.
- 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.
물리적 I/O = 논리적 I/O * (100% - BCHR)- 즉, 논리적 I/O는 일정하므로 물리적 I/O는 BCHR에 의해 결정된다. 하지만 BCHR은 시스템 상황에 따라 달라지므로 물리적 I/O는 결국 시스템 상황에 의해 결정되는 통제 불가능한 외생변수!
그렇다면 논리적 I/O를 줄이는 방법?
=> SQL 튜닝을 통해 논리적 I/O를 줄임으로써 물리적 I/O 줄이기.
Single Block I/O vs Multiblock I/O
1) Singleblock I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
2) Multiblock I/O : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
댓글