by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

05. 데이터베이스 Call 최소화 원리




SQL수행 중에 발생하는 Execute, Fetch Call을 줄이는 방법
데이터베이스 Call을 User Call과 Recursive Call로 나누어 각각을 최소화하는 원리와 방안에대한 설명

01. Call 통계

SQL 트레이스 레포트)

select * from cust_nm, birthday from customer where cust_id = :cust_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   5000      0.18       0.14          0          0          0           0
Fetch     5000      0.21       0.25          0      20000          0       50000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      0.39       0.40          0      20000          0       50000

Misses in library cache during parse: 1
  • Parse Call : 커서를 파싱하는 과정에 대한 통계, 실행계획을 생성하거나 찾는 과정에 관한 정보를 포함
  • Execute Call : 커서를 실행하는 단계에 대한 통계
  • Fetch Call : select문에서 실제 레코드를 읽어 사용자가 요구한 결과집합을 반환하는 과정에 대한 통계
DML문 : insert, update, delete, merge

Execute Call 시점에 모든 처리과정을 서버내에서 완료하고 처리결과만 리턴하므로 Fetch Call이 전혀 발생하지 않는다.

delete from objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.21       0.22          0        321      20987       18853
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.21       0.22          0        322      20987       18853

Misses in library cache during parse: 1
insert ... select

클라이언트로부터 명시적인 Fetch Call을 받지 않으며 서버 내에서 묵시적으로 Fetch가 이루어짐

insert into objects
select * from dba_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.03          0          0          0           0
Execute      1      0.15       0.19          3       1563       2946       18853
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.20       0.22          3       1563       2946       18853

Misses in library cache during parse: 1
select 문

Execute Call단계에서는 커서만 오픈하고, 데이터처리는 Fetch 단계에서 발생

select owner, count(*)
from objects
group by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        330          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.01          0        331          0          14

Misses in library cache during parse: 1
For update문

for update 구문을 사용하면 Execute Call 단계에서는 모든 레코드를 읽어 Lock을 설정

select * from
emp for update


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7         14           0
Fetch        2      0.00       0.00          0          8          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0         15         14          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54

02. User Call vs Recursive Call

  • DB Call 커서 활동 상태에 따른 구분 : Parse, Execute, Fetch
  • DB Call 발생 위치에 따른 구분 : User Call, Recursive Call

User Call

  • User Call 은 OCI(Oracle Call Interface)를 통해 오라클 외부로부터 들어오는 Call
  • User Call 최소화 방법
  1. Loop 쿼리를 해소하고 집합적 사고를 통해 One-SQL로 구현
  2. Array Processing : Array 단위 Fetch, Bulk Insert/Update/Delete
  3. 부분범위처리 원리 활용
  4. 효과적인 화면 페이지 처리
  5. 사용자 정의 함수/프로시저/트리거의 적절한 활용

Recursive Call

  • Recursive Call은 오라클 내부에서 발생하는 Call을 말한다.
  • SQL 파싱과 최적화 과정에서 발생하는 Data Dictionary 조회, PL/SQL로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행이 여기에 해당한다.
  • Recursive Call 최소화 방법
  1. 바인드변수를 적극적으로 사용해 하드파싱 횟수를 줄인다.
  2. PL/SQL로 작성한 프로그램을 이해하고 시기 적절하게 사용한다.

SQL 트레이스 파일을 TKProf 유틸리티로 포맷팅하면 맨 아래쪽에 아래와 같은 Overall Total 통계가 나온다. 이 중 NON-RECURSIVE 통계가 User Call에 해당하며, 그 아래쪽 RECURSIVE 통계가 Recursive Call에 해당한다.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.01       0.02          0          1          0           0
Execute      9      0.01       0.09          0         19         69          28
Fetch        4      0.00       0.00          0         16          0          28
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.03       0.11          0         36         69          56

Misses in library cache during parse: 5


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        8      0.00       0.01          0          0          0           0
Execute     41      0.04       0.07          0          0          0           0
Fetch       64      0.01       0.03          4        122          0         318
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      113      0.06       0.12          4        122          0         318

Misses in library cache during parse: 7
Misses in library cache during execute: 7

   10  user  SQL statements in session.
   40  internal SQL statements in session.
   50  SQL statements in session.

데이터베이스의 종류와 튜닝원리 요약

03. 데이터베이스 Call이 성능에 미치는 영향

기간계 시스템을 정보계 시스템으로 가져오기 위한 ETL 배치 프로그램이 필요

테이블을 생성하고 데이터를 넣는다.

CREATE TABLE 월요금납부실적
AS
SELECT TO_CHAR(OBJECT_ID) 고객번호
     , '200903' 납입월
     , round(dbms_random.value(1000, 10000), -2) 지로
     , round(dbms_random.value(1000, 10000), -2) 자동이체
     , round(dbms_random.value(1000, 10000), -2) 신용카드
     , round(dbms_random.value(1000, 10000), -2) 핸드폰
     , round(dbms_random.value(1000, 10000), -2) 인터넷
FROM   ALL_OBJECTS
WHERE ROWNUM <= 30000;

