<오라클 성능 고도화 원리와 해법2> Ch03-03 옵티마이저의 한계

오라클 성능 고도화 원리와 해법2 - Ch03-03 옵티마이저의 한계

앞서 옵티마이저 행동에 영향을 미치는 요소들에 대해 살펴봤는데, 강조하고자 하는 핵심은 옵티마이저가 절대 완벽할 수 없다는 것이다. 옵티마이저도 결국 사람이 만든 소프트웨어 엔진에 불과하며, 모든 프로그램이 업그레이드를 통해 조금씩 개선되듯 옵티마이저도 여러 가지 제약과 한계점들을 극복하며 발전해나가는 과정 속에 있다.

기술적으로도 저히 극복할 수 없는 한계점들이 있을 수 있고, 정보를 저장할 공간과 시간 제약(특히 OLTP 환경에서 파싱에 허용되는 시간은 극히 짧음) 때문에 아직 적용하지 못하는 최적화 기법들도 있다. ‘자동 튜닝 옵티마이저’라 불리는 오프라인 옵티마이저가 이런 사실을 잘 말해주는데, ‘튜닝 모드’에서 작동하는 이 옵티마이저는 시간에 구애받지 않고 충분한 시간 동안 동적 샘플링을 포함한 여러 기법을 활용해 튜닝을 실시하고 사용자에게 권고안을 제시한다.

자동 튜닝 옵티마이저

자동 튜닝 옵티마이저(Automatic Tuning Optimizer)를 ‘오프라인(Offline) 옵티마이저’라고도 한다. 반대로 말하면, 우리가 흔히 말하는 옵티마이저는 ‘온라인(Online) 옵티마이저’ 또는 ‘런타임(Runtime) 옵티마이저’란 셈이다. 자동 튜닝 옵티마이저는 통계를 분석하고, SQL 프로파일링(Profiling)을 실시하며, 액세스 경로 및 SQL 구조 분석을 통해 SQL 튜닝을 실시한다. 튜닝 모드에서 작동하는 이 옵티마이저에게는 한 문장을 튜닝하는 데에 런타임 옵티마이저보다 훨씬 긴 시간(예를 들어, 10분)이 주어진다. 넉넉한 시간 동안 풍부한 정보를 수집 및 활용함으로써 데이터 액세스 비용과 카디널리티를 보다 정확하게 계산할 수 있다. 예를 들어, 동적 샘플링을 통해 부가적인 정보를 수집하고, 심지어 부분적인 실행을 통해 예측치(조인 카디널리티 등)를 검증함으로써 잘못된 정보를 조정하는 테크닉을 사용한다. 그렇게 각 SQL 단위로 수집된 프로파일(상관관계 있는 컬럼 간 결합 분포, 조인에 의한 테이블 간 상관관계 등 해당 SQL만을 위한 보조적인 통계 정보)을 데이터 딕셔너리에 영구 저장해 런타임 옵티마이저가 참조할 수 있도록 하는 기능도 제공한다(Sotune category 파라미터 참조). 자동 튜닝 옵티마이저 기능을 활용하려면 SQL Tuning Advisor라 불리는 서버 유틸리티를 이용하면 된다. SQL Tuning Advisor에 SQL 문을 입력하면 내부적으로 자동 튜닝 옵티마이저를 호출해 SQL 분석을 실시한다. 분석이 완료되면 SQL 성능을 높이기 위해 사용자가 취해야 할 조치 사항들을 보고서 형태로 출력해준다.

요컨대, 옵티마이저는 우리가 경험하고 있는 것보다 이미 높은 수준의 테크닉을 갖고 있지만 현실적인 제약 때문에 실 운영 환경에 적용하지 못하고 있다는 뜻이다. 주로 시간과 수집해야 할 정보량이 문제이며, 구체적으로 어떤 제약들이 있는지 지금부터 살펴보자. 옵티마이저의 현실적인 어려움이 무엇인지 이해한 사용자만이 적절한 도움을 줄 수 있다.

(1) 부족한 옵티마이징 팩터

