오라클 JDBC 튜닝

Part 2 APM │335
All about JDBC Performance Tuning
㈜엑셈 컨설팅본부/APM팀 임 대호
1 개요
JDBC 란 Java Database Connectivity 의 약어이며, 데이터베이스 표준 접근 API(Application
Programing Interface)를 말한다. JDBC 를 사용하면 어떤 관계 형 데이터베이스에서도, 각 데
이터베이스에 맞는 접근 프로그램을 따로 생성할 필요 없이 사용할 수 있다. 이 JDBC 는 WAS
(Web Application Server)와 DB Server (Database Server) 사이에서의 연결을 담당하는 중
요한 역할을 수행하고 있다. WAS 에서 SQL 을 제대로 보내고 DB 에서 수행결과를 빠르게 리턴
한다고 해도, JDBC 에 문제가 있다고 하면 성능문제를 야기할 수 있다. 앞으로 JDBC 로 인해 발
생할 수 있는 성능 문제가 무엇인지 살펴보고 좀 더 나은 Performance 를 내기 위한 방법이 어
떤 것이 있는지 알아 볼 것이다.
2 JDBC 구조
JDBC 는 크게 JDBC API, JDBC Manager, JDBC Driver API 로 구성되어 있다.
2-1 JDBC 역할
우선 간단히 JDBC의 역할에 대해서 알아보자.
[그림 1] JDBC 역할
JDBC Interface JDBC Driver
Database
Application 336│2013 기술백서 White Paper
Application에서 Parameter 값을 받아 JDBC Interface로 보내게 되면 적절한 JDBC Driver를 통해 SQL을
Database로 보내주게 되고 결과를 받아서 사용자에게 보여주게 된다.
2-2 JDBC Interface
JDBC 의 핵심 Interface 는 Connection, Statement, ResultSet 인데 간단히 살펴보도록 하자.
Connection Interface
데이터베이스에 접근하기 위한 객체를 생성한다.
Statement Interface
SQL Query를 실행하고 그것에 대한 결과 값을 가져온다.
ResultSet Interface
테이블에 접근해서 값을 추출하는 기능을 한다.
[그림 2] JDBC 핵심 Interface
JDBC Interface 를 사용하려면 java.sql, javax.sql Package 를 사용해야 하는데 java.sql
Package 은 DBMS 에 저장된 정보를 다루기 위해 사용되고, javax.sql Package 는
Datasource 로 접근하기 위해 사용된다. 핵심 인터페이스인 Connection, Statement,
ResultSet 을 사용하려면 java.sql Package 를 Import 해주면 된다.
2-3 JDBC Interface의 사용
핵심 Interface 가 어떤 일련의 과정으로 사용되는지 살펴보도록 하자. Driver Manager 를 사용
할 때를 예로 들겠다. 일단 JDBC Driver Manager 를 통해서 적합한 Driver 를 로딩하게 된
다.(JDBC Driver 는 JDBC Call 을 DBMS 에 전달하고 Result 를 받아서 JDBC Interface 에 넘
겨주는 역할을 한다) Driver 가 로딩되면 Connection 객체를 생성한다. 그런 다음 획득한
Connection 객체로부터 Statement 객체를 획득하고 SQL 을 수행한다. Select 의 경우는
Resultset 이 있으므로 결과를 가져오기 위해 ResultSet 객체를 생성한다.
[code 1] Part 2 APM │337
1 import java.io.*;
2 import java.sql.*;
3 public class DBConnection {
4 public static void main(String [] args) throws SQLException, ClassNotFoundException{
5 Class.forName("oracle.jdbc.driver.OracleDriver");
6 Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@192.168.123.52:1521:intermax","dh","dh");
7 Statement stmt = con.createStatement();
8 ResultSet rs = stmt.executeQuery("select * from emp");
9 while(rs.next()){
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
}
10 rs.close();
11 stmt.close();
12 con.close();
}
}
앞으로 사용될 Driver 는 Oracle 을 기준으로 하겠다.
위의 코드를 간단하게 살펴보자 5 번째 줄을 보면 Oracle 의 경우 Driver 클래스 명이
oracle.jdbc.driver.OracleDriver 인 것을 알 수 있다. 6 번째 줄은 Driver 가 로딩이 되면
Driver Manager 클래스의 getConnection(url,user,password) Method 를 이용해서
Connection 객체를 생성하는 것을 알 수 있다. Oracle 의 경우 Url 은
jdbc:oracle:thin:@ip:port:SID 형식이다.
8 번째 줄을 보면 Select 를 사용했으므로 ResultSet Interface 가 사용되었다. 10~12 번째 줄
을 보면 close() 처리를 해줬다. 이는 모든 java.sql 관련된 Resource 는 사용하면 반드시 반납
을 해줘야 하기 때문이다. 338│2013 기술백서 White Paper
만약 반납하지 않는다면 모든 Connection 을 다 사용하게 되어서 Thread pool 이 밀리는 현상
이 발생하고 최악의 경우는 WAS Crash 현상이 나타날 수 있다. 지금까지 간단히 JDBC 의 구조
를 알아보았다. 이제 JDBC 로 인해 발생할 수 있는 성능문제에 대해서 살펴보도록 하자.
3 JDBC 성능문제
JDBC 로 인해 다양한 성능문제가 발생할 수 있다 어떤 성능문제 발생하는지 살펴보도록 하자.
3-1 JDBC Driver Type
JDBC Driver는 4가지의 종류가 있다.
Type1 JDBC-ODBC Bridge Driver
Type2 Native-API Driver
Type3 Net-Protocol Driver
Type4 Native-Protocol Driver
[그림 3] JDBC Driver Type
간단히 각 Driver 에 대해서 살펴보자. Type1 인 JDBC-ODBC Bridge Driver 는 JDBC 를 사용
해서, ODBC 데이터소스에 접근하는 방식으로 제한이 많고 비효율적이다. Type2 인 Native
API Driver 는 Java + JNI 방식으로 Native Call 하는 부분이 추가된다. Type3 인 Net-Protocol
Driver 는 Net protocol 을 사용하여 Middleware 계층 서버와 통신하는 방식이다. Type4 인
Native-Protocol Driver 는 Pure java 방식이고 TCP/IP 로 Socket 을 이용하는 방식이다
Type1 방식은 플랫폼에 종속적이고 JDBC 의 요청을 ODBC 로, ODBC 에서의 응답을 JDBC 로
변경해주는 과정 때문에 불필요한 Overhead 가 발생하게 된다. Type2 방식은 Native Code 로
되어 있는 Database Library 를 설치해줘야 한다. Type3 방식은 Application 과 DBMS 사이에
Middleware 를 구현하고 JDBC Driver 는 Middleware 에 요청하는 방식인데 Middle Tier 가 추
가되기 때문에 Bottleneck 현상의 원인이 될 수 있다. Type4 방식은 Socket 통신을 하기 때문
에 Socket 통신에 소비되는 시간을 고려해야만 한다. 이와 같이 4 가지의 Driver Type 이 있다.Part 2 APM │339
3-2 DriverManager
Connection 객체를 생성하고 제거하는 작업은 상당히 고비용의 작업이다.
달리 말하자면 DriverManager 의 getConnection() Method 를 이용해서 Connection 객체를
얻어내는 것이 고비용의 작업이란 이야기이다.
Connection 이 비록 Idle 한 상태에 있는다 하더라도 생성 및 제거 작업을 하는 것 보다 차라리
Open 해 놓은 상태로 있는 것이 훨씬 유리하다. Mark Chamness 의 Performance Tuning For
the JDBC API 문서에 따르면 Connection 을 생성하고 제거하는 작업이 평균 0.5 초에서 2 초
사이가 걸린다고 한다 만약 반복적인 Open 과 Close 가 발생한다면 Application 성능 저하가 나
타날 것이다.
Connection 관리를 효율적으로 하기 위해서는 Connection Pool 을 사용하면 된다..
3-3 Statement
Statement 객체는 주로 간단한 SQL 을 전송할 때 쓰인다. 하지만 Statement 객체는 다음과
같은 데이터처리의 한계를 가진다.
1. 반복되는 똑 같은 질의도 새로운 질의로 해석된다.
2. 질의 조건이 달라질 때 마다 하드코딩 해야 한다.
이와 같이 Statement가 갖는 한계점은 Prepared Statement를 사용하면 해결할 수 있다.
3-4 Transaction Isolation Level
Transaction Isolation Level 은 다음과 같이 4 가지로 나뉘게 된다.
ISOLATION LEVEL DESCRIPTION
TRANSACTION_READ_UNCO
MMITE
트랜잭션에서 처리중인, 아직 commit 되지 않은 데이터를 다른
트랜잭션이 읽는 것을 허용한다
TRANSACTION_READ_COMM 트랜잭션이 commit 되어 확정된 데이터만 읽는 것을 허용한다340│2013 기술백서 White Paper
IT
TRANSACTION_REPEATABL
E_READ
선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행
트랜잭션이 갱신하거나 삭제하는 것을 불허한다
TRANSACTION_SERIALIZABL
E
선행 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신하거나
삭제하지 못할 뿐만 아니라 중간에 새로운 레코드를
삽입하는 것도 막아준다
[그림 4] Transaction Isolation Level
트랜잭션 고립화의 수준을 높일수록 데이터의 일관성은 향상되지만, 확장된 Lock 을 사용하게
되므로 동시성이 저하가 된다. (단 Oracle 은 Lock 이 아닌 Undo 메카니즘을 사용한다)
3-5 SQL
SQL 은 WAS 와 DB 성능에 직결된다 하드웨어의 Spec 을 아무리 뛰어나게 구성한다고 해도 업
무성격을 잘못 이해하고 SQL 을 작성하거나 (Outer Join) 비효율적으로 작성하게(Subquery
남용) 되면 성능 저하 및 장애를 유발할 수 있다. 또한 과도한 Fetch Count 는 Out Of Memory
Error 를 유발하고 WAS Crash 를 유발하기도 한다.
4 JDBC 성능문제 해결
이전에 살펴보았듯이 JDBC 성능문제는 여러 원인과 연관되어 있다. 이제 JDBC 성능문제를 해
결하는 방법에 대해서 알아보도록 하자.
[그림 5] Connection Pool
Connection Pool
Application
DAO
saddsda
sds
ad
객
체
사 객용
체
반
납
C c
c cPart 2 APM │341
4-1 Connection Pool 사용
Connection Pool 을 사용하게 되면 Connection 객체를 생성하고 제거하는 작업을 하지 않아도
되기 때문에 Overhead 가 적고 응답속도가 빠르게 된다. 또한 Connection 의 수의 제한을 두기
때문에 연결관리에 효율적이다. 그리고 JNDI Look Up 을 하여 접근할 수 있으므로 특정한 드라
이버나 속성에 구애 받지 않고 참조가 가능하다.
Connection Pool 을 사용할 때 주의해야 할 사항이 있는데 그것은 적절한 Pool Size 에 대해 고
려해야만 한다는 것이다. 이는 Pool Size 와 Performance 가 Trade Off 의 관계에 있기 때문이
다. 적절한 Pool Size 설정을 위해서는 실제 Connection 사용량 및 OS Resource 사용량을 체
크하고 부하 Test 를 거쳐야 한다.
Connection Pool 의 동작 방식은 다음과 같다. 만약 WAS 에서 제공하는 Connection Pool 을
사용하게 된다면 Minimum 과 Maximum Size 를 설정할 수 있다. 최초 Pool 을 생성시
Connection 을 Minimum Size 만큼 생성하게 되고 추가로 요청이 발생하면 Maximum Size 까
지 생성한다. 그리고 사용이 없게 되면 다시 Minimum Size 까지 작아지게 된다. 만약
Connection 생성 Delay 로 인한 이슈가 있다면 Minimum 과 Maximum Size 를 같게 설정하도
록 한다.
주의 해야 할 점은 Connection 객체를 close()해주지 않으면 다른 사용자가 사용할 수 없으므로
반드시 close() 처리를 해주도록 한다.
4-2 Transaction Isolation Level 사용
동시성 제어가 어려운 이유는 동시성과 일관성이 Trade Off 의 관계에 있기 때문이다.
Transaction Isolation Level 에 따라 다음의 문제점이 발생할 수 있다.342│2013 기술백서 White Paper
LEVEL PROBLEM
READ_UNCOMMIT Dirty Read, Non-Repeatable, Phantom Read
READ_COMMIT Non-Repeatable, Phantom Read
REPEATABLE_READ Phantom Read
SERIALIZABLE
[그림 6] Transaction Isolation Level에 따른 문제점
업무 성격에 비해 Transaction Isolation Level 을 높게 사용할 경우 불필요하게 Transaction 의
동시성을 제한하게 되고 반대로 낮게 사용하게 되면 데이터의 무결성이 보장되지 않을 수 있다.
그러므로 업무성격에 맞는 Transaction Isolation Level 을 설정하는 것이 중요하다.
4-3 Prepared Statement, Callable Statement 사용
SQL 문장을 반복적으로 DB 에 전송할 필요가 있다면 Prepared Statement 객체를 사용하도록
한다. Prepared Statement 장점은
두 가지가 있다 첫째는 반복적으로 실행해야 하는 SQL 을 미리 컴파일 해서 저장해 놓음으로 실
행속도를 빠르게 할 수 있다. 둘째는 Parameter 로 값을 넘길 수 있기 때문에 Hard Coding 할
필요가 없고 쉽게 인자를 전달하여 수행속도를 향상 시킬 수 있다.
Callable Statement 는 DB 에서 미리 컴파일 된 Stored Procedure 을 실행하기 위해 사용한다.
앞서 말한 Prepared Statement 와 Callable Statement 를 사용하게 되면 Statement
Caching 기능을 사용할 수 있다.
4-4 Statement Caching 사용
Prepared Statement 또는 Callable Statement 를 Application 이나 EJB 에서 사용하게 되면
WAS 와 DB 에 상당한 오버헤드가 발생할 수 있다. JDBC 3.0 이상에서는 Statement Caching
기능을 사용할 수 있는데, Statement Caching 이란 Prepared Statement 와 Callable Part 2 APM │343
Statement 를 Caching 하여 재사용할 수 있는 기능이다. 이는 DB 의 CPU 사용량을 줄일 수 있
을 뿐 아니라 성능 향상을 도모할 수 있다. 자료를 찾다가 Weblogic 의 Statement Caching 에
관한 자료가 있어서 옮겨 적어 본다.
Weblogic 은 Statement Cache Algorithm 이 두 가지가 존재하는데 LRU 방식과 Fixed 방식이
있다.
LRU 방식
-캐시가 가득 찰 때까지 Prepared Statement 와 Callable Statement 를 Caching 한다. 모든
캐시가 가득 차게 되면 가장 적게 사용된 Statement 를 밀어내고 새로운 Statement 를 저장한
다.
Fixed 방식
-Statement 를 캐시하고 사이즈가 가득 차게 되면 더 이상 Statement 를 Caching 하지 않는다.
Statement Cache 기능을 사용하기 전에 각 Connection 마다의 Cache Size 를 고려해야만 한
다. 그 이유는 대부분의 경우에 있어서 Open Statement 하나당 하나의 커서를 유지하기 때문
이다. 만약 많은 Statement 를 Caching 한다면 DB 의 Open Cursor 의 제한을 초과할 수 있다.
예를 들어 Statement Cache Size 가 10(default)이고 Connection 이 10 개 그리고 2 개의
WAS 가 Clustering 된 구조라면 DB 에서는 캐시를 위해 200 개(10*10*2)의 Cursor 가 Open
되게 된다.
Statement Cache Size 를 0 으로 설정하게 되면 해당 기능은 꺼지게 된다. Caching 된
Statement 는 DDL 이 수행되고 나면 재 수행에 실패하게 되는데, 이는 Caching 된 Statement
는 명시한 데이터베이스 오브젝트를 참조하기 때문이다. 이는 Oracle 에서는 Breakable Parse
Lock 과 관계가 있다. Cursor 가 참조하는 오브젝트에 DDL 이 수행되면 실행가능 LCO 가 무효
화 된다.344│2013 기술백서 White Paper
4-5 JDBC Driver의 적절한 사용
JDBC 드라이버 Type 에 대한 적적한 이해가 필요하고 때에 따라서는 Test 도 필요하게 될 것이
다. 그 이유는 WAS 와 DB 의 구성 환경에 따라 Type 의 선택이 달라질 수 있기 때문이다. 예를
들어
WAS 와 DB 가 같은 Machine 에 있는 경우를 생각해보자. Type4 의 경우는 Socket 을 이용한
다고 했는데 불필요한 TCP/IP Socket 통신이 발생하게 된다. 만약 이런 시간으로 인해 데이터
베이스에 직접 접근하는 시간이 오래 걸린다고 하면 Native Call 을 하는 Type2 보다 유리할게
없다 환경(구성, JVM)에 따라서는 Type2 가 Type4 보다 성능 면에서 유리할 수 있다.
최적의 성능을 위해서 DB Server 와 DB Client JDBC Driver 는 동일/최신 버전을 사용하도록
한다.
4-6 SQL Tuning의 최적화
SQL 을 어떻게 작성하느냐 또는 어떻게 처리하느냐에 따라서 성능은 극과 극의 상황을 보여줄
수 있다. 이처럼 SQL 작성은 WAS 와 DB 성능에 직결된다고 볼 수 있는데, 개발자 입장에서 어
떤 SQL Tuning 방법이 있는지 살펴보도록 하자.
1. 한번에 한 행을 처리하지 않고 다수 행을 처리하는 addBatch(), executeBatch()를 사용
하도록 한다.
2. 읽기 전용 혹은 Data Update 가 일어나지 않는 질의는 caching 을 고려한다.
3. Statement 보다는 Prepared Statement 를 사용해라
4. 대량의 Data 조회 시 Paging 처리를 하도록 한다.
5. 적절한 예외처리를 이용해 반드시 Connection leak 을 막아 주도록 한다.
6. 복잡한 SQL 질의 문이나 여러 개의 DB 호출을 하나로 통합하는 Stored 프로시저를
사용하라.
7. 불필요한 Meta Data 호출은 피한다.
8. Application 요구에 맞는 가장 낮은 수준의 Transaction 을 사용한다.
9. Transaction 과부하가 우려된다면 Auto commit 모드를 사용하지 않도록 한다.
10. SELECT * FROM TABLE 과 같은 조회는 피하고 필요한 칼럼만 기술하도록 한다. Part 2 APM │345
11. Result Set 을 Network 로 넘어오기 전에 줄여서 Round Trip 을 적게 발생시키도록 한
다.
4-7 Oracle JDBC Performance Extension
Oracle JDBC Driver 는 Round Trip 을 줄여 성능을 향상시키는 다음의 기능을 지원한다
Row Prefetch
- 접근 가능성이 높은 블록을 Fetch 할 때 Client Buffer 에 저장하여 불필요한 Round Trip 을 줄
인다
Update Batching
-Update, Delete, Insert 문을 하나의 batch 로 수행한다
-Update Batching 은 Prepared Statement 와 함께 사용시 유용하다
- Oracle JDBC 의 Update Batching 방식에는 Standard Model 과
Oracle-Specific Model 이 있지만 두 모델을 함께 사용할 수는 없다
5 결론
Framework 가 개발의 대부분을 차지하고 있는 이시대 JDBC 의 성능에 대한 고찰은 시대에 뒤
떨어져 보일 수 있다. 하지만 WAS 에서 수행한 Application 이 어떻게 DB 까지 전달되어서 결
과를 사용자에게 보여주는지에 대한 궁금증은 어느 정도 해소가 되었으리라 본다. 그 역할의 중
심에는 JDBC 가 있다. 우리는 지금까지 JDBC 가 어떤 일을 수행하고 어떤 구조로 되어 있는지
또 어떤 문제를 일으키는지에 대해 알아보았다. 어플리케이션 성능 관리가 주 업무인 나에게
JDBC 의 동작원리를 이해하는 것은 WAS DB 성능 전문가가 되기 위한 초석이 될 것이라고 생
각한다.
JDBC 를 사용할 때 가장 큰 성능 개선을 할 수 있는 방법은 WAS 와 DB 사이에서의 Round Trip
을 적게 발생시키는 것이다. Oracle Driver 는 이미 Round Trip 을 줄일 수 있는 Row Prefetch 346│2013 기술백서 White Paper
나 Update Batching 기능을 제공하고 있다. 하지만 이런 좋은 기능이 있음에도 불구하고 SQL
의 수행의 결과가 불필요하게 많은 ResultSet 을 가져온다면 심각한 성능 문제를 야기할 수 있
다. 대표적으로 과도한 Fetch Count 로 인한 Out Of Memory Error 현상을 말할 수 있다. 이를
해결하기 위해서는 업무 성격을 잘 파악하여 Network 를 통해 넘어 오기 전에 ResultSet 을 줄
여 Round Trip 을 적게 발생시켜야 한다..
앞서 우리는 JDBC 의 핵심 Interface 에 대해서 살펴보았다. 그렇다면 SQL 의 Parse 와
Execute 와 Fetch 는 어느 객체에서 수행이 될까? 답은 Java Performance Fundamental 에 나
오는데 Statement 객체의 executeQuery() Method 단계에서 Parse, Execute, 1 회의 Fetch
가 수행된다고 한다. 그리고 ResultSet 객체의 next() Method 에서 나머지 Fetch 가 수행된다.
Cursor 가 Open 되는 시점은 Parse 하는 시점과 동일하고 Close 되는 시점은 Connection 객체
가 Close 될 때이다. 잘 이해가 되지 않는 다면 [code1]을 참조하라
지금까지 JDBC 에 대해서 정리를 해보았다. APM(Application Performance Management)을
지원하지만 큰 관점에서 보면 Database 역시 Application 의 한 부분이고 대부분의 성능 이슈
는 WAS 와 DB 상태를 같이 봐야 명확히 장애 원인이 밝혀지는 경우가 많다. Oracle 에 대한 내
용을 추가한 것도 그런 맥락에서이다.
기초를 단단히 잡고 싶은 마음에 JDBC 를 주제로 잡았다. 뭐든지 가장 중요한 것은 기반을 튼튼
히 하는 것이라고 생각을 한다. JDBC 지식을 기반으로 다음 백서의 주제는 Framework 가 될
것 같다
참고 문헌
서적
김한도, [Java Performance Fundamental]
Soluvill, [Enterprise Java JDBC]
문서 자료
Mark Chamness, [Performance Tuning For the JDBC API]Part 2 APM │347
웹사이트
http://alumnus.caltech.edu/~chamness/JDBC_Tuning.pdf
http://etutorials.org/Programming/Java+performance+tuning/Chapter+16.+Tuning+J
DBC/16.2+Tuning+JDBC/
http://docs.oracle.com/cd/E11035_01/wls100/jdbc/performance.html.