데이터베이스 Call과 네트워크 부하

데이터베이스 Call 종류

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

• Parse Call
  : SQL 파싱을 요청하는 Call
• Execute Call
  : SQL 실행을 요청하는 Call
• Fetch Call
  : SELECT문의 결과 데이터 전송을 요청하는 Call

Call 발생 위치에 따른 구분

• User Call
  : DBMS 외부로부터 요청되는 Call. DBMS 성능과 확장성을 높이려면 User Call을 최소화하려는 노력이 중요하다.
  (One SQL, Array Processing, 부분범위처리 원리 활용, 효과적인 화면 페이지 처리, 사용자 정의 함수/프로시저/트리거의 적절한 활용)
• Recursive Call
  : DBMS 내부에서 발생하는 Call. SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, 사용자 정의 함수/프로시저 내에서의 SQL 수행

데이터베이스 Call과 성능

One SQL 구현의 중요성

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(); 
	}
}

만약 처리해야 할 월요금납부실적이 10만 건이면 이 테이블에 대한 Fetch Call이 10만번, INSERT를 위한 Parse Call과 Execute Call이 각각 최대 50만번(10만건의 데이터에 if문 5개가 모두 해당하는 경우), 따라서 최대 100만 번의 데이터베이스 Call이 발생할 수 있다.

따라서 아래와 같이 One SQL로 통합해보자.

public static void execute(Connection con, 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 FROM DUAL CONNECT BY LEVEL <= 5) y " 
                         + "WHERE x.납입월 = ? " 
                         + "AND y.NO IN ( DECODE(지로, 0, NULL, 1), DECODE(자동이체, 0, NULL, 2) " 
                         + " , DECODE(신용카드, 0, NULL, 3) , DECODE(핸드폰, 0, NULL, 4) " 
                         + " , DECODE(인터넷, 0, NULL, 5) )" ; // 모든 데이터를 SELECT 한 후 INSERT

        PreparedStatement stmt = con.prepareStatement(SQLStmt); 
        stmt.setString(1, input_month); 
        stmt.executeQuery(); stmt.close(); 
  } 

이렇게 통합하면 데이터베이스 Call(Parse Call 1회, Execute Call 1회)을 단 2회로 줄일 수 있다.

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

데이터베이스 Call은 개별 프로그램의 수행 속도에 큰 영향을 미칠 뿐만 아니라 궁극적으로 시스템 전체의 확장성에 영향을 미친다.

Array Processing 활용

Array Processing 기능을 활용하면 한 번의 SQL (INSERT/UPDATE/DELETE) 다량의 레코드를 동시에 처리할 수 있다. 이는 네트워크를 통한 데이터베이스 Call을 줄이고, 궁극적으로 SQL 수행시간과 CPU 사용량을 획기적으로 줄여준다.

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(); // batch 처리할 작업 저장
  } 
 
  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); 
} 

INSERT할 데이터를 계속 Array에 담기만 하다가 1000건 쌓일 때마다 executeBatch를 수행해준다. One SQL로 통합했을 때 나타나는 극적인 성능개선 효과가 데이터베이스 Call 횟수를 줄이는데 있음을 알 수 있다.

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

부분범위처리 : 쿼리 결과집합을 전송할 때, 전체 데이터를 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어서 전송하는 것

네트워크를 통해 전송해야 할 데이터량에 따라 ArraySize를 조절할 필요가 있다. (대량 데이터를 파일로 내려 받는다면 ArraySize를 크게, 앞쪽 일부 데이터만 Fetch하다가 멈추는 프로그램이라면 ArraySize를 작게 설정하는 것이 유리하다.)
ArraySize를 증가시키면 네트워크 부하가 줄어들 뿐만 아니라 서버 프로세스가 읽어야 할 블록 개수까지 줄어드는 효과를 얻게 된다.

ArraySize와 Fetch Count 및 블록 I/O는 반비례 관계를 보인다.
(ArraySize가 커질수록 Fetch Count 및 블록 I/O가 작아진다.)

String sql = "select custid, name from customer";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setFetchSize(100);
ResultSet rs = stmt.executeQuery();
// rs.setFetchSize(100); 여기서 조정해도 된다.

while(rs.next()){
	int empno = rs.getInt(1);
    String ename= rs.getString(2);
    System.out.println(empno+ ":" + ename);
}

rs.close();
stmt.close();

최초 rs.next() 호출 시 한꺼번에 100건 가져와서 클라이언트 Array 버퍼에 캐싱한다.
이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
버퍼에 캐싱되어 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.

분산 쿼리

분산쿼리의 성능을 높이는 핵심 원리는, 네트워크를 통한 데이터 전송량을 줄이는 데에 있다.
ex) 원격에 있는 테이블을 전송받아 NL 방식으로 조인 시, 원격 서버에서 쿼리를 처리한 후(where절, group by절 등으로 결과집합 생성) 전송받으면 네트워크를 통한 데이터 전송량을 줄일 수 있다.

옵티마이저 힌트 : driving_site(원격 테이블명)

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

일반 프로그래밍 언어와 달리 DBMS 내부에서 수행하는 사용자 정의 함수/프로시저(User Defined Function/Procedure)를 함수로써 모듈화하여 사용하는 것은 좋지 않다.

사용자 정의 함수/프로시저는 내장함수처럼 Native 코드로 완전 컴파일된 형태가 아니어서 가상머신같은 별도의 실행엔진을 통해 실행되며, 실행될 때마다 SQL 실행 엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.

또한, 메인 쿼리가 참조하는 사용자 정의 함수에 또 다른 쿼리문이 내장되어 있으면 내장된 쿼리가 수행될 때마다 Execute Call, Fetch Call이 재귀적으로 일어나기 때문이다. 수행 성능이 훨씬 나빠진다.

-> 사용자 정의 함수는 소량의 데이터를 조회할 때, 부분범위처리가 가능한 상황에서 제한적으로 사용해야 한다. 가급적이면 함수를 풀어 조인 또는 스칼라 서브쿼리 형태로 변환하려고 해야 한다.