TM
2012.03.16 13:30

upgrade 시 sorting 문제

조회 수 16458 추천 수 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 84839
159 Q&A 오라클 복구 질문 희달이 2015.05.08 1148
158 Q&A import후 simple_size 0일때 고구마 2014.01.13 3194
157 Tip 통계복원하기 고구마 2013.05.13 8032
156 Tip 유저별 Segment 개수 및 사이즈 조회 쿼리, n2bfree 2013.03.12 10936
155 Tip online reorg script 담벼락 2013.01.10 10630
154 TM 11G new feature SQL performance analyzer 1 file Sean 2013.01.09 14283
153 TM MEMORY TARGET 1 도로시 2013.01.03 19588
152 TM check whether crs auto start is enable or disable 담벼락 2012.10.12 26265
151 TM ARDCI file 이현정 2012.10.09 12520
150 TM (10gR2)Full UNDO tablespace Tech1 2012.04.04 23918
149 TM audit 관련 2 이현정 2012.03.29 26502
148 TM 테이블과 인덱스의 관계 및 인덱스 종류 및 생성 방법. Tech1 2012.03.28 25512
147 TM windows에서 Opatch 진행시 oci.dll 에러 조치 1 이현정 2012.03.26 37410
146 Q&A [답변] 테이블 수정, 삭제 및 LOB에 대해 알려주세요 Tech1 2012.03.26 10643
145 Q&A ORACLE_BASE, ORACLE_HOME 각각의 위치에 대한 설명 부탁드립니다 Tech1 2012.03.20 10842
144 TM 테이블 생성방법 및 각종옵션 Tech1 2012.03.20 20710
» TM upgrade 시 sorting 문제 담벼락 2012.03.16 16458
142 Tip 오라클11g에서 SE1,SE,EE제품의 옵션비교입니다. file perfstat 2012.03.07 27010
141 Tip 오라클 업그레이드 메뉴얼 1 file perfstat 2012.02.06 11894
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8