GROUP BY 와 ORDER BY 최적화 (오라클 10.2 이후버젼)

Optimizing GROUP and ORDER BY

                                                                                                                  Wednesday, August 5, 2009 at 02:51AM

    

오라클 10.2를 시작할때, Group by와 Order by를 동일한 칼럼에 결합하여 사용할때, 성능과 관련하여 괄목할 만한 성능 절감을 발견하게 될 것이다. 

오라클 10.2에서는 Hash 기반의 Group By에 대해서 소개했다. 이전 Group by를 수행하면 연관된 칼럼의 소팅을 포함해서 처리했었고, 이러한 수행결과를 모아서 결과로 반환했다. Hash Group By 메소드는 소팅 없이 이러한 결과를 생성한다. 즉, 이 처리방식은 Sort-Based Group by보다 항상 낳은 성능을 발휘한다. 불행하게도 Order By 절을 동일 칼럼에 Group By절과 함께 사용한다면 Sort-Based Group by로 변경하며, 응답속도는 떨어질 것이다. 그러나 요청한 순서대로 데이터를 획득하고 싶다면 Hash-Based Group by를 이용하면 조금더 이점은 있다.

예를 들어 다음 단순한 샘플 구문을 확인해보자.

10.2 이전에서는 SORT GROUP BY 오퍼레이션을 이용하여 수행하게 된다.

 

10.2 버젼 이후부터는 HASH GROUP BY를 수행하는것을 확인할 수 있다.

Alex Gorbachev  noted에 의하면 새로운 GROUP BY 는 이전 버젼과 같이 수행할 수 있도록(11.1.0.7 혹은 10.2.0.4 버젼 이전 오라클을 말한다.) 수정할 수있음을 가르쳐 준다. 다음 파라미터인 _GBY_HASH_AGGREGATION_ENABLED 를 FALSE로 바꾸도록 설정하면 된다. 아래 예와 같이 OPT_PARAM 힌트를 이용하여 개별 SQL의 설정을 바꿔 보자. 혹은 ALTER SESSION이나 ALTER SYSTEM을 변경하여 세션이나 인스턴스 레벨로 동작하게 할 수 있다.
 

기억해야할 것은 GROUP BY의 결과가 정렬되어 나온다고 생각하면 안된다는 것이다. 10.2 버젼의 GROUP BY는 보통 정렬된 GROUP BY를 이용했고, 필수적으로 함께 ORDER BY 절이 추가 되었었다. 10.2에서는 쿼리의 결과가 갑자기 랜덤한 순서로 나타나는것을 보고 아마 깜짝 놀랐을 수 있다. Tom Kyte는 다음과 같이 이야기 하고 있다. : "기본 원칙은 결코 부분적인 정렬에 대한 다른 영향은 없을 것이다. 만약 정렬된 group 결과를 획득하고 싶다면 ORDER BY절을 항상 사용해야 한다."

일반적로 말해서 새로운 HASH GROUP BY는 과거의 소트 방법보다 훨씬 향상된 결과를 가져온다. 아래에서 보는것과 같이 GROUP BY 알고리즘을 2백 50만건 로가 있는 샘플 데이터를 그룹바이 하여 20만 로의 통합된 로가 나온다면 수행결과 차이는 다음 그림과 같이 나타난다.

물론 결과가 다르게 나올수 있지만 HASH GROUP BY의 선능이 SORT GROUP BY 보다 우수하다는 것을 보게 될 것이다.

불행하게도 오라클은 몇몇 환경에서 HASH GROUP BY를 사용하지 않을 것이다. 공평하게 동일한 칼럼에 GROUP BY와 ORDER BY를 동시에 사용한경우에 그렇게 동작하며, 임의의 순서로 통합된 결과가 보이지 않을 것이다. 

