태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Oracle Upgrade에 의한 실행 계획 이상과 Stored Outline

오라클 2007.12.27 11:04
Oracle Upgrade시 가장 골치 아픈 문제를 뽑으라면? 아래 한 문장으로 요약할 수 있다.

"Oracle 9i에서 10g로 Upgrade했더니 쿼리 성능이 느려졌다!!"

절대 발생하지 말아야할 일임에도 불구하고 여전히 많은 시스템들이 Upgrade에 의한 쿼리 성능 저하로 고생을 하는 경우가 많다.

Oracle Upgrade이후 성능이 느려지는 이유는 너무나 다양하다. 이 중 가장 보편적으로 발생하는 것이 바로 실행 계획의 불안정성이다.

가령 9i에서는 Index Range Scan을 하던 쿼리가 10g로 업그레이드 후 별안간 Full Table Scan을 한다. 또는 Netsted Loop Join을 하던 쿼리가 별안안 Hash Join을 수행한다. 이런 경우를 흔히 "실행 계획이 변했다", "실행 계획의 안정성이 깨어졌다"라고 표현할 수 있겠다.

왜 이런 현상이 발생하는 것일까? 불행하게도 원인은 너무 다양하다. 하지만 오라클의 동작 원리를 이해하면 어느 정도 원인 추적이 가능하다.

오라클의 Optimizer는 간단한게 표현하면 다음과 같은 절차를 통해 실행 계획을 생성한다.

1. Query Rewrite
Query를 내부적으로 변환한다. 이 과정에서 Transitivity, View Merging, Subquery Unnesting, Predicate Pushing, Materialized View Rewrite 등이 발생한다.
2. Optimizing
통계 정보를 이용해서 비용을 계산하고 가장 저렴한 비용을 갖는 실행 계획을 찾는다.

즉, Query Rewrite 과정과 Optimizing 과정을 통해 실행 계획이 생성된다. 문제는 Oracle이 버전업될 때마다 이 과정들의 알고리즘들이 바뀐다는 것이다.

가령 8i에서 9i로 업그레이드시 _COMPLEX_VIEW_MERGING이라는 히든 파라미터가 추가되면서(8i에서는 히든 파라미터가 아니었고 기본값이 False였음) 기본값이 True로 변경되었다.
이로 인해 View를 사용하던 쿼리들에 대해서 View Merging이 훨씬 공격적으로 발생하게 되었다.
View Merging은 대부분의 경우 성능에 유리하지만, 특정 생각지 못한 상황에서는 변형된 쿼리에 의해 오히려 실행 계획의 변형을 불러온다. 이로 인해 갑자기 쿼리 성능이 저하되는 것이다.

Oracle은 이런 식으로 버전 업에 따른 Query Rewrite 기능 개선을 위해 많은 수의 히든 파라미터를 활용한다. 이 히든 파라미터들의 True/False 여부에 따라 예상치 못한 성능 문제가 발생하게 되는 것이다.

Oracle 10g에서 이런 류의 파라미터가 많다. _OPTIMIZER_TRANSITIVITY_RETAIN, _OPTIMIZER_COST_BASED_TRANSFORMATION, _OPTIMIZER_PUSH_PRED_COST_BASED, _GBY_HASH_AGGREGATION_ENABLED 등이 이에 해당한다. 경우에 따라서 이런 파라미터들을 다 False로 변경해야 하는 극단적인 경우도 발생한다. 때로는 OPTIMIZER_FEATURES_ENABLE을 과거 버전으로 회귀시킴으로써 발전된 Optimizer의 기능을 사용하지 못하게 막아버리는 경우도 허다하다.

비용(Cost)를 계산하는 방식의 변화도 종종 문제를 일으킨다. System Statistics와 CPU Model이 추가되면서 이전과는 다른 비용을 산출할 수 있고 이로 인해 실행 계획의 이상이 발생할 수 있다.

