TM
2012.03.16 13:30

upgrade 시 sorting 문제

조회 수 16594 추천 수 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 84902
58 TM 11G new feature SQL performance analyzer 1 file Sean 2013.01.09 14319
57 TM MEMORY TARGET 1 도로시 2013.01.03 19605
56 TM check whether crs auto start is enable or disable 담벼락 2012.10.12 26288
55 TM ARDCI file 이현정 2012.10.09 12532
54 TM (10gR2)Full UNDO tablespace Tech1 2012.04.04 23930
53 TM audit 관련 2 이현정 2012.03.29 26572
52 TM 테이블과 인덱스의 관계 및 인덱스 종류 및 생성 방법. Tech1 2012.03.28 25524
51 TM windows에서 Opatch 진행시 oci.dll 에러 조치 1 이현정 2012.03.26 37459
50 TM 테이블 생성방법 및 각종옵션 Tech1 2012.03.20 20814
» TM upgrade 시 sorting 문제 담벼락 2012.03.16 16594
48 TM 11 newfeature(Upgrade) 10.2.0.5 to 11.2.0.3 1 file 윤현 2012.01.12 18362
47 TM TDE (column, tablesapce) 1 file 윤현 2011.12.27 13238
46 TM 11g newfeature (dbca) 2 file 윤현 2011.12.27 13918
45 TM 11g 데이타베이스 에러수집방법 2 perfstat 2011.11.02 20147
44 TM long type column 1 담벼락 2011.10.11 17685
43 TM 백업이 필요없는 대용량 table skip하고 export 받기 고구마 2011.06.14 18766
42 TM TDE tablespace 1 윤현 2011.06.09 17297
41 TM openvms command perfstat 2011.05.31 17530
40 TM Oracle Silent Install Guide 3 file 송기성 2011.03.13 20238
39 TM 엔지니어필수품 file perfstat 2011.03.03 14115
Board Pagination Prev 1 2 3 Next
/ 3