오라클은 SORT GROUP BY를 이용할때, 그룹핑된 결과에 사이트 이펙트로 소트를 수행한다. 그래서 아래 예와 같이 SORT가 오직 한번만 일어나며, 결과적으로 GROUP BY와 ORDER BY를 모두 동시에 사용한 결과가 나타나게 된다. (주의할 것은 여기서는 HASH GROUP BY를 OPT_PARAM 힌트를 이용하여 기능을 꺼둔상태라는 것이다.)

 

상단의 플랜은 10.2 이전에서 주로 보이는 결과이며, HASH GROUP BY가 릴리즈되기 이전에 사용된 것이다.

그러나 11g나 10.2에서는 오라클은 여전히 SORT GROUP BY를 선택할 것이라는 것을 알 수 있다.

 

여기에 중요한 포인트가 있다.

만약 GROUP BY와 ORDER BY를 동일한 칼럼에 혼합해서 사용할경우,
오라클은 HASH GROUP BY옵션을 사용하지 않을것이다.

추측건데 옵티마이저의 생각은 그룹을 수행하는 동안 SORT GROUP BY를 수행하는 것이다. GROUP BY와 마찬가지로 ORDER BY 요청을 수행할때 SORT GROUP BY는 가장 좋은 방법인 것이다. 그러나 로직에는 중요한 하자가 있다. 보통 ORDER BY를 위한 입력은 GROUP BY의 입력보다 작은 로가 입력이 된다. 상단의 예에서도 GROUP BY는 ORDER BY가 20만건을 처리하는 동안, 2백 50만건이 처리되고 있다. 이것은 비싼 GROUP BY를 수행하여 상대적으로 싼 ORDER BY에 입력으로 이용하는것은 의미상 문제가 있는 반 옵디마이징이다.

그래서 오라클에서 ORDER BY를 해야하는 상황에서 강제적으로 HASH GROUP BY를 수행하도록 하면 어떨까? 하는것이 핵심이다. 그래서 오라클이 HASH GROUP BY를 수행할 수 있도록 서브쿼리에 GROUP BY를 수행하도록 만들었다. 그리고 외부 쿼리에서 ORDER BY를 수행할때 NO_MERGE 힌트를 지정하여, 외부 쿼리가 내부 서브쿼리에 통합되지 않도록 지정했다.
결과적으로 플랜을 보면, HASH GROUP BY를 수행하고, 그 결과를 SORT ORDER BY를 타도록 재설정 되었다.

 

 

생각하는것과 같이 상단의 내용은 HASH GROUP BY와 SORT ORDER BY를 함께 사용하여 SORT GROUP BY보다 훨씬 낳은 수행을 하도록 하고 있다. 그러나 기억해야할 것은 SORT ORDER BY는 그룹된 결과를 소트한다는 것을 꼭 확인해야한다는 것이다. 즉 예제에서는 20만개의 로를 소트하는 것이고, 이것은 2백 50만개의 HASH GROUP BY를 수행한 결과로 처리한다는 것이다. 그래서 GROUP BY의 최적화는 중요함이 작은 두번째 소트처리를 피하는것 보다 더 중요한 사항이 되기도 한다.

이것은 2개의 성능을 비교한 결과를 보여준다.
응답 시간이 2/3으로 획기적으로 줄어든 것을 확인할 수 있다.

결론

GROUP BY를 ORDER BY와 동일한 칼럼에 결합해서 적용할때에는 오라클 옵티마이저는 더 효과적인 HASH GROUP BY를 사용하지 않고, SORT GROUP BY를 수행한다. SORT ORDER BY 계획을 피사기 위해서 SORT GROUP BY를 수행한 것이다. 그러나 성능은 형편 없다는것을 확인 할 수 있다.

더 낳은 결과를 위해서 GROUP BY를 인라인 뷰에 작성하고, 외부 쿼리에서 ORDER BY를 수행하도록 하자.
여기서 두개의 쿼리가 머지되어 작동하지 않도록 NO_MERGE 힌트를 부여하는것에 주목하자.