1. SQL 쿼리를 실행하면 일어나는 일
사용자가 SQL 쿼리를 실행하면 DBMS 내부에서 SQL 파서가 파싱을 한다.
파싱을 한다는 건 아래와 같은 일들을 하는 것이다.
- 문법 체크
- 오브젝트에 권한이 있는지 확인
- 쿼리문에 사용한 테이블이나 컬럼이 존재하는지 확인
이런걸 확인하고 통과하고나면 옵티마이저는 여러 실행경로를 생성하고 가장 효율적인 실행경로를 선택한다.
2. 옵티마이저란?
영단어로 Optimizer는 최적화하는 사람 또는 도구를 의미한다.
DBMS에서 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 골라주는 핵심 엔진이다.
옵티마이저는 오브젝트 및 시스템 통계정보를 바탕으로 실행계획의 예상비용(cost)를 산정하고 예상 비용이 가장 적게 드는 실행 계획을 선택한다.
3. 통계정보와 비용 계산 원리
비용 계산에 중요한 지표는 '선택도'다.
선택도란, 전체 레코드 중에 조건절에 의해 선택되는 레코드 비율을 뜻한다.
얼마전에 빨간머리앤 500피스 퍼즐을 맞췄는데, 효율적으로 퍼즐을 빨리 맞추기 위해서 네 종류의 퍼즐 조각으로 분류했었다.
1. 글씨가 포함된 퍼즐 조각
2. 하늘색 퍼즐
3. 녹색 퍼즐
4. 캐릭터그림이 포함된 퍼즐.
가장 처음 퍼즐을 맞출 때, 글씨가 포함된 퍼즐 조각만 골라 모았다.
이 때 선택도는 1/4이 된다.
4. 비용 계산 모델
1) I/O 비용 모델
Cost : 예상 I/O call 횟수
2) CPU 비용 모델
Cost : Single Block I/O를 기준으로 한 상대적 시간.
ex) Cost 가 100이라면, 우리 시스템에서 Single Block I/O를 100번 하는 정도의 시간
💡 'CPU 비용 모델' 이 더 최신 모델이다.
'I/O 비용 모델'은 옵티마이저가 최적이 아닌 실행계획을 수립할 수 있다. 왜냐하면, 애플리케이션이나 하드웨어 성능 특성을 고려하지 않기때문이다.
I/O비용 모델에서 Cost가 같은 비용으로 측정되더라도 시스템 사양에 따라, 애플리케이션 특성에 따라, 싱글 블록 I/O인지 멀티블록 I/O인지에 따라 소요시간이 모두 다를 수 있다.
5. 옵티마이저 모드
이 책을 읽으면서 옵티마이저에 검색모드가 있다는 것을 처음 알았다.
옵티마이저는 크게 두가지 모드가 있다.
1) ALL_ROWS : 전체 처리속도 최적화 모드
시스템 리소스를 가장 적게 사용하는 실행계획을 선택한다.
응답 속도보다 전체 처리 효율을 우선시 한다.
2) FIRST_ROWS 와 FIRST_ROWS_N : 응답속도 최적화 모드
사용자의 응답속도를 우선시 한다.
💡FIRST_ROWS는 되도록 쓰지말자.
FIRST_ROWS는 최초 응답 속도 최적화가 목표다.
그래서 현재는 더 이상 권장되지 않는 모드이다.
어느정도 데이터를 읽고 멈출지 설정한것이 아니기 때문에 예측가능한 비용산정이 어렵다.
FIRST_ROWS_N은 사용자가 초기 몇개의 행을 읽고 멈출지 설정해줄 수 있다.
'N'에 초기에 읽을 행 갯수를 설정해주면 된다.
✅ FIRST_ROWS_N 사용법
ALTER SESSTION SET OPTIMIZER_MODE = FIRST_ROWS_100단, 시스템이나 세션 수준에서 옵티마이저는 '1, 10, 100, 1000' 이 네 가지중 하나를 택해야한다.
하지만 SQL 문장 내에서 힌트로 FIRST_ROWS_N을 사용한다면 0보다 큰 정수값을 자유롭게 사용할 수 있다.
/*+ FIRST_ROWS(99) */힌트에 쓸때는 언더바(_)가 아닌 괄호() 안에 초기 행 갯수를 써주면 된다.
6. 개발자 마인드 장착하기
옵티마이저는 안내자일 뿐이다.
개발자라면 옵티마이저에만 의존하면 안된다.
최소한의 블록을 읽도록 쿼리를 작성하자.
옵티마이저가 실행계획을 적절히 찾을 수 있도록 단서를 제공하자.
(예를 들면, 조건절에 자주 사용하는 컬럼을 인덱스로 만들어 준다던가. 등등.)
필요한 경우, 옵티마이저 힌트를 사용해서 실행계획을 컨트롤하자.
🤔요즘 회사에서 DB파트 부장님과 소통할 기회가 많아졌는데, 친절한 SQL튜닝을 읽으며 부장님께서 가르쳐주셨던게 떠올랐다.
얼마전에 페이징쿼리를 작성할 때 명심해야할 꿀팁을 전수해주셨는데, 그 꿀팁이 그대로 책에 나와있었다...!!!
꿀팁을 요약하자면,
1. ROWNUM 페이징 방식보단 OFFSET/FETCH 로 페이징을 해라.
2. ROWNUM 페이징을 쓰는 경우에는 함수연산이나 서브쿼리를 페이징 처리가 끝난 뒤에 하도록 밖으로 빼라.
난 그동안 습관적으로 페이징 쿼리를 작성할때, 아래 예시처럼 조회하려는 전체쿼리를 감싼 뒤, ROWNUM조건을 걸어서 페이징 처리를 했었다.
SELECT *
FROM (
SELECT emp.*,
(SELECT dept_name FROM departments d WHERE d.dept_id = emp.dept_id) AS dept_name,
get_salary_bonus(emp.salary) AS bonus
FROM employees emp
)
WHERE ROWNUM <= 10;
페이징 쿼리를 이렇게 작성하게 되면, ROWNUM으로 조회 대상 페이지를 필터링하기 전에 모든 행에 대해 서브쿼리와 함수 연산을 수행한다.
따라서 출력대상이 아닌 데이터에도 불필요한 처리 비용이 발생하게 되고 조회 성능이 저하된다.
이제 1번, 2번 꿀팁을 적용한 쿼리를 살펴보자.
✅ 1번 꿀팁 적용한 쿼리
SELECT emp.*,
(SELECT dept_name FROM departments d WHERE d.dept_id = emp.dept_id) AS dept_name,
get_salary_bonus(emp.salary) AS bonus
FROM employees emp
ORDER BY emp.hire_date
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
Oracle 12c 버전 이상에서는 OFFSET/FETCH는 선택된 N개 행에 대해서만 서브쿼리와 함수를 수행하기때문에 TOP-N쿼리 최적화를 수행할 수 있어서 ROWNUM보다 조회 속도를 개선시킬 수 있다.
✅ 2번 꿀팁 적용한 쿼리
SELECT e.*,
(SELECT dept_name FROM departments d WHERE d.dept_id = e.dept_id) AS dept_name,
get_salary_bonus(e.salary) AS bonus
FROM (
SELECT emp.emp_id, emp.emp_name, emp.dept_id, emp.salary
FROM employees emp
WHERE ROWNUM <= 10
) e;
이렇게 필요한 컬럼만 먼저 조회한 후, 밖에서 부가적인 연산을 하도록 하면 출력대상에만 연산을 수행하기때문에 조회 성능이 개선된다.
오랜만에 칼퇴하고 공부하니까 기분 상쾌하다 굿
