SQL?옵티마이저?프로시저?SQL최적화 방법?소프트파싱?하드파싱?는 무엇일까? with oracle

본 포스팅은 '친절한 SQL 튜닝' 책을 보고 정리한 내용입니다.

 

 

 

Contents

---

 

 

SQL란?

SQL은 Structured Query Language의 줄임말이다. 말그대로 구조적 질의언어이다.

 

 

프로시저란?

SQL은 기본적으로 구조적 집합적이고 선언적 질의언어이다.  프로시저는 이러한것들을 결과 집합을 만드는 역할을한다.

 

 

옵티마이저란?

프로시저를 만들어내는 DBMS 내부 엔진이며, 옵티마이저가 존재하지 않았을때 사람이 직접 프로시저를 코드로 짜서 데이터를 출력했다. 하지만 옵티마이저가 등장함으로써 사람대신 프로시저 프로그래밍을 직접 해준다.

 

 

SQL최적화란?

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전과정을 'SQL최적화'라고한다.

 

1.SQL 파싱

사용자로부터 SQL을 전달받으면 SQL파서는 파싱을 진행한다.

 

파싱 트리 생성 -> Syntax체크 -> Semantic 체크

 

1-1. 파싱 트리 생성 : SQL문을 분석하여 파싱 트리생성

1-2. Syntac체크 : 문법적 오류 체크

1-3. Semantic 체크 : 의미상 오류가 없는지 확인.

 

2.SQL 최적화

파싱이 끝난후 옵티마이저는 SQL 최적화를 진행한다. SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 실행경로를 생성하고 가장 효율적인 것을 선택한다.

 

3.로우 소스 생성

SQL 옵티마이저가 선택한 최적 경로를 실제 실행가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계이며, 로우 소스 생성기가 그 역할을 한다.

 

 

 

 

라이브러리 캐시 및 SGA(System Global Area)

위 과정 SQL 파싱 > SQL 최적화 > 로우소스 생성과정을 거쳐 생성된 내부 프로시저를 한번만 쓰기엔 리소스 낭비가 많기에 반복 재사용할수 있도록 캐싱해두는 메모리 공간을 라이브러리 캐시라고한다.

 

 

라이브러리 캐시는 SGA(System Global Area) 구성요소이며, SGA는 서버 프로세스 와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.

 

 

 

 

소프트 파싱,하드 파싱

사용자가 SQL문을 작성하고 전달 > DMBS가 SQL 파싱 >해당 SQL이 라이브러리 캐시에 존재하는지 확인

1. 캐시에 존재하면 바로 실행한다 -> 소프트 파싱
2. 캐시에 존재하지않다면 다시 최적화 및 로우 소스 생성한다. -> 하드파싱

 

SQL 속도에 영향을 끼치는것?

sql이 느린이유는 디스크 I/O 때문이다. OS에서는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을잔다. 즉, 일을 하지 않는다.

프로세스가 일을 하지않고 잠을 자는 이유는 여러가지이지만 대체로 I/O때문에 잠을잔다.

프로세스는 생명주기를 갖는다.

 

생성,종료 전까지 준비,실행,대기 상태를 반복한다. 여러프로세스가 하나의 cpu를 공유할수 있지만 , 특정 순간에는 하나의 프로세스만 cpu를 사용할수 있기 때문에 이런 메커니즘이 필요하다.

그러므로 프로세스는 I/O가 필요하면 cpu를 OS 반환하고 대기상태가 되며 I/O가 끝날때까지 잠을 자니 디스크 I/O가 많을수록 성능이 느리다.

 

테이블스페이스,세그먼트(테이블),익스텐트,블록

익스텐트 : 블록들을 보관하는 녀석 익스텐트는 익스텐트 맵이라는것이 존재하며 , 첫번째 블록 주소값을 갖는다. 

블록 : 데이터 raw를 갖는녀석

 

시퀀셜 액세스 vs 랜덤 액세스

시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식 익스텐트맵에서 첫번째 블록부터 시퀀셜 액세스를 진행한다면 이것을 TABLE FULL SCAN이라 부름

랜덤 엑세스 : 논리적,물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식.

 

 

 

DB버퍼 캐시

자주 읽는 블록을 캐싱해 두는 장소이며 , 반복적인 I/O Call을 줄이는데 목적이 있다.

 

 

 

논리적I/O , 물리적 I/O

