Oracle 10g R2부터 Group By절에 의한 Grouping Column순으로 Sorting되지 않는 문제

■ Oracle 10g R2부터 Group By절에 의한 Grouping Column순으로 Sorting되지 않는 문제

 

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하였으나 10g R2부터는 "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 |

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

 

##########################################################################