CREATE TABLE 납입방법별_월요금집계 (
  고객번호      NUMBER
, 납입월        VARCHAR2(6)
, 납입방법코드  VARCHAR2(1)
, 납입금액       NUMBER
) ;
  • PL/SQL 수행
    DECLARE 
      CURSOR C(INPUT_MONTH VARCHAR2) IS 
        SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷 
        FROM   월요금납부실적  
        WHERE 납입월 = INPUT_MONTH; 
     
      REC C%ROWTYPE; 
      LTYPE VARCHAR2(1); 
    BEGIN 
      OPEN C('200903'); 
     
      LOOP 
        FETCH C INTO REC; 
        EXIT WHEN C%NOTFOUND; 
     
        IF REC.지로 > 0 THEN 
          LTYPE := 'A'; 
          INSERT INTO 납입방법별_월요금집계 
                (고객번호, 납입월, 납입방법코드, 납입금액)  
          VALUES(REC.고객번호, REC.납입월, LTYPE, REC.지로); 
        END IF; 
     
        IF REC.자동이체 > 0 THEN 
          LTYPE := 'B'; 
          INSERT INTO 납입방법별_월요금집계 
                (고객번호, 납입월, 납입방법코드, 납입금액)  
          VALUES(REC.고객번호, REC.납입월, LTYPE, REC.자동이체); 
        END IF; 
     
        IF REC.신용카드 > 0 THEN 
          LTYPE := 'C'; 
          INSERT INTO 납입방법별_월요금집계 
                (고객번호, 납입월, 납입방법코드, 납입금액)  
          VALUES(REC.고객번호, REC.납입월, LTYPE, REC.신용카드); 
        END IF; 
     
        IF REC.핸드폰 > 0 THEN 
          LTYPE := 'D'; 
          INSERT INTO 납입방법별_월요금집계 
                (고객번호, 납입월, 납입방법코드, 납입금액)  
          VALUES(REC.고객번호, REC.납입월, LTYPE, REC.핸드폰); 
        END IF; 
     
        IF REC.인터넷 > 0 THEN 
          LTYPE := 'E'; 
          INSERT INTO 납입방법별_월요금집계 
                (고객번호, 납입월, 납입방법코드, 납입금액)  
          VALUES(REC.고객번호, REC.납입월, LTYPE, REC.인터넷); 
        END IF; 
     
      END LOOP; 
     
      CLOSE C; 
     
    END;
    
  • JAVA 프로그램 수행
    public class JavaLoopQuery{ 
      public static void insertData( Connection con 
                                   , String param1 
                                   , String param2 
                                   , String param3 
                                   , long param4) throws Exception{ 
        String SQLStmt = "INSERT INTO 납입방법별_월요금집계  "  
                + "(고객번호, 납입월, 납입방법코드, 납입금액) " 
                + "VALUES(?, ?, ?, ?)"; 
        PreparedStatement st = con.prepareStatement(SQLStmt); 
        st.setString(1, param1); 
        st.setString(2, param2); 
        st.setString(3, param3); 
        st.setLong(4, param4); 
        st.execute(); 
        st.close(); 
     
      } 
     
      public static void execute(Connection con, String input_month)  
      throws Exception { 
        String SQLStmt = "SELECT 고객번호, 납입월" 
                       + "     , 지로, 자동이체, 신용카드, 핸드폰, 인터넷 " 
                       + "FROM   월요금납부실적 " 
                       + "WHERE  납입월 = ?"; 
        PreparedStatement stmt = con.prepareStatement(SQLStmt); 
        stmt.setString(1, input_month); 
        ResultSet rs = stmt.executeQuery(); 
        while(rs.next()){ 
          String 고객번호 = rs.getString(1); 
          String 납입월 = rs.getString(2); 
          long 지로 = rs.getLong(3); 
          long 자동이체 = rs.getLong(4); 
          long 신용카드 = rs.getLong(5); 
          long 핸드폰 = rs.getLong(6); 
          long 인터넷 = rs.getLong(7); 
          if(지로 > 0)     insertData (con, 고객번호, 납입월, "A", 지로); 
          if(자동이체 > 0) insertData (con, 고객번호, 납입월, "B", 자동이체); 
          if(신용카드 > 0) insertData (con, 고객번호, 납입월, "C", 신용카드); 
          if(핸드폰 > 0)   insertData (con, 고객번호, 납입월, "D", 핸드폰); 
          if(인터넷 > 0)   insertData (con, 고객번호, 납입월, "E", 인터넷); 
        } 
        rs.close(); 
        stmt.close(); 
      } 
     
      public static void main(String[] args) throws Exception{ 
     
        long btm = System.currentTimeMillis(); 
     
        Connection con = getConnection(); 
     
        execute(con, "200903"); 
     
        System.out.println("elapsed time : " + (System.currentTimeMillis() - btm)); 
     
        releaseConnection(con); 
      }
    
  • PL/SQL문을 수행한 SQL 트레이스 결과
    SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷 
    FROM
     월요금납부실적 WHERE 납입월 = :B1 
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.03          0          1          0           0
    Fetch    30001      0.32       0.25          0      30004          0       30000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    30003      0.32       0.29          0      30005          0       30000
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 41     (recursive depth: 1)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
      30000  TABLE ACCESS FULL 월요금납부실적 (cr=30004 pr=0 pw=0 time=150156 us)
    ********************************************************************************
      
    INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액) 
    VALUES
    (:B4 , :B3 , :B2 , :B1 )
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        5      0.00       0.00          0          0          0           0
    Execute 150000      3.38       3.14          0       2440     162387      150000
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total   150005      3.38       3.14          0       2440     162387      150000
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 41     (recursive depth: 1)
    
  • 총소요시간 : 1377 msc
  • 데이터베이스 call : 180008
  • 처리해야 할 월요금납부실적이 100만 건이면 이 테이블에 대한 Fetch Call이 100만 번, 납입방법별_월요금집계 테이블로의 insert를 위한 Execute Call이 최대 100만번, 따라서 최대 200만 번의 데이터베이스 Call이 발생하게 된다.
  • 그나마 PL/SQL문으로 코딩했기 때문에 네트워크 트래픽 없는 Recursive Call이므로 제법 빠르게 수행된다.
  • Java 프로그램을 수행한 SQL 트레이스 결과
    SELECT 고객번호, 납입월     , 지로, 자동이체, 신용카드, 핸드폰, 인터넷 
    FROM
       월요금납부실적 WHERE  납입월 = :1
    
    
    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     3001      0.14       0.27          0       3121          0       30000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     3003      0.14       0.27          0       3121          0       30000
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 41  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
      30000  TABLE ACCESS FULL 월요금납부실적 (cr=3121 pr=0 pw=0 time=60062 us)
    
    
    ********************************************************************************
    
    INSERT INTO 납입방법별_월요금집계  (고객번호, 납입월, 납입방법코드, 납입금액) 
    VALUES
    (:1, :2, :3, :4)
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse   150000      1.35       1.26          0          0          0           0
    Execute 150000      8.31       9.56          2     150130     746331      150000
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total   300000      9.66      10.82          2     150130     746331      150000
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 41  
    
    
    ********************************************************************************
    
  • 총소요시간 : 942519 msc
  • 데이터베이스 call : 303,000번
  • select 문에서 Fetch Call이 앞에서보다 1/10 수준으로 준 것은 JAVA에서 FetchSize 기본 설정이 10이기 때문이다.
  • insert문은 애플리케이션 커서 캐싱 기법을 사용하지 않았으므로 Execute Call과 같은 횟수만큼 Parse Call 발생했다.
  • 여기서 중요한것은 순수하게 서버에서 실행된 시간은 10여초에 불과, 나머지는 네트워크구간에서 소비한 시간, 그리고 데이터베이스Call이 발생할 때마다 매번 OS로부터 CPU와 메모리 리소스를 할당받으려고 소비한 시간이다.(User Call이 Recursive Call에 비해 더 심각한 부하를 일으키는 이유가 바로 여기있다.)
  • 개선
    1. One-SQL로 통합
    INSERT INTO 납입방법별_월요금집계(납입월,고객번호,납입방법코드,납입금액) 
    SELECT x.납입월, x.고객번호 
         , CHR(64 + Y.NO) 납입방법코드 
         , DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷) 
    FROM   월요금납부실적 x 
         ,(SELECT LEVEL NO FROM DUAL CONNECT BY LEVEL <= 5) y 
    WHERE x.납입월 = '200903' 
    AND   y.NO IN (  
            DECODE(지로, 0, NULL, 1) 
          , DECODE(자동이체, 0, NULL, 2) 
          , DECODE(신용카드, 0, NULL, 3)  
          , DECODE(핸드폰, 0, NULL, 4)  
          , DECODE(인터넷, 0, NULL, 5)  
    ) ; 
     
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows 
    ------- ------ -------- ------------ ---------- ---------- ---------- ---------- 
    Parse        1    0.000        0.000          0          0          0          0 
    Execute      1    1.190        1.304          0       1590       3835     150000 
    Fetch        0    0.000        0.000          0          0          0          0 
    ------- ------ -------- ------------ ---------- ---------- ---------- ---------- 
    Total        2    1.190        1.304          0       1590       3835     150000 
     
    Misses in library cache during parse: 0 
    Optimizer goal: FIRST_ROWS 
    Parsing user: IBS_ADM (ID=41) 
     
    Rows     Row Source Operation 
    -------  --------------------------------------------------- 
          0  STATEMENT 
     150000   NESTED LOOPS  (cr=710 pr=0 pw=0 time=600157 us) 
          5    VIEW  (cr=0 pr=0 pw=0 time=96 us) 
          5     CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=87 us) 
          1      FAST DUAL  (cr=0 pr=0 pw=0 time=4 us) 
     150000    TABLE ACCESS FULL 월요금납부실적 (cr=710 pr=0 pw=0 time=450321 us) 
     
    

2. 소트 머지 조인 또는 해시 조인으로 유도하기 위한 튜닝

 
INSERT INTO 납입방법별_월요금집계(납입월,고객번호,납입방법코드,납입금액) 
SELECT */*+ USE_MERGE(X Y) NO_EXPAND NO_MERGE(X) */* x.납입월, x.고객번호 
     , CHR(64 + Y.NO) 납입방법코드 
     , DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷) 
FROM  (SELECT 1 DUMMY,납입월,고객번호,지로,자동이체,신용카드,핸드폰,인터넷  
       FROM   월요금납부실적 
       WHERE  납입월 = '200903') x 
     ,(SELECT 1 DUMMY, LEVEL NO FROM DUAL CONNECT BY LEVEL <= 5) y 
