1. 쉘스크립트로 oracle sqlplus 사용시 문제발생과 해결
이전 글에서 설명했듯이 기존의 데이터를 select 해오는 DB 는 오라클이다.
오라클에서 데이터를 select 해오는 과정 역시 여러가지 시행착오를 겪어야 했다.
우선 sqlplus 를 이용한 쉘스크립트 방식으로는 결과물 리스트에서 개행이 빠지는 현상이 발생한다.
list=`sqlplus -s $HOST/$PASSWD@"$host" << EOF
set head off
set feedback off
set linesize 300
select t1.column1 t2.column2 from table1 t1, table2 t2
and t1.column1 = t2.column2
order by t2.column2 asc
/
exit
EOF`
echo $list > tmp.$host
즉, 이런 식으로 80,373 건의 결과물이 1 line 에 담기게 된다.
이를 해결하기 위해서는 두가지 방법이 있다.
첫번째, echo $list 를 echo "$list" 로 바꿔주면 된다.
list=`sqlplus -s $HOST/$PASSWD@"$host" << EOF
set head off
set feedback off
set linesize 300
select t1.column1 t2.column2 from table1 t1, table2 t2
and t1.column1 = t2.column2
order by t2.column2 asc
/
exit
EOF`
echo "$list" > tmp.$host
두번째, sqlplus 에서 spool 기능을 이용한다.
spool 명령으로 테이블의 데이터를 파일로 출력하는 방법이다.
다음의 내용을 sql 확장자 파일로 저장한다.
set echo off // 실행하는 SQL문을 출력하지 않는다.
set pages 0 // 한페이지로 지정할 라인 수. 0으로 입력시 heading, page break, title을 표시하지 않는다.
set trimspool on // 라인 뒤 공백을 제거한다.
set colsep ',' // 컬럼 간 데이터 구분자
set lines 30000 // 한 라인에 표시할 글자 수
set termout off // SQL문으로 실행한 결과를 표시하지 않는다.
set feed off // SQL문으로 실행한 결과 건수를 표시하지 않는다.
spool /경로/test.csv;
select * from 테이블명;
spool off
quit
파일을 저장한 후 다음과 같이 실행하면 지정한 경로에 파일이 생성된다.
sqlplus userid/passwd @파일명.sql
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#i2698970
http://ss64.com/ora/syntax-sqlplus-set.html
spool 을 사용 유무 성능비교를 하려고 하는데 생각지도 못한 문제가 발생했다.
성능비교를 위해 스크립트를 약간 수정해서 돌려보니 결과가 2건씩 중복 출력되는 현상이 발생했다.
수정해서 사용하던 스크립트는 아래와 같다.
list=`sqlplus -s $HOST/$PASSWD@"$host" << EOF
set head off
set feedback off
set linesize 300
select t1.column1 t2.column2 from table1 t1, table2 t2
and t1.column1 = t2.column2
order by t2.column2 asc;
/
exit;
EOF`
본 글 상단의 기존 스크립트와 비교해보면 sql 구문의 마침부분에 ; 이 추가되었다.
그리고 oracle sql 을 다시 실행하라는 / 는 그대로 존재한다.
따라서 sql 이 이중수행되었던 것이다.
주의해서 사용하도록 하자.
원인을 모르고 2중 쿼리만 반복 수행하다보면 쿼리가 끝나지 않는 hang현상도 심심치 않게 볼 수 있었다.
3. sqlplus spool 사용여부 성능 비교
다시 원래의 목적으로 돌아와서, spool 을 사용하면 성능향상이 있는지 확인해보자
위의 spool 사용법을 참고하여 예상과 다르게 속도가 안나온다.
( sql 구문은 동일하지만 오라클 상태가 동일한 조건에서 테스트 하지 않았기 때문에 각각의 조회시 약간의 편차는 감안해야한다.)
Golden 으로 조회 | 7초 |
sqlplus 와 쉘변수로 결과 쓰기 | 44초 |
sqlplus 에 spool 로 결과 쓰기 | 5분 30초 |
쉘스크립트에서 변수에 결과값을 담고 echo 로 찍어주는 방식은
변수에 결과값이 전부 리턴될때 까지 기다리는 시간이 대부분이고 그 이후 echo 로 값을 기록하는 건 한순간이다.
사용자 화면 출력에 시간이 걸리는 것이지 출력 이전에 값을 기록하는 단계는 아주 짧고 보통 배치성 스크립트에서는 사용자 화면 출력이 필요하지 않다.
spool 방식은 이와 달리 출력파일에 tail -f 를 걸어놓고 살펴보면 약 20 line 씩 툭툭 끊기며 작성되는게 눈에 보인다.
( 아마 20 line 이 1 page 단위이고 defaul 설정인듯 )
그렇다면 page 옵션을 설정해주면 성능향상이 일어나지 않을까?
- sql 구문에 set pagesize 100000; 을 추가해주고 spool 로 조회를 해보니 45초가 소요되었다.
- sql 구문에 set pagesize 0; 을 추가해주고 spool 로 조회를 해보니 35초가 소요되었다.
위 결과를 봤을때 spool 사용시 한번에 전체 결과를 받는게 아니라 page size 단위로 결과를 받아오고 있는 것처럼 보이고,
page size 를 조절해주면 쉘스크립트 변수를 사용하는 방식과 비교했을때 성능에 큰 차이는 없어 보인다.
그런데 spool 은 실제로 page size 단위로 결과값을 리턴받고 받은 만큼만 기록하게 될까?
리턴값이 엄청나게 많아서 메모리에 한번에 담지 못할 경우라면 (out of memory) spool 이 유리한것 아닌가?
약간의 구글링을 해봤는데 원하는 답을 찾을 수 없었다.
그리고 위 상황을 가정하는 것도 별로 의미가 없어 보인다.
내 생각에는 쿼리 결과를 단위별로 끊어서 리턴할 수는 없을 것 같고
(가능하다고 해도 그렇게 엄청나게 큰 결과를 단위별로 끊어서 파일에 기록하려면 작업에 소요되는 시간도 만만치 않을 것이다.)
spool 기능은 배치성 스크립트가 아닌 sqlplus 접속화면에서 파일입출력을 지원하기 위한 도구일 것 같다.
배치 쉘스크립트 위주로 작업을 하는 나는 익숙한 방식을 사용하면 될 일이다.
그리고 spool 로 파일작성하면 append 가 아니라 overwrite 된다.
