Oracle용 insert batch 샘플

이번에 좀 황당한 일을 하게 되었는데...

약 2만개의 파일에, 해당 파일 내부 정보를 파싱해서 데이터 구조를 만들어 DB에 때려박는 일이었다.

각 파일의 45만개 정도의 값을 가지고 있고, 그 중에 유효한 값은 10만개 정도.

총 값의 갯수는 18억개... rows가 18억이 좀 넘는다.

흔히들 하는 JDBC 2.0 범용 batch로 작성했더니, 대충 일주일 (6.3일)정도의 예상 시간이 나왔다.

iBatis로 돌려도 비슷한 예상시간 도출.

어차피 이쪽 업무 특성상 DB가 바뀔 일은 거의 없기도 하고, 애초에 데이터 생성이 목적이지 그 기능을 범용으로 쓰는것도 아니기에,

어찌어찌 검색하다보니 Oracle-specific 라는게 걸렸고, '퍼포먼스 향상'이라는 말에 열심히 검색해봤다.

https://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm

http://fruitdev.tistory.com/111

http://hyeonil.blogspot.kr/2013/08/preparedstatement-database-batch-update.html

개발환경

JAVA : JDK 1.7.0_79

RDBMS : Oracle 11g (11.2.0.1.0)

IDE : eclipse Mars.2 (4.5.2)

기록성 블로그이므로 최하단의 소스 부분만 oracle 성능 향상 batch 코드라 앞의 것들은 무시해도 된다.

일단, 해당 파일을 리스트 형태로 불러온다. (파일, 디렉토리 구조는 /temp/a/1.txt, /temp/b/2.txt...)

getFileList(String realPath)

private static List<String> getFileList(String realPath) throws IOException {
    List<String> fileList = new ArrayList<String>();
        
    File path = new File(realPath);
    File[] fileArr = path.listFiles();
        
    for(File file : fileArr){
        if(file.isDirectory()){
            for(String str : file.list()){
                fileList.add(file.getCanonicalPath() + File.separator + str);
            }
        }
    }
 
    return fileList;
}

만일 하나의 디렉토리 내에 모든 파일이 있다고 하면, if와 그 안의 for문은 빼도록 한다.

그 다음 파일 내용을 읽어서 데이터 생성

private static HashMap<String, String> setFileToData(String realPath, String fullPath) throws Exception {
    File file = new File(fullPath);
    BufferedReader br = null;
 
    String fileName = file.getName();
 
    int headerCnt = 0;
    int totalCnt = 0;
    double retTime = 0;
 
    try {
        String[] splitStr = null;
        String[] cdArr = selectFileToCode(realPath, fullPath);    // 파일명을 통한 코드 생성
 
        br = new BufferedReader(new FileReader(file));
 
        String line = null;
 
        // 헤더 정보
        int x= 0, y= 0;
        BigDecimal xc = null, yc = null, cSize = null;
        String noVal = "";
            
        while (null != (line = br.readLine())) {
            splitStr = line.split(" ");
            if (10 > headerCnt) {    // 동일한 txt 형태로, header 부분이 10라인
                // 헤더 파싱하여 값 설정
                headerCnt++;
            } else {
                // 본문 내용 파싱하여 DB에 INSERT할 데이터 생성
            }
        }
        retTime = insertBatchFileToDB(list);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            br.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
    HashMap<String, String> retMap = new HashMap<String, String>();    // 필요값 리턴
 
    retMap.put("retFileName", fileName);
    retMap.put("retTime", String.valueOf(retTime));
    retMap.put("retSize", String.valueOf(list.size()));
    
    return retMap;
}

selectFileToCode(...) 를 통해 해당 파일명을 기준으로 코드값을 생성하고,

while 구문 내에서 각 라인 단위로 로직을 처리했다.

그리고 insertBatchFileToDB(...) 메소드에 파싱된 데이터를 List로 구성하여 넘긴다.

이제부터가 Oracle 전용 퍼포먼스 향상된 Batch.

일단 Connection을 생성 메소드를 하나 만든다. 그냥 내부에서 해줘도 상관은 없다.

private static Connection getOrclConn() throws Exception{
    Class.forName("oracle.jdbc.OracleDriver");
    return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "test", "test");
}

batch 실행.