WHERE x.DUMMY = y.DUMMY 
AND   y.NO IN (  
        DECODE(지로, 0, NULL, 1) 
      , DECODE(자동이체, 0, NULL, 2) 
      , DECODE(신용카드, 0, NULL, 3)  
      , DECODE(핸드폰, 0, NULL, 4)  
      , DECODE(인터넷, 0, NULL, 5)  
) ; 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows 
------- ------ -------- ------------ ---------- ---------- ---------- ---------- 
Parse        1    0.000        0.000          0          0          0          0 
Execute      1    1.470        1.550          0       1037       3778     150000 
Fetch        0    0.000        0.000          0          0          0          0 
------- ------ -------- ------------ ---------- ---------- ---------- ---------- 
Total        2    1.470        1.550          0       1037       3778     150000 
 
Misses in library cache during parse: 0 
Optimizer goal: FIRST_ROWS 
Parsing user: IBS_ADM (ID=41) 
 
Rows     Row Source Operation 
-------  --------------------------------------------------- 
      0  STATEMENT 
 150000   MERGE JOIN  (cr=142 pr=0 pw=0 time=1004484 us) 
      5    SORT JOIN (cr=0 pr=0 pw=0 time=115 us) 
      5     VIEW  (cr=0 pr=0 pw=0 time=70 us) 
      5      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=55 us) 
      1       FAST DUAL  (cr=0 pr=0 pw=0 time=4 us) 
 150000    FILTER  (cr=142 pr=0 pw=0 time=854395 us) 
 150000     SORT JOIN (cr=142 pr=0 pw=0 time=254934 us) 
  30000      VIEW  (cr=142 pr=0 pw=0 time=60074 us) 
  30000       TABLE ACCESS FULL 월요금납부실적 (cr=142 pr=0 pw=0 time=72 us)
  • 총소요시간 : 1초안밖
  • 다음절에 나오는 Array Pocessing 기법을 활용하면, JAVA 같은 프로그램에서도 네트워크 트래픽을 획기적으로 줄여 줘 굳이 One-SQL로 작성하지 않더라도 성능개선 효과를 얻을수 있다.

"납입방법별_월요금집계" 테이블을 읽어 "월요금납부실적"을 가공하고자 할 때는 어떻게 하면될까?

  • 잘못된 사례
    INSERT INTO 월요금납부실적 
    (고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷) 
    SELECT K.고객번호, '200903' 납입월 
         , A.납입금액 지로  
         , B.납입금액 자동이체  
         , C.납입금액 신용카드  
         , D.납입금액 핸드폰  
         , E.납입금액 인터넷  
    FROM   고객 K 
         ,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계 
           WHERE  납입월 = '200903' 
           AND    납입방법코드 = 'A') A 
         ,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계 
           WHERE  납입월 = '200903' 
           AND    납입방법코드 = 'B') B 
         ,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계 
           WHERE  납입월 = '200903' 
           AND    납입방법코드 = 'C') C 
         ,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계 
           WHERE  납입월 = '200903' 
           AND    납입방법코드 = 'D') D 
         ,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계 
           WHERE  납입월 = '200903' 
           AND    납입방법코드 = 'E') E 
    WHERE  A.고객번호(+) = K.고객번호 
    AND    B.고객번호(+) = K.고객번호 
    AND    C.고객번호(+) = K.고객번호 
    AND    D.고객번호(+) = K.고객번호 
    AND    E.고객번호(+) = K.고객번호 ; 
    
  • 잘된 사례
INSERT INTO 월요금납부실적 
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷) 
SELECT 고객번호, 납입월 
     , NVL(SUM(DECODE(납입방법코드, 'A', 납입금액)), 0) 지로 
     , NVL(SUM(DECODE(납입방법코드, 'B', 납입금액)), 0) 자동이체 
     , NVL(SUM(DECODE(납입방법코드, 'C', 납입금액)), 0) 신용카드 
     , NVL(SUM(DECODE(납입방법코드, 'D', 납입금액)), 0) 핸드폰 
     , NVL(SUM(DECODE(납입방법코드, 'E', 납입금액)), 0) 인터넷 
FROM   납입방법별_월요금집계 
WHERE  납입월 = '200903' 
GROUP BY 고객번호, 납입월 ;

04. Array Processing 활용

1. 한 번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delet 수행
2. 네트워크를 통한 데이터베이스 Call을 감소
3. SQL 수행시간과 CPU 사용량을 획기적으로 줄여준다.

"납입방법_월요금집계" 테이블을 가공하는 사례를 Array Processing을 이용하는 방식 적용

public class JavaArrayProcessing{ 
  public static void insertData( Connection con 
                               , PreparedStatement st 
                               , String param1 
                               , String param2 
                               , String param3 
                               , long param4) throws Exception{ 
    st.setString(1, param1); 
    st.setString(2, param2); 
    st.setString(3, param3); 
    st.setLong(4, param4); 
    st.addBatch(); 
  } 
 
  public static void execute(Connection con, String input_month)  
  throws Exception { 
    long rows = 0; 
    String SQLStmt1 = "SELECT 고객번호, 납입월" 
                    + "     , 지로, 자동이체, 신용카드, 핸드폰, 인터넷 " 
                    + "FROM   월요금납부실적 " 
                    + "WHERE  납입월 = ?"; 
                    
    String SQLStmt2 = "INSERT /*+ test3 */ INTO 납입방법별_월요금집계  "  
            + "(고객번호, 납입월, 납입방법코드, 납입금액) " 
            + "VALUES(?, ?, ?, ?)"; 
 
    con.setAutoCommit(false); 
 
    PreparedStatement stmt1 = con.prepareStatement(SQLStmt1); 
    PreparedStatement stmt2 = con.prepareStatement(SQLStmt2); 
    *stmt1.setFetchSize(1000);* 
    stmt1.setString(1, input_month); 
    ResultSet rs = stmt1.executeQuery(); 
    while(rs.next()){ 
      String 고객번호 = rs.getString(1); 
      String 납입월 = rs.getString(2); 
      long 지로 = rs.getLong(3); 
      long 자동이체 = rs.getLong(4); 
      long 신용카드 = rs.getLong(5); 
      long 핸드폰 = rs.getLong(6); 
      long 인터넷 = rs.getLong(7); 
      if(지로 > 0)     insertData (con, stmt2, 고객번호, 납입월, "A", 지로); 
      if(자동이체 > 0) insertData (con, stmt2, 고객번호, 납입월, "B", 자동이체); 
      if(신용카드 > 0) insertData (con, stmt2, 고객번호, 납입월, "C", 신용카드); 
      if(핸드폰 > 0)   insertData (con, stmt2, 고객번호, 납입월, "D", 핸드폰); 
      if(인터넷 > 0)   insertData (con, stmt2, 고객번호, 납입월, "E", 인터넷); 
      *if(++rows%1000 == 0) stmt2.executeBatch();* 
    } 
 
    rs.close(); 
    stmt1.close(); 
 
    *stmt2.executeBatch();* 
    stmt2.close(); 
 
    con.commit(); 
    con.setAutoCommit(true); 
  } 
 
