[강정식의 오라클 이야기] 통계정보 변경이 DB에 주는 영향 분석 - #1 0 4 5,728

by 강정식 [강정식] statistics 통계정보 dbms_stat [2009.04.29 11:55:24]


우리가 SQL 튜닝을 한다는 것은 일반적으로 비효율적인 실행계획을 효율적인 실행계획으로 변경하는 것을 말한다.
그렇다면 효율적인 실행계획으로 변경하기 위한 방법에는 어떤것들이 있을까?

 

1. SQL 변경
2. HINT 사용
3. STATISTICS(통계정보) 변경
4. Stored Outline 사용
5. SQL Profile 사용

 

Oracle 10g 기준으로 대략 이정도를 말할 수 있는데, 이 방법들 중 3번째 방법 ’통계정보 변경’에 대해 자세히 살펴보고자 한다.

 

우선 SQL이 수행될 때 Oracle 내부적으로 어떻게 작동하는지 간단히 살펴보면 다음과 같다.

 

 

이 그림을 간단히 설명하자면 SQL은 Parse -> Execute -> Fetch 3 단계를 거친다.

 

첫번째 Parse 단계는 다시 세부적으로 아래와 같은 단계를 거친다.
    1) Syntax CHECK         : SQL의 구문이 맞는지 확인
    2) Semantics CHECK      : SQL에서 사용되는 OBJECT가 있는지 또는 권한이 있는지 등을 확인
    3) Query Transformation : Optimizer가 효율적인 실행계획을 만들기 위해 SQL 구조를 변경
    4) Estimator            : Table의 통계정보를 참조하여 Cost를 계산함
    5) Optimization         : Estimator를 바탕으로 Optimizer가 최적의 수행계획을 계산함
    6) Execution Plan       : Optimization을 통해 최종적으로 실행계획을 만듬


두번째 Execute 단계는 Parse의 Execution Plan을 바탕으로 DB에서 데이터를 가져오는 단계이다.


마지막으로 세번째 Fetch 단계는 가져온 데이터를 Client에게 보내주는 단계이다.

 

이처럼 SQL이 수행될 때 내부적으로 여러 단계를 거치는데 선두에 얘기했었던 비효율적인 실행계획이 만들어지는데 가장 영향을 주는 단계는 어디일까? 바로 Parse 단계에서 Estimator을 고려하기 위해 통계정보를 참조하는 단계이다.


Optimizer는 10g부터 통계정보를 기반으로 한 CBO Mode를 Default로 하여 실행계획을 만든다. 이는 Oracle 10g 이전 버전에서 SQL의 구문만을 가지고 실행계획을 만드는 Rule Mode 방식을 버리고 통계학적인 접근으로 보다 합리적인 실행계획을 만들고자 함에서 나온 결과이다.


하지만 통계정보는 말 그대로 통계된 정보이기 때문에 모든 SQL 형식에 대해 100%로 최적화된 실행계획을 생성할 수는 없다. Optimizer의 Rule Mode 방식이 50% 정도의 최적화된 실행계획을 보인다면 CBO Mode는 약 80%의 최적화된 실행계획을 만들 수 있다.


하지만 이 또한 100%를 만족할 수 없기 때문에 SQL 튜닝이 필요한 것이다.

 

이를 다시 그림으로 표현하면 다음과 같다.

 

 

이처럼 통계정보는 CBO Mode에서 SQL의 Execution Plan을 만드는데 가장 막강한 영향력을 발휘하는 위치에 놓여져있다.

 

자, 그럼 여기서 질문을 하나 하겠다. SQL을 변경하거나 HINT를 사용하지 않고 Execution Plan을 변경하고자 할 때 어떤 튜닝방법을 사용해야 할까? 바로 통계정보 값을 변경하여 동일한 SQL에서 비효율적으로 작성 된 Plan을 원하는 Plan으로 변경하는 방법이다.


그렇다면 통계정보를 변경하였을 때 다른 이슈는 발생되지 않는가? 바로 이런 이슈들을 이번 주제에서 살펴볼 것이다.

 

앞으로 살펴볼 서브 주제는 다음과 같다.

 

1. 통계정보 변경을 통해 Execution Plan을 변경하는 실사례
2. 통계정보 변경이 DB에 주는 여러 영향들 분석

 

이번시간에는 여기에서 마치고 다음에 서브주제를 가지고 살펴보도록 하겠다.

 

blog : http://blog.naver.com/xsoft

by 타락천사 [2009.04.29 12:20:53]
굿 잡 ㅇㅇ!!

by TeLl2 [2009.04.29 16:55:05]
또다른 이야기가 시작 되었구나~~~
기대가 만땅입니다....
좋은 글 많이 부탁드려요...

ps : 그런데 parse단계에서 semantics check가 빠진거 같은데요...

by 강정식 [2009.04.29 17:44:30]
Tel2님께서 알려주신 내용 수정하였습니다.
지적 감사합니다 ^^

by 이재현 [2009.04.30 12:32:13]
불가능.. 존재하지 않는다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입