SQL튜닝 - 데이터베이스 CALL 최소화방안

서버 프로세스로의 작업 지시와 결과 요청은 데이터 베이스 CALL을 통해 전달됩니다. 데이터베이스 CALL과 결과 전송은 네트워크를 통하며, 서버와의 ROUNDTRIP 횟수가 많을수록 쿼리 수행 속도가 저하됩니다. 따라서 데이터베이스 CALL의 종류와 특성을 정확히 파악하여 CALL 횟수를 최소화하려는 노력이 필요합니다.

 

데이터베이스 CALL의 종류

 

SQL 커서에 대한 작업 요청에 따른 구분

 

- PARSE CALL : SQL 파싱을 요청하는 CALL
- EXECUTE CALL : SQL 실행을 요청하는 CALL
- FETCH CALL : SELECT 문의 결과 데이터를 요청하는 CALL

 

CALL 발생위치에 따른 구분

USER CALL

외부에서 DBMS로 요청되는 CALL을 말합니다. 동시 접속자가 많은 환경에서 성능 저하의 큰 요인입니다. 아래와 같은 방식으로 USER CALL 발생을 최소화할 수 있습니다.

 

- LOOP 쿼리를 최소화하고 ONE SQL로 구현
- ARRAY PROCESSING
- 부분범위처리 원리 활용
- 화면 페이징 처리
- 사용자 정의 함수, 프로시저, 트리거 활용

 

RECURSIVE CALL

DBMS 내부에서 프로세스 간에 발생하는 CALL 입니다. SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, 사용자 정의 함수 프로시저 내에서의 SQL 수행이 여기에 해당합니다. RECURSIVE CALL을 줄이려면, 바인드 변수를 사용해 하드파싱 횟수를 줄이고 사용자 정의 함수와 프로시저에서 발생하는 RECURSIVE CALL 제어에 유의해야합니다.

DATABASE CALL과 성능

ONE SQL 구현

 

LOOP로 작업을 반복수행하는 프로그램을 ONE SQL로 구현하면 DATABASE CALL 횟수를 줄일 수 있습니다.

 

public static void execute(Connection co, String input_month) throws Exception {
    String SQLStmt = "SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷"
                    + "FROM 월요금납부실적 "
                    + "WHERE 납입월 = ?";
    PreparStatment stmt = con.pepareStatement(SQLSmtm);
    stmt.setSTing( 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 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.setString(4, param4);
      st.execute();
      st.close()
  }

 

위와 같은 JAVA 프로그램이 10만번 수행될때, SELECT 하기위한 FETCH CALL은 10만 번, INSERT 하기위한 PARSE CALL과 EXECUTE CALL은 최대 50만번 까지 발생할 수 있습니다. 위 프로그램을 DBMS 내에서 수행되는 사용자 정의 프로시저로 개발하면 네트워크 트래픽이 없는 RECURSIVE CALL 만 발생하므로 빠르게 수행할 수 있습니다.

 

public static void execute(Connectioncon, String input_month) throws Exception {
      String SQLStmt = "INSERT INTO 납입방법별_월요금집계 "
     + "(고객번호, 납입월, 납입방법코드, 납입금액)"
     + "SELECT x.고객번호, x.납입월, CHR(64 + Y.NO) 납입방법코드"
     + "        ,DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷)"
     + "FROM 월요금납부실적 x, (SELECT LEVEL NO ROM DUAL CONNECT BY LEVET <= 5)Y "
     + "WHERE 납입월 = ?"
     + "AND Y.NO IN (DECODE(지로, 0, NULL, 1),DECODE(자동이체, 0, NULL, 2),
     DECODE(신용카드, 0, NULL, 3),DECODE(핸드폰, 0, NULL, 4),DECODE(인터넷, 0, NULL, 5))"
  PreparStatment stmt = con.pepareStatement(SQLSmtm);
    stmt.setSTing( 1, input_month);
    ResultSet rs = stmt.executeQuery();
    rs.close();
    stmt.close()
}

 

수행시간이 줄어드는 이유는 최대 110만 번 발생할 수 있는 데이터 베이스 CALL이 단 2회로 줄기 때문입니다.

 

데이터베이스 CALL과 시스템 확장성

 