  public static void main(String[] args) throws Exception{ 
    long btm = System.currentTimeMillis(); 
    Connection con = getConnection(); 
    execute(con, "201206"); 
    System.out.println("elapsed time : " + (System.currentTimeMillis() - btm)); 
    releaseConnection(con); 
} 

SELECT 고객번호, 납입월     , 지로, 자동이체, 신용카드, 핸드폰, 인터넷 
FROM
   월요금납부실적 WHERE  납입월 = :1


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       31      0.06       0.29          0        169          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.06       0.29          0        169          0       30000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 41  

Rows     Row Source Operation
-------  ---------------------------------------------------
  30000  TABLE ACCESS FULL 월요금납부실적 (cr=169 pr=0 pw=0 time=25 us)

********************************************************************************

INSERT /*+ test3 */ INTO 납입방법별_월요금집계  (고객번호, 납입월, 
  납입방법코드, 납입금액) 
VALUES
(:1, :2, :3, :4)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     30      0.63       0.81          2       1142       5106      150000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.63       0.82          2       1142       5106      150000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41  
  • insert문에 대한 Execute Call이 30회만 발생
    insert된 로우 수가 150,000건이므로 매번 5,000건씩 Array Processing
    =>커서에서 Fetch되는 각 로우마다 5번씩 insert를 수행하고, 1000 로우마다 한번식 executeBatch를 수행
  • select 결과를 Fetch 할 때도 1,000개 단위로 Array Fetch 하도록 조정하여 (JAVA에서 기본값은 10) 3,000건을 읽는데 Fetch Call이 31회만 발생했다.
Bulk Select/Bulk Insert 예제

PL/SQL을 이용해 데이터를 Bulk로 1,000건씩 Fetch해서 Bulk로 insert

create table emp
as
select object_id empno, object_name ename, object_type job
       ,round(dbms_random.value(1000,5000), -2) sal
       ,owner deptno, created hirdate
  from all_objects
 where rownum <= 10000;


create table emp2
as
select * from emp where 1=2;

DECLARE
  l_fetch_size NUMBER DEFAULT 1000;  -- 1,000건씩 Array 처리

  CURSOR c IS
    SELECT empno, ename, job, sal, deptno, hirdate
    FROM   emp;

  TYPE array_empno      IS TABLE OF emp.empno%type;
  TYPE array_ename      IS TABLE OF emp.ename%type;
  TYPE array_job        IS TABLE OF emp.job%type;
  TYPE array_sal        IS TABLE OF emp.sal%type;
  TYPE array_deptno     IS TABLE OF emp.deptno%type;
  TYPE array_hiredate   IS TABLE OF emp.hirdate%type;

  l_empno     array_empno     := array_empno   ();
  l_ename     array_ename     := array_ename   ();
  l_job       array_job       := array_job     ();
  l_sal       array_sal       := array_sal     ();
  l_deptno    array_deptno    := array_deptno  ();
  l_hiredate  array_hiredate  := array_hiredate();

  PROCEDURE insert_t( p_empno     IN array_empno
                    , p_ename     IN array_ename
                    , p_job       IN array_job
                    , p_sal       IN array_sal
                    , p_deptno    IN array_deptno
                    , p_hiredate  IN array_hiredate ) IS

  BEGIN
    FORALL i IN p_empno.first..p_empno.last
      INSERT INTO emp2
      VALUES ( p_empno   (i)
             , p_ename   (i)
             , p_job     (i)
             , p_sal     (i)
             , p_deptno  (i)
             , p_hiredate(i) );

  EXCEPTION
    WHEN others THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      RAISE;
  END insert_t;

BEGIN

  OPEN c;

  LOOP

    FETCH c BULK COLLECT
    INTO l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate
    LIMIT l_fetch_size;

    insert_t( l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate );

    EXIT WHEN c%NOTFOUND;
  END LOOP;

  CLOSE c;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

트레이스 결과

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, HIRDATE 
FROM
 EMP


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       11      0.02       0.03          1         82          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.03       0.03          1         83          0       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS FULL EMP (cr=82 pr=1 pw=0 time=30 us)


********************************************************************************

INSERT INTO EMP2 
VALUES
 ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.04       0.07          0        151        976       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.04       0.07          0        151        976       10000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41     (recursive depth: 1)

  • SQL 트레이스 결과를 보면, 10,000건을 처리하는데 select문의 Fetch Call과 insert문의 Execute Call이 각각 10번씩만 발생한 것을 알 수 있다.
    (select의 Fetch Call이 11번이 발생한 것은 데이터가 더 있는지 확인하기 위한 것임)
  • EXP, IMP 명령을 통해 데이터를 Export, Import 할 때도 내부적으로 Array Proccessing이 활용

05. Fetch Call 최소화

  • 부분범위처리 원리
  • OLTP 환경에서 부분범위처리에 의한 성능개선 원리
  • ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
  • 프로그램 언어에서 Array 단위 Fetch 기능 활용

1. 부분범위처리의 원리

-- 테이블 생성 ( Array Size : 5)
create table t (
  x NUMBER   not null
, y NUMBER   not null ) ;

-- 데이터 insert(랜덤으로)
insert into t
select *
from (
 select rownum x, rownum y
 from   dual
 connect by level <= 500000
)
order by dbms_random.value
;

alter table t add
constraint t_pk primary key (x);

alter system flush buffer_cache;

-- 6건 검색
select /*+ index(t t_pk) */ x, y
from   t
where  x >  0
and    y <= 6 ;

       X         Y
-------- ---------
       1         1
       2         2
       3         3
       4         4
       5         5  -> 엔터를 치자마자 여기까지 출력하고 잠시 멈춤
       6         6  -> 일정시간이 지난 후에 이 라인을 출력하고 수행 종료

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.96       1.06       1040     500546          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.98       1.07       1040     500548          0           6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  TABLE ACCESS BY INDEX ROWID T (cr=500546 pr=1040 pw=0 time=55 us cost=307 size=312 card=12)
    500000     500000     500000   INDEX RANGE SCAN T_PK (cr=1047 pr=1040 pw=0 time=643856 us cost=12 size=0 card=4687)(object id 23893)

2. OLTP 환경에서 부분범위처리에 의한 성능개선 원리

  • 첫번째 Fetch Call
    1. 오라클 서버는 이 5개 레코드를 아주 빠르게 찾았으므로 지체 없이 전송 명령을 통해 클라이언트에게 전송
    2. 클라이언트는 Array 버퍼에 담긴 5개의 레코드를 곧바로 화면에 출력.

*두번째 Fetch Call
1. 두번째 Fetch Call 명령을 받자마자 x=y=6인 레코드를 찾아 Oracle Net으로 내려보낸다.
2. 조건에 더이상 만족하는 레코드가 없다는 사실을 오라클은 모르기 때문에 계속 인덱스를 스캔하면서 테이블을 엑세스해 본다.
끝까지 가 본 후에야 더는 전송할 데이터가 없음을 인식하고 한 건만 전송하도록 Oracle Net에 명령을 보낸다.

  • One-Row Fetch
    select /*+ index(t t_pk) */ x,y
    from t
    where x > 0
    and y <= 1
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          2          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.57       0.59          0     500545          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.57       0.59          0     500547          0           1
    
    PARSE #215280104:c=31200,e=31689,p=0,cr=75,cu=0,mis=1,r=0,dep=0,og=1,plh=3772518221,tim=3274073163
    EXEC #215280104:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3772518221,tim=3274074399
    FETCH #215280104:c=0,e=71,p=0,cr=4,cu=0,mis=0,[r=1],dep=0,og=1,plh=3772518221,tim=3274074707
    FETCH #215280104:c=577203,e=592163,p=0,cr=500541,cu=0,mis=0,[r=0],dep=0,og=1,plh=3772518221,tim=3274667442
    
    select /*+ index(t t_pk) */ x, y
    from   t
    where  x >  0
    and    y <= 6 ;
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.00          0          2          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        3      0.96       1.06       1040     500546          0           6
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        5      0.98       1.07       1040     500548          0           6
    

PARSE #215280104:c=31200,e=30590,p=0,cr=75,cu=0,mis=1,r=0,dep=0,og=1,plh=3772518221,tim=1112506656
EXEC #215280104:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3772518221,tim=1112506951
FETCH #215280104:c=0,e=39,p=0,cr=4,cu=0,mis=0,[r=1],dep=0,og=1,plh=3772518221,tim=1112507068
FETCH #215280104:c=0,e=60,p=0,cr=6,cu=0,mis=0,[r=5],dep=0,og=1,plh=3772518221,tim=1112507405
FETCH #215280104:c=561604,e=566956,p=0,cr=500536,cu=0,mis=0,[r=0],dep=0,og=1,plh=3772518221,tim=1113079488

3. [ArraySize] 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과

대량 데이터를 내려받을 때 [ArraySize]를 크게 설정하면

