오라클 데이터베이스 옵티마이저는 최적의 SQL 실행 계획 생성을 목표로 합니다. 하지만 다양한 요인(통계 정보의 부정확, 데이터 분포의 변화, 쿼리의 복잡성 등)으로 인해 항상 최적의 계획을 생성하는 것은 아닙니다. 이러한 문제를 해소하기 위해 오라클 데이터베이스는 적응형 쿼리 계획(Adaptive Query Plans, AQP) 기능을 제공합니다.
1. 적응형 쿼리 계획(AQP)의 개념
AQP는 쿼리 실행 중에 실행 계획을 동적으로 조정하는 쿼리 최적화 기능입니다.
일반적인 경우 옵티마이저는 여러 가지 예측을 기반으로 여러 가지 실행 계획을 미리 생성해 놓고 가장 효율적인 실행 계획을 선택해 실행합니다. 옵티마이저가 선택한 실행 계획은 쿼리 종료까지 변경할 수 없습니다.
AQP는 SQL을 실행하면서 실시간 통계 정보를 수집하고 예측과 실제 결과를 비교하여, 예측 결과와 실제 결과가 현저히 차이가 발생할 경우 미리 생성해 놓은 다른 실행 계획으로 전환합니다.
통계 정보가 부정확하거나 데이터 분포가 예상과 다를 경우에도 최적의 성능을 유지하는데 도움을 줄 수 있는 기능입니다.
2. 적응형 쿼리 계획 동작 원리
AQP는 크게 다음과 같은 단계로 작동합니다.
- 초기 계획 생성
쿼리 최적화기는 쿼리 실행 전에 기본 실행 계획(Default Plan)과 하나 이상의 대체 실행 계획(Alternative Plan)을 생성합니다. 기본 계획은 기존의 통계 정보를 기반으로 생성되며, 대체 계획들은 다양한 액세스 방법(Table Full Scan, Index Scan 등)이나 조인 방법(Hash Join, NL Join 등)을 사용하여 생성됩니다. - 실행 시간 통계 수집
최적화기는 기본 계획을 실행하면서 동시에 Optimizer Statistics Collector라는 특별한 기능을 통해 실행 시간 통계 정보를 수집합니다. 이 통계 정보에는 실제 행 수, 데이터 분포 등이 포함됩니다. 특히, 카디널리티(cardinality)에 대한 실제 건수가 중요합니다. - 계획 선택
특정 시점(예: 특정 조인 연산 이후)에서 Collector가 수집한 통계 정보를 기반으로 미리 생성해 놓았던 대체 실행 계획들과 비교하여 더 효율적인 계획을 선택합니다. 이 선택은 Dynamic Plan Coordinator에 의해 이루어집니다. - 계획 전환
선택된 계획으로 쿼리 실행이 전환되고 Collector는 더 이상 통계 정보를 수집하지 않고 다음 단계를 진행합니다. - 계획 저장
옵티마이저는 최종적으로 선택된 계획과 실행 시간 통계 정보를 저장하고 재사용할 수 있도록 합니다. 단, 통계 정보가 다시 변경되거나 시스템 환경이 바뀌면 새로운 계획을 생성할 수 있습니다.
3. 적응형 쿼리 계획이 사용되는 상황
- Cardinality 추정 오류: 옵타마이저가 테이블의 조건에 해당하는 결과 건수를 잘못 추정했을 때
- 데이터 분포의 변화: 테이블의 데이터 분포가 시간에 따라 변화하여 초기 통계 정보가 부정확해졌을 때
- 복잡한 쿼리: 조인, 집계, 필터링 등 여러 연산이 포함된 복잡한 쿼리의 경우
- 병렬 실행: 병렬 실행 환경에서 데이터 분배 전략을 동적으로 조정할 필요가 있을 때
4. 적응형 쿼리 계획의 활성화 및 제어
AQP는 OPTIMIZER_ADAPTIVE_PLANS 초기화 매개변수를 통해 활성화/비활성화할 수 있습니다. 기본값은 TRUE로 AQP가 활성화되어 있습니다. OPTIMIZER_ADAPTIVE_REPORTING_ONLY 매개변수를 TRUE로 설정하면 적응형 최적화는 실행되지 않고 보고서만 생성됩니다. 실제로 실행 계획이 어떻게 변화하는지 확인하는데 유용합니다.
5. 적응형 쿼리 계획 예시
다음은 두 개의 테이블, orders 와 products 를 조인하는 쿼리입니다.
SELECT o.order_id, p.product_name
FROM orders o, products p
WHERE o.product_id = p.product_id
AND o.order_date > '2023-10-26';AQP가 활성화되어 있다면 최적화기는 초기 계획으로 NL 조인을 선택하여 우선 실행합니다. 실행 시간에 orders 테이블의 실제 건수를 확인하여 결과가 예상보단 많으면 해시 조인으로 전환할 수 있습니다.
AQP가 활성화 되어있는 경우 DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADAPTIVE') 를 사용하여 적응형 실행 계획을 확인할 수 있습니다. 보고서에는 기본 계획과 실제로 선택된 계획이 모두 표시됩니다. ‘-‘로 표시된 부분은 실행되지 않은 대체 계획의 부분입니다.
6. 적응형 쿼리 계획의 장점
- 더 나은 성능: 실행 시간 통계 정보를 활용하여 최적의 계획을 선택함으로써 쿼리 성능을 향상시킵니다.
- 유연성: 데이터 분포의 변화나 예상치 못한 상황에도 유연하게 대처하여 최적의 성능을 유지합니다.
- 자동화: DBA의 개입 없이 자동으로 최적의 계획을 선택합니다.
7. 적응형 쿼리 계획의 단점
- 오버헤드: 실행 시간 통계 수집 및 계획 전환 과정에서 약간의 오버헤드가 발생할 수 있습니다.
- 복잡성: AQP의 동작을 이해하고 문제를 해결하는데 전문 지식이 필요합니다.
오라클 데이터베이스의 AQP는 쿼리 최적화의 효율성을 높이고, 다양한 상황에서 최적의 성능을 유지하는 데 매우 유용한 기능입니다. 하지만 AQP의 동작 원리를 제대로 이해하고 사용하는 것이 중요하며, 단순히 AQP를 활성화하는 것만으로는 항상 최적의 성능을 보장할 수 없다는 점을 명심해야 합니다. 실제 데이터와 쿼리 특성에 따라 AQP가 적절한지 여부를 신중하게 판단해야 합니다.