데이터베이스 CALL은 개별 프로그램의 수행 속도뿐 아니라 시스템 전체의 확장성에 영향을 미칩니다. SQL의 EXECUTE 수를 최소화 하여 시스템 확장성을 높여야합니다.

 

ARRAY PROCESSING

 

ARRAY PROCESSING 기능을 활용하면 한 번의 SQL 수행으로 다량의 레코드를 동시에 처리할 수 있습니다. 이를 통해 네트워크를 통한 데이터베이스 CALL Q발생을 줄이고 SQL 수행시간과 CPU 사용량을 낮출수 있습니다.

 

대용량 데이터를 처리할때 ARRAY PROCESSING은 필수적입니다. 연속된 일련의 처리과정을 모두 ARRAY 단위로 진행합니다. PL/SQL을 통해 데이터를 BULK로 1000건씩 FETCH 해서 INSERT 하는 예제입니다.

 

DECLARE
  1_FETCH_SIZE NUMBER DEFAULT 1000;
  CURSOR C IS
  SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, HIREDATE
  FROM EMP;

  ...

  BEGIN

  OPEN C;

  LOOP

    FETCH C BULK COLLECT
    INTO P_EMPNO, P_ENAME, P_JOB, P_SAL, P_DEPTNO, P_HIREDATE
    LIMIT 1_FETCH_SIZE;

    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)
      );
      EXIT WHEN C%NOTFOUND;
    END LOOP;
    CLOSE C;

 

FETCH CALL 최소화

 

부분범위처리 원리

 

ARRAYSIZE 설정을 통해 데이터의 운반단위를 설정할 수 있습니다.

 

set arraysize 100

 

DBMS는 전체 결과 집합 중 아진 전송하지 않은 분량이 남아 있어도 클라이언트로부터 추가적인 FETCH CALL을 받기전까지 대기합니다. 이처럼 전체 데이터를 연속적으로 전송하지 않고 사용자로부터 FETCH CALL이 있을때마다 일정량 나눠서 전송하는 것을 '부분범위처리' 라고 합니다.

 

특히, OLTP성 업무에서는 출력 대상 레코드가 많아도 그중 일부만 FETCH 해서 보여주고 멈추는 경우가 많습니다. 부분범위처리가 가능한 업무라면, 출력 대상 레코드가 많을수록 ARRAY를 빠르게 채울 수 있어 응답속도도 빨라집니다.

 

출력 대상 레코드가 많을수록 응답속도가 빨라지는 것은 부분범위처리가 가능한 업무에만 해당됩니다. 전체 집합을 FETCH 하는 DW 또는 OLAP성 업무나 서버 내에서 데이터를 가공하는 프로그램에선 당연히 결과집합이 많을수록 느려집니다.

 

ARRAYSIZE 조정에 의한 FETCH CALL 감소 및 블록 I/O 감소효과

 

네트워크를 통해 전송해야할 데이터량에 따라 ARRAYSIZE를 조정할 필요가 있습니다. 예를들어, 대량 데이터를 파일 형식으로 내려받으면 어차피 전체 데이터를 전송해야하므로 가급적 값을 크게 설정해 FETCH CALL 발생을 줄여야합니다. 반대로 부분범위만 처리하는 경우 ARRAYSIZE를 작게 설정하는 것이 유리합니다. 많은 데이터를 읽어 전송했지만 사용하지 않는 비효율을 줄이기 위함입니다.

 

ARRAYSIZE를 늘리면 네트워크 부하가 줄어들 뿐아니라 서버 프로세스가 읽어야할 블록의 수도 줄어듭니다. 10개의 행으로 구성된 블록이 3개있을때 ARRAYSIZE 가 3이면 FETCH 횟수는 10회가 되고 블록 I/O는 12번 발생합니다. 한 블록을 4번에 걸쳐 반복 액세스 하기 때문입니다.

ARRAYSIZE가 10이면 3번의 FETCH와 3번의 블록 I/O가 발생합니다. 즉, ARRAYSIZE와 FETCH는 반비례관계입니다. 하지만 ARRAY 사이즈가 과도하게 큼면 리소스를 낭비하게 됩니다.

 

ORACLE PL/SQL에서 커서를 열고 레코드를 FETCH 하면 9i까지는 한 번에 한 건씩 처리했습니다. 10g 부터는 자동으로 100개씩 ARRAY FETCH가 일어납니다. 다만 아래처럼 커서의 OPEN, FETCH, CLOSE가 내부적으로 이뤄지는 CURSOR FOR LOOP 구문을 이용할때만 작동합니다.

 

