TM
2012.03.16 13:30

upgrade 시 sorting 문제

조회 수 16599 추천 수 0 댓글 0

9i 에서 10gr2 이상으로 업그레이드 할 때 sorting에 관련된 파라메터 TEST


Upgrading from 9i to 10g - Potential Query Tuning Related Issues [ID 295819.1]

If an application has 9i to 10g upgrade related query tuning problem, please consider the following changes:

  • Costed Query transformations

    10g adds costed Subquery unnesting and View merging functionality. This functionality can be disabled using the following dynamic database parameter:
    alter session set "_optimizer_cost_based_transformation" =off;
    
  • Hash Group by aggregation enabled

    Oracle 10g release 10.2 Introduces a new feature called Hash Group by aggregation which allows a hash algorithm to process group by statements. This functionality can be disabled using the following dynamic database parameter:
    alter session set "_gby_hash_aggregation_enabled" = FALSE;
    


_gby_hash_aggregation_enabled Test


9i>>


idel> select deptno,count(*) from emp group by deptno;


    DEPTNO   COUNT(*)

---------- ----------

10    3

20    5

30    6


idel> set autot trace exp

idel> select deptno,count(*) from emp group by deptno;


Execution Plan

----------------------------------------------------------

   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=3 Bytes=9)

   1 0   SORT (GROUP BY) (Cost=6 Card=3 Bytes=9)

   2 1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=42)



11G>>


SQL> select deptno,count(*) from emp group by deptno


    DEPTNO   COUNT(*)

---------- ----------

30    6

20    5

10    3


Execution Plan

----------------------------------------------------------

Plan hash value: 4067220884


---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |  | 3 | 9 | 4  (25)| 00:00:01 |

|   1 |  HASH GROUP BY   |  | 3 | 9 | 4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |    42 | 3   (0)| 00:00:01 |

---------------------------------------------------------------------------


SQL> alter session set "_gby_hash_aggregation_enabled"=FALSE;


Session altered.


SQL> select deptno,count(*) from emp group by deptno;


Execution Plan

----------------------------------------------------------

Plan hash value: 15469362


---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |  | 3 | 9 | 4  (25)| 00:00:01 |

|   1 |  SORT GROUP BY   |  | 3 | 9 | 4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |    42 | 3   (0)| 00:00:01 |

---------------------------------------------------------------------------


SQL> set autot off

SQL> select deptno,count(*) from emp group by deptno;


    DEPTNO   COUNT(*)

---------- ----------

10    3

20    5

30    6


_optimizer_cost_based_transformation


실행 계획 이상과 Cost Based Transformation

9i 버전에서 정상적으로 Subquery Unnesting이나 View Merging이 

이루어지는 쿼리가 10g에서는 변환이 발생하지 않을 수 있다. 

통계 정보가 정확하다면 오라클의 대부분의 경우 최적의 판단을 하지만, 

간혹 특정 쿼리의 경우에는 변환이 이루어지지 않음으로써 비효율적인 실행 계획으로 바뀔 수 있다. 

이런 경우에는 _OPTIMIZER_COST_BASED_TRANSFORMATION 파라미터 값을 OFF로 변경하거나 

OPT_PARAM 힌트를 이용해서 해당 파라미터를 Statement 레벨에서 변경해야 한다.


alter session set _optimizer_cost_based_transformation = off;


-- 혹은


select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ ...

TAG •

List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84911
160 Q&A 오라클 복구 질문 희달이 2015.05.08 1183
159 Q&A import후 simple_size 0일때 고구마 2014.01.13 3215
158 자료 oracle 문제발생시 대처 가이드 2 file dbkill 2011.05.16 5652
157 자료 Flashback 10g 1 file 고구마 2010.04.09 6324
156 자료 ASM 1 file 고구마 2010.04.09 6778
155 자료 solaris sparc 64bit oracle 10.2.0.1 file 김준호 2010.03.29 7005
154 자료 C컴파일시 makefile의 구조설명 file 김준호 2010.04.22 7231
153 자료 윈도우 tail 사용법 1 file 유주환 2010.04.19 7374
152 자료 ORA-600 metalink notes for help file dbkill 2011.03.29 7641
151 자료 Data_Pump 세미나자료 file 고구마 2010.04.09 7672
150 Tip 통계복원하기 고구마 2013.05.13 8054
149 Q&A alert_log는 5 perfstat 2011.04.22 8111
148 TM ORA-1578 조치를 위한 event 10231 1 이성철 2010.12.22 8456
147 TM fine-grained_auditing 1 유주환 2010.05.19 8598
146 자료 instance 여러개인 경우 profile 보기 편하게 수정 1 유주환 2010.04.18 8656
145 TM Flashback Query 승현짱 2010.05.30 8677
144 TM 어플리케이션 페일오버와 로드밸런싱 유주환 2010.05.19 8847
143 TM analyze connection failover options 유주환 2010.05.19 8869
142 TM library_cache_lock,_library_cache-orapybubu 유주환 2010.05.19 8915
141 Q&A RAC 쿼리 속도 관련 질문입니다. 3 고구마 2011.05.20 8994
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8