  • Fetch Call 횟수가 줄어 네트워크 부하가 감소하고, 쿼리 성능이 향상
  • 서버 프로세스가 읽어야 할 블록 개수까지 감소

[ArraySize]를 조정하는데 왜 블록 I/O가 줄어드는 것일까?

SQL> create table test 
     as 
     select * from all_objects;

테이블이 생성되었습니다.

SQL> set autotrace traceonly statistics;
SQL> set arraysize 2;
SQL> select * from test;

47094 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      23871  consistent gets
        530  physical reads
          0  redo size
    6247169  bytes sent via SQL*Net to client
     259498  bytes received via SQL*Net from client
      23548  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      47094  rows processed

[ArraySize]를 2로 설정하고 47094로우를 가져오게 함.
읽은 블록개수(consistent gets) : 23871
Featch 횟수(SQL*Net roundtrips to/from client) : 23548
===> Fetch할 때마다 2개의 로우(47094/23548 = 1.9999....) 씩 읽는것을 알 수 있다.

SQL 트레이스

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    23548      0.27       1.54          0      23871          0       47094
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    23550      0.27       1.58          0      23871          0       47094

[ArraySize]를 계속 늘려서 위와같은 테스트를 반복해 보자.
[ArraySize]를 키울수록 Fetch Count는 횟수가 줄고 더불어 Block I/O까지 주는것을 볼수 있다. 즉, 반비례다.

표 참조)

[ArraySize]를 키운다고 같은 비율로 Fetch Count와 Block I/O가 줄지 않는다.
따라서, 무작정 크게 설정한다고 좋은것만은 아니며, 오히려 리소스 낭비가 된다.

[ArraySize]가 늘면서 블록I/O가 감소하는 원리는?

10개의 행으로 구성된 3개의 블럭이 있다고 가정한다.

  • 총 30개의 레코드이므로 [ArraySize]를 3으로 설정하면 Fetch 횟수는 10이고, Block I/O는 12번 발생한다.
  • 1번블록 : 2~4번째 Fetch에서 반복 엑세스
  • 2번블록 : 4~7번째 Fetch에서 반복 엑세스
  • 3번블록 : 7~10번째 Fetch에서 반복 엑세스
  • 만약 [ArraySize]를 30으로 설정하면 Fetch횟수는 1로 줄어든다.

4. 프로그램 언어에서 Array 단위 Fetch 기능 활용

PL/SQL을 포함한 프로그램 언어에서 어떻게 [ArraySize]를 제어하는지 확인하자.

Cursor FOR Loop문을 사용할 경우

  • 9i까지 : 한 로우씩 처리 (4절 Array Processing의 Bulk Collect 구문을 사용하지 않는 한)
  • 10g : 자동으로 100개씩 Array Processing

Cursor FOR Loop문의 커서

  • Open, Fetch, Close가 내부적으로 이루어지는 것이 특징
  • Implicit Cursor FOR Loop, Explicit Cursor FOR Loop 두가지 형태가 있다. (두개 다 Array Fetch 효과 얻음)

Implicit Cursor FOR Loop

declare
  l_object_name big_table.object_name%type;
begin
  for item in ( select object_name from big_table where rownum <= 1000 )
  loop
    l_object_name := item.object_name;
    dbms_output.put_line(l_object_name);
  end loop;
end;
/

Explicit Cursor FOR Loop

declare
  l_object_name big_table.object_name%type;
  cursor c is select object_name from big_table where rownum <= 1000;
begin
  for item in c
  loop
    l_object_name := item.object_name;
    dbms_output.put_line(l_object_name);
  end loop;
end;
/

--  sql트레이스는 동일

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       11      0.00       0.00          0         24          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.00       0.00          0         24          0        1000


  • 일반 커서사용*
    declare
    cursor c is
      select object_name
      from test where rownum <= 1000;
      l_object_name test.object_name%type;
    begin
      open c;
      loop
        fetch c into l_object_name;
        exit when c%notfound;
        dbms_output.put_line(l_object_name);
      end loop;
      close c;
    end;
    
    -- sql 트레이스 결과
    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     1001      0.00       0.00          0       1003          0        1000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     1003      0.00       0.00          0       1003          0        1000
    
    * sys_refcursor 사용
     declare
     p_cursor sys_refcursor;
     o_name dba_objects%rowtype;
     begin
       open p_cursor for select * from dba_objects where rownum <= 1000;
       loop
        fetch p_cursor into o_name;
        exit when p_cursor%notfound;
        dbms_output.put_line(o_name.object_name);
       end loop;
       close p_cursor;
     end;
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.00          0          0          0           0
    Execute      1      0.10       0.08          0          0          0           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.12       0.09          0          0          0           1
    
    SELECT * 
    FROM
     DBA_OBJECTS WHERE ROWNUM <= 1000
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.03       0.03          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch     1001      0.01       0.03          0       2104          0        1000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     1003      0.04       0.07          0       2104          0        1000
    

위의 결과 Cursor FOR Loop를 사용하지 않으면 Array단위 Fetch가 작동하지 않음을 알 수 있다.

  • JAVA 프로그램에서 ArraySize를 조정하는 방법
    String sql = "select id,pw from customer";
    PreparedStatment stmt = conn.prepareStatment(sql); 
    Stmt.setFetchSize(100);
    ResultSet rs = stmt.executeQuery();
    // rs.setFetchSize(100); -- ResultSet에서 조정할 수도 있다.
    
    while(rs.next()){
    ......
    }
    
    rs.close();
    stmt.close();
    
  • JAVA에서 FetchSize 기본 값은 10
  • 대량 데이터를 Fetch 할 때 이 값을 100~500 정도로 늘려 주면 기본 값을 사용할 때보다 데이터베이스 Call 부하를 1/10 ~ 1/50로 줄일 수 있다.
  • FetchSize를 100으로 설정했을 때 데이터를 Fetch 해오는 메커니즘은 아래와 같다.
  1. 최초 rs.next() 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
  2. 이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
  3. 버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
  4. 모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.

06. 페이지 처리의 중요성

  • 클라이언트/서버환경
    대용량데이터 조회시 커서를 닫지않고 이벤트가 발생할때마다 결과 집합을 Fetch 하도록 구현.
  • 웹 애플리케이션환경
    커서를 계속 오픈한 채로 결과집합을 핸들링할 수 없다.
    사용자가 다음 패치를 요청하거나, 스크롤 바를 내릴 때마다
    개별적인 SQL문을 수행하는 방식으로 페이지 처리를 구현
  • 페이지 처리를 하지 않을때 발생하는 부하요인
    1. 다량의 Fetch Call 발생
    2. 대량의 결과 집합을 클라이언트로 전송하면서 발생하는 네트워크 부하
    3. 대량의 데이터 블록을 읽으면서 발생하는 I/O 부하
    4. AP 서버 및 웹 서버 리소스 사용량 증가
  • 페이지 처리의 부하를 해소 하는 방법
    1. 페이지 단위로, 화면에서 필요한 만큼씩 Fetch Call
    2. 페이지 단위로, 화면에서 필요한 만큼씩 네트워크를 통해 전송
    3. 인덱스와 부분범위처리 원리를 이용해 각 페이지에 필요한 최소량만 I/O
    4. 데이터를 소량씩 나누어 전송하므로 AP , 웹 서버 리소스 사용량 최소화

07. PLSQL 함수의 특징과 성능 부하

(1)PL/SQL 함수의 특징

1. 인터프리터 언어다.

SQL> show parameter plsql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
plsql_debug                          boolean     FALSE
plsql_native_library_dir             string
plsql_native_library_subdir_count    integer     0
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL

