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


sql> set autot trace exp

sql> 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') */ ...