태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'TCF'에 해당되는 글 2건

  1. 2008.07.01 Optimizing Unoptimizable SQL (3)
  2. 2008.01.24 Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback

Optimizing Unoptimizable SQL

오라클 2008.07.01 15:02

Optimization이 불가능한(즉, SQL Text 수정이 불가능한) Query를 Optimization할 수 있는가?


[Oracle is Mad(R)] 7월 세미나에서 준비하고 있는 주제 중 하나이다.

SQL Text 수정이 불가능한 경우에는 어떻게 튜닝을 할 수 있는가? 이 질문은 점점 더 중요해질 것이다. ERP, CRM 같은 Package 형태의 업무 어플리케이션들이 늘어나고, Java, .NET에서 Persistent Framework(Hibernate같은)들이 보편화되면서 SQL Text에 대한 직접 수정이 불가능한 경우가 점점 늘어나고 있기 때문이다.

직간접적으로 Query 튜닝과 관련된 모든 사람들에게 중요한 도전인 셈이다.

(내가 알기로는) 이런 문제에는 6가지 정도의 해결책이 있다.
(Oracle Upgrade를 제외하고)

1.  Parameter 수정
Parameter 변경은 전역적이어서 위험성이 높다. 그리고 실제로 사용할 수 있는 파라미터의 수는 매우 제한적이다.

2. Physical Design 수정
Partition 나누기, Block Size 바꾸기, 기타 Phsyical Atribute를 수정하는 방법이 있다. 이런 변화는 전역적이고 영구적(Permanent)이어서 쉽사리 선택하기 힘들다. 선택의 폭 또한 매우 제한적이다.

3. 통계 정보 조작
Wolfgang Breitling에 의해 체계화된 TCF(Tuning By Cardinality Feedback) 기법이 여기에 속한다. CBO가 올바른 판단을 할 수 있도록 통계 정보를 보완해주는 기법이다.

언뜻 생각하면 제한적인 것 같지만, 매우 강력한 기법이다. CBO에 대한 신뢰를 바탕으로 CBO를 도와준다는 관점에서 보면 가장 Oracle 친화적인 방법이라고 할 수 있다.

조작 가능한 통계 정보에는 제한이 없으며 Table/Column/Index/Histogram 등 모든 통계 정보를 수동으로 조작할 수 있다. 하지만, 수동으로 변경된 통계 정보는 자동 백업(10g)이 되지 않는다는 사실과 통계 정보 수집시 덮어 써버린다는 사실에 주의해야 한다.

Index를 생성하거나 Index Key를 변경하는 것도 넓은 범위에서는 이 범주에 속한다.


4. Stored Outline
Stored Outline은 원래 Plan Stability를 보장하기 위해 제안된 개념이지만, 실세계에서 이 목적으로 사용되는 경우는 거의 없다. 오히려 Outline 바꿔치기를 통해 Query Tuning의 도구로 활용되는 경우가 많을 정도이다. 아래 글을 참조...

2008/03/20 - [Advanced Oracle] - Stored Outline을 이용한 SQL Tuning 기법

5. SQL Profile
10g에서 소개된 SQL Profile는 SQL Tuning Advisor가 제공하는 기능 중 하나이다. SQL Tuning Advisor는 SQL 분석 후 Hint 조작을 통해 Query 성능 개선이 가능한 경우에는 해당하는 Profile을 제공한다. 이 Profile를 사용하면 Query의 실행 계획을 조작할 수 있다.

dbms_sqltune 패키지의 undocumented procedure들을 이용하면 수동으로 Profile을 생성하고 조작할 수도 있다. 이 관점에서 보면 Stored Outline을 사용하는 것과 거의 동일한 기법이라고 할 수 있다.

6. Advanced Rewrite
10g에서 소개된 Advanced Rewrite는 특정 SQL Text를 가로채서 다른 SQL Text로 변환하는 기능을 의미한다. dbms_advanced_rewrite 패키지를 이용한다.

언뜻 보면 가장 강력하고 확실한 기법으로 보인다.


하지만, Bind 변수가 있는 Query 등이 기본적으로 지원되지 않는다는 점은 치명적이다. 또한 Parse 과정에서의 부하를 생각해보면 OLTP에는 맞지 않다는 결론을 얻을 수 있다. DW 성의 쿼리에서 사용될 목적으로 고안된 것이다. 하지만 Parse 과정에서의 오버헤드를 감수해서라도 Query 성능을 높여야할 명분이 있다면 고려해볼 만한 방법이다.

혹시 다른 방법을 알고 있는 분들이 있는지...?


신고

'오라클' 카테고리의 다른 글

