[ Oracle ] Select절 Function 튜닝

오늘은 고객사에서 문의가 들어왔던 Query 튜닝 관련하여 글을 쓸예정이다. 

고객사는 기존에 쓰던 Query에 Function이 들어가게되면서 속도저하가 일어나게되었고 튜닝을 해달라고 하였다.

TEST환경을 만들어 재구현을 해볼 예정이다.

 

TEST DATA 생성

- 우선 TEST TABLE / Function을 만들었다.

** 1.TEST DATA 생성 **
SQL>CREATE TABLE MAXTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER,
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30),
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30), COLJ VARCHAR2(30));

Table created.
SQL> CREATE INDEX IDX_COLB ON MAXTEST(COLB);
Index created.


set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for d in 1..70 loop
FOR i IN 1..100000 LOOP
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=999;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
   w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
   FORALL i in 1..10000 INSERT INTO MAXTEST VALUES w_ins(i);
   COMMIT;
DBMS_OUTPUT.PUT_LINE(d);
end loop;
END;
/


SQL> select count(*) from MAXTEST;


  COUNT(*)
----------
    200000

SQL> select * from MAXTEST where rownum < 10;


COLA                       COLB       COLC COLD                           COLE                           COLF             COLG COLH                    COLI
-------------------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------
59                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
60                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
61                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
62                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
63                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
64                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
65                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
66                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII
67                       300000         99 DDDDDDDDDDDDDDDD               EEEEEEEEEEEEEEEE               FFFFFFFFFFFFFFFF               9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH     IIIIIIIIIIIIIIIIIIIIIIIIII


9 rows selected.


** 2.FUNCTION 생성 **
create or replace function test_func(COLA varchar) return VARCHAR
is 
 new varchar2(100);
begin
 new := substr(COLA,0,1);
return new;
end;
/

SQL> select test_func('ASD') as TEST from dual;

TEST
----------
A

 

Function 그냥 호출시

- 아래와 같이 Function을 그냥홀출하게되면 Function을 200K 만큼 실행하게되면서 속도에 저하가 발생하게된다.

SQL> select test_func(COLA),COLB,COLC from lee.maxtest;

PLAN_TABLE_OUTPUT
SQL_ID  8djs5mah7kp0k, child number 0
-------------------------------------
select test_func(COLA),COLB,COLC from lee.maxtest
Plan hash value: 3833668883
---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |    200K|00:00:00.11 |   27178 |
|   1 |  TABLE ACCESS FULL| MAXTEST |      1 |    158K|    200K|00:00:00.11 |   27178 |
---------------------------------------------------------------------------------------
Note
-----

 

스칼라 서브쿼리 이용

- 아래와 같이 Function을 스칼라 서브쿼리로 풀어서 ( select test_func(COLA) from dual) 로 바꾸게되면 DUAL 자체가 가상의 한개의 ROW이기 때문에 Function이 한번만 실행되게 되면 FAST DUAL이라는 실행계획으로 풀리게된다.

 

SQL> select (select test_func(COLA) from dual),COLB,COLC from lee.maxtest;

PLAN_TABLE_OUTPUT
SQL_ID  1fmrs0udmmpyj, child number 0
-------------------------------------
select (select test_func(COLA) from dual),COLB,COLC from lee.maxtest
Plan hash value: 3977623913
---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |    200K|00:00:00.09 |   27178 |
|   1 |  FAST DUAL        |         |    200K|      1 |    200K|00:00:00.05 |       0 |
|   2 |  TABLE ACCESS FULL| MAXTEST |      1 |    158K|    200K|00:00:00.09 |   27178 |
---------------------------------------------------------------------------------------
Note
-----

해당 TEST에서는 속도차이가 얼마나지 않지만 실제 고객 사례의 실행계획을 보면 차이가 많이 나는 것을 볼 수 있다.

** 1.사용자 함수 그냥호출 **
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     93 |00:00:34.58 |    6303 |       |       |          |
|   1 |  HASH GROUP BY     |           |      1 |  13549 |     93 |00:00:34.58 |    6303 |   723K|   723K| 1423K (0)|
|*  2 |   TABLE ACCESS FULL| (생략)    |      1 |  18981 |  19180 |00:00:00.09 |    6303 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

** 2.스칼라 서브쿼리 이용 **
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     93 |00:00:05.25 |    6301 |       |       |          |
|   1 |  FAST DUAL         |           |  19143 |      1 |  19143 |00:00:00.02 |       0 |       |       |          |
|   2 |  HASH GROUP BY     |           |      1 |  13549 |     93 |00:00:05.25 |    6301 |   723K|   723K| 2581K (0)|
|*  3 |   TABLE ACCESS FULL| (생략)    |      1 |  18981 |  19180 |00:00:00.08 |    6301 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

 

 

참고

http://www.gurubee.net/article/14081

[강정식의 오라클 이야기]저장형 함수를 사용할 때 튜닝방법