[DB] SQL 튜닝 part 1 - 옵티마이저

대용량 데이터에서 일부 데이터를 빠르게 자주 읽는 OLTP성 작업은 원하는 데이터에 얼마나 빠르게 접근하는 지가 성능을 좌우한다.

그래서 SQL을 튜닝하여 불 필요한 데이터 스캔을 막음으로써 데이터 엑세스 속도를 높여 성능을 개선할 필요가 있다.

이에 대해 공부한 내용을 옵티마이저, 인덱스, 조인 순서로 정리하고자 한다.

 

참고로 대용량 데이터 가끔 Full Scan하는 경향을 띄는 OLAP 처리의 경우 튜닝의 주요 포인트가 Hash Join, 병렬처리 등이기 때문에 SQL 튜닝은 큰 효율을 발휘하지 못한다.

 

SQL 옵티마이저

옵티마이저(Optimizer)는 SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 생성해 주는 DBMS 내부의 핵심엔진이다. 

사용자로부터 입력 받은 SQL문장과 데이터베이스의 통계자료를 기반으로 최소의 비용으로 데이터에 엑세스 할 수 있는 실행계획을 세우고 프로시저를 자동으로 생성해 준다.

 

출처 : DBGuide.net

SQL 처리과정

SQL Parsing -> Optimization -> Row-Source Generation -> Execution

 

SQL Parsing

SQL 문장에 문법적 오류를 체크하고 Shared Pool에 SQL문장에서 필요로 하는 데이터가 캐시되어 있는지 확인

 

Optimization

옵티마이저가 DB 통계자료와 SQL문장을 기반으로 실행계획을 생성한다.

 

Row Source Generation

SQL문장으로 생성된 실행계획을 기반으로 실행 가능한 형태로 코드 또는 프로시저를 생성한다.

 

실행계획

DB통계자료를 기반하여SQL을 분석하고 최적의 데이터 처리절차를 트리 구조로 표현한 것

 

옵티마이저 힌트

SQL 문장에 포함시켜 옵티마이저가 실행 계획을 세울 때 원하는 형태로 유도할 때 사용된다.

데이터베이스 통계자료 수집이 제때 수행되지 않으면, SQL 작업시점에 옵티마이저가 비효율적인 실행계획을 세울 수 있기에 힌트를 사용하여 최적의 실행계획을 세울 수 있도록 유도한다.

 

힌트 사용법

SELECT /*+ hint*/ * FROM test;

 

자주 사용하는 힌트 리스트

Access - index : index scan하도록 유도 - index_desc : index를 역순으로 scan하도록 유도 - index_ffs : index fast full scan하도록 유도 - index_ss : index skip scan하도록 유도

Join 순서 - ordered : from 절에 나열된 순서로 테이블을 조인 - leading : leading 힌트 괄호에 조인순서를 명시 ( ex> leading(test test2 test3) ) - swap_join_inputs : 해시 조인 시, 해시맵을 생성할 테이블을 명시적으로 선택 ( ex> swap_join_inputs(test2) )

Join 방식 - use_nl : Nested Loop join으로 유도 - use_merge : Sort Merge Join으로 유도 - use_hash : Hash Join으로 유도

Query 변환 - use_concat : 조건절에 사용된 or 또는 in 조건을 union all로 내부적으로 변환

그 외 다수

 

힌트 사용 주의사항

쿼리문에서 테이블 명을 Alias하여 사용했다면 힌트에서 Alias를 명시하여 사용해야한다.

 

 

출처 : DBGuide.net

System Global Area(SGA) 

DB Buffer Cache, Log Buffer, Shared Pool을 포함하는 캐시 메모리 영역

 

DB Buffer : SQL문이 실행되면서 DB Block에서 메모리로 캐시 된 테이블 레코드 블럭

 

Shared Pool

Library Cache, Data Dictionary Cache 포함

 

Library Cache

최적화 과정을 거친 SQL문장과 실행 계획의 캐시

 

소프트 파싱

호출된 SQL문장의 데이터가 Library Cache에 존재하여 Table Record I/O Call X

 

하드 파싱

Library Cache 데이터가 없어 DB 블럭에 엑세스하는 I/O 발생

 

SQL 공유

SQL문장은 그 자체로 캐시영역에서의 식별자이기 때문에 아래의 SQL문장들은 전부 다른 SQL문장으로 인식되어 개별적으로 캐시된다.

SELECT * FROM emp WHERE empno = 1;	대/소문자
select * from EMP where EMPNO = 1;	대/소문자
select * from emp where empno = 1   ;	공백 포함
SELECT /*+ hint */ * FROM emp WHERE empno = 1;	옵티마이저 힌트 사용
SELECT /* comment */ * FROM emp WHERE empno = 1;	주석 추가

 

하나의 SQL문장을 공유하기 위해서는 바인드 사용하여 내부적으로 생성되는 프로시저의 형태를 동일하게 만들어준다.

SELECT * FROM EMP WHERE EMPNO = :EMP_NO;

 

바인드 변수의 부작용

조건에 해당하는 값의 비율에 따라 최적의 실행계획이 다름에도 불구하고 값이 바인딩 되는 시점이 실행계획 생성 이후이기 때문에 DB 데이터와 비교하는 방식이 비 효율적일 수 있다.

이를 해결하기 위해서는 데이터 분포에 따라 실행계획을 분리할 수 있도록 하는 SQL문장을 작성해야한다.

 

바인드 변수 사용가 비효율적인 예

조건절 컬럼의 값의 종류가 많지 않을 경우

OLAP성 배치 작업으로 수행시간이 긴 경우

호출 빈도가 낮은 경우

 

데이터 저장구조

Tablespace > Segment > Extent > Block > Row

하나의 테이블스페이스는 여러 데이터파일로 구성

Block들은 연속된 공간이지만 Extent끼리는 비 연속 공간

 

Segment 종류

Table, Index, Partition, LOB

 

데이터베이스가 느린 이유?

I/O Call이 발생하면 결과값을 반환받기 전까지 CPU resource를 반난하고 Transaction이 Sleep되기 때문이다.

 

SQL튜닝

자주 읽는 블록에 대한 반복적인 I/O Call 발생을 최소화시켜 Sleep으로 발생하는 응답 지연을 줄이는 것

I/O에 영향을 주는 요소들은 인덱스, 조인, 파티셔닝 등이 있다.