Why Creativity Matters?  (0) 2008.07.07
Automize your test case  (0) 2008.07.04
Optimizing Unoptimizable SQL  (3) 2008.07.01
Subquery is just like Join  (4) 2008.06.22
Visual의 힘  (0) 2008.06.20
Trackback 0 : Comments 3
  1. extremedb 2008.07.02 09:07 신고 Modify/Delete Reply

    집계테이블이나 목적성 테이블을 만들 경우는 목적성 테이블 대신에 Mview 를 만들고 Query Rewrite 를 적용하시면 되겠습니다.

  2. 쏘심이 2008.07.02 18:44 신고 Modify/Delete Reply

    블로그가 날로 활성화 되는거 같아 제가 다 기분이 좋네요.. 정보 얻어 갑니다.

  3. orapybubu 2008.07.09 10:37 신고 Modify/Delete Reply

    열린 질문으로 글을 마치셨기에 덧글을 달아봅니다. ^^

    (1) 하드웨어 추가
    (2) resource manager를 이용해서 가용 자원을 조정

Write a comment


Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback

오라클 2008.01.24 20:10
Wolfgang Bretiling이라는 스위스를 기반으로 활동하는 Consultant가 있다. 이 사람의 전문 분야는 Oracle + PeopleSoft의 튜닝이다.

PeopleSoft과 같은 ERP 솔루션들은 Oracle과 무관하게, 아니 Oracle을 무시해서 설계된 Application으로 유명하다. 모든 DBMS에서 일관되게 동작 가능한 Application에 의한 필요악이라고나 할까... 이런 3rd Party Application의 튜닝에서 가장 큰 애로 사항이 쿼리를 직접 수정하는 것이 불가능하다는 것이다. 때문에 간혹 Oracle 관점에서의 튜닝이 불가능한 것으로 간주하기도 한다.

이 Wolfgang이라는 사람은 이런 상황에 착안해서 자신만의 쿼리 튜닝 기법을 개발했다. 하지만 쿼리 수정이 전혀 불가능한 상태에서 어떻게 (비교적) 자유롭게 쿼리를 튜닝할 수 있단 말인가?
놀랍게도 이 사람은 많은 시스템에서의 경험과 Oracle Optimizer에 대한 심도 깊은 지식을 기반으로 이것을 가능하게 하는 단순하면서도 심오한 방법을 체계화했다.

그 이름이 바로 TCF - Tuning by Cardinality Feedback이다. 풀어쓰면 "실행 예상 계획과 실제 실행 계획의 차이(Cardinality Feeback)에 의한 쿼리 튜닝 기법"이다. TCF는 다음과 같은 가정에 근거한다.

  • Oracle의 CBO는 Cardinality만 정확하면 나름대로 최적의 실행 계획을 생성한다.
  • Oracle의 통계 해석에서 오는 몇 가지 오류로 인해 Cardinality 계산이 비현실적인 경우가 있다.
  • 따라서, Oracle이 Cardinality를 잘 계산할 수 있도록 힌트를 주면 많은 경우 정상적인 실행 계획을 만들 것이다.


이런 가정 하에서 DBMS_STATS.SET_XXX 류의 메소드를 이용해서 Oracle이 최적의 Cardinality를 계산할 수 있도록 통계 값을 보정시켜주는 것이 이 TCF의 핵심이다. Oracle이 필요로 하는 Cardinality를 추론하게 해주는 것이 Explain Plan(실행 예상 계획)과 Execution Plan(실제 실행 계획)을 비교하고 해석하는 능력이다.

마침 Oracle 10g부터는 gather_plan_statistics 힌트나 statistics_level = all 과 함께 dbms_xplan.display_cursor 함수를 사용하면 TCF 적용에 필요한 데이터(실행 예상 로우 건수와 실제 실행 로우 건수)를 손쉽게 구할 수 있으니 이 Wolfgang의 TCF의 뛰어난 점을 Oracle이 수용한 것처럼 생각될 정도이다.

아래 URL에서 TCF 사용에 필요한 모든 이론적 도구를 얻을 수 있다.
http://www.centrexcc.com/papers.html

쿼리를 직접 수정할 수 없는 3rd party Application 튜닝에 최적의 도구를 하나 확보하게 된 셈이다.

PS) 이 TCF는 실제로 많은 엔지니어들이 자신도 인식하지 못하고 사용하고 있는 방법이다. 이런 것을 체계화하고 이론적 무장을 갖추는 능력이 부족한 우리 현실이 아쉬울 뿐이다.
신고
Trackbacks 7 : Comment 0

Write a comment

티스토리 툴바