DB에 몸 담고 있다면 꼭 해야할 공부 10

DB에 몸 담고 있다면 꼭 해야할 공부 10

http://oracle10g.tistory.com/651

1.      Troubleshooting: server upgrade results in slow query performance: 160089.1

 

이 문서는 software 업그레이드 또는 데이터베이스 이관시 성능 저하 진단 및 시작포인트를 설명하고 있다.

 

이 문서는 오직 쿼리의 성능에 대해서 다룬다 즉 database 성능이 아니다.

일반적으로 upgrade전에 문제에 대해서 보다 빠른 접근을 위해서 관련정보를 기록해둘 것을 권고한다. : 167086.1 : tips for avoiding upgrade related query problems

 

● known issue

  Note 258167.1 Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues

  Note 295819.1 Upgrading from 9i to 10g - Potential Query Tuning Related Issues

 

1.파라메터의 변화가 있는가?

                1) 과거로 돌릴 수 있는가?

 

2.어떤 optimizer가 사용되는가?

                1) 가장 쉬운 방법으로 set autot trace explain 을 해서 cost가 나오면 cbo가 사용된 것이다.

           66484.1 which optimizer is used?

 

             세션 단위의 파라메터 지정은 플랜을 변경할 수 있다.

             가장 신뢰할 수 있는 플랜은 sql_trace 또는  event:10046을 덤프해서 얻는 플랜이다.

             참고로 세션이 exit를 해야 플랜정보가 확실히 덤프에 기록된다.

   

             플랜은 tkprof를 통해서 얻을 수 있다. (단 tkprof는 실행시점의 플랜을 생성하기 때문에

             파라메터 설정에 의해서 영향을 받을 수 있다)

 

3.통계정보 체크

CBO가 사용된다면 새로운 버전에서 모든 오브젝트에 대해서 통계정보가 생성 되어 있어야 한다. 즉 새로운 옵티마이져를 이용해서 통계를 수집해야 한다. 일반적으로 어플리케이션은 동일버전에서 생성된 통계정보를 이용할 때 가장 유리한 플랜을 세울 수 있다. 그러나 새로운 통계를생성하면 통계에 의해서 성능의 저하가 유발 되었다는 증가는 사라지게 된다.

 

                Note 44961.1 Statistics Gathering: frequency and strategy guidelines

 

4.droped/creaed/rebuilt 오브젝트가 있는가?

  새로운 모든 스크립트가 정상적인가?

  생성 또는 컴파일이 실패한 오브젝트 또는 없어진 오브젝트는 없는가?

 

5.실행계획 체크

● 업그레이드 전후 비교

Please refer to: Note 223806.1 Query with unchanged execution plan is slower after database upgrade for examples of how to handle this.

 

● 일반적인 솔루션/시도할 수 있는 것들..

 

6.옵티마이져 변경

            옵티마이져가 좋은 플랜을 생성할 수 있도록 수정한다. (rbo or cbo(all_rows/first_rows)

 

7.통계 재생성

● skewed 컬럼에 대해서 히스토그램 생성

● 새로운 optimizer 기능 비활성화

 

  Example:

    <Parameter:OPTIMIZER_FEATURES_ENABLE>    = Database version upgraded from. See below.

    <Parameter:COMPLEX_VIEW_MERGING>         = FALSE/TRUE

    <Parameter:STAR_TRANSFORMATION_ENABLED> = FALSE/TRUE

    <Parameter:OPTIMIZER_INDEX_COST_ADJ>      < 100

    <Parameter:OPTIMIZER_INDEX_CACHING>       = value between 1 and 100

    <Event:10127> If remote joins are involved, this can disable the optimizer choosing them

 

8.힌트 사용

9.쿼리 재작성

                 ● 튜닝 : 163563.1 : resolving query tuning issues

 

► <Parameter:OPTIMIZER_FEATURES_ENABLE>

이 파라메터는 옵티마이져의 중요한 기능을 제어하기 때문에 매우 중요한 파라메터이다.

Note 62337.1Init.ora Parameter "OPTIMIZER_FEATURES_ENABLE" Reference Note

 

10053 trace를 이용하여 옵티마이져가 참조하는 파라메터를 조사할 수 있다,.

 

REFERENCES

==============================================================================

Note 258167.1 Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues

Note 295819.1 Upgrading from 9i to 10g - Potential Query Tuning Related Issues

Note 223806.1 Query with unchanged execution plan is slower after database upgrade

Note 167086.1 Tips for avoiding upgrade related query problems

Note 233112.1 START HERE> Diagnosing Query Tuning Problems Using a Decision Tree

Note 372431.1 TROUBLESHOOTING: Tuning a New Query

Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries

Note 163563.1 ADVANCED Query Tuning: Resolving Query Tuning Issues

Note 199083.1 Query Handling and Tuning Overview

Note 179668.1 Suggested Query Tuning Workflow

Note 66484.1 Which Optimizer is Used? .

 

 

2.      Upgrading from 8.1.x to 9.x potential query tuning related issues: 258167.1

 

CBO는 계속 성능이 개선되어 일반적으로 쿼리의 성능을 향상 시킨다. 그러나 특수한 경우 성능을 악화시키는 경우도 발생한다.  8.1에서 9i로upgrade를 한 경우 성능이 악화 될 가능성에 대하여

논의해보자..

 

이 문서는 CBO에 촛점이 되어 있지만 일부는 RBO에도 영향을 줄 수 있다.

 

1.중요한 변화

8i에서 9i로 upgrade 이후 쿼리에 문제가 있다면 다음과 같은 변화에 대한 이해를 해야 한다.

 

► subquery unnesting 강화

  (_unnest_subquery = true): 258945.1

 

► complex view의 merge 강화

  (_complex_view_merging =true) : 258946.1

 

► bitmap index가 없더라도 b*tree 인덱스에 대해서 bitmap plans이 활용

  (_b_tree_bitmap_plans=true) : 259126.1

 

만약 위 히든 파라메터의 true로 인하여 쿼리의 플랜이 악성으로 풀리는 경우 application을 수정 가능하다면 application을 수정하는 것이 최선이다. 그러나 패키지 또는 application을 수정할 수 없는 상황이라면 init.ora에서 파라메터를 통해서 비활성 시킬 수 도 있다. 또는OPTIMIZER_FEATURES_ENABLE=8.1.7 로 설정하여 이전 플랜으로 돌아갈 수도 있다. 그러나 반듯이 과거와 플랜이 동일함을 보장하지못한다.

 

따라서 신기능은 Upgrade전에 반듯이 테스트 되어야 한다.

 

167086.1 : tips for avoiding upgrade related query problems

 

REFERENCES

==============================================================================

160089.1 why are my queries slow after upgrading my database?

223806.1 query with unchanged execution plan is slower after database upgrade

261079.1 internal script setting individual parameters seperately to mimic setting

         optimizer_features_enable=8.1.x