[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 5. SQL옵티마이저

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.
 
 
3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

7. Lock과 트랜잭션 동시성 제어

 

 

 

 

 

비용기반 옵티마이저 (CBO)
- 통계정보 : 데이터양, 컬럼값의 수, 컬럼값 분포, 인덱스 높이, 클러스터링 팩터, 최대값, 최소값, NULL개수, 히스토그램 등
- 시스템 통계정보 : CPU속도, Single Block I/O 속도, Multi Block I/O 속도, Multi Block 개수 등
- 통계정보를 사용해서 실행계획을 세운다.
- 실행계획 선택에 대한 내부 규칙이 있다.
- 효율적이다.
- 버퍼 캐싱 효과는 고려하지 않는다. (디스크 I/O 를 기준으로 한다.)

규칙기반 옵티마이저 (RBO)
- 통계정보를 전혀 사용하지 않고, 단순히 규칙에만 의존한다.
- 대량 데이터 처리에 적합하지 않다.
- 인덱스 구조, 연산자, 조건절의 형태로 우선순위 규칙에 따라 최적화 한다.
- 인덱스가 있으면 무조건 인덱스를 사용한다. : Full Table Scan 이 우선순위가 가장 낮다.
- 부등호보다 between 이 우선순위가 높다.

옵티마이저 서브엔진
- Query Transformer : 쿼리 변환 과정
- Estimator : 예상 비용 산정
- Plan Generator : 실행계획 후보군 생성

스스로 학습하는 옵티마이저 (Self Learning Optimizer)
- Adaptive Cursor Sharing : 바인드변수에 따라 실행계획을 선택적으로 사용
- Statistics Feedback : 실행계획의 로우수와 실제 로우수가 다를때, 다음번에 이전 로우수에 따른 실행계획이 수립되도록 함
- Adaptive Plans : 런타임에 실행계획을 변경


최적화

전체 처리속도 최적화 (ALL_ROWS)
- 전체를 읽기 때문에 시스템리소스(I/O, CPU, 메모리) 를 적게 사용하는 실행계획을 선택

최초 응답속도 최적화 (FIRST_ROWS, FIRST_ROWS_N)
- 응답속도가 빠른 실행계획을 선택
- 2-Tier 클라이언트/서버 구조 : 커서를 오픈해서 데이터를 Fetch 하다가 중간에 멈출 수 있는 기능
- 인덱스를 많이 선택하고, 해시조인보다 NL 조인을 더 많이 선택한다.

SQL 최적화 과정
- 테이블, 컬럼, 인덱스 정보, 각각 통계 정보를 사용한다.
- 시스템 통계정보(CPU 속도 등) 을 사용한다.
- 옵티마이저 관련 파라미터 사용
- 통계 정보를 사용한다. 통계 정보를 저장하지는 않는다.

SQL 파싱과 최적화 순서
1. Parser : SQL 개별 구성요소를 분석해서 파싱트리를 만든다.
2. Query Transformer : SQL 을 일반적이고 표준적인 형태로 변환
3. Plan Generator & Estimator : 실행 후보군을 도출하고, 비용을 계산
4. Row-Source Generator : SQL 엔진이 실행할 수 있는 프로시저 형태로 포매팅


SQL 파싱
- 소프트파싱 : SQL 이 라이브러리 캐시에 존재하면 이를 사용하여 실행
- 하드 파싱 : SQL 이 라이브러리 캐시에 존재하지 않으면 최적화 및 로우 소스 생성 단계까지 모두 거쳐서 실행

선택도(Selectivity)
- 조건절에 의해 선택될 것으로 예상되는 레코드 비중 : 1 / NDV

카디널리티(Cardinality)
- 조건절에 의해 선택될 것으로 예상되는 레코드 수 : 전체레코드수 * 선택도

I/O 비용 모델의 비용(Cost)
- 예상되는 디스크 I/O Call 횟수

CPU 비용 모델의 비용(Cost)
- 예상소요시간을 Single Block I/O 횟수로 환산한 값


서브쿼리
- 하나의 SQL 문장에서 괄호로 묶은 별도의 쿼리 블록

인라인 뷰
- FROM 절에 나오는 서브쿼리로 레코드를  선택하는 쿼리다.

중첩된 서브쿼리
- WHERE 절이나 HAVING 절과 같은 조건절에서 사용되는 서브쿼리로, 메인 쿼리의 데이터를 필터링하거나 비교할 때 사용

스칼라 서브쿼리
- 주로 SELECT 절이나 다른 쿼리의 일부로서 단일 값을 반환하는 서브쿼리

no_unnest
- 중첩된 서브쿼리를 각각 그대로 FILTER 방식으로 처리한다.
- 서브쿼리의 결과가 작을 때 유리하다. : 캐싱
- 조건절의 스칼라 서브쿼리 : 실행계획에 FILTER 로 나타나게 된다.

unnest
- 중첩된 서브쿼리를 풀어낸다.
- 조건절의 스칼라 서브쿼리 : 실행계획에 NL 조인으로 나타나게 된다.
- unnest 를 사용하게 되면, rownum 조건을 제거해야 한다.

use_concat
- OR 조건을 union all 로 변환해준다.

- where job = 'cleark' or deptno = 20 => ... where job = 'cleark' union all ... where deptno = 20

no_expand
- OR 조건을 union all 로 변환하지 않고 그대로 수행하도록 한다.


merge
- 뷰머징 : 인라인뷰를 해체하여 상위 쿼리로 올린다.

no_merge
- 인라인뷰를 해체하지 않고 그대로 수행

push_subq
- 일반적으로, 조건절의 FILTER 조건인 서브쿼리는 제일 마지막에 실행된다.
- push_subq 를 사용하면, FILTER 조건인 서브쿼리를 먼저 실행되도록 한다.
- /*+ no_unnest push_subq */


push_pred
- 메인테이블과 인라인뷰의 JOIN 시에, 조인조건을 인라인뷰 안으로 유도한다. : 인라인뷰 성능향상
- /* no_merge push_pred */



바인드 변수
- 파라미터 드리븐 방식으로 SQL을 작성하는 방법
- SQL 의 재사용 가능
- 바인드 변수 사용 시 옵티마이저는 균등 분포 가정하고 비용을 계산한다.


애플리케이션 커서 캐싱
- Parse Call을 발생시키지 않고 SQL을 반복 수행하는 기능
- Parse Call이 최초 한번만 발생하고 이후로는 발생하지 않는다.