오라클 19c group by 컬럼 나열 및 집계함수 적용 성능 비교

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c group by 컬럼 나열 및 집계함수 적용 성능 비교

쿼리를 작성할때 그룹함수를 사용하는 컬럼이 아닌 문자열 컬럼들은 group by에 넣어주거나 select 절에서 max, min 함수 처리를 함
상황에 따라서 두 방식 중 알맞은 방식을 사용하는데
두 경우 어느쪽이 리소스를 많이 사용하는지 확인해보기 위해 테스트를 진행함

 

 

테스트1. 문자 컬럼 group by에 모두 나열해서 쿼리 실행

테스트2. 문자 컬럼 집계함수 처리 후 쿼리 실행

 

 

샘플 테이블 생성

1 2 3 4 5 6 7 8 9

SQL> conn imsi/imsi drop table big_emp; create table big_emp as select * from emp connect by level <= 6; select count(*) from big_emp;     COUNT(*) ----------    8108730

 

 

버퍼캐시 플러쉬

1 2 3

SQL> alter system flush buffer_cache;   System altered.

 

 

테스트1. 문자 컬럼 group by에 모두 나열해서 쿼리 실행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> alter session set statistics_level = all; select empno, ename, mgr, job, sum(sal) from big_emp group by empno, ename, mgr, job;        EMPNO ENAME             MGR JOB         SUM(SAL) ---------- ---------- ---------- --------- ----------       7902 FORD             7566 ANALYST   1737585000       7876 ADAMS            7788 CLERK      637114500       7788 SCOTT            7566 ANALYST   1737585000       7698 BLAKE            7839 MANAGER   1650705750       7900 JAMES            7698 CLERK      550235250       7934 MILLER           7782 CLERK      752953500       7782 CLARK            7839 MANAGER   1419027750       7521 WARD             7698 SALESMAN   723993750       7839 KING                  PRESIDENT 2895975000       7369 SMITH            7902 CLERK      463356000       7566 JONES            7839 MANAGER   1723105125       7499 ALLEN            7698 SALESMAN   926712000       7654 MARTIN           7698 SALESMAN   723993750       7844 TURNER           7698 SALESMAN   868792500   14 rows selected.

 

 

플랜 확인

1 2 3 4 5 6 7 8

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'); ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation          | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |         |      1 |        |       | 13716 (100)|          |     14 |00:00:00.89 |   47733 |  47729 |       |       |          | |   1 |  HASH GROUP BY     |         |      1 |   2079 | 54054 | 13716   (6)| 00:00:01 |     14 |00:00:00.89 |   47733 |  47729 |   993K|   993K| 1473K (0)| |   2 |   TABLE ACCESS FULL| BIG_EMP |      1 |   8108K|   201M| 13163   (2)| 00:00:01 |   8108K|00:00:00.25 |   47733 |  47729 |       |       |          | ------------------------------------------------------------------------------------------------------------------------------------------------------------

디스크에서 47729 블록을 읽고(Reads 부분), 메모리는 각각 OMem이 993K(예측치), 1Mem이 993K(예측치), Used-Mem이 1473K(실제 사용한 메모리)임
시간은 0.89초 소요됨

 

 

버퍼캐시 플러쉬

1 2 3

SQL> alter system flush buffer_cache;   System altered.

 

 

테스트2. 문자 컬럼 집계함수 처리 후 쿼리 실행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> alter session set statistics_level = all; select empno, max(ename), max(mgr), max(job), sum(sal) from big_emp group by empno;        EMPNO ENAME        SUM(SAL) ---------- ---------- ----------       7698 BLAKE       117907350       7902 FORD        124113000       7369 SMITH        33096800       7934 MILLER       53782300       7499 ALLEN        66193600       7844 TURNER       62056500       7788 SCOTT       124113000       7654 MARTIN       51713750       7876 ADAMS        45508100       7839 KING        206855000       7566 JONES       123078725       7782 CLARK       101358950       7521 WARD         51713750       7900 JAMES        39302450   14 rows selected.

 

 

플랜 확인

1 2 3 4 5 6 7 8

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'); ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation          | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |         |      1 |        |       | 13716 (100)|          |     14 |00:00:00.99 |   47733 |  47730 |       |       |          | |   1 |  HASH GROUP BY     |         |      1 |     14 |   364 | 13716   (6)| 00:00:01 |     14 |00:00:00.99 |   47733 |  47730 |   903K|   903K| 2580K (0)| |   2 |   TABLE ACCESS FULL| BIG_EMP |      1 |   8108K|   201M| 13163   (2)| 00:00:01 |   8108K|00:00:00.23 |   47733 |  47730 |       |       |          | ------------------------------------------------------------------------------------------------------------------------------------------------------------

디스크에서 47730 블록을 읽고(Reads 부분), 메모리는 각각 OMem이 903K(예측치), 1Mem이 903K(예측치), Used-Mem이 2580K(실제 사용한 메모리)임
시간은 0.99초 소요됨

 

 

테스트1 쿼리의 10046 트레이스

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          2          2          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1      0.88       0.89      47729      47733          0          14 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3      0.89       0.89      47731      47735          0          14   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 1   Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------         14         14         14  HASH GROUP BY (cr=47733 pr=47729 pw=0 time=891518 us starts=1 cost=13716 size=54054 card=2079)    8108730    8108730    8108730   TABLE ACCESS FULL BIG_EMP (cr=47733 pr=47729 pw=0 time=238513 us starts=1 cost=13163 size=210826980 card=8108730)     Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   PGA memory operation                           16        0.00          0.00   SQL*Net message to client                       1        0.00          0.00   Disk file operations I/O                        1        0.00          0.00   direct path read                              384        0.00          0.03   SQL*Net message from client                     1        0.00          0.00 ********************************************************************************

테스트1 쿼리에서 10046 트레이스에 특이점은 볼수없었고 시간이 0.89초 소요됨을 알수 있었음
PGA memory operation 이벤트는 16으로 확인됨

 

 

테스트2 쿼리의 10046 트레이스

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1      1.04       1.04      47729      47733          0          14 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3      1.04       1.04      47729      47733          0          14   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 1   Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------         14         14         14  HASH GROUP BY (cr=47733 pr=47729 pw=0 time=1044855 us starts=1 cost=13716 size=364 card=14)    8108730    8108730    8108730   TABLE ACCESS FULL BIG_EMP (cr=47733 pr=47729 pw=0 time=299448 us starts=1 cost=13163 size=210826980 card=8108730)     Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   PGA memory operation                           17        0.00          0.00   SQL*Net message to client                       1        0.00          0.00   Disk file operations I/O                        1        0.00          0.00   direct path read                              384        0.00          0.03   SQL*Net message from client                     1        0.00          0.00 ********************************************************************************

테스트1 쿼리에서 10046 트레이스에서도 특이점은 볼수없었고 시간이 테스트1보다 긴 1.04초 소요됨을 알수 있었음
PGA memory operation 이벤트는 17로 확인됨

 

 

결론 :
두가지 방식을 비교했을때 성능상 크게 차이는 없었지만 테스트2가 조금 더 느렸음

그리고 테스트2 쿼리를 실행할때 메모리 사용량이 테스트 1에 비해 2배로 측정되긴 했지만 고작 2MB임

 

추가로 샘플 테이블 생성시 level <=6을 7로 변경해서 테스트 해봐도

테이블 full scan 으로 인해 속도가 저하되기만 할뿐 group by 로 인한 메모리 사용량은 크게 차이나지 않았음

 

 

참조 : https://scidb.tistory.com/entry/About-DBMSXPLAN-1%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D