2. PL/SQL엔진(가상머신, Virtual machine)만 있으면 어디서든 실행가능.
3. native 코드로 완전 컴파일된 내장 함수에 비해 많이 느리다.
4. 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.

(2)Recursive Call을 포함하지 않는 함수의 성능 부하

내장함수 TO_CHAR와 사용자정의한 함수를 사용했을때 차이

SQL> create or replace function data_to_char(p_Dt date) return varchar2
  2  as
  3  begin
  4    return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
  5  end;
  6  /

함수가 생성되었습니다.

SQL> create table t (no number, char_time varchar2(21));
테이블이 생성되었습니다.

경   과: 00:00:00.01

-- 내장함수사용
SQL> insert into t
  2  select rownum no,
  3         to_char(sysdate+rownum, 'yyyy/mm/dd hh24:mi:ss') char_time
  4         from dual
  5         connect by level <= 1000000;

1000000 개의 행이 만들어졌습니다.
경   과: 00:01:52.21

-- 사용자가만든 함수사용
SQL> insert into t
  2  select rownum no,
  3         data_to_char(sysdate+rownum) char_time
  4         from dual
  5         connect by level <= 1000000;

1000000 개의 행이 만들어졌습니다.
경   과: 00:03:27.40

(3) Recursive Call를 포함 하는 함수의 성능 부하

select 문 삽입후 테스트


SQL> create or replace function date_to_char(p_dt date) return varchar2
  2  as
  3   l_empno number;
  4  begin
  5    select 1 into l_empno from dual;
  6
  7    return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
  8  end;
  9  /

함수가 생성되었습니다.

경   과: 00:00:00.03

SQL> delete from t;

2000000 행이 삭제되었습니다.

경   과: 00:01:27.14
SQL> insert into t
  2  select rownum no,
  3         date_to_char(sysdate+rownum) char_time
  4         from dual
  5         connect by level <= 1000000;

1000000 개의 행이 만들어졌습니다.
경   과: 00:01:51.39

- 트레이스결과
SELECT 1 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1000000     15.09      12.98          0          0          0           0
Fetch   1000000     10.64       8.73          0          0          0     1000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   2000001     25.73      21.71          0          0          0     1000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 73     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
1000000  FAST DUAL  (cr=0 pr=0 pw=0 time=6174443 us)


1. 사용자 정의함수는 소량의 데이터 조회 시에 사용
2. 대용량 조회 시에는 부분범위처리가 가능한 상황에서 제한적으로 사용
3. 조인, 스칼라 서브쿼리 형태호 변환

(4) 함수를 필터 조건으로 사용할 때 주의 사항

함수를 where절에 필터조건으로 사용할때

SQL> create or replace function emp_avg_sal return number
  2      is
  3       l_avg_sal number;
  4      begin
  5        select avg(sal) into l_avg_sal from emp;
  6
  7        return l_avg_sal;
  8      end;
  9      /

함수가 생성되었습니다.

SQL> create index emp_x01 on emp(sal);
인덱스가 생성되었습니다.

SQL> create index emp_x02 on emp(deptno);
인덱스가 생성되었습니다.

SQL> create index emp_x03 on emp(deptno, sal);
인덱스가 생성되었습니다.

SQL> create index emp_x04 on emp(deptno, ename, sal);
인덱스가 생성되었습니다.

<케이스1 : 인덱스를 사용하지 않고 Full Scan할때는 읽은 전체 건수만큼 함수호출>

 
=================================================================================
select /*+ full(emp) */ * from emp
       where sal >= emp_avg_sal
-------------------------------------
SELECT AVG(SAL) 
FROM
 EMP

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute     14      0.01       0.02          0          0          0           0
Fetch       14      0.00       0.00          0         98          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       29      0.03       0.02          0         98          0           6
=================================================================================
(테스트 결과)
SELECT AVG(SAL) 
FROM
 EMP


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          0         72          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.00       0.00          0         72          0          12

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 48     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=6 pr=0 pw=0 time=125 us)
        14         14         14   TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=84 us cost=3 size=20 card=2)
===========================================================================================

<케이스 2 : 인덱스를 이용하도록 하면 함수호출이 한번일어난다.>

select /*+ index(emp(sal)) */ * from emp
       where sal >= emp_avg_sal;

=====================================================================================
SELECT AVG(SAL) 
FROM
 EMP


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      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 48     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=1 pr=0 pw=0 time=86 us)
        12         12         12   INDEX FULL SCAN EMP_X01 (cr=1 pr=0 pw=0 time=61 us cost=1 size=20 card=2)(object id 23899)
==============================================================================================

<케이스 3 : 조건절에 deptno = 20 추가, exp_x02인덱스이용하여 조회>

SQL>  select /*+ index(emp, emp_x02) */ * from emp
  2         where sal >= emp_avg_sal   <-- 필터링 건수 3건
  3           and deptno =20;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X02 |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">="EMP_AVG_SAL"())
   2 - access("DEPTNO"=20)

테이블액세스 하는 횟수만큼 5번 함수호출이 일어난걸 확인할수있다.


<케이스 4 : deptno + sal 순으로 구성된 emp_x03 인덱스이용
SQL>  select /*+ index(emp(deptno, sal) */ * from emp
  2         where sal >= emp_avg_sal
  3           and deptno =20;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X03 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"() AND "SAL" IS NOT NULL)

sal >= 까지 조건까지 인덱스 조건으로 사용으로 함수호출이 한번 일어난걸확인


<케이스 5 : 조건은 같고, deptno 와 sal 컬럼중간인 ename 컬럼이 낀 emp_x04 인덱스사용
SQL> select /*+ index(emp(deptno, ename, sal)) */ * from emp
  2         where sal >= emp_avg_sal
  3           and deptno =20;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X04 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"())
       filter("SAL">="EMP_AVG_SAL"())

첫번째 액세스 단계에서 1번
필터 단계에서 나머지 4건을 찾는동안 4번 , deptno = 20 범위를 넘어 더이상 조건을 만족하는 레코드가 
없음을 확인하는 one-plus 스캔과정에서 1번, 하여 총6번의 함수 호출이 일어난다고 하는데 1번만 발생.


<케이스 6 : = 조건이 아닌경우
SQL>  select /*+ index(emp(deptno, sal)) */ * from emp
  2         where sal >= emp_avg_sal
  3           and deptno >= 10;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_X03 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO">=10 AND "SAL">="EMP_AVG_SAL"() AND "DEPTNO" IS NOT NULL)
       filter("SAL">="EMP_AVG_SAL"())

인덱스 스캔할 첫 번째 레코드를 액세스하는 단계에서 1번
deptno >= 10 조건을 만족하는 나머지 13건을 스캔하는 동안 13번
하여 총 14번 스캔이 말생한다고 하는데 테스트 결과는 한번만 나왔다.

  • 대용량테이블에서 조건절과 인덱스 구성에 따라 성능 차이가 매우 크게나타날 수 있음을 확인할수있다.

(5) 함수와 읽기 일관성

함수 내에서 수행하는 Recursive 쿼리는 메인 쿼리의 시작 시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을
읽기 때문에 문장수준 읽기 일관서을 보장하지 않는다

** 읽기 일관성을 보장 못하는 예

select a.지수업종코드
      ,min(a.지수업종명) 지수업종명
      ,avg(SF_현재가(b.종목코드)) 평균주식가격
      ,sum(SF_시가총액(b.종목코드)) 시가총액
from 지수업종 a, 지수업종구성종목 b
where a.지수업종유형코드 = '001'
  and b.지수업종코드 = ㅁ.지수업종코드
group by a.지수업종코드;
---------------------------------------------
select a.지수업종코드
      ,min(a.지수업종명)지수업종명
      ,(select avg(SF_현재가(b.종목코드)) from dual) 평균주식가격
      ,(select sum(SF_시가총액(b.종목코드)) from dual) 시가총액
