SQL 옵티마이저

1. SQL 옵티마이징 원리

• 규칙기반 옵티마이저(RBO)는 미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행계획을 선택한다.
• 비용기반 옵티마이저(CBO)는 오브젝트(테이블, 인덱스, 칼럼) 통계와 시스템 통계정보까지 이용해서 예상 비용을 산정하고 그중 예상 비용이 가장 낮은 실행계획을 선택한다. 
• CBO는 SQL 쿼리문과 옵티마이징 팩터, DBMS 제약 설정, 힌트, 옵티마이저 파라미터, DBMS 종류와 버전에 따라 수행능력과 방식이 다르다.
• CBO가 항상 최적의 실행계획을 선택하는 것은 아니다. 옵티마이저 팩터가 부족하거나 통계정보가 부정확하고 바인드 변수 사용 시 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문이다. 또한 일정 부분 규칙에 의존하기도 하며 애플리케이션 특성에 따라서도 성능이 달라진다.
• 선택도는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말하며 CBO는 선택도를 가지고 카디널리티를 구하고 다시 비용을 구해 인덱스를 사용할지 조인 순서와 방법 등을 결정한다. 히스토리그램이 있으면 히스토리그램으로 선택도를 산정하며 히스토리그램이 없거나 조건절에 바인드 변수를 사용하면 데이터 분포가 균일하다고 가정한 상태에서 선택도를 계산한다. 히스토리그램이 없을 경우 선택도 계산은 1/Distinct Value 개수이다. 
• 카디널리티는 특정 액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수를 말하며 총 로우 수 * 선택도로 계산한다. 
• 히스토그램은 데이터 분포를 나타내는 데이터로 분포가 균일하지 않은 칼럼을 조회할때 효과적이다. 오라클은 도수분포, 높이균형, 상위도수분포, 하이브리드 히스토그램을 제공한다. 우선 도수분포 히스토그램은 값별로 빈도수를 저장하며 칼럼이 가진 값의 수가 적을 때 사용한다. 높이 균형 히스토그램은 각 버킷의 높이가 같은 히스토그램으로 칼럼이 가진 값의 수가 많을 때 사용한다.
• CBO는 I/O 비용 모델과 CPU 비용 모델이 있고 I/O 비용에서 비용은 디스크 I/O Call 횟수를 의미한다.

2. SQL 공유 및 재사용

• SQL과 실행계획이 라이브러리 캐시에  캐싱되어 있어 바로 실행 단계로 넘어가는 것을 소프트 파싱이라고 하고 캐싱되어 있는 게 없어 옵티마이저가 최적화 과정을 거치고 실행 단계로 넘어가는 것을 하드 파싱이라고 한다.
• 캐시에서 SQL을 찾기 위해서는 SQL 문장 그 자체가 키 값이 되기 때문에 소프트 파싱을 하기 위해 띄어쓰기, 단어, 주석, 대소문자 모두 일치해야 한다.
• 리터럴 SQL의 경우 바인드 변수를 사용하자. 단 배치나 OLAP 환경에서는 지양하고 조건절 칼럼의 Distinct Value가 소수일 때는 칼럼 히스토리그램 정보를 활용하는 게 더 효율적일 때도 있기 때문에 두 가지를 제외하고 OLTP 환경에서는 바인드 변수를 사용하는 편이 좋다
• 바인드 변수를 사용하면 최소 수행될 때 최적화를 거치게 되는데 이때 조건절 칼럼의 데이터 분포가 균일하다고 가정하고 최적화를 수행하기 때문에 실행 시점에 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있다. 
• 애플리케이션 커서 캐싱은 소프트 파싱에서 더 최적화시키기 위해 문법 오류 확인, 캐시에서 실행계획을 찾고 실행하기 위해 메모리 공간을 할당하는 작업을 생략한다. 자바에서는 묵시적 캐싱 옵션을 사용하면 된다. 하지만 다이내믹 SQL이나 Cursor Variable을 사용할 때는 캐싱하는 효과가 사라진다.

3. 쿼리 변환

• 쿼리 변환이란 옵티마이저의 서브엔진 중에서 Query Transformer가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성한다. 개발팀의 판단이 반영되는 휴리스틱 쿼리 변환과 변환된 쿼리의 비용이 더 낮을 때만 변환하는 비용기반 쿼리 변환이 있다. 
• 서브쿼리 Unnesting은 중첩된 서브쿼리를 풀어내는 쿼리 변환이다. 중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 계층적인 관계가 존재하는데 Unnesting은 메인과 서브쿼리 간의 계층구조를 풀어 일반 조인문처럼 다양한 최적화 기법을 사용한다. 
• 뷰 Merging은 인라인뷰를 사용하여 조인하게 되면 옵티마이저가 인라인뷰를 풀어서 더 다양한 액세스 경로를 조사 대상으로 삼는 것을 말한다. 하지만 group by 절이나 distinct 연산이 있다면 오히려 성능이 나빠진다. 
• 조건절 Pushing은 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 기능이다. 개발자가 꼼꼼히 조건절을 사용해서 구성하면 pushing은 이루어지지 않지만 그렇지 않다면 옵티마이저가 판단해서 조건절 pushing을 수행한다. 조건절 Pushdown은 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어 넣는 것이다. 조건절 Pullup은 조건절  Pushdown의 반대 경우이다. 조인 조건 Pushdown은 NL 조인 수행 중에 outer 테이블에서 읽은 값을 건건히 inner 뷰 쿼리 블록 안으로 밀어 넣는 것이다.
• 조건절 이행은 조인 조건 Pushdown과  비슷한 맥락으로 같은 쿼리 블록에서 추론을 통해서 새로운 조건절을 생성해 주는 것이다.
• 불필요한 조인을 제거하기도 하는데 1:M 관계에서 1쪽 테이블을 사용하지 않는다면 M쪽 테이블만 읽도록 쿼리를 변환하는 것으로 PK와 FK(not null) 제약이 설정되어 있어야 한다.
• OR-Expansion은 조건절에 각각 다른 칼럼을 조건을 정하고 or 연산을 사용했을 때 옵티마이저가 or 연산을 Union all 연산으로 변환하는 것이다. 
• 그 외로 집합 연산을 조인으로 변환하거나 조인 칼럼에 IS NOT NULL 조건을 추가, 필터 조건 추가(바인드 변수로 between 검색할 때), 조건절 비교 순서(옵티마이저가 선택도가 낮은 칼럼의 조건식부터 처리)가 변하는 쿼리 변환이 있다.

 

참고 서적 - SQL 전문가 가이드