1. SQL과 옵티마이저
옵티마이저가 뭔가요?
SQL 옵티마이저는 최소비용, 최적의 경로를 선택하여 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 프로시저를 자동으로 생성해 주는 DBMS의 핵심기능이다.
옵티마이저의 수행 단계
-
사용자가 던진 쿼리 수행을 위해, 후보군이 될만한 실행계획들을 찾아본다
-
데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다
-
각 실행계획의 비용을 비교해서 최소비용을 갖는 하나의 실행계획을 선택한다
SQL을 파싱하고, 필요하면 SQL 최적화를 수행하며, 커서를 열어 SQL을 실행하면서 블록을 읽고, 읽은 데이터를 정렬해서 클라이언트가 요청한 결과집합을 만들어 네트워크를 통해 전송하는 일련의 작업들을 모두 서버 프로세스에서 처리한다.
2. SQL 처리 과정
소프트 파싱이란?
SQL 파싱을 거친 후 해당 SQL이 메모리에 캐싱 돼 있어 곧바로 실행단계로 넘어가는 것
하드 파싱이란?
SQL 파싱을 거친 후 해당 SQL을 메모리에서 찾지 못해 SQL 최적화 단계 및 Row-Source 생성 단계를 거치고 실행단계로 넘어가는 것
1. SQL 파싱
사용자가 던진 SQL을 가장 먼저 받아서 처리하는 엔진이 SQL 파서이다.
SQL 파서가 하는 일
-
SQL 문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱트리를 만듦
-
이 과정에서 사용자가 던진 SQL에 문법적 오류가 없는지 Syntax 체크
-
파싱트리를 만들면 Semantic 체크를 통해 의미상 오류 체크
-
모두 오류가 없다면 해싱 알고리즘을 이용해 해당 SQL 커서가 Shared Pool에 캐싱돼 있는지 확인
-> Shared Pool에서 찾은 SQL 문장이 현재 수행하려는 SQL문장과 100% 일치하더라도 파싱을 요청한 사용자가 다르거나 옵티마이저 관련 파라미터 설정이 다르다면 새로운 SQL 커서를 생성 -
Shared Pool에 캐싱돼 있다면 실행단계로 넘김 -> 소프트 파싱
Shared Pool에 없다면 SQL최적화 및 로우소스 생성 -> 하드 파싱
2. SQL 최적화
옵티마이저는 SQL 최적화를 진행할 때 3개의 서브 엔진을 사용한다
1. Query Transformer
사용자가 던진 SQL을 그대로 둔 채 최적화하는 게 아니라 우선 최적화하기 쉬운 형태로 변환을 시도한다.
물론 쿼리 변환 전후 결과가 동일함이 보장될 때만 그렇게 한다.
2. Plan Generator
하나의 쿼리를 수행하는 데 있어, 후보군이 될만한 실행계획들을 생성해 내는 역할을 한다.
오라클의 두 가지 탐색 테크닉
1. Adaptive search stratgy
쿼리 수행 시 예상되는 총 수행시간에 비해 쿼리 최적화에 걸리는 시간이 일정비율을 넘지 않도록하는 적응적 탐색 전략
2. Multiple Initial orderings heuristic
조인 순서를 무순위로 평가하는 게 아니라 최적의 실행계획을 발견할 가능성이 높은 순서대로 비용을 평가하는 것
3. Estimator
쿼리 오퍼레이션 각 단계의 선택도, 카디널리티, 비용을 계산하고, 궁극적으로 실행계획 전체에 대한 총 비용을 계산해 낸다.
각 단계를 수행하는데 필요한 I/O, CPU, 메모리 사용량 등을 예측하기 위해 데이터베이스 오브젝트 통계정보와 하드웨어적인 시스템 성능 통계정보를 이용한다.
여기서 사용하는 통계정보들은 오라클이 자동으로 수집해주거나 DB관리자들이 수집한다.
3. Row-Source Generation
SQL 최적화 과정에서 만들어진 실행계획을 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 작업을 수행한다.
Row-Source는 레코드 집합을 반복 처리하면서 사용자가 요구한 최종 결과집합을 실제적으로 생성하는데 사용되는 제어 구조를 말한다.
하드 파싱은 CPU를 많이 소비하는 몇 안 된는 작업 중 하나에 속한다.
하드 파싱 과정에서 Shared Pool과 라이브러리 캐시에 대해 발생하는 래치 경합도 CPU를 많이 소비하게 만드는 요인이다.
그리고 한번 수행할 때마다 내부적으로 많은 데이터 딕셔너리 조회를 수반하므로 하드 파싱은 우리가 생각하는 것 이상으로 그 비용이 매우 크다.
3. 라이브러리 캐시 구조
라이브러리 캐시는 Shared Pool 내에 위치하며, SQL 공유 커서 및 데이터베이스 오브젝트에 대한 정보를 관리한다.
SQL 커서뿐 아니라 컴파일을 거친 프로시저, 함수, 패키지, 트리거 등 PL/SQL 프로그램을 담는 PL/SQL Area도 라이브러리 캐시에 저장한다.
그리고 여기에 저장되는 정보의 단위를 라이브러리 캐시 오브젝트(LCO)라고 부른다.
라이브러리 캐시도 DB 버퍼 캐시처럼 해시구조로 관리된다. 즉, 해시 버킷에 LCO 핸들이 체인으로 연결돼 있고, 핸들을 통해 LCO 힙을 찾아가는 구조이며, 해시 함수를 통해 리턴된 해시값을 가지고 해시 버킷을 할당한다.
shared pool 래치와 library cache 래치 경합은 소프트/하드 파싱을 동시에 심하게 일으킬 때 발생하고, library cache lock과 library cache pin 대기이벤트는 주로 SQL 수행 도중 DDL을 날릴 때 발생한다.
-> 트랜잭션이 활발한 주간에 DDL문을 날려 데이터베이스 오브젝트 정의를 변경하면 라이브러리 캐시에 심한 부하를 유발하게 되므로 주의해야 한다.
라이브러리 캐시 최적화를 위한 튜닝 기법들
1. 커서를 공유할 수 있는 형태로 SQL 작성
바인드 변수를 사용해 같은 형태의 SQL에 대한 반복적인 하드파싱이 일어나지 않도록 한다.
2. 세션 커서 캐싱
라이브러리 캐시에서 SQL을 찾는 비용을 줄인다.
3. 애플리케이션 커서 캐싱
Parse Call 발생량을 줄인다.