from 지수업종 a, 지수업종구성종목 b
where a.지수업종유형코드 = '001'
  and b.지수업종코드 = ㅁ.지수업종코드
group by a.지수업종코드;
=============================================================
** 읽기 일관성을 보장하는 예
(조인)
select a.지수업종코드
      ,min(a.지수업종명)지수업종명
      ,avg(c.현재가)   평균주식가격
      ,sum(c.현재가*c.발행주식수) 시가총액
from 지수업종 a, 지수업종구성종목 b, 종목별시세 c
where a.지수업종유형코드 = '001'
  and b.지수업종코드 = ㅁ.지수업종코드
  and b.종목코드 = c.종목코드
group by a.지수업종코드;

(스칼라 서브쿼리)
select a.지수업종코드
      ,min(a.지수업종명)지수업종명
      ,avg(select 현재가 from 종목별시세
            where 종목코드 = b.종목코드) 평균주식가격
      ,sum(select 현재가*발행주식수 from 종목별시세
            where 종목코드 = b.종목코드) 시가총액
from 지수업종 a, 지수업종구성종목 b
where a.지수업종유형코드 = '001'
  and b.지수업종코드 = ㅁ.지수업종코드
group by a.지수업종코드;
=====================================================================

(6) 함수의 올바른 사용 기준

  • Dependency 체인에 의한 라이브러리 캐시 부하를 최소화하려면 가급적 함수/프로시저보다 패키지 사용이 유리

08. PL/SQL 함수 호출 부하 해소

  • 사용자정의 함수의 사용범위
    1) 소량의 데이터 조회시
    2) 대용량 데이터를 조회 할때는 부분범위 처리가 가능한 상황에서 제한적으로
    3) 조인 또는 스칼라 서브쿼리 형태로 변환하려는 노력이 필요
    4) 어쩔 수 없을 때는 사용하지만, 호출 횟수를 최소화 할수 있는방법을 강구
  • 함수호출 부하 해소 방안 요약
    1) 페이지 처리 또는 부분범위처리 활용
    2) Decode함수 또는 Case문으로 변환
    3) 뷰머지 방지를 통한 함수 호출 최소화
    4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
    5) Deterministic 함수의 캐싱 효과 활용
    6) 복잡한 함수 로직을 풀어 SQL로 구현

1) 페이저 처리 또는 부분범위 처리활용

클라이언트에게 데이터를 전송하는 단계에서 함수 호출이 일어나게 처리함으로 속도개선향상

Select *  
From ( 
 Select memb_nm(매수회원번호) 매도 회원명  
      ,memb_nm(매수회원번호)  매수회원명 
      ,code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명 
      ,code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명 
      ,code_nm('418' , 체결 유형코드) 체결 유형명 
 . . . . . . . 
 From 체결 
 Where 종목코드 = : 종목코드 
 And   체결일자 = : 체결일자  
 And   체결시간 between sysdate-10/21/60 and sysdate 
 Order by 체결시각 desc 
     ) a 
     Where rownum <= 30  
   ) 
Where no between 21 and 30 
) 
- 조건절에 부합하는 전체 레코드 건수만큼 함수 호출을 일으키고 그 결과 집합을 Sort Area  또는 Temp 테이블 스페이스 에 저장한다. 
  그리고 최종 결과집합 10건만 전송한다. 
 
Select   memb_nm(매수회원번호) 매도 회원명  
 ,memb_nm(매수회원번호)  매수회원명 
 ,code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명 
 ,code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명 
 ,code_nm('418' , 체결 유형코드) 체결 유형명 
 . . . . . . . 
From ( 
 Select rownum no, a.* 
 From (
    select 매도회원번호, 매수 회원번호, 
           매도투자자구분코드, 매수 투자자구분코드 
          . . . . . . . . . . . . .  
 From 체결 
 Where 종목코드 = : 종목코드 
 And   체결일자 = : 체결일자  
 And   체결시간 between sysdate-10/21/60 and sysdate 
 Order by 체결시각 desc 
 ) a 
 Where rownum <= 30  
) 
Where no between 21 and 30 
) 
- Order by 와 rownum에 의한 필터 처리 후 사용자에게 전송하는 결과 집합에 대해서만 함수 호출이 일어난다.

2) Decode, Case 함수 문으로 변환


- 체결 테이블 생성  
CREATE TABLE 체결(체결일자, 체결번호, 시장코드, 증권그룹코드, 체결수량, 체결금액) 
NOLOGGING 
AS 
SELECT '20090315' 
     , ROWNUM  
     , DECODE(SIGN(ROWNUM-100000), 1, 'ST', 'KQ')        -- 유가증권, 코스닥  
     , DECODE(MOD(ROWNUM, 8), 0, 'SS', 1, 'EF', 2, 'EW'  -- 주식, ETF, ELW 
                            , 3, 'DR', 4, 'SW', 5, 'RT'  -- DR, 신주인수권, 리츠 
                            , 6, 'BC', 7, 'MF')          -- 수익증권, 투자회사 
     , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)  
     , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)  
FROM   DUAL 
CONNECT BY LEVEL <= 500000 
UNION ALL 
SELECT '20090315' 
     , ROWNUM + 300000  
     ,(CASE WHEN MOD(ROWNUM, 4) < 2 THEN 'SD' ELSE 'GD' END) 
     ,(CASE WHEN MOD(ROWNUM, 4) IN (0, 2) THEN 'FU' ELSE 'OP' END) 
     , ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)  
     , ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)  
FROM   DUAL 
CONNECT BY LEVEL <= 500000 
; 
 
- 업무에 따라 주식 상품을 다르게 분류하고 집계함  
- 집계용 쿼리를 작성할 때마다 분류 기준을 적용하기 어려워 함수 정의  
CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2)  
RETURN VARCHAR2 
IS 
  L_분류 VARCHAR2(20); 
BEGIN 
  IF 시장코드 IN ('ST', 'KQ') THEN  -- 유가증권, 코스닥 
    IF 증권그룹코드 = 'SS' THEN  
      L_분류 := '주식 현물'; 
    ELSIF 증권그룹코드 IN ('EF', 'EW') THEN  -- ETF, ELW 
      L_분류 := '파생'; 
    ELSE  
      L_분류 := '주식외 현물'; 
    END IF; 
  ELSE   
     L_분류 := '파생'; 
  END IF; 
   
  --SELECT 순서 || '. ' || L_분류 INTO L_분류  
  --FROM   분류순서 
  --WHERE  분류명 = L_분류; 
   
  RETURN L_분류; 
END; 
/ 
 
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY SF_상품분류(시장코드, 증권그룹코드) 
ORDER BY 1 ; 
*경   과: 00:00:07.31* 
 
-  CASE 문으로 변경  
SELECT CASE 
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드  = 'SS' THEN '주식 현물' 
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물' 
       WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생' 
       END 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY  
       CASE 
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드  = 'SS' THEN '주식 현물' 
       WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물' 
       WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생' 
       END 
ORDER BY 1 ; 
*경   과: 00:00:00.68* 
 
-  DECODE 문으로 변경  
SELECT DECODE( 시장코드||증권그룹코드 
             , 'STSS', '주식 현물' 
             , 'KQSS', '주식 현물' 
             , 'SDFU', '파생' 
             , 'SDOP', '파생' 
             , 'GDFU', '파생' 
             , 'GDOP', '파생' 
             , 'STEF', '파생' 
             , 'STEW', '파생' 
             , 'KQEF', '파생' 
             , 'KQEW', '파생' 
             , '주식외 현물' ) 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY  
       DECODE( 시장코드||증권그룹코드 
             , 'STSS', '주식 현물' 
             , 'KQSS', '주식 현물' 
             , 'SDFU', '파생' 
             , 'SDOP', '파생' 
             , 'GDFU', '파생' 
             , 'GDOP', '파생' 
             , 'STEF', '파생' 
             , 'STEW', '파생' 
             , 'KQEF', '파생' 
             , 'KQEW', '파생' 
             , '주식외 현물' ) 