통계 정보 문제는 더 심각하다. 10g의 통계 정보 생성 방식이 9i와 다르기 때문에 10g에서 새로 통계 정보를 수집한 경우 실행 계획의 변화가 오는 경우가 발생할 수 있다. 통계 정보 방식의 미세한 차이점을 이해해야만 통계 정보 생성 방식을 최적화할 수 있다. DBMS_STATS 프로시저의 파라미터의 값의 기본값(Default)이 변경되는 것만으로 일부 쿼리는 극단적인 성능 저하를 일으킬 수 있다.

이런 모든 문제들을 사전에 방지하려면 역시 충분한 테스트를 수행하는 수 밖에 없다. 하지만 시간적/공간적 제약으로 완벽한 테스트를 수행한다는 것은 역시 불가능한 경우가 많을 것이다.

이런 실행 계획 이상 현상을 원천적으로 해결하기 위해 Oracle이 제안하는 기능이 "Stored Outline"이다. 즉 쿼리가 수행되는 윤곽(Outline)을 저장해두었다가 이를 사용함으로써 실행 계획 이상 현상을 막겠다는 것이다.

"Stored Plan"이 아니라 "Stored Outline"이라고 불리는 이유을 이해할 필요가 있겠다. 실행 계획을 저장하고 있을 것이라는 착각과는 달리 Stored Outline은 실행 계획을 생성하기 위한 "윤곽"만을 가지고 있다. 정확하게 말하면 실행 계획을 재현할 수 있도록 "힌트"를 저장하고 있다.
아래 쿼리 결과를 보면 이것을 잘 이해할 수 있다.

select name, category, sql_text from user_outlines;
==>
Name: SYS_OUTLINE_07122621581798118   
Category: DEFAULT   
Sql_Text: SELECT /*+ outline_proc */ COUNT(*) FROM PL_TEST

select * from user_outline_hints
where name = 'SYS_OUTLINE_07122621581798118';
==>
NAME                                            NODE    STAGE    JOIN_POS    HINT
SYS_OUTLINE_07122621581798118     1      1           1    FULL(@"SEL$1" "PL_TEST"@"SEL$1")
SYS_OUTLINE_07122621581798118     1      1           0    OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07122621581798118     1      1           0    FIRST_ROWS(1)
SYS_OUTLINE_07122621581798118     1      1           0    OPT_PARAM('_optimizer_mode_force' 'false')
SYS_OUTLINE_07122621581798118     1      1           0    OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
SYS_OUTLINE_07122621581798118     1      1           0    IGNORE_OPTIM_EMBEDDED_HINTS

Stored Outline을 이용하면 Oracle Upgrade에 의한 실행 계획 이상 현상을 대부분 방지할 수 있으며, 가장 손쉬운 방법이기도 한다. 하지만 실제로는 많이 사용되지 않는 것 같다.
아마 새로운 기능을 익히는데 대한 부담감도 있을 것이고 100% 방지는 되지 않는다는(어차피 힌트만 저장하고 있기 때문에) 불안감도 있을 수 있겠다. 혹은 특정 시스템에서의 실패담이 고착화되어 사용해서는 안되는 기능으로 인식되었는지도 모르겠다.

하지만 Oracle Upgrade시마다 히든 파라미터를 이전 버전과 동일한 수준으로 맞추어 Oracle의 새로운 기능을 사용하지 못한다든지, 통계 정보 수집을 이전 버전과 동일한 수준으로 해야한다든지하는 퇴행적인 방식보다는, Stored Outline을 사용하는 방식이 훨씬 체계적이고, 쉽다.

Stored Outline을 이용해 기본적인 실행 계획 호환성을 맞춘 다음, 쿼리 자체를 변경하는 작업은 여유를 가지고 진행할 수도 있다. 실제로 많이 사용되고 경험담이 공유되었으면 하는 바램이 있다.

PS)
Oracle 11g에서는 Stored Outline이 deprecated되었고(지원은 되지만 더이상 발전은 없음), SQL Plan Baseline이라는 기능이 추가되었다.





신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바