하지만 이 SQL문도 맹점은 있다. 왜냐하면 우편번호 인덱스와 데이터 파일을 계속적으로 몇 천만번 데이터를 액세스하기 때문이다. 이는 [리스트 1]에서 나타난 프로그램의 구조와 별 차이가 없게 된다. 따라서 이를 개선하기 위한 방법이 해시 조인이다.
해시 조인은 중첩 루프(NESTED LOOP) 방식 조인의 단점을 획기적으로 개선해준다. 통상적으로 작은 테이블을 기준으로 해시 테이블을 만들어 정렬한 후 해시 함수를 이용하여 조인하는 방식이다. 해시 조인이 내부적으로 어떻게 일어나는지는 이해하지 못하더라도, 과도한 중첩 루프 방식을 피하고자 할 때 특히 건수가 큰 테이블을 추출하여 건수가 작은 테이블과 조인시에는 훨씬 더 좋은 성능을 낸다. 단 해시 조인은 ‘=’ 조건일 때 가능하다.
SELECT /*+ USE_HASH(B A) FULL(B) FULL(A) */
A.고객번호, A.고객명, A.우편번호, B.우편번호 주소, A.나머지 주소
FROM 고객 A, 우편번호 B
WHERE A.우편번호 = B.우편번호;
또 한 가지 방법으로는 데이터베이스를 사용하지 말고 메모리에 사용정보를 올려서 이를 참조하는 방식이다. 조인되는 테이블이 많아 해시 조인으로도 목표 시간을 획득하기 힘들다면 자주 변하지 않는 정보에 한해 데이터베이스상의 정보를 프로세스 시작시에 메모리로 로드한 뒤 메모리를 참조하라는 뜻이다. 앞의 예와 같이 우편번호 테이블은 거의 변동이 없는 정보이다. 이 정보를 메모리에 올려 사용하더라도 데이터 무결성이 깨질 위험은 적다. 상품 정보, 요율 정보, 각종 코드 정보 등이 이에 해당한다.
하지만 이 방법은 무결성을 깨트릴 위험이 어쨌든 존재하므로 반드시 다른 업무팀과 협의하여 결정해야 하며, 대상 프로세스가 수행시에는 관련 정보를 수정하는 일이 없도록 해야 한다.
마지막 방법으로는 LOOP문 안에 SQL문 실행 횟수를 줄이는 좋은 아이디어를 짜내어야 한다. 예를 들어 지역별로 서버가 나눠진 경우라면 우편번호가 같은 고객이 연달아 읽혀질 가능성도 있다. 그렇다면 이전 고객에서 읽혀진 고객 우편번호와 지금 읽혀진 고객 우편번호가 같다면 굳이 우편번호 테이블을 또 읽으러 갈 이유가 없다. 이전 데이터를 메모리에 저장했다가 이 값을 적용하면 된다. 이는 예상 밖의 소득을 올릴 수 있다.
3. 몇천 만건은 속도가 빨리 나오는데 왜 겨우 100건 조회 시에는 느린가요
(전체 범위 처리와 부분 범위 처리의 고려)
“SQLPLUS에서 몇천 만 건 이상인 테이블을 조회하면 금방 결과가 화면에 나오는데 몇건 되지 않는 데이터는 왜 오래 걸리나요?”라고 질문을 하는 개발자가 많이 있다.
SELECT * FROM 통화내역 WHERE 발신지역 = ‘서울’;
SELECT 년월, SUM(금액) FROM 체납정보 GROUP BY 년월;
이것이 일반적으로 튜닝 책에서 말하는 부분 범위와 전체 범위를 뜻하는 것이다. 첫 번째 SQL문에서 통화내역은 몇억 건이 되지만 DB에서 읽은 결과를 그대로 화면에 나타내면 되기 때문에 일정 버퍼에 차면 출력한다. 두 번째 SQL문에서는 DB에서 읽은 결과를 그대로 화면에 출력하는 것이 아니라 GROUP BY와 SUM 작업 후 그 결과를 출력한다.
따라서 첫 번째 SQL문은 엔터 키를 치면 주르륵 화면에 디스플레이되고 두 번째 문장은 일정시간을 기다린 후 출력되는 것이다. 이는 튜닝에서 중요한 의미를 가진다. 특히 OLTP성 업무(온라인, 즉 화면 처리)에서 더욱 의미가 크다. 첫 번째 SQL문은 빨리 빨리 결과 값이 차례로 리턴되지만 완전히 전 데이터를 화면에 출력한다면 두 번째 SQL문보다 느리다. 첫 번째 같은 처리를 부분 범위 처리라 하고 두 번째 같은 처리를 전체 범위 처리라 부른다.
OLTP 업무는 한정된 화면, 많아야 만건 정도를 사용자가 온라인에서 처리를 하게 된다. 따라서 얼마나 빨리 사용자에게 결과를 처음 내보내 주어야 하는가가 튜닝 포인트다. 따라서 건수가 많은 경우에는 ‘NEXT’ 버튼을 이용하여 다음 결과부터 100건 정도씩 보여주면 된다. 우리나라 사용자는 무척 속도에 민감하다. 5초 10초가 넘어가면 엄청 짜증을 낸다. 30초 지나서 화면에 디스플레이된다면 사용자는 당장 개발자에게 시정을 명할 것이다. 많은 출력 데이터를 가지고 있더라도 화면 단위로 빠르게 처리를 해 나가야 한다.
반대로 배치성 업무는 주로 전산 작업자가 일정한 작업 시나리오로 작업을 한다. 화면에 나타나는 중간 값보다는 최종 작업 결과가 중요하다. 화면에 빨리 디스플레이된다고 빨리 끝나는 것은 절대 아니다. 이런 이유로 OLTP에서는 주로 중첩 루프 방식을 선호하고 배치 업무에서는 해시 조인 방식을 선호하게 된다. 해시 조인은 해시 테이블을 생성하기 때문에 처음 리턴하는 시간이 지연된다. 하지만 중첩 루프 방식은 해당 조건에 맞는 데이터를 차례차례 검색하기 때문에 일정 버퍼에 채우기까지 얼마의 시간이 소요되지 않는다. 예를 들어 고객 테이블과 사용요금 테이블이 있다면 온라인 업무에서는 다음과 같이 SQL문이 구사될 것이다.
SELECT /*+ USE_NL(A B) */ A.고객명, B.사용요금 FROM 고객 A, 요금 B
WHERE A.고객ID = B.고객 ID
이런 경우 B 테이블에 고객 ID라는 인덱스가 구성되어 고객 ID를 차례로 조인해 가면서 읽게 되며 일정 화면 버퍼에만 차면 결과를 리턴하는 것이다. 만약에 배치 프로그램이라면 앞서 말했듯이 중첩 루프 방식이 내부적인 DB작업이 부담이 더 많기 때문에 해시 조인으로 바꾸어 실행할 것이다.
SELECT /*+ USE_HASH(A B) */ A.고객명, B.사용요금 FROM 고객 A, 요금 B
WHERE A.고객ID = B.고객ID
이렇다면 고객 테이블을 해시 테이블로 만드는 동안은 화면에 아무것도 출력되지 않는다. 하지만 최종 결과는 해시 조인 방식이 훨씬 빠르다. 따라서 온라인 프로그램 튜닝시에는 화면 버퍼에 나타낼 부분을 어떻게 빠르게 할 것인가를 고민하여 되도록 부분 범위로 만들어 주는 연습을 많이 해야 하며, 배치 프로그램인 경우는 전체 완료시간 단축을 목표로 튜닝해야 한다. GROUP BY, ORDER BY로 되어 있는 문장은 그에 맞는 인덱스를 생성하여 별도의 정렬 작업으로 인해 전체 범위로 실행되는 것을 막아 줄 수 있다.
온라인 화면 설계시에도 PC 사양이 많이 좋아진 탓에 몇만 라인이 넘도록 화면 버퍼를 잡는 사람들도 많다. 이는 1000건 단위로 화면을 만들었을 때보다 속도가 떨어질 수밖에 없다. 필자는 어떤 경우 온라인 프로그램이 마치 출력 프로그램을 보는 듯한 느낌을 받을 때가 많다. 예를 들어 한 달 동안의 체납자를 조회하는 화면이 있었다. 한 달에 200만 건 정도의 체납자가 있는데 화면 조건에는 년 월 조건만 있었으며 한 화면에서 1만 건 정도씩 보여 주었던 것으로 생각난다. 그럼 이 사용자는 200번을 ‘NEXT’ 버튼을 보며 전 체납자를 조회하는 것인가?
이런 화면은 리포트로 출력하여 보관의 의미로만 하며 온라인 프로그램으로서의 존재 의미가 별로 없다. 특정 체납자를 찾기 위해 이 사람은 최대 200번의 ‘NEXT’ 버튼을 눌러야 한다. 이는 이 사람에게 필요 없는 정보를 제공하면서 귀중한 데이터베이스 자원을 낭비하고 있는 것이다. 주민등록번호를 입력하면 특정 체납자를 찾도록 해 주든지, 체납 금액이 얼마 이상인 고객을 찾는다든지 이런 주요 조건을 가지고 찾을 수 있어야 한다.
그렇다면 “이런 주민등록번호라든지, 체납금액, 체납 횟수 등에 대해 조회를 하기 위해 모든 컬럼에 인덱스를 만들어야 하는가?”라는 질문이 나올 수 있다. 원칙적으로 이 프로그램만을 위해서는 ‘YES’가 정답이다. 하지만 인덱스 하나를 만들면 이 프로그램의 속도를 개선할지 몰라도 이 체납 테이블을 생성하는 프로세스는 그 이상 느려진다.
이 문제가 튜닝시 자주 부딪히게 된다. 조회를 빠르게 하자니 생성 프로세스가 느려지고 생성 프로세스를 빠르게 하자니 인덱스를 포기하게 되고. 아마 튜닝 담당자들이 가장 애를 먹는 부분이 아닐까 싶다. 하지만 왕도는 없다. ‘둘 중 하나는 포기하라’가 답이다. 포기의 조건은 업무의 중요성과 사용 빈도가 기준이 된다. 하나를 포기한 다음 최소의 피해가 가는 방향을 선택해야 한다. 매우 어려운 선택이며 오랜 경험이 필요한 부분이다.
4. 데이터 건수가 많아서 도저히 속도 개선이 힘들다?
진짜 무지막지하게 큰 데이터를 가진 테이블끼리의 조인은 풀 스캔에 해시 조인 함수를 쓰더라도 힘든 경우가 많다. 이런 경우에는 패러럴(PARALLEL) 옵션을 힌트 절에 추가해 속도를 개선하는 것이 가장 손쉬운 튜닝 법이다. 이래도 안 된다면 프로세스를 잘게 나누어 병렬 처리를 해야 하는데 이는 프로그램을 수정해야 하므로 부담이 많이 되는 작업이다. 일단 패러럴 옵션을 사용하기 위해서는 힌트에 다음과 같이 써주면 된다.
/*+ PARALLEL(테이블 명 패러럴 서버 개수) */
예를 들어 TAB1, TAB2, TAB3라는 테이블을 서로 조인하여 PARALLEL OPTION을 주게 되면 다음과 같다.
SELECT /*+ PARALLEL(A 5) PARALLEL(B 5) PARALLEL(C 5) */ * FROM TAB1 A, TAB2 B, TAB3 C
WHERE A.COL1 = B.COL1 AND
B.COL1 = C.COL1;
힌트 절에 되도록 패러럴 옵션 수를 같이 맞추어 주면 성능을 향상시킬 수 있다. 여러분이 패러럴 옵션을 사용하는 경우 플랜 결과를 보면 PARALLEL-TO-PARALLEL, SERIAL-TO-PARALLEL, PARALLEL-TO-PARALLEL이라는 것을 발견한다. PARALLEL-TO-PARALLEL이라는 것이 나타날 때 가장 좋은 성능을 발휘한다. 우선순위에서 읽은 결과를 다음 병렬 프로세서로 계속적으로 넘기기 때문에 좋은 결과를 넘기는 것이다. 만약 두 개 이상의 테이블을 조인시에 최적의 성능을 내기 위해서는 되도록 같은 수를 지정해 주어라.
그러나 패러럴 옵션은 과도한 CPU 부하를 가지고 올 수 있다. CPU 개수를 초과하는 패러럴 옵션은 별로 도움이 안 된다. 그러므로 프로세스를 빠르게 수행하고자 하는 욕심에 병렬 프로세서를 너무 많이 띄운다면 다른 프로세서에 방해가 되며, 자신의 프로세서에도 결코 도움이 되지 않는다.
그리고 DDL 문장에 패러럴 옵션을 사용한다면 해당 테이블 스페이스의 디폴트 INITIAL_EXTENT, NEXT_EXTENT 의 스토리지 절에 유의해야 한다. 병렬 프로세서가 초기에 이 스토리지 절의 옵션을 그대로 적용하기 때문이다.
5. DB가 문제인가요? 내 SQL문이 문제인가요?
개발자들은 프로세스가 수행이 느려지면 먼저 서버나 데이터베이스의 성능을 의심하게 된다. 그러나 서버가 계획 하에 도입되었다면 그럴 가능성은 희박하다. 가장 확실한 방법은 오라클의 성능 뷰를 조회를 통해 알 수 있지만 다음 회에 다루기로 하고 SQL이 문제인지 데이터베이스내의 문제인지를 쉽게 판별해 보기로 하자. 간단하다.
데이터베이스에 문제가 발생한 것이라면 SQLPLUS가 제대로 접속되지 않거나 엄청 느리게 접속된다. 또한 ps ?ef|grep 프로그램명을 치면 숫자가 두 개 나오는 데 서버에서 SQL*NET을 통하지 않은 경우 뒷 숫자가 해당 프로그램과 연결된 오라클 서버 프로세스 번호이다.
Ps ?ef|grep 프로세스 ID를 하면 데이터베이스와 연관된 작업을 하고 있을 경우 분명 CPU 점유율 또는 사용량이 변하기 마련이다. 이 숫자가 변하고 있는데 느리다면 분명 자신의 SQL문이 뭔가 문제가 있다고 보고 플랜을 작성해 분석해 봐야 한다.
현재 프로그램을 수행 중이어서 어떤 SQL문이 수행되는지 모른다면 다음의 SQL문을 실행해 보기 바란다. 먼저 v$session이라는 성능 뷰에서 자신의 프로세스에 해당하는 SID를 찾은 다음(권한이 없다거나 잘 모른다면 다음에 자세히 설명할 테이니 DBA의 도움을 받을 것) 해당 SQL을 실행하고 SQL문을 수행해 보아라. 자신이 마지막으로 수행한 SQL문이 보일 것이다. 이를 반복하면 수행이 오래 걸리는 SQL문이 쉽게 보일 것이다. 튜닝 툴을 이용하면 더욱 쉽게 알 수 있다.
col piece for 999
col sql_text for a85
select a.piece,b.osuser,b.process,a.sql_text
from v$sqltext_with_newlines a, v$session b
where a.address=b.sql_address and b.sid=&SID
order by 1 asc
/
만약 변하고 있지 않다면 LOCK을 의심해 보고 다음의 SQL문을 실행시켜 보길 바란다.
[리스트 2] 제목
column username format a10
column sid format 999
column lock_type format a25
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
select
a.sid,
decode(a.type,
''MR'', ''Media Recovery'',
''RT'', ''Redo Thread'',
''UN'', ''User Name'',
''TX'', ''Transaction'',
''TM'', ''DML'',
''UL'', ''PL/SQL User Lock'',
''DX'', ''Distributed Xaction'',
''CF'', ''Control File'',
''IS'', ''Instance State'',
''FS'', ''File Set'',
''IR'', ''Instance Recovery'',
''ST'', ''Disk Space Transaction'',
''IR'', ''Instance Recovery'',
''ST'', ''Disk Space Transaction'',
''TS'', ''Temp Segment'',
''IV'', ''Library Cache Invalidation'',
''LS'', ''Log Start or Switch'',
''RW'', ''Row Wait'',
''SQ'', ''Sequence Number'',
''TE'', ''Extend Table'',
''TT'', ''Temp Table'',
a.type) lock_type,
decode(a.lmode,
0, ''None'', /* Mon Lock equivalent */
1, ''Null'', /* N */
2, ''Row-S (SS)'', /* L */
3, ''Row-X (SX)'', /* R */
4, ''Share'', /* S */
5, ''S/Row-X (SSX)'', /* C */
6, ''Exclusive'', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, ''None'', /* Mon Lock equivalent */
1, ''Null'', /* N */
2, ''Row-S (SS)'', /* L */
3, ''Row-X (SX)'', /* R */
4, ''Share'', /* S */
5, ''S/Row-X (SSX)'', /* C */
6, ''Exclusive'', /* X */
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
where a.type not in (''MR'', ''DM'', ''RT'')
and (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0)
order by 5,6
/
이 SQL문을 수행한다면 다른 프로세스의 영향으로 LOCK이 걸린 것을 발견할 수 있다. 만약 데이터베이스에 문제가 있다면 [리스트 2]의 두 SQL문은 조회조차 되지 않는다. 이제 느리다고 다른 사람의 프로세스 탓이라든지, 데이터베이스가 느리다든지 하는 오류는 범하지 않길 바란다.
6. 온라인 프로그램에서 목표 시간내 프로그램이 수행되지 않아요
정신없이 설명하다 보니 잠시 머리를 식히라는 의미에서 적었다. 온라인 프로그램인 경우 조회 조건이 까다롭고 건수가 많은 테이블은 속도를 줄이는데 한계가 있다. 심지어 일부 프로젝트에서는 ‘온라인 프로그램 수행 목표시간을 3초 이내’라고 절대적인 수치를 정해 놓고 무조건 시간 초과 시에는 불합격을 선언하기도 한다. 하지만 필자는 이런 경우도 무난히 통과했다.
대개 튜닝을 해도 안 되는 조회 화면은 대개 건수가 많은 테이블을 다양한 검색 조건을 통해 분석용 프로그램이 많다. 이는 특정 사용자가 일 몇회 미만으로 사용하는 것이 대부분이다. 이를 억지로 튜닝하느라 다른 프로세스를 추가하거나 인덱스를 추가한다면 이는 득보다 실이 많다.
온라인 프로그램은 사용자와의 대화이다. 이들이 갑갑하게 여기는 것은 엔터를 친 다음 PC가 다운된 것처럼 아무런 메시지 없이 마우스의 모래시계만 돌다가 수십 초 경과 후 조회되는 것이다. 그리고 건수가 많은 테이블을 분석용으로 사용하는 것은 월 1～2회의 한정된 사용자만이 사용한다. 이런 프로그램은 조회 버튼을 누르자마자 ‘잠시만 기다리세요’라는 메시지 박스를 띄워라. 한 메시지 박스를 띄우는 것만으로는 부족하다. 왜냐하면 또 화면이 가만히 있을 테니까. ‘데이터를 처리 중입니다’ 계속 3초 간격으로 번갈아 메시지를 보여주라. 남은 시간을 보여주거나 남은 처리 건수를 보여준다면 더욱 효과적이다. 물론 증권회사의 주문 등록 같은 온라인 프로그램을 이렇게 해서는 안 된다. 하지만 입찰 안내서나 요구사항이 명시되어 절대적인 기준을 삼거나 사용자가 프로그램이 느리다고 불평한다면 이렇게 해보라. 신기하게도 사용자는 아무 말 안한다.
7. 패러럴 옵션을 주었는데 속도가 나지 않아요
이론상으로는 알고 있었지만 언뜻 상황이 되면 떠오르지 않는 때가 있다. 데이터 이행을 하였을 때 경험한 것이다. 백업 서버에서 몇일 간을 테스트하면서 하루 이상 걸리던 프로세스를 2시간 이내로 줄이는데 성공을 했다. 실행서버에서 새로 유저를 만들어 프로젝트 오픈 몇 일전 최종 리허설을 하게 되었다. 그런데 이게 웬일인가. 2시간이면 끝났던 프로세스가 5시간을 넘어도 끝나지 않는 것이었다. 모니터링 해보니 패러럴 옵션이 먹히지 않은 것이었다. “실행 서버는 CPU도 백업 서버보다 많은데…” 문제는 MAX_PARALLEL_SERVER 개수가 백업 서버보다 작게 잡혀져 있었다. 공교롭게도 실행 서버에서 다른 프로세서는 다 중단시켰는데 데이터 백업 프로세서가 수행되면서 10개 정도를 사용하고 있었던 것이다. 패러럴 옵션은 앞에서 말한대로 CPU 개수를 초과하면 그리 효력이 없다. 또한 총 수행되는 패러럴 서버 개수가 MAX_PARALLEL_SERVER 개수를 초과한다면 병렬 서버는 수행되지 않는다. 만약 이것이 실제 이행시 일어났다면 분명 필자는 해고되었을 것이다. 문제는 예기치 않은 곳에서 특히 자신하고 체크하지 않는 부분에서 일어난다는 사실을 기억하기 바란다.
8. 예전에는 빨리 수행되었는데 지금은 속도가 안나요
인덱스의 추가나 변경
프로그램이 작성된 이후 신규로 추가되거나 컬럼이 변경된 인덱스가 영향을 미치는 것이다. 예를 들어 WHERE 고객ID LIKE ‘200310% AND 고객유형 = ‘기업’이란 조건 절이 있다면 이 SQL문이 작성되기 전에 없었던 고객 유형이 첫 컬럼으로 시작하는 인덱스가 신규로 추가되었다고 가정하자. 이런 경우 ‘=’ 조건이 우선할 수 있으므로 고객 ID를 컬럼으로 한 인덱스를 사용하다가 고객 유형으로 시작하는 인덱스를 사용하도록 바뀔 수 있다.
그러므로 전체 프로젝트의 관점에서 볼 때 자기 혼자서 잘한다고 되는 것이 아니다.
다른 팀과 공유해서 사용하는 테이블에 대해서는 스키마 변동 내역을 반드시 공유해야 한다.
데이터 건수 증가, 분포도 변경
이는 개발환경에서는 몇 건 안 되는 시험용 데이터를 사용하며, 프로젝트 초기에는 데이터가 많이 누적되지 않아 시간이 지나면 지날수록 속도가 점점 저하되어 나중엔 하드웨어 증설이라든지 대대적인 애플리케이션 튜닝을 하는 경우가 많다. 이는 정말로 심각한 문제다.
이는 각각의 테이블에 대해 디스크 보관 주기라는 개념이 없이 계속 데이터를 누적해 나가는 현실과도 무관하지 않다. 업무 설계시 시간 개념과 마감 개념이 도입되지 않는다면 이 문제는 쉽사리 해결되지 않는다. 즉, 장기간 보관해서 사용할 데이터를 따로 어떻게 관리할 것인지 전략을 수립해야 한다. 통계 테이블을 만들고 일정 기간이 지난 상세 테이블은 따로 백업 테이블로 옮긴다든지 하여 현재 업무를 진행하고 있는 데이터가 과거 데이터와 같이 섞여 있어 현재 업무가 지연되는 것을 막아야 한다.
예를 들어 매출 테이블을 들자면 최근 3년치만 보관하고 3년이 지난 데이터는 백업 매출 데이터로 옮겨라. 그리고 장기 분석하는 데이터를 분석하는 프로그램은 실제 매출 테이블과 백업 매출 테이블을 묶어 조회하도록 해라. 분석은 어느 정도 시간이 걸려도 크게 문제가 안 되지만 실제 이번 달 매출을 처리하기 위한 테이블은 훨씬 가벼워지기 때문에 상당한 성능 개선효과가 나타난다.
9. 파티션 테이블의 사용
건수가 많은 테이블은 파티션 테이블을 고려해 주어야 한다. 파티션 테이블은 오라클 8 버전부터 지원된다. 필자는 오라클이 파티션 테이블 기능으로 인하여 명실상부하게 대용량 데이터베이스의 발판을 이룩하였다고 본다. 파티션 테이블은 테이블을 파티션이란 개념으로 쪼개어 놓아 WHERE 조건절을 해석하여 해당 조건에 맞는 파티션만 액세스할 수 있도록 한 것이다. 따라서 8번에서 기술한 것처럼 굳이 테이블을 나누지 않아도 파티션 기능을 이용한다면 효과적으로 최신 정보에 대한 관리가 가능하다.
[그림 3] 파티션테이블의 사용
파티션 테이블을 사용함으로써 얻는 큰 이점 중 하나는 작업을 병렬처리 할 수 있다는 것이다. 특히 이행이나 큰 배치 작업시 파티션 별로 프로세스를 동시에 수행하여 효과를 볼 수 있다는 것이다.
파티션 테이블을 만드는 방법은 ADMINISTRATOR’S GUIDE를 참고하기 바란다. 특히 EXCHANGE PARATITION 이란 기능은 큰 데이터를 지닌 테이블을 관리하는 DBA라면 눈여겨 보길 바란다.
10. 이론은 알겠는데 막상 닥치면 어떻게 해결해야 할까요
튜닝에 관심이 있었던 독자들은 아마 필자가 기술한 내용을 다른 책에서도 많이 봐왔을 것이다. 하지만 실제적으로 OCP에 튜닝 과목이 있지만 제대로 튜닝을 할 줄 모르는 DBA가 많다는 것은 실제 닥쳐서 문제를 해결할 수 있는 능력을 키우는 것이 쉽지 않다는 것을 의미한다.
아직 실전을 통해 튜닝 마인드를 쌓아야 한다. 앞에서 필자가 나열한 내용을 자세히 살펴보라. 그럼 공통적인 사항이 도출될 것이다. 필자는 튜닝 비법이라면 두 가지의 측면에서 계속적인 시뮬레이션을 해 본다는 것이다.
(1) 적게 읽거나 적게 실행되도록 노력
=> 안 읽어도 될 부분이 읽혀지고 있지는 않은가?
=> 결과와 상관없는 데이터가 조인되고 있지는 않은가?
=> 한번 읽은 것을 또 읽은 것이 없는가?
=> LOOP문 안에서의 SQL문 실행 횟수 제거
=> 멀티 블록 액세스(풀 스캔)과 인덱스의 부분 액세스의 대비 분석
=> 중첩 루프 방식과 해시 조인 중 어느 것이 나은가?
(2) 패러럴(병렬) 처리 기법을 반드시 활용
=> 패러럴 옵션의 적절한 활용
=> 프로세스 수행 단위를 최소화하여 병렬 처리가 가능토록 조정
=> THREAD 기법을 활용한 병렬 프로그래밍 기법 향상
=> 작업 스케쥴 수립을 통하여 누수 시간을 방지
즉, 이론을 고민하지 말고 두 가지 원칙에 의거하여 데이터의 분포도를 파악하고 플랜을 작성하면서 어떤 경우의 수가 데이터를 적게 읽히는가? 어떤 경우의 수가 데이터를 한꺼번에 처리할 수 있는가? 어떤 경우의 수가 내부적인 실행 횟수를 줄이는가를 고민해 보길 바란다.
그리고 많은 데이터가 있을 경우에는 되도록 병렬 프로세서(PARALLEL SERVER)를 활용하든 프로그램을 병렬로 수행하든 병렬 처리 기법을 익혀야 한다.
성능 저하 요인을 찾아 보자
대략의 10가지 유형을 가지고 개발자가 범하기 쉬운 오류나 자주 들어왔던 질문을 중심으로 여러분과 같이 고민해 보았다. 결론적으로 SQL 튜닝은 온라인 프로그램에서는 되도록 적게 읽혀지고 조인되는 횟수를 줄이도록 노력하며, 배치에서는 되도록 멀티블럭으로 처리하고 병렬로 처리해 주는 노력을 해주는 것이라 보면 된다. “적게 읽자, 적게 조인하자, 쓸데없는 부분 읽지 말자, 멀티블럭 활용하자, 병렬처리하자” 머리에 새겨야 한다.
처음에는 이런 방법으로 액세스 방식을 떠올리고 하나하나 자신이 DB 엔진이 되어 어떤 것이 효율적인가를 플랜을 작성하면서 연습해야 한다. 마지막 회(5회)에 종합 편으로 오늘 기술한 내용에 대해 실제로 많은 데이터를 발생시켜 실습해 볼 것이다. 다음 시간에는 성능 뷰를 통하여 성능저하 요인을 찾는 연습을 해보기로 하자.
옵티마이저 활용 예제
이제부터 여러분이 옵티마이저라고 생각하고 다음의 활용 예제를 판단해 보자. 모든 문제를 작게 읽고 조인 횟수를 줄이며, 멀티블럭 READ, 병렬 처리의 관점에서 어느 것이 유리한지를 판단하고 접근해야 한다.
활용 1 : 읽혀지는 횟수, 반복적인 READ 없애기
예를 들어 같은 건수의 부서와 사원 테이블이 있다. 부서 테이블은 인덱스가 있고 사원 테이블은 인덱스가 없다. 이를 중첩 루프 방식으로 조인할 때 어느 테이블부터 읽는 것이 빠르겠는가?
SELECT 사원명, 부서명 FROM 사원, 부서 WHERE 사원.부서 코드 = 부서.부서 코드 [표 1]의 박스 안에 있는 것이 매번 반복 실행된다. 어느 것이 빠르겠는가? 한 부서 당 1000건 되는 데이터를 전부 다 읽어 조인하는 것보다는 당연히 한건씩 정확하게 찾아올 수 있는 1안이 빠를 것이다(온라인). 1안을 개선한다면 전체-전체 테이블이 조인되므로 멀티블럭 액세스를 하는 풀 스캔을 활용한 해시 조인이 더 효율적일 것이다(배치).
[표1] 인텍스 사용의 비교 예 활용 2 : 조인 횟수 줄이기
SELECT A.EMPNAME, SUM(B.SALARY) FROM EMP A, SALARY B
WHERE A.empno = B.empno AND b.date between ‘20010101’ AND ‘20011231’ GROUP BY A.EMPNAME 이 SQL문에는 무슨 문제가 있는가? 이는 조인 횟수를 줄일 수 있는 요소가 있는데 간과한 경우이다.
SELECT /*+ ORDERED */ A.EMP_NAME, B.SALARY
FROM (SELECT EMPNO, SUM(SALARY) FROM SALARY WHERE DATE BETWEEN ‘20010101’ AND ‘20011131’
GROUP BY ENPNO) B,
EMP A
WHERE A.empno = B.empno;
먼저 SUM을 한 다음 그 결과를 가지고 조인이 일어나기 때문에 조인 횟수가 많이 줄어든다.
활용 3 : UPDATE문에서의 조인
UPDATE문은 데이터를 변경하기 때문에 락(Lock)을 걸고 작업을 하게 된다. 그렇기 때문에 UPDATE SQL문을 잘 못 사용하면 완료가 될 때까지 다른 프로세스가 수행되지 못하는 결과를 초래한다.
UPDATE TAB1 A SET COL2 = (SELECT COL2 FROM TAB2 WHERE COL1 = A.COL1)
WHERE COL1 IN (SELECT COL1 FROM TAB2) 이와 같이 조인을 이용한 UPDATE문은 두 가지의 인라인 뷰(InLine-View)를 가지는 경우가 많다. 이런 경우 WHERE절은 데이터의 분포도에 따라 인덱스를 활용한 중첩 루프 방식으로 가든 아니면 해시 조인 방식으로 경우에 따라 조절해 주어야 하고, SET절의 TAB2 테이블을 읽을 시에는 반드시 인덱스를 활용한 중첩 루프 방식의 조인 방법을 택해 주어야 한다.
UPDATE문은 WHERE절에서 해당하는 로우(ROW)를 가지고 와서 한 건마다 SET절 안의 SQL문을 수행하기 때문이다. 따라서 앞의 경우 반드시 TAB2에는 COL1을 제1 컬럼으로 하는 인덱스가 있어야 한다.
그 외 프로그램을 느리게 하는 요인
- 과도한 DECODE 등의 시스템 함수나 유저가 정의한 함수를 사용
DECODE(DECODE(DECODE………………) 등의 함수를 많은 데이터를 처리하는 SQL문에 사용하거나 유저가 FUNCTION을 작성하여 이를 SQL문에서 호출한 경우 함수에 있는 SQL문은 반드시 튜닝되어 있어야 한다.
- 데이터 존재 유무 확인을 위해 조건에 해당하는 건수를 모두 세는 행위
SELECT COUNT(*) FROM TAB1 WHERE COL1 = ‘조건’
=> SELECT ‘X’ FROM TAB1 WHERE COL1 = ‘조건’ AND ROWNUM = 1 (한건만 세는 것으로 바꿈, EXISTS 문장을 써도 무방)
- 불필요한 I/O를 줄이기
테스트 단계의 디버그나 로그는 실행시에는 필수적인 것만 남길 것. 특히 printf문
- 사소한 오류나 임의의 오류는 일단 건너뛰고 다시 처리할 것
무슨 조건만 안 맞으면 무조건 프로그램을 중단시켜 롤백시키는 경우가 있다. 해당 부모 테이블에서 데이터를 못 찾은 경우, INSERT시 데이터가 중복된 경우 등 어떤 특이한 경우로 발생되는 오류는 따로 로깅 파일에 쌓아 분석하게 하고 연속적으로 이런 오류가 나오지 않는다면 프로그램은 일단 정상 완료시켜야 좋다.
1000만건 이상을 처리하는 프로그램에서 막판에 약간 잘못된 데이터가 있어 3건 정도가 중복돼서 전체 데이터를 롤백 처리한다면 엄청난 시간적인 손실이다. 일단 3건을 로깅하고 프로그램을 정상 종료시킨 다음 로깅 파일을 보고 별도처리를 하면 된다. 하지만 중복이 계속적으로, 예를 들어 연속적으로 1000건 이상 중복이 일어난다면 무엇인가 잘못 처리되고 있는 경우이므로 이런 경우는 프로그램을 중단시키면 된다.
- PRO*C를 사용하는 경우 ARRAY 프로세싱 프로그래밍을 해야 한다. I/O 횟수를 줄여주기 때문에 한건씩 처리하는 것 보다 몇 배의 시간이 단축된다. 2시간의 작업시간이 10분 이내로 줄어든다. 반드시 책을 보고 이 부분은 따로 익히기를 바란다.
참고 : 힌트에 대한 활용
자신이 뜻하는 대로 옵티마이저는 움직여주지 않는다. 이런 때에는 적절한 힌트를 구사한 후 플랜을 작성하여 자신이 원하는 대로 SQL 구문이 해석되었는지를 검증해야 한다. 다음에 자주 쓰이는 힌트를 모아 놓았다. 꼭 알아야 할 힌트는 굵게 표시하였으니 꼭 알아두길 바란다.
- Optimizer 모드에 대한 힌트
-. RULE
룰 기반 옵티마이저
-. CHOOSE
주로 비용 기반을 유도하고자 할 때 사용
-. ALL_ROWS
전체 범위로 처리
-. FIRST_ROWS
부분 범위로 처리
- ACCESS PATH에 대한 힌트
-. FULL : /*+ FULL (테이블명) */
-. ROWID : /*+ ROWID (테이블명) */
-. CLUSTER : /*+ CLUSTER (테이블명) */ CLUSTER에 사용
-. HASH : /*+ HASH (테이블명) */ CLUSTER에 사용
-. HASH_AJ : /*+ HASH_AJ */ Not IN시 사용
-. HASH_SJ : /*+ HASH_SJ (테이블명) */ EXISTS
-. INDEX : /*INDEX (테이블명, 인덱스명1, 인덱스명2…) */
-. INDEX_ASC : /*+INDEX_ASC (테이블명, 인덱스명) */
-. INDEX_DESC : /*+INDEX_DESC (테이블명, 인덱스명) */
-. INDEX_FFS : /*+INDEX_FFS (테이블명, 인덱스명) */
-. MERGE_AJ : /*+MERGE_AJ */ NOT IN시 사용
-. MERGE_SJ : /*+MERGE_SJ */ EXISTS시 사용
-. AND_EQUAL : /*+AND_EQUAL (테이블명, 인덱스명, 인덱스명….) */
-. USE_CONCAT : /*+USE_CONCAT */ OR조건을 UNION ALL로
- JOIN 순서에 대한 힌트
-. ORDERED : /*+ORDERED */ FROM 절의 순서대로 테이블을 읽음. 반드시 WHERE절을 맞춰주어야 함
- JOIN 연산에 대한 힌트
-. USE_NL : /*+USE_NL(테이블명,테이블명,..) */
중첩 루프 조인 방식
-. USE_MERGE : /*+USE_MERGE(테이블명,테이블명,..) */
정렬 병합 조인 방식
-. USE_HASH : /*+USE_HASH(테이블명,테이블명,..) */
해시 조인 방식
-. DRIVING_SITE : /*+DRIVING_SITE (테이블명,테이블명,..) */
원격지 DB에 있는 테이블과 조인시
- PARALLEL에 대한 힌트
-. PARALLEL : /*+PARALLEL (테이블명,숫자1,숫자2) */
-. NOPARALLEL : /*+NOPARALLEL (테이블명) */
-. APPEND : INSERT /*+APPEND [PARALLEL..] */
빠른 INSERT시 사용, 중단시 인덱스 깨짐
-. NOAPPEND : INSERT /*+NOAPPEND */
-. PARALLEL_INDEX : /*+PARALLEL_INDEX (테이블,
-. NOPARALLEL_INDEX : /*+NOPARALLEL_INDEX (테이블, 인덱스)*/
- 기타
-. CACHE : /*+CACHE (테이블명) */
-. NOCACHE : /*+NOCACHE (테이블명) */
-. MERGE : /*+MERGE (테이블명) */
-. NO_MERGE : /*+NO_MERGE (테이블명) */
-. PUSH_JOIN_PRED : /*+PUSH_JOIN_PRED (테이블명) */
(PUSH_JOIN_PREDICATE 파라미터 FALSE 설정)
-. NO_PUSH_JOIN_PRED : /*+NO_PUSH_JOIN_PRED (테이블명) */
(PUSH_JOIN_PREDICATE 파라미터 TRUE 설정)
-. PUSH_SUBQ : /*+PUSH_SUBQ */ |