ORDER BY 1 ; 
*경   과: 00:00:00.87*

- 상품분류를 사용자가 원하는 순서대로 출력하도록 메타정보 관리를 위한 테이블 생성 
CREATE TABLE 분류순서(분류명, 순서) 
AS 
SELECT '주식 현물', 1 FROM DUAL 
UNION ALL 
SELECT '주식외 현물', 2 FROM DUAL 
UNION ALL 
SELECT '파생', 3 FROM DUAL ; 
 
- sf_상품분류 함수에 위 테이블을 조회해 출력순서를 위한 숫자값을 앞에 붙이도록 추가 
CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 IN VARCHAR2, 증권그룹코드 IN VARCHAR2)  
RETURN VARCHAR2 
IS 
  L_분류 VARCHAR2(14); 
BEGIN 
  IF 시장코드 IN ('ST', 'KQ') THEN 
    IF 증권그룹코드 = 'SS' THEN  
      L_분류 := '주식 현물'; 
    ELSIF 증권그룹코드 IN ('EF', 'EW') THEN  
      L_분류 := '파생'; 
    ELSE  
      L_분류 := '주식외 현물'; 
    END IF; 
  ELSE   
     L_분류 := '파생'; 
  END IF; 
   
  SELECT 순서 || '. ' || L_분류 INTO L_분류  
  FROM   분류순서 
  WHERE  분류명 = L_분류; 
   
  RETURN L_분류; 
END; 
/ 
 
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 
     , COUNT(*) 체결건수 
     , SUM(체결수량) 체결수량 
     , SUM(체결금액) 체결금액 
FROM   체결 
WHERE  체결일자 = '20090315' 
GROUP BY SF_상품분류(시장코드, 증권그룹코드) 
ORDER BY 1 ; 
*경   과: 00:00:45.42* 
 
- 분류순서 테이블을 쿼리하는 Recursive Call이 100만번 수행으로 성능 저하

SELECT C.순서 || '.' || B.분류명 상품 분류,
       SUM(체결건수) 체결건수,
       SUM(체결수량) 체결수량
       .....
FROM ( SELECT 시장코드, 증권그룹코드,
              COUNT(*) 체결건수,
              SUM(체결수량) 체결수량
              ....
         FROM 체결
        WHERE 체결일자 = '2090315'
        GROUP BY 시장코드, 증권그룹코드 )A, 상품분류 B, 분류순서 C
...........
- 상품분류, 분류순서를 테이블로 관리하고, 조인으로 변경하여 성능 이슈 해결

3) 뷰 머지 방지를 통한 함수 호출 최소화

SELECT SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량))    "주식현물_체결수량"    
     , SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '2. 주식외 현물', 체결수량))  "주식외현물_체결수량"  
     , SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '3. 파생', 체결수량))         "파생_체결수량"        
FROM   체결 
WHERE  체결일자 = '20090315' ; 
*경   과: 00:02:13.51*

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ; 
*경   과: 00:02:13.64*

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT /*+ NO_MERGE */ SF_상품분류(시장코드, 증권그룹코드) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ; 
*경   과: 00:00:45.34* 
 
SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT ROWNUM, SF_상품분류(시장코드, 증권그룹코드) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ; 
*경   과: 00:00:45.29* 
 
SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
  AND    ROWNUM > 0 
) ; 

4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화

1. 스칼라 서브쿼리를 사용하면 오라클은 그 수행횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장해 둔다.
2. 서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력 값을 리턴하고, 없으면 쿼리를 수행한 후 입력값과 출력값을 캐시에 저장해 두는 원리이다.
3. 함수를 Dual 테이블을 이용해 스칼라 서브쿼리로 한번 감싸는 것이다.
4. 함수 입력 값의 종류가 적을 때 이 기법을 활용하면 함수 호출횟수를 획기적으로 줄일 수 있다.

SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"     
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"  
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량"        
FROM ( 
  SELECT /*+ NO_MERGE */  
        (SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류 
       , 체결수량  
  FROM   체결 
  WHERE  체결일자 = '20090315' 
) ; 
 
SELECT 순서||'.'||:B1  
FROM 
 분류순서 WHERE 분류명 = :B1  
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          0          0           0 
Execute 725010     11.37      10.39          0          0          0           0 
Fetch   725010     17.57      17.63          0    2175030          0      725010 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total   1450021     28.95      28.03          0    2175030          0      725010

1. 함수 호출 횟수를 20번으로 예상했지만 너무 많은 함수를 호출
2. 해시 충돌이 발생했기 때문
3. 해시 충돌이 발생하면 기존 엔트리를 밀어내고 새로 수행한 입력 값과 출력 값으로 대체할 것 같지만, 오라클은 기존 캐시 엔트리를 그대로 둔채 스칼라 서브쿼리만 한 번 더 수행한다.
4. 8i, 9i에서는 256개 엔트리를 캐싱
5. 10g에서는 입력과 출력 값 크기, _query_execution_cache_max_size 파라미터에 의해 캐시 사이즈가 결정된다(defult : 65536)

*ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;* 
 
SELECT SUM(DECODE(상품분류, '1. 주식 현물'  , 체결수량)) "주식현물_체결수량"  
     , SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량" 
     , SUM(DECODE(상품분류, '3. 파생'       , 체결수량)) "파생_체결수량" 
FROM ( 
  SELECT /*+ NO_MERGE */  
        (SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류 
       , 체결수량  
  FROM   체결  
  WHERE  체결일자 = '20090315' 
) ; 
*경   과: 00:00:00.79* 
 
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          0          0           0 
Execute     20      0.00       0.00          0          0          0           0 
Fetch       20      0.00       0.00          6         60          0          20 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total       41      0.00       0.00          6         60          0          20

5) Deterministic 함수의 캐싱 효과 활용
1. 10gR2에서 함수를 선언할 때 Deterministic 키워드를 넣어 주면 캐싱 효과가 나타난다.
2. 함수의 입력 값과 출력 값은 CGA(Call Global Area)에 캐싱된다.
3. CGA에 할당된 값은 데이터베이스 Call 내에서만 유효하므로 Fetch Call이 완료되면 그 값은 모두 해제된다.
4. Deterministic 함수의 캐싱 효과는 데이터베이스 Call 내에서만 유효하다.
5. 스칼라 서브쿼리에서의 입력, 출력 값은 UGA에 저장되므로 Fetch Call에 상관없이 그 효과가 캐싱되는 순간부터 끝까지 유지 된다.

create or replace function ACCUM (p_input number) return number 
DETERMINISTIC
as 
  rValue number := 0 ; 
  call_cnt number := 0; 
begin 
  dbms_application_info.read_client_info(call_cnt); 
  if call_cnt is null then 
    call_cnt := 0; 
  end if; 
 
  dbms_application_info.set_client_info(call_cnt + 1);
 
  for i in 1..p_input loop 
    rValue := rValue + i ; 
  end loop; 
  return rValue ; 
end; 
/ 

select sum(accum_num) 
from ( 
  select accum(mod(rownum, 50)) accum_num 
  from dual 
  connect by level <= 1000000 
) ; 
*경   과: 00:00:01.54* 

select sys_context('userenv', 'client_info') from dual; 
 
SYS_CONTEXT('USERENV','CLIENT_INFO') 
----------------------------------------------------------------- 
50

- DETERMINISTIC 제거 후 재수행
exec dbms_application_info.set_client_info( NULL ); 
select sum(accum_num) 
from ( 
  select accum(mod(rownum, 50)) accum_num 
  from dual 
  connect by level <= 1000000 
) ; 
*경   과: 00:00:13.56* 
 
select client_info 
from   v$session 
where  sid = sys_context('userenv', 'sid'); 
 
CLIENT_INFO 
------------------------------------------------------- 
1000000

6) 복잡한 함수 로직을 풀어 SQL로 구현

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.