Oracle 10g R2 부터 Group by 절에 의한 자동 정렬 이 되지 않는 이유


Oracle 10g R2부터 Group By절에 의한 Grouping Column순으로 Sorting되지 않는 문제에 대한 접근 방식입니다.


기존의 고객이 Order by를 사용하지 않고 Group By만을 사용하였다면 문제를 제기할 수 있을 것입니다.

이러한 문제에 대한 대처방식입니다.

 

Oracle 10g R2의 “New in-Memory Sort Algorithm”에 따른 문제점 및 개선점

======================================================================
>>>> Oracle 10g R2 New Feature – New in-Memory Sort Algorithm 이란?
———————————————————————

1. 새로운 sort 적용 방식
– 기존에는 Sort 알고리즘으로 Sort하였으나 “Hash-based 방식“의 New Feature

2. 성능 개선 효과
– 충분한 Memory일 경우 (즉 In-Memory Sort)일 경우 효과적
– Sort operation이 기존 방식에 비해 최대 5~10%까지 빠를 수 있다.

3. SORT특징에 따른 개선 효과
– 높은 cardinality (Row들의 Distinct가 많은 경우)일 경우 특히 효과적 (HASH방식 이므로)
– Faster CPU일 경우 더욱 효과적
– 적은 Column을 Select 했을 경우 특히 효과적 (Hash는 Memory부족에 의해 Disk로 내려가면 꽝)

======================================================================

>>>> New in-Memory Sort Algorithm 의 문제점?
———————————————————————
“GROUP BY”를 사용한 App가 “ORDER BY”를 기술하지 않더라도 Ordering된 결과를 Display하던
App들이 10g R2로 오면서 이 기능이 깨지게 되었음.
즉 반드시 Ordering이 필요하면 “GROUP BY”와 함께 “ORDER BY”를 기술해야 함.
(참고. 이는 Oracle의 Bug은 아니며 App의 잘못임)

======================================================================

>>>> New in-Memory Sort Algorithm 의 문제점인 “GROUP BY”를 기존 방식으로 사용하기 위해서는?
———————————————————————
1. Optimizer Mode가 RULE일 경우는 발생 안함.
2. OPTIMIZER_FEATURES_ENABLE를 10.1  로 함
3. init.ora “_gby_hash_aggregation_enabled”=FALSE  (New방식 사용 안함)
위의 방식 중 3번이 해당 기능 만 막으므로 가장 많이 사용될 것임.
그러나 New in-Memory Sort Algorithm은 아주 유용한 방식이므로 App를 수정할 것을 고객들에 권장할 필요가 있음.
관련 Doc
è Note:295819.1 , Bug : 4604970

Subject:  Upgrading from 9i to 10g – Potential Query Tuning Related Issues

## 실무사례 ##


 

 

## 해결책 ##

Init.ora 의 _gby_hash_aggregation_enabled=FALSE 의 경우 group by 절 다음에 Single Column 인 경우에는

정상적인 결과가 나오지만 2개 이상의 Column 이 나오는 경우에는 결과가 달라진다. 즉 위 Union all 의

From 절 In-Line View 의 group by –a.s4 의 하나의 Column 으로 결과를 구하면 정상적으로 Sorting 이

되어져서 결과가 나오지만, 예처럼 –a.s4, a.ctr_cd 처럼 Column 을 하나 더 추가하면 결과가 달라진다.

아래와 같이 group by 절 다음에 order by 절을 추가하여 준다.


 


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다