Oracle10g R2부터 Group By절에 의한 자동정렬이 안되는 이유

http://www.en-core.com/bin/main/module/board/view.asp?searchString=&column=&board_id=opentechnical&state=view&article_id=22634&page_num=1&group_id=22630&direction=n&step=0

 

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의 잘못임)

1. 기존 Sorting 알고리즘(<=10g R1)은 Sort방식을 사용하므로 "GROUP BY"절을 사용할 경우

   Grouping절로 Ordering된 결과를 Display했음. (물론 Parallel등의 처리일 경우는 다름)

   SQL> select deptno,count(*) from emp group by deptno;

       DEPTNO   COUNT(*)

   ---------- ----------

           10          3      <<<<< DISPLAY값이 Grouping Column순으로 나옴.

           20          5

           30          6

   -----------------------------------

   | Id  | Operation          | Name |

   -----------------------------------

   |   0 | SELECT STATEMENT   |      |

   |   1 |  SORT GROUP BY     |      |      <<<<<<<<<<<<<<<<<<<< Sort Operation이 나왔음

   |   2 |   TABLE ACCESS FULL| EMP  |

   -----------------------------------

   Statistics

   ----------------------------------------------------------

          1  recursive calls

          0  db block gets

          7  consistent gets

          6  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)                  <<<<<<<<<<<<<<<<<<<< Sort Operation이 나왔음

          0  sorts (disk)

          3  rows processed

2. 10g R2부터는 Sorting 알고리즘이 HASH방식을 사용. 그러므로 "GROUP BY"절을 사용할 경우

   Grouping절로 Ordering된 결과를 Display못 할 수도 있음. (거의 대부분 못함)

   SQL> select deptno,count(*) from emp group by deptno;

       DEPTNO   COUNT(*)

   ---------- ----------

           30          6      <<<<< DISPLAY값이 Grouping Column순으로 나오지 않음.

           20          5

           10          3

   ---------------------------------------------------------------------------

   | 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 | <<<<< HASH Operation이 사용

   |   2 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |

   ---------------------------------------------------------------------------

   Statistics

   ----------------------------------------------------------

          1  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)        <<<<<<<<<<<<<<<<<<<< Sort Operation이 사용 안ㅤㄷㅚㅆ음

          0  sorts (disk)

          3  rows processed

>>>> 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

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

>>>> New in-Memory Sort Algorithm 관련 TEST 내용

---------------------------------------------------------------------

SQL> set autotrace on

SQL> alter session set optimizer_mode=RULE;

Session altered.

SQL> select deptno,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)

---------- ----------

        10          3

        20          5

        30          6

Execution Plan

----------------------------------------------------------

Plan hash value: 15469362

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|   0 | SELECT STATEMENT   |      |

|   1 |  SORT GROUP BY     |      |

|   2 |   TABLE ACCESS FULL| EMP  |

-----------------------------------

Note

-----

   - rule based optimizer used (consider using cbo)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          7  consistent gets

          6  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          3  rows processed

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> select deptno,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)

---------- ----------

        30          6

        20          5

        10          3

Elapsed: 00:00:00.06

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 |

---------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

SQL>

SQL> alter session set "_gby_hash_aggregation_enabled"=FALSE;

Session altered.

SQL>  select deptno,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)

---------- ----------

        10          3

        20          5

        30          6

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 |

---------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          3  rows processed

[출처] [펌]Oracle10g R2부터 Group By절에 의한 자동정렬이 안되는 이유|작성자 겨우리