private static double insertBatchFileToDB(List<Object> list) throws Exception{
    long start = System.currentTimeMillis();    // 작동 시간 측정용
 
    // ojdbc6.jar 사용. import는 oracle.jdbc.*  
    OracleConnection conn = null;        // 통상 Connection으로 선언
    OraclePreparedStatement ps = null;    // 통상 PreparedStatement로 선언
 
    StringBuffer sql = new StringBuffer("INSERT INTO TB_TEST (")
                    .append("COL1")
                    .append(", COL2")
                    .append(", COL3")
                    .append(", COL4")
                    .append(", COL5")
                    .append(", COL6")
                    .append(", COL7")
                    .append(", COL8")
                    .append(") VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
 
    try{
        conn = (OracleConnection) getOrclConn();    // Connection 생성 시 OracleConnection으로 형변환
        conn.setAutoCommit(false);                    // 자동 commit 끔
 
        // prepareStatement를 OraclePreparedStatement으로 형변환
        ps = (OraclePreparedStatement) conn.prepareStatement(sql.toString());
 
        int cnt = 0;    // row Count
 
        for(Object obj : list){
            ps.setString(1, obj.getCol1());
            ps.setString(2, obj.getCol2());
            // 생략 ...
            ps.setBigDecimal(8, obj.getCol8());
 
            ps.addBatch();    // OraclePreparedStatement에 batch로 완성된 SQL 추가
            ps.clearParameters();    // OraclePreparedStatement에 지정된 Parameter값 초기화
            
            cnt++;
            if((cnt % 10000) == 0){    // batch에 누적된 건수가 1만건
                cnt = 0;
                ps.executeBatch();    // 누적된 batch 실행
                ps.clearBatch();    // 누적된 batch 초기화
                conn.commit();        // Commit하여 적용
            }
        }
 
        // 최종적으로 누적된 채 남은 batch 작업(위의 if문) 실행
        ps.executeBatch();
        ps.clearBatch();
        conn.commit();
 
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        ps.close();
        conn.close();
    }
 
    long end = System.currentTimeMillis();    // 작동시간 측정용
 
    return (end - start) / 1000.0;    // insertBatchFileToDB 실행에 걸린 시간 
}

위 코드 중 cnt 대신 batch에 등록된 건수를 기준으로 executeBatch()를 하는 방법도 있다.

일단 데이터 생성이 급해서 하긴 했는데, 몇가지 의문이 있다.

1. clearParameters()를 해야 하는가. 어차피 for문 돌면서 덮어씌울텐데. 메모리 쪽에 부하가 가나?

2. for문 밖, 맨 밑부분의 clearBatch()는 필요한가. 어차피 close() 되는데.

3. 대체 왜 이렇게 압도적인 속도가 나오는가.

뭐, 그건 나중에 찾아보고... 다음 실행할 main이다.

public static void main(String[] args) throws Exception {
    // String realPath = "C:\\temp\\";
    String realPath = args[0];
 
    List<String> fileList = getFileList(realPath);
 
    for(String fullPath : fileList){
        System.out.println(setFileToData(realPath, fullPath));
    }
}

경로를 파라미터로 하여 실행할거라면 위처럼 받으면 되고, 하드 코딩 하려면 그 위의 주석처럼.

fileList를 받아와서 그 중 필요한 부분을 인자로 던진다.

해서 돌려보니,

파일 하나당 값 갯수는 약 45만건, 그중 유효 데이터는 약 9만 5천건. 소요 시간은 평균 0.2~4초. 중간중간 2~4초로 튀는 경우가 있는데, 대충 20여건에 한두번씩 그런다. GC때문인가...

이전에 해 놓은 iBatis나 JDBC로는 24시간 기준으로 8-9천만건 전후였다.

아무튼, 그래서 최종적으로,

파일 : 약 1만 9천개

파일 파싱 데이터 : 약 45만개

유효 파싱 데이터 : 약 9만 5천개

총 INSERT ROWS : 약 18억 rows.

iBatis Batch : 약 6일 소요 예상

JDBC 2.0 Batch : 상동(사알짝 빠른 듯 싶다)

Oracle-specific model batch : 약 11시간

Oracle DB에 무식하게 데이터 때려박으려면 oracle-specific을 이용한 batch가 최선으로 보인다.