1권 6장에서 IVO 효율화 원리에 대한 설명을 마치면서 강조하였듯이, 옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫이다. 적절한 인덱스도 제공하지 않은 채 옵티마이저가 고성능 실행 계획을 수립해주기를 기대해선 안된다. 이미 존재하는 길을 찾아줄 뿐 옵티마이저가 없는 길까지 만들어낼 수는 없는 일 아닌가.

(2) 부정확한 통계

정보는 곧 비용이다. 많은 정보를 수집• 보관한다면 그만큼 좋은 결과를 낼 수 있겠지만 현실적으로 100% 정확한 통계를 유지하기는 어렵다. 이런 현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다 보니 실제 데이터와 불일치가 발생하기 마련이다.

샘플링 방식으로 통계를 수집할 때는 정확성 문제와 더불어 안정성 측면에도 관심을 기울여야 한다. 분포가 균등하지 않은 컬럼이라면 수집할 때마다 통계치가 바뀔 수 있어 실행 계획을 불안정하게 만든다.

통계 수집 주기도 매우 중요하다. 특히, 어느 날 갑자기 데이터가 아주 많이 변경되거나 새로 입력됐을 때(2차 가공을 위한 임시 테이블에 흔히 발생) 곧바로 통계를 재수집해주지 않는다면 옵티마이저가 잘못된 선택을 하더라도 나무랄 수 없다.

(3) 히스토그램의 한계

부정확한 통계의 연장선으로 볼 수 있는데, 히스토그램 버킷 개수로 254개까지만 허용된다는 점도 옵티마이저에겐 중요한 제약 사항이다. 컬럼의 Distinct Value 개수가 그 이상이면 모든 값 별로 빈도수를 기록해둘 수 없고, 이때는 뒤에서 설명하겠지만 높이 균형(height-balanced) 히스토그램을 사용하게 되므로 발생빈도가 낮은 값들(non-popular value)에 대한 정확한 분포를 반영할 수 없다.

버킷 개수를 무한정 많이 가져갈 수 있으면 더 정확한 카디널리티를 구하는 데도 도움이 되겠지만, 정보를 수집하는 데 소요되는 시간과 저장 공간 때문에 생기는 어쩔 수 없는 제약 사항이다.

(4) 바인드 변수 사용 시 균등 분포 가정

컬럼 히스토그램이 있으면 옵티마이저가 그것을 가지고 조건절에 대한 선택도를 구한다. 그런데 아무리 정확한 컬럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 무용지물이 되고만다. 왜냐하면, 조건에 바인드 변수를 사용하면 옵티마이저가 균등 분포를 가정하고 비용을 계산하기 때문이다.

이는 옵티마이저가 가장 극복하기 어려운 난제다. 특히 OLTP 환경에선 라이브러리 캐시 부하를 피하기 위해 바인드 변수를 적극 사용하는 것이 필수 권고 사항이기 때문이다. 그런 탓에 이 제에 대해선 DBMS 벤더들이 아직 완벽한 해법을 내놓지 못하고 있다.

11g에서 적응적 커서 공유 기법을 도입하면서 문제 해결의 실마리가 약간 보이는 듯하지만 1권에서 설명했듯이 이 기능도 아직 완벽하게 문제를 해결했다고 보기 어렵다.

(5) 결합 선택도 산정의 어려움

조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다. 예를 들어보자.

select * from 사원 where 직급 = '부장' and 연봉 >= 5000;

직급이 (부장, 과장, 대리, 사원)의 집합이고 각각 25%의 비중을 갖는다. 그리고 전체 사원이 1000명이고 히스토그램 상 ‘연봉 = 5000’ 조건에 부합하는 사원 비중이 10%면, 옵티마이저는 위 쿼리 조건에 해당하는 사원 수를 25 = 100 X 0.25 X 0.1명으로 추정한다. 하지만 잘 알다시피 직급과 연봉간에는 상관관계가 매우 높아서, 만약 연봉이 5000만원 이상인 사원이 모두 부장이라면 실제 위 쿼리 결과는 100(= 1,000 X 0.1)건이다. 다른 예로서, 사원 급여와 상여금 간에도 상관계가 매우 높다.