for item in cursor
loop
  ....
end loop;

 

페이징처리 활용

 

부분범위처리 원리를 이용한 대용량 온라인 조회 성능개선은 커서를 닫지 않은 상태에서 사용자가 명시적으로 요청할 때 FETCH 할 수있는 환경에서 사용가능합니다. 즉, DB와의 연결을 지속하지 않는 웹 어클리케이션 환경에서는 커서를 계속 연 채로 결과집합을 핸들링 할 수 없습니다. 웹 환경에서는 사용자가 다음 페이지를 요청할때마다 개별적인 SQL 문을 수행하는 방식인 페이징 처리 방식으로 구현해야합니다.

 

- 페이지 단위로, 화면에 필요한 만큼만 FETCH CALL
- 페이지 단위로, 화면에서 필요한 만큼만 네트워크를 통해 결과전송
- 인덱스와 부분범위처리 원리를 이용해 각 페이지에 필요한 최소량만 I/O
- 데이터를 소량씩 나눠 전송

 

분산쿼리

 

분산 환경 데이터베이스를 구축하면 원격 조인시 성능문제가 자주 발생합니다.

 

SELECT CHANNEL_ID, SUM(QUANTITY_SOLD) AUANTITY_COLD
FROM ORDER A, SALES@K_SALES B
WHERE A.ORDER_DATE BETWEEN :1 AND :2
AND B.ORDER_NO = A.ORDER_NO
GROUP BY CHANNEL_ID

-- EXECUTION PLAN
-- SORT GROUP BY
--  NESTED LOOPS
--    REMOTE
--    TABLE ACCESS BY INDEX ROWID ORDER
--      INDEX UNIQUE SCAN(ORDER_PK)

 

위 쿼리는 원격 테이블을 전송받아 로칼 테이블과 조인합니다. 50만건이나 되는 데이터를 네트워크를 통해 전송받으면 성능이 크게 저하됩니다. 로컬 테이블의 필터조건을 이용해 조인할 데이터만 원격으로 보내면 네트워크 전송량을 크게 줄일 수 있습니다.

 

-- 다음은 원격 서버가 쿼리를 처리하도록 driving_site 힌트를 지정하고 다시 수행한 결과다

SELECT /*+DRIVING_SITE(B)*/ CHANNEL_ID, SUM(QUANTITY_SOLD) AUANTITY_COLD
FROM ORDER A, SALES@K_SALES B
WHERE A.ORDER_DTE BETWEEN :1 AND :2
AND B.ORDER_NO = A.ORDER no
GROUP BY CHANNEL_ID

-- EXECUTION PLAN
-- SORT GROUP BY
--  NESTED LOOPS
--    TABLE ACCESS (BY INDEX ROWID) OF 'ORDER'
--    TABLE (RANGE SCAN) OF 'ORDER_IDX' (NON-UNIQUE)
--  REMOTE

 

사용자 정의함수, 프로시저의 특징과 성능

 

사용자 정의 함수와 프로시저의 특징을 잘 파악한다면 그것을 잘 활용해 성능을 높일 수 있습니다.

 

사용자 정의함수/프로시저의 특징

 

사용자 정의 함수와 프로시저는 가상엔진을 통해 실행됩니다. 실행될 때마다 컨텍스트 스위칭이 발생하며 이는 성능저하요인으로 작용합니다. 게다가 메인 쿼리가 참조하는 사용자정의함수에 또 다른 쿼리문이 내장돼있으면 수행성능이 더욱 저하됩니다. 사용자 정의함수에 내장된 쿼리를 수행할때마다 EXECUTE CALL, FETCH CALL 이 재귀적으로 일어나기 때문입니다.

 

사용자 정의 함수/프로시저에 의한 성능 저하 해소방안

 

대용량 조회 쿼리에서 함수를 사용하면 레코드 수만큼 함수 호출과 RECURSIVE CALL이 반복돼 성능이 극도로 저하됩니다. 따라서 사용자 정의함수는 소량의 데이터를 조회할 때, 또는 부분범위처리가 가능한 상황에 제한적으로 사용해야 합니다.