아마도 프로그래머로서 정규 데이타베이스 교육을 받았다면 데이타베이스 커서는 아마 피해야 할 무언가이다. 기본적으로 관계형 데이타베이스는 결과 셋(Set) 지향이기 때문에 레코드 지향의 커서는 가능한 사용하지 말라고 추천되어 지기 때문이다. 그래서 마치 해리포터의 볼트모트처럼 이름을 부르지 않는다면 괜찮을 것이다라는 미신에 휩싸여 프로그래머는 커서에 대해 알려고 하지 않는다. 하지만 사실 프로그래밍의 대부분은 레코드 기반이며 데이타베이스의 셋 지향 방식과 프로그래밍의 레코드 지향 방식의 부조화를 이따금 임피던스 불일치(impedance mismatch)라고 부른다. 이 불일치의 다리 역할을 하는 것이 커서(cursor)이므로 우리는 볼트모트의 이름을 부르는걸 두려워 해서는 안된다.
DB에 대해 강의 같은걸 할때 종종 처음 묻는 질문중의 하나는 아래와 같습니다.
"세계적인 다국적 기업 '다팔아회사'의 오라클의 customer_tblc 테이블에는 지금까지 등록된 1억명의 고객정보가 등록되어 있습니다. 자 select * from customer_tblc 라는 쿼리를 Toad나 Orange에서 실행하면 몇초만에 화면에 결과 정보가 보일까요?".
실제로 직접 해보면 바로 알수 있는 간단한 질문이지만 경험상 그자리에서 답을 맞추는 사람은 단순히 확률을 벗어나지 못하는 반 정도에 불과합니다. 간단합니다. 실행 버튼을 클릭하자마자 화면에 실행결과가 출력됩니다.
이 글은 커서에 대한 글이지만 관련하여 꼭 먼저 알아야 할 것(물론 그 밖에도 알아두어야 할것은 많습니다.:-)이 부분범위 처리이기 때문에 먼저 간단히 소개 하겠습니다. 부분 범위 처리란 실제 쿼리가 처리해야 할 범위와는 상관없이 일부만 억세스하여도 결과 조건을 만족할 수 있다면 일정 단위(Array Size 혹은 Fetch Size)가 되면 결과를 추출할 수 있다는 것을 말합니다. 원래 SQL이란 Java 등의 프로그래밍 언어처럼 처리과정을 기술하는 언어가 아니라 원하는 집합을 표현하는 것이므로 집합 모두에 대한 책임을 가지지만, 온라인 등에서는 집합의 일부분만 먼저 처리하여 제공하고 나머지는 추가적으로 원할 때 제공해도 된다면 사용자의 요구가 있을때까지 잠정적으로 수행을 멈추는 처리방식을 말합니다.
가령 쿼리의 결과로 100만건이 나오는데 그 결과의 집합을 눈으로 확인하고자 하는 것이라면 100만건을 모두 한꺼번에 화면에 보여주기보단 일단 일부분만 제공하고 나머지는 추가적으로 원할때 제공하더라도 문제될 것이 없습니다. 이러한 처리 방식은 주어진 조건을 만족하는 처리범위가 아무리 넓다고 하더라도 실제로 처리할 데이터는 아주 소량이 되므로 조건범위와 무관하게 처리량을 크게 줄일 수 있는 큰 장점이 있습니다.
예컨대 위의 고객 테이블을 대상으로(englishName 컬럼에는 인덱스가 없다고 가정합니다.)
-
select * from customer_tblc where englishName > 'A'
-
select * from customer_tblc where englishName > 'zzz'
이 두개의 쿼리를 Toad로 실행했을때 1번 쿼리는 실행하자 마자 결과가 출력되지만 2번 쿼리는 답이 나올때까지 아마도 커피를 몇잔 들이키거나 상황에 따라서는 식사를 하고와도 여전히 모래시계를 볼수 있을 수도 있습니다. 결과의 전체 양과 상관없이 화면에 출력하고자 했던것이 단지 20건 정도였다면 1번 쿼리의 조건을 만족하는 row는 아주 많기 때문에 customer_tblc의 일부분만 읽어도 화면에 출력할 20건 정도를 보이는데 충분할 것입니다. 그에 비해 2번 쿼리는 cust_tblc 테이블의 전부를 읽어도 englishName이 zzz보다 큰 이름을 발견할 확률은 아주 작습니다. (어쩌면 한건도 없을지도 모릅니다.) 그렇기 때문에 한건도 없다는 결과를 보여주기 위해 1억건을 모두 읽지 않으면 안됩니다. (만약 englishName 컬럼에 인덱스가 있었다면 결과는 어떻게 됐을까요? 굳이 여기에 적지 않아도 아마 충분히 알 수 있으리라 생각합니다.)
부분범위 처리는 조건을 만족하는 모든 데이타를 보여주는게 아니라 결과중 일부만 추출하므로 사용자가 조건을 만족하는 데이타가 아주 많더라도 아주 빠른 수행속도를 낼 수 있습니다.
토막토막 -----------
만약에 위의 예제 상황과 동일한 상황에서
select * from customer_tblc where englishName > 'a' order by englishName
이라는 쿼리를 실행한다면 어떻게 될까요? 위의 경우는 아마도 커피는 커녕 내일 아침에 출근할때까지도 답을 나오지 않을 것입니다. 아마도 그전에 DB는 먹통이 되버리겠지만요. 무엇이 다를까요? where 조건을 만족하는 집합은 아주 많은데 왜 이경우는 부분범위가 되지 않을까요? 물론 조건을 만족하는 집합은 아주 많지만 본문에 예시한 경우와 달리 일부분만 읽고 결과 데이터를 화면에 보여줄수 없습니다. 왜냐하면 사용자는 englishName로 정렬된 데이타를 원했기 때문입니다. where 조건을 만족하는 데이타 adam을 먼저 찾았더라도 adam보다 정렬순위가 더 낮은 이름 20개가 더 있을지 모르니까요. 결국 1억건의 데이타 모두를 확인하지 않고서는 가장 낮은 정렬순위를 가지는 데이타를 추출할 수 없기때문에 위 경우는 전체범위 처리가 됩니다. 그러나 만약에 englishName 컬럼에 인덱스가 설정되어 있다고 가정한다면 이 경우는 부분범위 처리가 가능해집니다. 인덱스는 정렬되어 있기 때문에 인덱스의 앞부분만 읽고도 가장 정렬순위가 낮은 데이타 20개를 얻을 수 있기 때문이죠.
그렇다면 select * from where englishName > 'a' order by phoneNumber 라는 쿼리는 부분범위 처리가 될까요? 안될까요? 만약 가능하다면 인덱스가 어떻게 설정되어 있어야 할까요? 여러 테이블의 join이나 subquery를 사용할때는 어떻게 될까요? 사실 이 문제는 아주 중요한 것이지만 이 모든걸 말하기에는 지면이 충분치 않으므로 커서 얘기에 집중하기 위해 이 글에서는 아주 간단한 예만 들었습니다. 좀더 자세한 원한다면 참고 자료의 '대용량 데이타베이스 솔류선'을 참고하길 바랍니다.
--------------
어쩌면 어떤분은 부분범위 처리는 아주 상식적인 문제라고 생각했을지 모르지만. common is not common라는 속담대로 보편적으로 알려진 사실은 아닙니다. 그리고 결과 집합 추출시 잠정적으로 처리를 멈출수 있는 것은 DBMS가 수행속도의 향상을 위해 특별히 제공하고 있는 기능이므로 DBMS나 개발툴 혹은 드라이버 종류 그리고 버전에 따라서도 달라질 수 있습니다. (이를테면 MSSQL은 부분범위 처리가 가능한 DBMS이지만 MSSQL의 SQL 쿼리 분석기는 전체범위 처리를 하므로 모든 결과를 읽을때까지 화면에 보여주지 않습니다. 그에 반해 SQL Enterprize Manager는 부분범위 처리를 하는 Tool입니다.)
이제 본격적으로 커서에 대해 이야기 하기전에 테스트를 위해 간단한 예제 코드를 보겠습니다. (MSSQLPoolDBManager는 커넥션 풀링을 해주는 만든 클래스이지만 이 글은 커넥션 풀링과 하등의 관계가 없으므로 그냥 JDBC URL을 인자로 받는 DB 커넥션을 담당하는 클래스라고 생각하시면 됩니다.)
------- 예제 소스
private DBManager dbm = new MSSQLPoolDBManager("com.microsoft.jdbc.sqlserver.SQLServerDriver", "jdbc:microsoft:sqlserver://bleudb:1433;DatabaseName=test", "bleu", "bleu") ;
....... (전반부 생략)
public void testStatic() throws Exception {
Connection conn = null;
ResultSet rs = null;
PreparedStatement pstmt = null ;
try {
conn = dbm.getConnection(); //.............................. 1번
pstmt = conn.prepareStatement(getProcSQL());
rs = pstmt.executeQuery(); //.............................. 2번
populate(rs) ; //............................... 3번
} catch (SQLException ex) {
throw ex ;
}
finally {
closeWithoutException(rs) ;
closeWithoutException(pstmt) ;
dbm.freeConnection(conn); // ............................ 4번
}
}
private String getProcSQL(){
return "select custNo, fixLength from customer_tblc";
}
private void populate(ResultSet rows) throws SQLException {
int i = 0 ;
while(rows.next() && i++ < 10){
System.out.print(rows.getString(1));
}
System.out.println() ;
}
..... (후반부 생략)
------ 예제 소스
테스트 조건은 MSSQL 2000에 Java1.5, MSSQL에서 제공하는 MSSQL2000용 JDBC 드라이버이며 DB서버와 테스트 서버는 별도의 컴퓨터로 분리된 환경입니다. 테스트에 사용된 customer_tblc은 약 59만건의 row를 가지고 있고 평균 row 사이즈는 150 byte 정도입니다. 사실 요즘의 DB 환경에서 59만건은 아주 작은 데이타지만 의미있는 결과를 확인하기에는 충분한 숫자입니다. 그리고 저는 잘못된 코드 예를 들기위해 제 컴퓨터가 멈춰버리기를 바라진 않습니다. :) 그리고 customer_tblc에는 PK로 cluster unique index가 설정되어 있습니다.(cluster 인덱스가 무언인가는 중요하지만 여기서는 설명하지 않겠습니다. 그게 먼지 모르는 분은 그냥 unique index 라고 생각해도 아래의 글을 읽는데 크게 무리는 없습니다.)
토막 토막 --------------------
위 예제에 사용된 customer_tblc 테이블 디자인은 아래와 같습니다.
CREATE TABLE [dbo].[customer_tblc] (
[custNo] [int] NOT NULL , -- 일련번호
[freeLength] [varchar] (100) NOT NULL , -- 30-60 byte의 random 단어를입력함
[fixLength] [char] (100) NULL -- 100byte의 고정길이를 가진다. (기본값 'abcde... XYZ')
)
GO
ALTER TABLE customer_tblc ADD CONSTRAINT customer_fixLength_dft DEFAULT ('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ') FOR fixLength
GO
ALTER TABLE dbo.customer_tblc ADD CONSTRAINT customer_pk PRIMARY KEY CLUSTERED (custNo) ON [PRIMARY]
GO
COMMIT
테스트에 사용된 테이블의 디자인은 위와 같은데 평균 row의 사이즈를 150byte로 하면서 테스트와 상관없는 많은 컬럼을 만들고 싶지 않았기 때문에 컬럼명이 다소 이상하더라도 양해 부탁합니다.
---------------------
아마도 위 소스는 인터넷을 뒤지면 쉽게 찾을 수 있거나 따라하기식의 책에서 보여주는 패턴과 크게 다르지 않기 때문에 코드를 이해하는데 크게 어려움은 없을것이라고 생각합니다. customer_tblc을 읽어서 처음 10건을 화면에 출력하는 코드입니다. 갑자기 다소 단도직입적일지 모르지만 위 코드는 무엇이 잘못되었을까요? (조금 도움이 되고자 힌트를 드린다면 쿼리 자체는 아무런 잘못이 없습니다.) 흔히 볼수 있는 기본적인 소스라 생각할지 모르겠지만 위 코드는 형편없는 코드입니다. 심각한 것은 여러 인터넷 사이트나 심지어는 JDBC를 설명하는 책에도, JDBC Driver를 다운로드 받으면 얻게 되는 help 파일에도 위와 같이 나와 있다는 것입니다. 물론 순수하게 문법 그 자체만으로 본다면 틀리지는 않았습니다. 그러나 다시 말씀드리지만 위 코드는 형편없는 코드입니다.
형편없다는 뜻에는 여러가지 의미가 있을 수 있지만 그리고 위 코드는 여러가지 면에서 형편없지만 이 글에서는 효율성 한가지에만 집중하도록 하겠습니다. 왜 위 코드가 형편없는지 이유에 대한 답을 먼저 하기 전에 먼저 DBMS는 select * from customer_cblc쿼리의 결과중 단지 일부만 사용하였을때 customer_tblc의 모든 Row를 읽을것인지 아니면 rows.next가 호출된 만큼만 읽고(위 코드에서는 10건) 나머지는 읽지 않을것인지 에 대해서 생각해 봐야 합니다. DBMS는 위와 같은 상황에서 모든 로우를 다 읽을까요? 아니면 10건 혹은 일부만 읽을까요?
자 마음속에 어떤 대답을 떠올렸겠지만 좀더 신중히 생각해 보길 바랍니다. 충분히 생각해 보셨나요? 정답은 "코드가 동일하더라도 상황에 따라 다르다." 입니다. 여기서 상황이란 DBMS의 종류와 버전, 사용된 언어의 종류와 버전, JDBC 드라이버의 종류와 버전을 말하는 것이며 6가지중 한가지만 달라져도 결과는 달라질 수 있습니다. "그게 머야~" 라고 불만의 소리를 낼 독자님들을 위해서 위 경우에 한정되서 말한다면 위 코드는 테이블 전체를 읽었을 가능성이 아주 높습니다. [그림 1]은 위 코드를 실행시켰을때 MSSQL의 Trace 화면입니다..(이 글에서 행해진 모든 테스트 캡쳐 화면은 정확한 숫자를 확인하기 위해 테스트마다 DB를 재시동하였습니다.)
[그림 1 : 코드 1을 실행하였을때의 추적 화면]
위 그림에서 보다시피 위 코드는 쿼리를 실행시키는데 cpu 시간이 718ms가 걸렸고 11409 page를 읽었으며 총 걸린 시간은 6623 ms입니다. (MSSQL에 익숙하지 않은 분은 page라는 용어가 낯설수 있는데 이는 오라클의 block 개념으로서 대부분의 데이타베이스 read와 write는 row 단위로 이루어지지 않고 block 단위로 이루어집니다. 오라클의 block는 2kb - 16kb로 설정가능하지만 MSSQL은 8kb로 고정되어 있으며 이를 page라고 부릅니다. 일반적으로 하드 디스크 할당이 byte 단위가 아니라 최소 저장단위가 클러스터 단위로 쓰여지는것과 비슷한 원리입니다. 윈도우 NTFS 클러스터에 사용되는 기본값이 4kb 혹은 8kb이므로 page는 그 공배수로 지정된 것 같습니다.) <그림 1>의 트레이스를 간단하게 해석하자면 화면에 10건을 찍는 프로그램을 위해 11400 page 즉 11400*8kb = 약 90M를 읽어야 했고 총 걸린 시간은 6.6초인데 그 중 cpu 시간은 0.7초가 소요되었다는 의미입니다. 프로파일러를 처음 보낟거나 이전에 특별히 생각해보지 않았다면 당황스러운 수치입니다.
[그림 2 : 테스트에 사용된 customer_tblc 테이블의 크기 정보]
우리는 처음에 부분범위 처리에 대해서 이야기를 했고 select custNo, fixLength from customer_tblc은 부분범위 처리가 가능한 쿼리라고 하였습니다. 그런데 왜 부분범위가 적용되지 않은 것일까요. (우리는 마지막에 페이징 처리에 대한 이야기도 할것이므로 select top 10 custNo, fixLength from customer_tblc 식의 쿼리는 여기서 감안하지 않을 것입니다.) 사실 조금만 이성적으로 생각해 본다면 간단한 문제입니다.역지사지라는 사자성어를 여기에 적당한지는 차치하고서라도 자신이 DBMS라고 생각해봅시다.(여기를 보세요. 레드~썬). select custNo, fixLength from customer_tblc 이라는 쿼리를 실행하라고 받았습니다. 이걸 전체를 읽어야 할까요? 부분을 읽어야 할까요? 물론 DBMS는 통계정보를 통해 customer_tblc에는 아주 많은 row 있다는 사전정보를 알수도 있겠지만 사용자가 그 결과 전부를 사용할지 아니면 일부만 사용할지 알수 없는 상태에서 쿼리 그 자체만으로 전체를 읽어야 하는지 부분을 읽어야 할지는 알 수 없습니다. 그래서 쿼리만으로는 판단이 어렵기 때문에 대부분의 DBMS는 이 판단을 위해 한가지 정보를 더 필요로 합니다. 그게 바로 어떤 커서 방식을 사용할것인지에 대한 정보 입니다. 그러나 우리는 위의 소스 어디에도 커서에 대한 설정을 하지 않았습니다. 그리고 커서에 대한 설정을 특별히 하지 않았기 때문에 DBMS의 디폴트 커서를 사용하게 되고 그 결과가 <그림 1>과 같습니다.
좀 더 문제에 접근하기 위해 코드 1에 주석으로 달린 숫자부분에 중간중간 경과 시간을 확인하도록 하였고 그 결과는 아래와 같습니다.
2375 (2375ms 연결하는데 걸린시간 - 커넥션 풀링을 만들고 커넥션을 얻는데 2.3초가 걸렸지만 이 글은 커넥션 풀링에 대한 글이 아니기 때문에 이는 상관하지 말고 넘어가기로 합시다. )
2422 (2422-2375= 47ms 쿼리를 실행하는데 걸린시간 )
2453 (2453-2422= 31ms 화면에 처음 10건을 찍는데 걸린시간)
9069 (9069-2453= 6616ms close를 하는시간)
다시한번 당황스러울수 있는 결과입니다. 이럴수가~ 쿼리를 실행하는데 47ms가 걸리지 않았는데 close를 하는데 6.6초가 걸렸습니다. 이상하다고 생각할지 모르겠지만 이상하다고 생각하기 때문에 위 코드의 문제를 잘 발견하지 못합니다. 언뜻 생각하면 쿼리를 실행하고 10건을 화면에 찍는데까지는 아주 빠른시간(커넥션풀링을 만드는 시간을 제외하면 고작 78ms)이 걸렸는데 DB가 이상해서 close 하는데 무려 6초가 넘게 걸렸기 때문에 이는 DBMS가 고작 close 하는데 무슨 엉뚱한 일을 잔뜩 하고 있는 것처럼 보이기 때문입니다. (그래서인지 어떤 사람들은 가끔 close를 안하던 사람을 보기도 했습니다. -ㅅ-) 그리고 앞에서 단순한 쿼리에 무려 DB가 90M나 읽었다고 겁을 줬는데 쿼리를 실행하는데 고작 0.047초 밖에 걸리지 않을 걸 보고(?) DB의 놀라운 능력에 감탄하면서 한편으로는 90M라고는 하지만 0.047초인데 어때? 라는 생각을 가질 수도 있습니다.하지만 조금만 더 생각해 보면 코드 중간에 찍어서 확인한 시간들은 그다지 의미가 없습니다. <그림 1>의 추적 화면에서 확인할 수 있듯이 쿼리를 실행하기 위해 11000 page의 90M를 읽어야 했는데 최근의 하드 디스크의 하드웨어 발전 속도를 감안하더라도 90M를 0.047 sec 만에 읽는 다는 것은 비상식이며 DBMS의 추적 화면에서도 실제 쿼리는 6.5초가 걸린걸 확인할 수 있습니다. 그래서 여기서 0.047 sec은 쿼리를 실행하고 테스트 서버가 첫 row를 받았을때의 시간으로 보는게 타당합니다. 물론 프로그램에서는 처음 10건만 확인하면 되기 때문에 쿼리의 모든 결과가 완료되기까지 기다리지 않는 방식은 바람직해 보입니다.
그러나 왜 close하는데 6.6초나 걸리느냐에 의문에 답을 하기 위해 우리는 조금 다른 관점에서 생각을 해야 할 필요가 있습니다. 앞서 <그림 1>에 보다시피 DBMS는 90M를 읽었고 화면에는 단지 10건(150byte*10 = 1.5kb)만을 찍었습니다. 그럼 나머지 DBMS가 불필요하게 읽게 된 90M-1.5kb의 데이타는 어떻게 됐을까요?
잠시 여러분이 생각할동안 딴 얘기를 하자면 우주 공간에 수명이 다한 로켓이나 인공위성 파편들로 생긴 우주쓰레기를 데브리라고 합니다. 이러한 데브리 중 1cm 이상의 크기가 약 3만 5천개로 추정하고 있으며 데브리는 초속 8km로 지구 궤도를 돌고 있기 때문에 우주선이나 우주인에게 생명의 위협이 됩니다. 그래서 10cm이상의 데브리 약 3000개는 북미항공우주방위 사령부에서 리스트를 만들어 그 위치를 실시간으로 감시하고 있을 정도로 중요하게 인식되고 있습니다. 데브리 처럼 지구퀘도를 돌고 있지는 않지만 개인적으로는 위 경우에 생긴 110-1.5kb의 데이타를 저는 개인적으로 네트워크 데브리라고 부르곤 합니다. '우주쓰레기 어쩌고 저쩌고 하는걸 보니 자연적으로 휘발되진 않겠군' 이라고 짐작하셨다시피 위 코드 1을 실행하면 나머지 모든 데이타도 DB에서 위 코드를 실행한 컴퓨터로 네트워크 전송이 일어납니다. 다만 우주 쓰레기와는 달리 계속 지구를 공전하지는 않고 받자마자 쓰레기로 간주되어 사라지겠지만요.
[
메시지가 비동기적이다. (DB서버 컴퓨터와 테스트 애플리케이션 컴퓨터는 비 동기적으로 통신을 하였다.)
패킷 사이즈가 유동적이며 대부분 1개 이상의 row가 담겨 있으므로 패킷 길이가 좀 더 길다.(54047 패킷의 경우 10row가 있으며 총 길이는 1460byte 입니다.)
패킷 교환 갯수가 많다.(약 55000개의 패킷을 교환하였다.)
<그림 2>는 리셋한 이후 위 코드를 전송하고 테스트 컴퓨터(192.168.10.102)와 DB서버가(192.168.10.101) 주고받은 네트워크 패킷을 캡쳐한 화면입니다. 물론 그 대부분의 패킷은 쓰레기로 간주되어 사라질 운명인 590000 - 10 row의 데이타 들입니다. 패킷은 최소 512 byte 단위로 통신하고 그리고 패킷을 받으면 인사성있게:) 잘 받았다고 답인사도 해줘야 하기때문에 약 55000개의 패킷을 주고 받게 되었습니다. (그림 2의 하단부의 패킷의 사이즈가 1460인걸 보면 해당 패킷은 10개의 row를 담고 있으리라 추측됩니다.) 그래서 위에서 DB가 고작 close를 하는데 먼가 알수 없는 수상한 짓이라고 생각했던게 사실은 사용하지도 않을 데이타를 잔뜩 받고 잘 받았다고 답 인사하는 걸리는 시간입니다. 그래서 우리는 불필요한 데이타를 읽는데 DBMS의 시간을 6초나 소요하게 했을뿐 아니라 동시에 중요한 네트워크 버퍼를 90M나 낭비했던 것입니다. (그리고 그 네트워크 데브리 처리를 하느라고 테스트 컴퓨터도 무려 6초가 소요되었습니다.)
여기까지 이야기하면 상당히 충격적인 결과일지도 모릅니다. 그래서 그동안 라면 받침대로 사용해왔던 JDBC 관련서적들을 다시 천천히 읽다보면
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setReadOnly(true) ;
rs.setFetchSize(DEFAULT_FETCHSIZE);
result.setTransactionIsolation(java.sql.Connection.TRANSACTION_READ_UNCOMMITTED);
와 같은 그럴듯해 보이는 코드들의 설명이 나옵니다. 게다가 위와같이 read only로 지정하거나 가장 낮은 레벨의 트랜잭션 레벨을 지정하면 웬지 더 빨라질것 같습니다. 바로 이게 함정입니다.(물론 함정을 판 사람은 없습니다. 스스로 함정을 만들어 빠지는 거죠) JDBC는 여러분이 잘 알다시피 규약일뿐 상세 구현은 Driver 구현업체가 담당하기 때문에 첫째로 규약대로 드라이버가 작성되는건 아니며, 두번째로 설사 구현이 되었다고 하더라도 어떤 특정한 선행 조건을 만족하지 않으면 코드만 저렇게 적어둔다고 작동하는게 아니라는 사실때문에 제대로 작동하지 않습니다. (심지어는 그 특정한 조건은 드라이버 별로 조금씩 다르기도 합니다. )
경험삼아 이야기 합니다만 여러분이 어떤 JDBC책을 열심히 뒤져봐도 아마도 [코드 1]의 문제를 해결하기 위한 답을 얻기는 힘들것입니다. 이 글이 커서에 관한 글이니 커서에 관한 문제라는걸 알았다고 하더라도 일단 기본적으로 JDBC에는 커서 설정하는 setCursor 같은 코드 자체가 없습니다. 커서에 대한 얘기를 찾아보면 프로그래밍 책에서는 updatabe resultset이니 holdable cursor 등등 다중 사용자의 update나 delete혹은 insert의 MDL 구문과 같이 사용될 경우의 예제만 있을 것이고, DBMS책에서는 Declare cursor 등의 특정 DB에 종속되어 있는 SQL 커서 얘기만 잔뜩 나올것입니다. 그리고 여기에서 이야기 하는 API Server 커서 - Select 시에 커서에 대한 이야기는 아마도 제대로 나와 있지 않을 것입니다.(커서에 일부 챕터를 할애한 경우는 드문드문 찾아볼 수 있지만 충분한 내용을 담고 있다고 하기에는 어려운 수준이며 표면적인 나열 수준을 벗어나지 못했습니다.)
전 오래 전부터 왜 대부분의 책에서 커서 이야기를 하지 않을까? 궁금해 했고 가능한 3가지의 이유가 있으리라 추측합니다. 첫번째로 책의 저자들 자신이 프로그래밍에서 사용되는 커서에 대해서 모르기 때문이고 두번째로 설사 안다고 하더라도 너무 복잡다양한 사항에서 그 모든 걸 설명하기란 무척 어렵기 때문입니다. 그리고 3번째로 그 모든걸 설명할 수 있다고 하더라도 실제 문제의 해결방법 역시 DB와 프로그래밍 언어, 드라이버의 문제 등으로 다양하기 때문에 하나의 분야를 가진 책에 담기가 어렵기 때문입니다. 그러나 그렇다고 해서 커서에 대한 내용을 무시할 수 있을만큼 배울 필요가 없는 내용이냐 하는것은 전혀 아닙니다. 10년동안의 프로그래밍과 DBA의 경험으로도 DBMS의 속도 장애와 관련한 대부분의 경우는 설정이나 쿼리 그 자체보다 커서의 잘못된 사용 - 커서의 문제라는게 비록 설정과 쿼리를 포함하는 복합적인 문제이긴 하지만 -에 있는 경우가 더 많기 때문입니다.
이미 말했듯이 JDBC는 직접적으로 커서를 설정하는 메소드가 없습니다. 아마도 커서는 프로그래밍보다는 DB에 더 가까운 얘기이고 DB마다 조금씩 다르기 때문에 WORA사상의 JDBC 표준 메소드에는 넣기 어려워서가 아닐까 추측합니다. 그러나 그렇다고 해서 JDBC가 커서를 사용하지 않느냐 하는것은 또 아닙니다. 커서는 마치 눈에 보이지 않는 공기처럼 DBMS를 사용하는 한 떨어질 수 없는 분야이기 때문입니다. JDBC에서는 Statement 객체를 만들때 ResultType이라는 상수타입을 설정할수 있는데 이 부분이 가장 커서와 가깝다고 할 수 있는 부분입니다. ResultType은 TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE 3가지 종류가 있으며 역시 드라이버마다 조금씩 차이가 있지만 아무 설정도 하지 않을 경우 TYPE_FORWARD_ONLY로 설정될 경우가 많습니다. 그래서 위 코드에서 pstmt = conn.prepareStatement(getProcSQL()); 대신 pstmt = conn.prepareStatement(getProcSQL(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 는 같은 의미이며 (위 예제의 드라이버와 DB 등이 동일하다면요.) ResultType을 ForwardOnly로 설정하면 [그림 1]과 [그림 3]의 결과가 나옵니다. 엄밀하게 말해 ForwardOnly나 SensitiveType 등은 커서의 종류가 아닙니다. 오히려 그 반대로 커서가 결정될때 종속적으로 결정되는 - 혹은 부가적으로 결정하는 - 타입에 불과하지만 JDBC에서는 Type을 설정하면 DB가 알아서 적절한(?) Cursor의 종류를 선택하게 됩니다. 좀 더 나중에 커서의 종류에 대해서 이야기를 하겠지만 미리 조금 얘기를 하면 위 코드에서 ForwardOnly Type로 설정하면 MSSQL의 경우 ForwardOnly cursor를 사용하게 되고, SensitiveType와 InSensitiveType에서는 Static cursor를 사용합니다.
그럼 pstmt = conn.prepareStatement(getProcSQL(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 렇게 코드를 변경하고 코드를 실행시켜 보기로 합시다.(단 한줄만 바꿨지만 이 코드를 [코드 2]라고 하겠습니다.)
<그림 3>은 그렇게 바꾸고 실행한 결과의 DB Trace 화면입니다.
한줄만을 바꿨지만 무언인가가 <그림 1>과 달리 잔뜩 바꼈습니다. 일단 화면을 그대로 해석하자면 Read하는데 178만 page(단위는 bit나 kb가 아니라 page라고 불리는 8kb block 개념이라고 위에서 말했죠)를 읽었고 8706 page를 write 하였습니다. 그리고 cpu시간은 4초정도 걸렸고 총 6.7초가 걸렸습니다. 그리고 이전에는 보이지 않던 sp_cursorfetch에 관련한 새로운 열들이 보입니다. [코드 1]의 ForwardOnly와 비교하여 실행시간의 조금의 차이는 있지만 10건을 읽고자 하는것에 비하면 여전히 느리다는 사실은 동일합니다.
아니 그전에 잠깐 178만 page를 read 했다고 했던가요? 178만 page는 byte로 환산하면 무려 1500G에 해당되는 엄청난 양입니다. 이 테이블에는 약 58만건이 들어있으며 인덱스를 포함한 전체사이즈는 <그림 2>에 보다시피 130M에 입니다. 그런데 이게 무슨 경우인가요. 설사 한 page당 1 row가 들어있는 최악의 경우라 해도 최대 58만 page를 읽어야 하는게 아닌가요? 178만 page read는 도대체 어떻게 나온 숫자인가요? 이거 버그 아닌가요? 게다가 웃기게도 8706 page의 write는 또 먼가요? 쿼리는 분명 select인데 웬 write를 하고 있나요? 그리고 저 read도 0이고 write도 0인 쓰잘데기 없어 보이는 sp_cursorfetch는 왜 일어나고 있는건가요? 등등 무수한 의문이 떠오르는 캡쳐 화면입니다. (드디어 Trace 화면에 이 글의 주제인 cursor가 나오기 시작했습니다.)
일단 그 의문에 답하기 전에 [코드 1]과 마찬가지 위치에 중간 Time을 살펴보도록 하겠습니다.
1. 2375 (2375ms connect 얻는데 걸린시간 - 역시 이 시간은 신경쓰지 말도록 하죠)
2. 9187 (9187-2375= 6812ms 쿼리를 실행하는데 걸린시간 )
3. 9218 (9218-9187= 31ms 화면에 처음 10건을 찍는데 걸린시간)
4. 9220 (9220-9218= 2ms close를 하는시간)
[코드 1]의 결과와 비교하여 비슷하게 느린 속도이지만 어느 지점이 오래 걸렸는가가 다릅니다. close는 거의 시간이 걸리지 않았는데 이번에는 좀전에 0.047초 밖에 걸리지 않았던 쿼리가 6.8초가 걸렸다고 나옵니다. 우리는 쿼리 내용을 바꾼적이 없습니다 다만 쿼리를 실행할때 ResultSet.TYPE_SCROLL_INSENSITIVE Type을 실행하라는 것만 바꿨을 뿐이죠.
그래서 같은 코드를 ResultSet.TYPE_SCROLL_SENSITIVE로 바꿔봅시다.
pstmt = conn.prepareStatement(getProcSQL(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ( [코드 3] 이라고 부르겠습니다.)
<그림 4>는 [코드 3] Trace 화면입니다.
cpu 시간하고 duration 시간이 조금씩 바뀐거 말고는 <그림 3>과 아무런 차이가 없습니다. (cursor fetch가 나타나는거나 심지어는 read나 write page는 숫자도 동일합니다. ) cpu 시간이나 duration은 그냥 오차범위내의 시간이므로 사실 <그림 3>과 <그림 4>는 동일한 결과라고 봐도 무방합니다. 즉 위 예제코드에서 insensitive와 sensitive 차이는 아무것도 없습니다. 물론 그렇다는 것이 본래적으로 insensitive와 sensitive가 차이가 없다는 뜻은 아닙니다. 다만 이 코드에서는 아무런 차이도 불러 일으키질 않았다는 뜻이고 좀더 정확히 말하자면 여러분이 insensitive type을 쓴다고 코드에 적어놓는다고 하더라도 위 코드에서는 sensitive type으로 작동한다는 뜻입니다. (왜 결과가 같은데 왜 insensitive가 아니라 sensitive로 작동했다고 하는 건지에 대한 설명은 나중에 하겠습니다.) JDBC에서 선택할 수 있는 ResultType은 3가지지만 SensitiveType와 InSensitive타입은 위 경우에 차이가 없으므로 같이 얘기하겠습니다.
일단 고작(?) 58만 row를 읽는데(더군다나 그 대부분은 프로그래밍 코드에서 사용하지 않겠지만요) 왜 178만 page나 읽어야 했는지를 살펴보겠습니다.
여기서 간단한 질문~ 178만 page를 read하고 8706를 write 했을까요? 아니면 8706 page를 write하고 178만 page를 read 했을까요? 상식적으로 read한게 없는데 write를 할수는 없는 일이니 write를 먼저 한다는건 말이 되지 않습니다. 그렇지만 178만 page를 read 한다음에 8700 page를 write 했다고 하려니 이도 상식에 맞지 않습니다. 고작 8700 page를 쓰기위해 row수보다 많은 page를 read 해야 할 이유가 없어 보이기 때문이죠. 사실 위의 과정은 read-write-read 과정으로 진행되었습니다. 그리고 1번째 read와 3번째 read를 합쳐서 결과를 보여주다보니 178만 이라는 read 수가 나온것입니다. 과정을 단계적으로 보면 아래와 같습니다.
1. read
일단 DB는 select custNo, fixLength from customer_tlbc를 모두 읽습니다. <그림 1>에서 보다시피 select custoNo, fixLength from customer_tblc 쿼리는 11400 page를 읽으면 모든 row를 읽게 됩니다. (만약에 DB에 2개 이상의 CPU가 있고 병렬화 실행계획이 세워진다면 - 위와 같이 where 조건이 없는 쿼리는 병렬화 실행계획이 세워질 확률이 높습니다. - read page수는 달라집니다. 하지만 이 글을 읽는 분들은 이 이상 꼬인 문제를 보고 싶어하지 않을 것이므로 위 테스트는 모두 병렬화 계획이 실행되지 않도록 설정하였습니다.)
2.write (1단계 read를 마치고 write를 하는게 아니라 read를 진행하면서 write를 같이 합니다.)
우리는 freeLength 컬럼을 select 대상에 넣지 않았으므로 11400페이지을 읽었지만 custNo와 fixLength만 DB의 메모리에 write를 합니다. 해당 쿼리는 길이 (4byte +100byte)의 row를 590000개 읽었으므로 104*590000/1024 kb는 대략 59921 kb인데 한 페이지는 대략 8k이므로 대략 7490 page을 쓰면 됩니다. 어라 1200 page 정도가 남네요? 계산에 하나 빠트린게 있습니다. 새로운 결과셋은 일종의 "메모리에 보관되는 임시 테이블"이므로 모든 row은 자신을 구별할수 있는 unique한 메모리 주소를 가져야 합니다.(오라클의 경우 rowId는 의사 컬럼일뿐 실제 테이블이 rowId라는 컬럼을 가지거나 공간을 차지하지 않지만 - index에 rowId가 저장됩니다 - MSSQL에서는 여러번의 테스트를 통해 추측하건데 메모리에 set 집합을 만들경우 rowid의 역햘을 하는 컬럼을 가지는 걸로 보입니다. ) 위의 경우 MSSQL의 row별 unique한 ID 사이즈는 알려지지 않았으나 오라클의 경우 rowId는 18byte이므로 대략 18byte로 하고 다시 계산해보겠습니다. 그러면 평균 row길이는 (4+100+18)이므로 122*590000/1024/8 = 대략 8786 page 입니다. 우와 write page의 비밀이 풀렸습니다. 즉 우리는 먼저 customer_tblc의 모든 row를 읽는데 11400 page를 읽었고 쿼리 결과를 일종의 임시 테이블인 set 상태로 보관하기 위해 DBMS가 관리하는 메모리에 8700 page를 write 하였습니다. 그리고 끝인가요? 아뇨 아직 read 단계가 하나 더 남았습니다.
3. read
-----
<그림 3>에서 @P1은 Handle Number 입니다. Handle Number가 같은 쿼리라면 해당 테이블에 변경이 없는 경우 위 결과셋은 재 활용될 수 있습니다. Handle Number가 같기 위한 조건은 쿼리와 설정내용이 같아야 하며 쿼리에 사용된 테이블에 변경사항이 없어야 합니다. @P2가 8이라는건 나중에 다시 이야기 하겠지만 DBMS는 static cursor를 사용한다는 뜻입니다. @P3이 1 이라는건 read-only 모드라는 뜻입니다.
그리고 @P4는 rowcount인데 1-2 단계를 거친 상태이므로 589824라는 정확한 rowcount를 알고 있습니다.
------
메모리에 write 하는 결과 셋은 쿼리에 따라 사이즈가 매우 커질 수 있으므로 수백 혹은 수기가에 이르는 연속적인 메모리 공간을 확보하기란 매우 어려울 것이므로 보통의 경우 연속적인 공간이 아닌 메모리에 페이지 별로 조각 조각 흩어져 있을 수 있기 때문에 마치 링크드 리스트처럼 페이지들을 - 그리고 row 들을 - 연결하는 과정인 populate 과정을 실행하는데 여기가 중요합니다. 이 과정은 동기적 - 즉 클라이언트는 이 과정이 끝날때까지 기다려야 합니다 - 작업입니다. (이 과정이 비동기적으로 이루어지면 프로그램상에서 rows.abstolute(int row)나 rs.last()를 실행할수 없게되고 JDBC에서 sensitive type으로 쿼리를 실행할경우 resultSet은 scroll 가능하다라는 조건에 위반됩니다.)
앞에서 write한 set은 연속적인 공간을 확보하기 위해 빠른 row의 셀렉팅을 위해 실제로는 Balanced Tree 형태를 가지게 됩니다. 앞서의 계산대로 rowId를 18byte로 계산하면 한 page에는 약 400개 page의 첫 rowId를 저장 할수 있습니다. 그리고 그 400개의 page는 다시 160000개 page의 처음 rowId를 저장할수 있습니다. 현재 set은 8700 page이므로 3depth(level)로 충분히 표현이 가능한 수치입니다.(400보다 크고 160000보다는 작다.) 그래서 populate 과정에서 매 row당 평균 3번의 read가 일어납니다. (테스트에 의하면 전형적인 Balanced Tree 형태는 아닌걸로 생각됩니다. 보통의 경우 Balanced Tree는 Leaf Level은 모두 동일한 Depth를 가지도록 설계되는데 위의 경우 page수가 많을 수록 기대 depth수 보다 커지고 페이지수가 작을수록 기대 depth수 보다 작아집니다. ) 그래서 추가적으로 590000*약3 의 추가적인 read가 발생하게 됩니다. (오라클의 경우 SGA의 Database buffer cache에 block를 복사하는 개념이기 때문에 위 과정은 MSSQL의 과정일 뿐입니다. 다만 세부 과정은 다르지만 1.read -> 2.write -> 3.read 과정은 DB와 상관없이 동일합니다. 그리고 1과 2과정은 캐싱될 수 있습니다. )
즉 <그림 3>의 1789259 read와 8706 wrtie는 실제로는 (11400 read + 8700 write + 590000 *3.x read) 의 결과치입니다. ( 산수에 익숙하지 않으신분은 이때쯤 '집어치워~ '라며 더이상 읽게 되지 않을까 조금 걱정이 됩니다. )
4. 그 이후의 과정을 이야기 하면
모든 row를 커서에 채운후 sp_cursorfetch 180150000, 2, 1, 1 로 맨 처음 위치로 돌아옵니다.(180150000은 handle 값이고 2는 next row라는 의미인데 populate를 완료한 후 last에서 next이므로 맨 처음으로 돌아옵니다. ) 그리고 sp_cursorfetch 18015000, 16, 1, 1(180150000은 handle값, 16은 absoleute row index, 1 rownum, 1 number of row) 로 처음의 1개 row를 fetch 합니다. 조건을 while(i++ < 10 && rows.next()) 렇게 적었다면 10개의 row를 fetch 했겠지만 코드는 while(rows.next() && i++ < 10) 와 같이 되어 있으므로 11개의 row를 fetch 합니다.
첫 row를 읽을때 다시 3 page를 읽지만(3 depth tree) 2번째 row는 같은 페이지에 있기 때문에 2번째 로우에서는 read가 일어나지는 않습니다. (위 source는 단지 10개의 행을 찍게 되어 있지만 1000개쯤 찍도록 바꾸고 다시 트레이스 화면을 보면 1페이지당 대략 55개의 row(8k/150)가 들어가 있으므로 약 55번의 fetch 마다 3번의 read가 일어납니다.그리고 sp_cursorclose를 하여 마무리 합니다.
위의 내용은 단지 기술적인 내용이지만 위 과정이 [코드 1]의 결과보다 나은가요. 아니면 나쁠까요?
일단 178만번 read의 숫자가 커 보입니다. (Duration 6.7초와 CPU 3.4 초의 차이인 3.3초는 대부분 Disk IO인 처음 1100 page(90M)를 Read하는데 걸린 시간일테고 CPU 3.4초의 대부분은 populate과정중의 177만 page의 Memory Read 속도일테니 심심한 분은 스펙상이 아닌 일반적으로 자신의 하드디스크와 메모리의 read 속도를 계산해 보는것도 재미있을지 모릅니다.-ㅅ-). 1번의 테스트와 비교하여 실제 총 Duration은 비슷 하다지만 (6.5초와 6.7초로 단지 0.2초의 차이지만 사실 정말로 그정도의 차이에 불과하냐고 물어보면 그 대답은 위 테스트만으로는 알 수 없다 입니다. 영화에서 대부분의 암살자는 단 한발의 사격으로 타겟을 죽이고 유유히 돌아서지만 실제로 정말 죽이는데 목적이 있는 거라면 2발 혹은 여러발을 쏘는게 확률이 훨씬 높아집니다. 치명적인 곳을 맞히지 못하더라도 출혈 과다로 죽일 수 있기 때문입니다. 갑자기 웬 엉뚱한 소리냐고 할지 모르겠지만 실제 걸린 속도를 한번의 테스트로 평가할 수는 없습니다. 그래서 퍼포먼스 테스트때는 총은 2발 쏴라라는 격언이 있습니다. 그래서 실제로는 테스트되는 컴퓨터 등의 사양에 따라 달라질수 있기에 총을 여러발 쏜후 확인해야 겠지만 여기서는 시간 그 자체보다 절대적으로 몇 page를 읽었는가가 더 중요합니다. ) 몇가지 단점을 가지고 있습니다.
첫번째로 화면에 첫 Row가 찍히는데 오래걸립니다. sensitive type은 동기적인 작업이 필요하므로 590000로우를 모두 읽기 전에는 단 한행도 클라이언트게 리턴시키지 않습니다. 그래서 처음 행을 화면에 찍는데 9초나 걸렸습니다. 그에 비해 테스트 1은 비 동기적으로 데이타를 받기 때문에 처음 행은 2.4초만에 찍을 수 있었습니다. 물론 테스트 1은 그 후에 네트워크 데브리를 처리하느라 close 하는데 6.6초나 걸리기 떄문에 전체 시간은 비슷하겠지만요.
두번째는 더 심각한 문제인데 write를 하느라고 공유 자원인 DBMS의 메모리를 약 70M나 소모했습니다. 데이타 베이스의 메모리는 혼자 쓰는 공간이 아니기 때문에 대부분 필요없게 될 데이타를 위해 70M 쓰느라 자주 사용되는 캐쉬에 올라와 있는 데이타를 메모리에서 내려버릴지도 모릅니다. 이를 흔히 미꾸라지라고 하는데요. LRU 알고리즘으로 자주 사용되는 데이타는 Disk IO를 하지 않기 위해 메모리에 로드가 되어 있는데, 위와 같은 코드가 실행되었을 때 충분한 여유 메모리가 없다면 캐쉬 데이타를 모두 지워 버릴 것입니다. 테스트 테이블의 데이타가 훨씬 더 많다면 최악의 경우에는 가상 메모리를 사용해야 할지도 모릅니다. (이때의 read 속도는 Memory IO가 아니기 때문에 무시 무시한 속도가 나올것입니다.) 냇가를 미꾸라지 한마리가 흐린다는 속담처럼 그냥 저냥 돌아가던 시스템이 위 코드처럼 미꾸라지 코드가 돌면 메모리에 캐쉬된 데이타는 모두 삭제 되버리므로 평소보다 훨씬 더 느려질 것입니다. A라는 특정 페이지만 갔다오면 기존에는 빨랐던 B..Z페이지마저 느려지다가 시간이 좀 지나면 다시 회복되고 다시 A 페이지를 누군가가 로드하면 또 B..Z 페이지가 느려지는 등의 같은 부분인데도 매번 속도 차이가 심하다면 이런 미꾸라지 코드를 의심할 필요가 있습니다. 사실 고작 10row를 보기 위해 메모리 90M를 쓰도록 하는것은 범죄에 가깝습니다.
세번째는 장점이지만 상황에 따라서는 단점이 될 수도 있습니다. [코드 1]의 경우 90M를 읽어서 클라이언트에게 90M모두를 보냈습니다. 클라이언트 입장에서는 단 10건만 사용하겠지만 DB는 데이타베이스는 비동기적으로 59만건의 데이타를 모두 보냅니다. 앞의 <그림 2>에서 DB와 클라이언트가 주고받은 패킷의 수는 5만개가 넘습니다. <그림 7>은 sensitive[insensitive] type으로 실행되었을때 네트워크 캡쳐 화면입니다. 패킷의 수는 45개 정도에 불과합니다. 비록 테이블의 모든 데이타를 읽었지만 네트워크를 통해 클라이언트로 전송된것은 단지 10 row 정도에 불과합니다. (실제로 해보면 12 row를 전송합니다. 하나는 while (i++ < 10 && rs.next()) 라고 하지 않고 while (rs.next() && i++ < 10) 로 했기 때문이고 또 하나는 <그림 2>에서 보다시피 last인 상태에서 first 상태로 이동하면서 first Row를 한번 더 전송합니다. 물론 이건 해당 JDBC Driver의 문제일뿐 일반적으로 적용되진 않습니다.) 그리고 매번 row를 받을때마다 잘 받았다고 답례 인사를 해줍니다. (2*12 를 제외한 나머지 패킷들은 연결정보를 설정하고 JDBC 드라이버의 기본 세팅정보, 쿼리등이 차지합니다.) 이건 분명 여기서는 장점으로 작용합니다. 네트워크 데브리를 만들지 않았으니까요. 대충 필요한 row*2 정도의 패킷만 주고 받으면 되니까요. 하지만 이는 다시 생각하면 단점이 되기도 합니다. 만약에 위 코드가 일부 만이 아니라 쿼리의 결과가 모두 필요한 경우를 가정해 봅시다. 어떤 where 조건이 있어서 그 결과인 1000건 모두를 출력해야 하는 프로그램이 있다고 해보죠. 결과를 모두 출력하고자 하는 경우라면 [코드 1]은 네트워크 데브리가 없습니다. 비 동기적으로 데이타를 전송하기 때문에 하나의 패킷에 10개의 row를 전송할 수도 있습니다. <그림 2>를 보면 하나의 메지지 프레임의 길이가 1450 byte 입니다. 이에 반해 테스트 2는 오직 한건씩 동기적으로 주고 받습니다. 클라이언트의 요구사항이 있을때(rows.next()) 하나씩의 row를 전송해주기 때문이죠. 모든 row가 필요하다는 가정에서 테스트 1은 59만 row를 전송하기 위해 5만 5천번의 패킷을 주고 받았지만 테스트 2는 115만번 이상의 패킷을 주고 받아야 할겁니다. 비록 패킷 사이즈의 총량은 동일하더라도 패킷의 수는 20배 가까이 늘어나므로 네트워크에 더 심각한 영향을 줍니다.
네번째로 - 이걸 장점이라고 부를 수 있을지 모르지만 - rs.absolute(int row)나 rs.last() 등이 메소드 들이 제대로 작동한다는 것입니다. rs.last()를 호출한후 rs.getRow()를 호출해 현재 쿼리결과의 갯수를 알수도 있습니다. [코드 1]은 비동기적으로 row를 받기 때문에 rs.absolute() 등이 작동하지 않고 갯수를 알기 위해서는 while(rs.isLast()) 까지 rs.next()를 해봐야 알 수 있습니다. 그래서 어떤 책들에서는 [코드 2]방식을 권장하곤 합니다. 단지 "XX건의 글이 등록되어 있습니다 " 식의 한줄의 메시지를 보여주기 위해서요. 하지만 상식적인 생각으로 간단히 알수 있는 사실이 있습니다. 여러분이 전체를 모두 읽지 않았다면 결코 정확한 count를 알수 없기 때문에 한 줄의 메시지를 위해 모든 row를 읽어서 메모리에 write를 해야 합니다. 그럴바에는 그냥 select count(*) from ... 의 쿼리를 한번 더 실행시키는 게 훨씬 더 빠릅니다. (select count(*) from... 도 DB에 따라서 테이블 전체를 읽는건 변함없습니다만 대부분의 상용 DB는 이런 경우에 Fast Full Scan의 MultiBlock IO을 할 확률이 높을뿐 아니라 모든 로우를 읽어서 클라이언트에 전송하거나 메모리에 write 할 필요가 없습니다. 필요한건 그냥 숫자 하나니까요). 쿼리 결과가 589824건이란 사실을 알기위해 589824 전체를 모두 전송하거나 전체를 데이타베이스 메모리에 write 하는건 당연히 아주 아주 바보짓입니다.
[위 그림에서 주목해야 할점 : 하나의 메시지마다 1건이 fetch 됐다.(메시지 사이즈 145), 클라이언트와 서버의 통신이 동기적이다(너한번, 나한번), 갯수가 작다 ]
아마도 어떤 분은 위의 경우는 특수한 경우일 뿐이다 라고 생각할지 모르겠습니다. 행운이 함께한다면 처음에 언급하였다시피 DB의 종류와 버전, Driver의 종류와 버전, 언어의 종류와 버전 이 6가지중 한가지만 바껴도 전혀 다른 결과가 나올수도 있으니까요. 하지만 위와 같은 형편없는 코드는 언어나 DB와 상관없이 아주 일반적으로 소개되어집니다.(다른 관점에서 형편없다는 이야기도 언젠가 할수 있기를 바랍니다.) 인터넷 사이트나 책 등에 권장하는 샘플코드로 쓰여있을뿐 아니라 개인적으로도 10년간의 프로그래밍과 컨설팅 경험 중 테이타베이스와 언어를 바꿔가면서 질리게 봐왔습니다. 물론 1차적인 책임을 코드와 그 코드 작성자에게만 돌릴수는 없습니다. 왜냐하면 그런 코드들은 쿼리의 결과가 아주 아주 작다면 문제가 드러나지 않을 것이고 한결같이 예로 든 테이블들은 아주 아주 크기가 작기 때문이죠. 중요한 것은 예제 테이블이 아니라 실제 테이블을 대상으로 row수가 많아지면 그럭저럭 돌아갔던 코드들이 아주 이상해져 버린다는 거죠. WORA가 모토인 Java는 더욱이 연결 문자열만 몇줄 바꿔주면 제대로 돌아가겠지라고 생각해버립니다.
악화가 양화를 망친다는 말처럼 사람들은 위와 같은 코드들을 당연시 하기 때문에 문제가 생겼을때 전혀 엉뚱한 방향으로 해결하려고 합니다. 테이블의 건수가 많아지니 느려졌다고 불평하며 하드웨어를 업그레이드하거나 업그레이드로도 따라가질 못하는 수준이 되면 테이블을 종으로 쪼개는 등의 역정규화를 아무렇지 않게 저질러 버리는 거죠. (이 때가 되면 프로그램으로 그 부분을 처리하느라고 거의 암호가 됩니다.) 그마저도 안되면 프로그래밍 언어나 DB를 바꿔버립니다. 그러다 어느날 우연한 조합으로 문제가 해결된것 처럼 보이면 문제는 역시 그거였군이라고 제멋대로 생각해 버립니다. 사실 현재 프로그램이 필요한 row만 정확히 처리할수 있다면 테이블의 row는 1000건이든 1000억건이든 하등의 상관이 없어야 합니다. 중요한건 프로그램이 필요한 row를 정확히 읽었느냐는 거죠. 비록 해결책이 상황마다 다르다- 어떤 상황에서는 DB 설정을 바꿔야 하며 어떤 상황에서는 프로그래밍 코드를 바꿔야 하며 어떤 상황에서는 다른 Driver를 사용해야 할 때도 있습니다. - 는 것때문에 문제가 되는 것이지 해결책이 전혀 없는 것은 아닙니다.
저는 커서가 DBA와 프로그래머의 간극의 문제라고 생각 합니다. DBA의 입장에서 select custNo, fixLength from customer_tblc는 전혀 잘못된 쿼리가 아닙니다. 부분범위 처리가 제대로 되었다면 이 쿼리는 0.01초 내에 처리가 되는 아주 가벼운 쿼리입니다. 그리고 DBA와 프로그래머 모두 여러개의 DB와 여러개의 버전 그리고 여러개의 드라이버와 여러개의 드라이버 버전, 여러개의 언어와 여러가지 언어의 버전 최소한의 경우의 수만 생각해도 수백 수천가지가 넘는 모든 경우의 수를 알 턱이 없습니다.(저도 그 많은 경우를 모릅니다. 다만 필요할 때 이 경우는 이렇군 이라는 사실을 테스트 하여 확인할수 잇을 뿐입니다.) 그래서 프로그래머는 현재 DB의 아키텍쳐에 신경쓰지 않고 DBA는 현재의 프로그래밍 처리 방식을 신경쓰지 않기 때문에 그 접점인 커서에서 폭탄이 꽝 하고 터져버리는 것입니다. 사실 저는 사람들이 커서가 중요하다는 걸 모른다고는 생각하지 않습니다. 다만 대부분의 프로그래머들은 "얼마나" 중요한지를 모르고 있을뿐입니다. 그리고 "단지 중요한것"과 "얼마나 중요한지를 아는 것" 의 차이는 필연적으로 결과에 매우 큰 차이를 일으킵니다.
많은 사람들은 WORA를 모토로 삼는 Javas는 물론이고 C# 등의 다양한 언어를 다루는데 있어서 데이타베이스를 마치 라디오의 건전지처럼 필요에 따라 교체될 수 있는 블랙박스처럼 취급합니다. 그리고 데이타베이스 종속은 마치 나쁜짓인양 무슨 수를 써서라도 피하고 싶어합니다. 개인적으로도 한명의 프로그래머로서 데이터베이스 종속을 피하고자 하는 것은 목표이긴 하지만 그게 단지 여러 데이타베이스의 교집합의 기능만을 사용하며 다른 모든 기능은 회피하는 것이라면 바람직하지 않다고 생각합니다. 그렇기에 종속을 피하는 과정은 연결 문자열 한줄만 바꿔주는 것처럼 그리 간단하지만은 않습니다. 이 부분에 대해 오랫동안 생각해 온 바가 있어서 언젠가 다시 기회가 되면 DB의 블랙박스 증후군이란 다른 주제로 찾아볼 수 있기를 기대합니다.
여기까지 꽤 긴 글이었지만 우리는 아직 문제를 해결하지 못했습니다. [코드 1](Forward Only)과 [코드 2](Sensitive, Insensitive -> Static] 두가지 모두 단점을 가지고 있었고 그 단점은 모른척 무시하기에는 너무 큽니다. 그러나 우리가 전혀 나아가지 못한 것은 아닙니다. 나쁜 방법을 알았고 왜 나쁜 방법인지를 배웠습니다. 그리고 더욱 중요한 "얼마나" 나쁜 방법인지를 알게 됐습니다. 4-5년쯤 마소를 통해 글을 쓰면서 "더 빠르다 혹은 더 느리다."가 중요한게 아니라 "어떤 상황에서 얼마만큼 더 빠른가 혹은 느린가"의 문제가 더 중요하다고 말한적이 있는데 이 글에서도 이 글의 "얼마나"가 더 중요하다고 생각합니다. 이 문제의 해결은 이 다음이나 이 다음다음 글에서 나오겠지만 모든것은 "문제의 인식"으로 시작하니까요.
해결은 다음글에 -ㅅ-..
참고 문헌
대용량 데이타 베이스
MSSQL Inside
'Framework > Database' 카테고리의 다른 글
| Framework (DBManager) (0) | 2009.03.04 |
|---|---|
| Framework (구조적 중복 제거) (0) | 2009.02.21 |
| Framework (실험실 코드) (0) | 2009.02.20 |
| Framework (개요) (0) | 2009.02.20 |
| Framework (블랙박스 증후군) (0) | 2009.02.07 |
