TM
2012.03.16 13:30

upgrade 시 sorting 문제

조회 수 16338 추천 수 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 84539
160 Q&A 오라클 복구 질문 희달이 2015.05.08 877
159 TM 11G new feature SQL performance analyzer file Sean 2013.01.09 14203
158 TM MEMORY TARGET 도로시 2013.01.03 19325
157 TM check whether crs auto start is enable or disable 담벼락 2012.10.12 25941
156 TM ARDCI file 이현정 2012.10.09 12465
155 TM (10gR2)Full UNDO tablespace Tech1 2012.04.04 23779
154 TM audit 관련 2 이현정 2012.03.29 24147
153 TM 테이블과 인덱스의 관계 및 인덱스 종류 및 생성 방법. Tech1 2012.03.28 24263
152 TM windows에서 Opatch 진행시 oci.dll 에러 조치 1 이현정 2012.03.26 36895
151 TM 테이블 생성방법 및 각종옵션 Tech1 2012.03.20 19769
» TM upgrade 시 sorting 문제 담벼락 2012.03.16 16338
149 TM 11 newfeature(Upgrade) 10.2.0.5 to 11.2.0.3 1 file 윤현 2012.01.12 18264
148 TM TDE (column, tablesapce) 1 file 윤현 2011.12.27 13179
147 TM 11g newfeature (dbca) 2 file 윤현 2011.12.27 13861
146 TM 11g 데이타베이스 에러수집방법 2 perfstat 2011.11.02 19387
145 TM long type column 1 담벼락 2011.10.11 17421
144 TM 백업이 필요없는 대용량 table skip하고 export 받기 고구마 2011.06.14 18251
143 TM TDE tablespace 1 윤현 2011.06.09 17218
142 TM openvms command perfstat 2011.05.31 17354
141 TM Oracle Silent Install Guide 3 file 송기성 2011.03.13 20073
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8