일반적으로 메모리I/O를 논리적 I/O라 칭하고 , 디스크 I/O를 물리적 I/O를 칭한다. 

물리적 I/O는 연속적인 반복 실행했을경우에 계속 줄어드는 경향이있다. 왜냐하면 반복적인 블록은 DB버퍼 캐시에 저장되므로 연속적으로 실행하면 줄어든다. 하지만 한참후에 다시 실행하면 다시 물리적 I/O가 늘어난다. DB버퍼캐시에 다른 데이터 블록이 채워지기 때문이다.

 

버퍼캐시 히트율(BCHR)

BCHR = (1-(물리적 I/O) / (논리적 I/O)) *100

BCHR은 캐시에서 바로 찾은 비율을 나타낸다. BCHR 공식에서 성능 원리를 발견할수있는데, 실제 SQL 성능을 향상 하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다는 사실이다.

 

물리적I/O = 논리적 I/O x (100%-BCHR)

 

논리적 I/O는 일정하므로 물리적 I/O는 BCHR에 의해 결정된다. BCHR은 시스템 상황에 따라 달라지므로 물리적 I/O는 결국 시스템 상황에 의해 결정되는 통제 불가능한 외생변수이다. 즉, SQL 성능을 높이긴위해선 논리적 I/O를 줄여야만 하는것이다.그리고 이러한 논리적 I/O를 줄이는것이 물리적 I/O를 줄이고 이것이 바로 SQL 튜닝이다.

 

Single Block I/O vs MultiBlock I/O

혹자는 버퍼캐시에 모든 데이터를 넣고 가져오면 더빠를것이니 버퍼캐시를 늘리면 되지 않냐고 한다. 물론 메모리 DB가 존재하긴 하지만  메모리는 매우 비싸고 기술적인 한계때문에 전체 데이터중 일부만 캐시에 적재해서 읽는 방식으로 DB는 진화해왔다.

Single Block I/O

• 물리적 I/O 콜을 할때 메모리에 한 블록씩 적재하는 방식
• 인덱스를 이용할때 기본적으로 사용한다.
• 소량 데이터를 읽을때 주로 사용한다

MultiBlock I/O

• 손수레를 이용하는 것처럼 여러 블록을 담고 메모리에 적재하는 방식
• 전체 스캔을 이용할때 사용한다.
• MultiBlock I/O 단위가 크면 프로세스 잠자는 횟수를 줄여준다. (성능 향상)

MultiBlock 단위가 크면 많은 데이터를 읽을때 성능이 좋아지는 이유?

읽고자 하는 블록을 DB버퍼 캐시에서 찾지 못하면 해당 블록을 읽기위해 디스크 I/O를 한다. 이때 프로세스는 잠을 자는데 대용량 테이블 일경우 여러번 디스크를 갔다와야하니 많은 잠을 자게 된다. 하지만 MultiBlock I/O의 크기를 늘리면 한번갈때 많은 양을 담고 메모리에 적재하므로 프로세스가 잠자는 횟수가 줄어든다 그래서 성능이 좋아진다. 참고로 Multiblock I/O는 디스크상에 '인접한' 블록들을 한꺼번에 읽으며 , '인접한 블록' 은 익스텐트에 속한 블록을 의미한다. 이방식으로 읽더라도 익스텐트 경계를 넘지 못한다.

 

Table Full Scan vs Index Range Scan

Table full scan

은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다. 캐시에서 못찾으면 '한 번의 수면'을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O 하는 메커니즘 이다.그래서 이방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다. 하지만 Multiblock I/O가 아무리 좋아도 수십~수백건의 소량 데이터를 찾을때 수백만~수천만건 데이터를 스캔하는 것은 매우 비표율적이다. 이럴때 인덱스 스캔을 사용해야한다.

 

Index Range Scan

은 랜덤엑세스와 Sinblg Block I/O 방식으로 디스크 블록을 읽는다. 캐시에서 블록을 못찾으면, 레코드 하나를 읽기위해 매번 잠을자는 I/O 메커니즘이다. 그래서 많은 데이터를 읽을때는 Table Full Scan보다 불리하며 , 여러번 I/O 한다는것은 그만큼 프로세스가 잠을 많이 잔다는것이고 이것은 스토리지 성능이 수십배 좋아져도 성능이 조금 밖에 나아지지 않는다.