이런 이유 때문에 카디널리티가 잘못 계산되면 다른 집합과 여러번 조인을 거치는 동안 카디널리티는 점점 더 부정확해지고 궁극적으로 옵티마이저가 잘못된 실행 계획을 수립하는 결과를 낳는다. 모든 컬럼간 상관계와 결합 선택도를 미리 저장해두면 좋겠지만 이것은 거의 불가능에 가깝다. 테이블 컬럼이 많을수록 잠재적인 컬럼 조합의 수는 기하급수적으로 증가하기 때문이다.

그 대안으로 오라클은 91부터 동적 샘플링을 통해 이 문제를 해결하려고 시도하고 있다. 소량의 데이터 샘플링을 통해 where 조건절에 사용된 두 개 이상 컬럼의 결합 분포를 구하는 기능으로 서, 동적 샘플링 레벨을 4 이상으로 설정할 때만 작동한다.

또한 11g부터는 사용자가 지정한 컬럼들에 대해 결합 선택도를 미리 수집해두는 기능을 제공하기 시작했다. 이 두 가지 대안 기능에 대해서는 6절(5) 항에서 다룬다.

(6) 비현실적인 가정

CBO는 쿼리 수행 비용을 평가할 때 여러 가정을 사용하는데, 그 중 일부는 매우 비현실적인 것들도 있다. 대표적인 예로서, Single Block I/O와 Multiblock I/O 비용을 같게 평가한다거나 캐싱 효과를 고려하지 않는다는 점을 들 수 있다.

I/O 비용 모델 하에서의 비용은 단순히 I/O Call 횟수(논리 또는 물리적으로 읽은 블록 개수가 아닌 V O Call 횟수)를 의미한다. 옵티마이저가 예상할 때 I/O Call 횟수가 가장 적은 실행 계획을 선택하는 셈이다. 그런데 I/O 비용 모델이 사용하는 기본 가정에 따르면 Single Block Read와 Multiblock Read의 비용은 같다. 상식적으로 생각해 한 번의 I/O Call로써 한 블록을 읽는 비용이 여러 블록(예를들어, 128 블록)을 읽는 비용보다 낮은데도 옵티마이저는 이 둘을 구분하지 않고 동일한 하나의 시스템 Call로 간주하는 것이다.

또한 옵티마이저는 다른 세션이나 다른 쿼리문에 의해 데이터 블록들이 이미 버퍼 캐시에 캐싱되어있을 가능성을 배제한다. 비용을 평가할 때 디스크 I/O Call 횟수만을 더한다는 것을 통해 이 사실을 알 수 있다. 다시 말해, 옵티마이저는 메모리 자원에 대한 최악의 상황을 가정하고 비용을 산정하는 것인데, 이는 DW 환경에 나적합한 가정이다.

이런 비현실적인 가정들을 보정할 수 있도록 오라클은 8i에서 아래 두 개의 파라미터를 제공하기 시작했다.

• optimizer index_caching
• optimizer index cost_adj

이 들 파라미터에 대해서는 7절에서 설명한다. 또한 IVO 비용 모델에서 CPU 비용 모델로 발전하면서 이 들 파라미터를 이용하지 않고도 옵티마이저의 비현실적인 가정들을 완화할 수 있는 방법들이 제공되는데, 이에 대해서도 7절에서 다룬다.

(7) 규칙에 의존하는 CBO

아무리 비용 기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다. 예를들어, 원격 (remote) 테이블이나 External 테이블에 대해서는 카디널리티, 평균행 길이, 블록수, 그리고 각종 인덱스 관련 통계 항목들에 대해 고정된 상수값을 사용한다.

1절에서 설명한 옵티마이저 모드 중 하나인 “first_rows” 모드를 사용할 때, ORDER BY 정렬을 대체할 수 있는 인덱스가 있다면 무조건 해당 인덱스를 사용하는 것이 좋은 예시이다.

또 다른 예로서, 4장에서 설명할 휴리스틱(Heurisic) 리변환을 들 수 있는데, 9i까지 옵티마이저는 뷰 또는 서브쿼리를 만나면 무조건 쿼리 블록을 풀어 메인 쿼리와 Merging하려고 시도한다. 즉, 규칙에 의해 움직이는 것이다. 물론, 10g에서 대부분 (쿼리변환이 항상 좋은 성능을 보장하는 경우를 제외하고) 쿼리변환이 비용 기반으로 개선되었다.

알파벳순 인덱스 선택 규칙

CBO가 사용하는 규칙과 관련해 꼭 기억해야할 사항이 있는데, 두 대안 인덱스의 예상 비용이 같을 때 알파벳 순에서 앞선 것을 선택한다는 사실이다. 이는 물리 데이터베이스 설계 단계에서 인덱스 명명 규칙을 정할 때 매우 중요한 시사점을 던져준다.

이처럼 두 인덱스의 예상 비용이 같아 비합적인 인덱스 선택이 이루어지는 것은 매우 흔한 일이다. 그런 현상을 발견했을 때 인덱스명을 바꿔줄 수 있으면 좋겠지만 실제 운영 중인 시스템에서 그러기는 쉽지 않다.

적어도 Unique한 조건으로 PK 인덱스를 액세스해야 하는 상황에서 이런 일이 발생하지 않게 하려면 인덱스명 규칙을 PK, XO1, XO2와 같은 식으로 정하는 것이 좋다. 참고로, 예상 비용이 같으면 오브젝트 ID가 큰 것을 우선적으로 선택하던 시절도 있었다.

(8) 하드웨어 성능 특성

옵티마이저가 기본적으로 개발팀이 사용한 하드웨어 사양에 맞춰져 있다. 따라서 운영 시스템의 실제 하드웨어 사양이 이와 다를 경우, 옵티마이저가 잘못된 실행 계획을 수립할 수 있는 가능성이 높아진다. 또한 애플리케이션의 특성에 따라 하드웨어 성능이 달라진다.

이런 한계점은 어느 정도 해결 방법을 찾은 상태다. 오라클 9i부터는 시스템 통계를 수집하는 기능이 도입되었지만, 실제 운영 중인 시스템에서 이를 제대로 활용하는 경우는 드물다. 또한, CPU 비용 모델이 기본 설정인 10g에서도 대부분의 시스템이 NoWorkload 시스템 통계를 그대로 사용하고 있다. 하지만 NoWorkload 시스템 통계를 사용한다 하더라도, 기본값이 아닌 실제 측정치를 사용해야 의미가 있다.

좋은 기능이 도입되었음에도 불구하고, 옵티마이저는 여전히 하드웨어 성능 특성을 반영하지 못한 채 최적화를 수행하고 있다.

동적 실시간 최적화(Dynamic Runtime Optimizations)

모든 데이터베이스 작업 부하가 매우 변화적이므로, 하드웨어 성능 특성을 반영한 실행 계획을 수립하더라도 쿼리 수행 시 시스템 부하 정도에 따라 최적이 아닐 수 있다. 정적인 통계 정보와 옵티마이저 모델만으로는 이 한계를 극복하기 어려우므로, 시스템 부하에 따라 실행 전략을 동적으로 조정하는 최적화 기법이 도입되고 있다. 이 기능의 핵심은 쿼리가 수행되는 시점의 시스템 상태에 따라 하드웨어 리소스를 적절히 배분해 주는 데 있다. 예를 들어, 시스템 부하 정도에 따라 병렬 쿼리의 프로세스 개수를 오라클이 동적으로 조절해 주는 기능을 들 수 있다. 또한 9부터는 PGA 메모리 크기를 자동으로 조절해 주기 시작했고, 10g부터는 SGA를 구성하는 서브 메모리 영역을 자동으로 조절해 주는 기능도 소개되었다. 이런 기능을 통해 고정된 하드웨어 리소스와 정해진 시간 동안, 개별 SQL이 아닌 전체 SQL의 처리량을 극대화할 수 있다. 쿼리 최적화가 단일 SQL 문 성능을 최적화하는 데 초점을 맞추는 반면, 동적 실시간 최적화는 수많은 SQL이 동시에 수행되는 환경에서 시스템 전체 최적화를 이루는 데 초점을 맞춘다.