다운로드 : http://info.scratch.mit.edu/Scratch_1.4_Download
Scratch is a new programming language that makes it easy to create your own interactive stories, animations, games, music, and art -- and share your creations on the web.
‘스크래치’는 한마디로 ‘비주얼 프로그래밍 플랫폼’이다. MIT 미디어랩 평생유치원 그룹(Lifelong Kindergarten Group)이 개발해 2007년 5월15일 처음 공개했다.
스크래치로 만든 ‘작품’들은 웹사이트에 올려 전세계 커뮤니티와 공유된다. 작품을 완성한 뒤 ‘공유’(Share) 버튼만 누르면 웹사이트에 등록되고 소스코드까지 공개된다. 모든 작품들은 ‘저작자 표시-동일조건 변경허용‘(BY-SA)이란 CCL 조건을 따른다. 프로그램 저작자를 밝히고 자유롭게 소스코드를 바꿀 수 있도록 허용하는 조건으로 누구나 저작권 부담 없이 프로그램을 가져다 쓸 수 있다는 얘기다. 지구촌 어딘가, 얼굴도 모르는 누군가가 만든 작품을 가져다 내가 만든 작품과 뒤섞어 전혀 새로운 프로그램을 만들어도 좋다. 현재 40만개가 넘는 프로젝트가 스크래치 웹사이트에 등록돼 있다.
MIT 미디어랩은 스크래치가 공개된 날인 5월15일을 ‘스크래치데이‘로 지정해 해마다 기념 행사를 열고 있다. 스크래치데이는 지구촌 스크래치 이용자들이 함께 참여해 스크래치 경험을 나누고 배우는 축제다. 한국에서도 5월16일(한국시각) 오전 10시부터 종로 아트센터 나비에서 ‘스크래치데이 인 서울‘ 행사가 열린다.
개발자가 범하기 쉬운 튜닝 이슈 10가지 유형 분석 박 성 우 dont4get@chol.com
현영씨스템즈와 LG-CNS를 거쳐 현재는 수로텍 연구소 팀장으로 일하고 있다. 태평양 판매 물류 개발, KT-ICIS 요금관리시스템 프로젝트 DBA, KTF-차세대 빌링 프로젝트 DBA, 통합 하수관거 유지관리 SW 개발 팀장으로 활동했다.
지난 2회에 걸쳐 기본을 튼튼히 하자는 의미에서 쉬운 예제를 중심으로 주로 플랜(PLAN)을 작성하고 해독하는 데 주력했다. 지난 강좌만 자기 것으로 만들었다면 적어도 튜닝 부문에서는 웬만한 평범한 DBA는 능가할 수 있다. 이번에는 지금까지 개발자들이 자신의 프로그램이 느리다고 찾아오는 가장 많은 유형을 10가지 정도로 요약해 설명하고자 한다. 필자가 10년 동안 DBA 업무를 하면서 이 10가지 유형을 제외하고 느린 경우는 10% 미만이었다. 10% 미만은 프로그램 구조 조정 및 프로세스의 조정을 통한 대대적인 튜닝 작업이 이뤄져야 한다. 이런 경우는 진짜 튜닝 전문가에게 맡겨라. 90%만 개발자들이 신경써서 자신의 프로그램을 튜닝한다면 프로젝트에서 엄청남 효과가 나타난다.
2회분 줄거리
지난 회를 간단하게 꼭 알아야 할 부분만 요약해 본다. 몇 번을 강조해도 지나침이 없는 부분이다.
1회 옵티마이저에는 룰 기반과 비용 기반의 옵티마이저가 있으며 플랜 작성법에는 다음의 네 가지가 있다. 1. SQLPLUS의 Autotrace 기능 활용 2. EXPLAIN 명령과 플랜 테이블 조회 3. tkprof 유틸리티 활용 방법 : 가장 자세히 볼 수 있는 방법 4. 튜닝 툴을 통해 조회하는 방법
2회 튜닝은 프로그램을 빠르게 하기 위한 튜닝 전문가의 노력이 아닌 제한된 자원으로 설정된 목표 시간 내에 정확한 결과를 지속적, 안정적으로 내기 위한 프로그래머의 모든 노력이다. 따라서 정확성, 신속성, 운영의 편이성, 장애 대처성, 자원 활용성, 가독성, 이식성의 모든 측면이 고려돼야 한다. 그리고 룰 기반 옵티마이저의 우선 순위에 따라 접근 경로를 플랜 작성을 위주로 추적하여 보았다.
1. 인덱스를 왜 안 타나요 튜닝에서 역시 가장 많은 문제를 일으키는 것은 인덱스의 잘못된 활용이다. 또한 개발자들 대부분 느리다고 생각되면 인덱스를 잘 사용하고 있는지를 검토한다. 인덱스를 사용한다고 무조건 빨라지는 것은 아니지만 인덱스의 올바른 활용은 SQL 튜닝의 첫 시작임은 분명하다. 특히 OLTP성 업무에서는 인덱스 활용이 중요하다. 하나씩 경우의 수를 나열해 보자. 인덱스가 존재하지 않는 경우(인덱스 구조를 볼 줄 모르는 사람을 위해) 우문현답이라고 할까? 인덱스가 존재하지 않으면 당연히 인덱스를 타지 않는다. 하지만 많은 개발자는 해당 테이블에 인덱스가 존재하는지, 어떤 컬럼으로 구성되어 있는지 알지 못한다. 심지어 OCP 자격증을 가진 개발자도 이런 경우가 있다. 지난 회에 간략하게 설명하였지만 USER_INDEXES, USER_IND_COLUMNS라는 딕셔너리에 해당 계정에서 만든 인덱스 및 인덱스 컬럼을 조회할 수 있다. 여기까지는 개발자들이 대부분 알 것이라 생각한다.
[화면 1]자신의 계정에 생성된 테이블의 인덱스를 조회하는 화면 하지만 대부분 프로젝트에서 개발자 계정에 테이블을 만들어 주는 경우는 없다. DBA 계정에 테이블을 만들고 시노님(synonym)이나 뷰(view)를 통해 개발자에게 권한을 주게 된다. SCOTT 말고 SYSTEM 계정으로 로긴한 다음 다음과 같이 실행해 보자.
[화면 2] 다른 계정에서 시노님으로 연결되는 예제 화면
이 다음 SELECT * FROM EMP;를 실행하면 이전과 결과는 같을 것이다. 하지만 SELECT * FROM USER_INDEXES WHERE TABLE_NAME = ‘EMP’하면 아무 인덱스도 없다고 나올 것이다. 물론 SELECT * FROM USER_TABLES WHERE TABLE_NAME = ‘EMP’해도 테이블은 없을 것이다. [화면 2]의 작업을 본인이 하지 않았을 경우에 어떤 계정의 테이블로 시노님이 생성되어 있는지 모르는 개발자가 의외로 많다. 일단 SELECT OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_NAME = ‘EMP’하여 EMP가 테이블인지 뷰인지 시노님인지를 확인한 후 이에 해당하는 테이블을 찾아가면 된다. 시노님일 경우에는 SELECT TABLE_OWNER, TABLE_NAME FROM USER_SYNONYMS WHERE SYNONYM_NAME = ‘EMP’라고 해서 해당 테이블의 오너와 테이블명을 찾으면 된다. 만약에 시노님이 PUBLIC으로 되어 있으면 ALL_SYNONYMS를 찾아 오너가 ‘PUBLIC’인 것을 찾으면 된다.
인덱스가 존재하는데도 인덱스를 활용 못하는 경우
첫 번째 컬럼에 조건을 주지 않은 경우 지난 호에도 설명했듯 첫 번째 컬럼에 = 또는 LILE, > 조건 등을 기술해야 한다. 10개 컬럼으로 구성된 결합 인덱스가 있어 9개 컬럼에 = 조건을 주더라도 첫 번째 컬럼 조건이 빠진다면 이 인덱스는 있으나마나 한 것이다. 인덱스란 B-트리 형태로 정렬된 순서를 가지고 있다. 이 순서를 가지고 액세스를 하여 해당 범위가 초과되면 검색을 중단한다. 그러므로 인덱스를 구성하는 컬럼 순서대로 조건이 많이 주어질수록 좋은 결과를 낸다. 중간에 하나라도 빠지게 되면 뒤 순서의 조건은 인덱스와는 무관하게 체크 조건이 될 뿐이다. 인덱스를 통해 검색 조건을 줄여주는 컬럼을 ‘인덱스 활용 컬럼’이라 하고, 검색 조건을 줄여주지 못하고 데이터를 추출하는 체크 조건만 될 때 이를 ‘데이터 체크 컬럼’이라 정의한다([표 1]).
구성 컬럼
조 건
비 교
인덱스 컬럼 ① 매장 코드 ② 판매 일자 ③ 제품 코드
첫 번째 컬럼 조건이 누락된 경우 WHERE 판매 일자 = ‘20030618' AND 제품 코드 = ‘50001’
인덱스 활용 못함 판매 일자, 제품 코드는 단지 데이터 체크 컬럼 => 전체 데이터 다 읽음
* 중간 컬럼 조건이 누락된 경우 WHERE 매장코드 = ‘R2001’ ?AND 제품 코드 = ‘50001’
인덱스 활용 가능 => 매장 코드 : 인덱스 활용 컬럼 => 제품 코드 : 데이터 체크 컬럼 판매 일자의 조건이 누락됨에 따라 매장 코드 조건에 의해 모두 데이터가 읽어지면서 제품 코드는 데이터를 가지고 올 것인가를 체크하는 조건만 된다.
* 첫 번?? 컬럼 조건이 ‘=’이 아닌 경우 WHERE 매장 코드 = ‘R2%’ ?AND 판매 일자 = ‘20030618’ AND 제품 코드 = ‘50001’
인덱스 활용 가능 => 매장코드 : 인덱스 활용 컬럼 => 판매일자 : 데이터 체크 컬럼 => 제품코드 : 데이터 체크 컬럼 하지만 첫 번째 컬럼이 LIKE 조건이므로 판매 일자와 제품 코드는 단지 데이터를 가지고 오기 위한 체크 컬럼만 된다. 즉, 우선 순서를 가진 컬럼이 = 조건이 아닌 경우는 뒤 컬럼은 모두 데이터 체크 컬럼이다.
결합 인덱스를 사용할 때에는 인덱스 활용 컬럼이 데이터 범위를 충분히 좁혀질 때 유용한 것이다. [그림 1]과 같이 데이터가 인덱스 활용 컬럼에 의해 점점 좁혀지는 구조가 돼야 한다. 데이터 체크 컬럼은 데이터를 좁혀주는 것이 아니라 읽은 후 버려지는 경우가 많기 때문이다.
[그림 1]
인덱스를 활용하지 못하도록 SQL을 작성한 경우 SQL문의 WHERE 조건절 기술시 해당하는 인덱스가 있어도 힌트를 강제적으로 사용하지 않으면 인덱스를 활용 못하는 경우는 [표 2]와 같다.
유형
예제 및 해결 방법
인덱스 컬럼 절을 변형한 경우
수식, 함수 등으로 인덱스 컬럼 절을 변형했을 경우이다. 반드시 함수나 수식을 사용하는 경우에는 인덱스 컬럼 부분에 적용하지 말고 여기에 대입되는 컬럼 또는 상수 부분에 적용해야 한다. WHERE TO_CHAR(등록일, ’YYYYMMDD) = ‘20030618’ => WHERE 등록일 = TO_DATE(‘20030618’, ’YYYYMMDD’) WHERE SAL * 30 > 30000 => WHERE SAL > 1000
내부적으로 데이터 형 변환이 일어난 경우
서로 대입되는 항목끼리 데이터 타입이 다르면 내부적인 형 변환에 의해 컬럼이 함수를 사용한 효과를 나타낼 수 있다. WHERE 등록일 = ‘20030618’ 등록일이 DATE 타입인데 문자형을 직접 대입하게 되면 WHERE TO_CHAR(등록일, ’YYYYMMDD’) = ‘20030618’로 기술된 것과 똑 같은 효과, 즉 인덱스 컬럼을 변형한 것과 같다. 따라서 상수 절을 변형시켜 주어야 한다. => WHERE 등록일 = TO_DATE(‘20030618’, ’YYYYMMDD’)
조건절에 NULL 또는 NOT NULL을 사용한 경우
WHERE 연체금액 IS NULL 기본적으로 인덱스를 구성한 컬럼 값이 전부 NULL이라면 인덱스는 이런 값을 저장하지 않는다. 따라서 NULL인 값이 많지 않아 인덱스를 통해 액세스를 하고자 한다면 데이터 생성시 디폴트로 0과 같이 데이터를 만들어 주는 것이 낫다. 반대로, 만약 연체금액이 NULL인 사람이 많고 연체금액이 없는 사람은 별로 분석 대상이 아니고 연체금액이 NOT NULL인 사람이 분석 대상이라면 연체금액 컬럼을 NULL 허용 컬럼으로 두는 것이 좋다. WHERE 연체금액 IS NOT NULL => WHERE 연체금액 > 0 앞서 말한 것처럼 인덱스에는 연체금액 NULL인 사람은 존재하지 않기 때문에, 연체금액 있는 사람만을 추출하고자 할 때에는 해당 인덱스를 활용하는 것이 훨씬 유리하다.
부정형으로 조건을 사용한 경우
WHERE 연체코드 != ‘부분납’ 부정문은 인덱스를 활용하지 못한다. WHERE 연체코드 < ‘부분납’ OR 연체코드 > ‘부분납’ 또는 테이블을 한번 더 읽어 NOT EXISTS를 사용하라 WHERE NOT EXISTS (SELECT ‘X’ FROM 체납 WHERE 연체코드=’부분납’)
LIKE 연산자를 맨 앞에 사용하는 경우
WHERE 주소 LIKE ‘%신림’ 이 경우에는 인덱스를 사용할 수 없다. 이런 이유로 컬럼을 시도, 군구, 동읍으로 주소 컬럼을 나누어 생성하는 것이 좋다.
[표 2] 인덱스 활용을 못하는 유형
역으로 인덱스를 사용하는 것을 방해하는 원리를 이용하여 인덱스가 불리하다고 생각되는 경우 일부러 앞의 예에 기술된 것처럼 써 주는 기법도 많이 사용된다. 하지만 필자 의견으로는 적절하게 힌트를 구사하여 제어를 하는 것이 가독성 측면에서 더 나은 방법이라 생각한다.
옵티마이저의 선택(힌트 이용) 룰 기반 옵티마이저가 아닌 경우 인덱스를 활용하고자 하나 옵티마이저가 판단하여 자신이 생각하는 인덱스를 활용하지 않고 풀(FULL) 스캔이나 다른 인덱스를 사용하는 경우가 있다. 이는 옵티마이저가 자체적으로 판단하여 SQL 실행계획을 작성하기 때문이다. 하지만 아직 최적화된 SQL 실행 계획을 제시해 주지 못하고 있다. 따라서 이런 경우 힌트를 사용한다면 자신이 생각하는 인덱스를 사용하라고 지시를 내릴 수 있다.
힌트 사용 문법은 /*+ INDEX(테이블명 인덱스명) */이다. 힌트는 /* */이라는 주석 문구에 해당하므로 다르더라도 컴파일이나 실행시 오류는 내지 않는다. 따라서 문제가 있어 수정했는데 잘 못 사용하여 힌트 문장이 제대로 수행되지 않을 수 있으니 반드시 플랜을 작성해 제대로 활용되었는지를 확인해야 한다.
2. 인덱스를 타는데 왜 느리죠 (데이터 분포도와 SQL문 호출 횟수를 고려)
데이터 분포도가 낮은 경우 과도한 소트를 방지하기 위해 인덱스를 활용하는 경우도 있지만 인덱스 활용의 주 목적은 수많은 데이터 중 자신이 읽고자 하는 부분이 적은 경우 해당 부분만을 읽고자 할 때 사용되는 것이다. 따라서 인덱스로 지정한 컬럼에 주어진 조건이 전체 데이터의 10% 미만이지 않을 때에는 되도록 풀 스캔을 하는 것이 일반 룰로 되어 있다. 이는 인덱스와 데이터 파일을 읽어야 하기 때문에 2배의 노력이 드는 것이고 멀티 블럭 Read를 하지 않기 때문에 5개 이상의 블럭을 한 번에 읽어오는 풀 스캔에 비해 5배, 즉 최소 10배 이상의 노력이 수반된다고 보기 때문이다. 따라서 조건이 인덱스로 읽혀진다면 반드시 데이터 분포도를 생각해야 한다. 예를 들어 전국 국민을 조회하는데 이중 서울에 사는 사람만 조회를 한다면 서울에 사는 인구 분포는 반 정도이다. 이를 인덱스를 활용한다면 분명 효과적인 인덱스 활용이 아니다. SQL문이 과도하게 호출되는 경우 만약 대용량 데이터를 활용하는 사람은 반드시 숙독하기 바란다. 전형적인 배치 프로그램을 예로 든다면 [리스트 1]과 같이 프로그램이 수행된다.
구 조
예 문
DECLARE MAIN-CURSOR; DECLARE SUB-CURSOR; OPEN MAIN-CURSOR ; LOOP FETCH MAIN-CURSR; OPEN SUB-CURSOR ; FETCH SUB-CURSOR; CLOSE SUB-CURSOR; END-LOOP; CLOSE MAIN-CURSOR;
DECLARE MAIN_CURSOR SELECT 고객, 고객명, 우편번호, 나머지 주소 FROM 고객; DECLARE SUB_CURSOR SELECT 우편번호주소 FROM 우편번호 WHERE 우편번호 = :변수; OPEN MAIN-CURSOR LOOP FETCH MAIN-CURSOR; :변수 = 고객.우편번호; SELECT 우편번호 주소 FROM 우편번호 WHERE 우편번호 = :변수; END-LOOP CLOSE MAIN-CURSOR;
요즈음은 SQL문으로 한 번에 데이터를 가지고 오는 걸 프로그래머는 선호하기 때문에 [리스트 1]처럼 프로그램을 작성하는 예는 드물다. 아마 MAIN CURSOR를 다음과 같이 구성할 것이다.
SELECT A.고객번호, A.고객명, A.우편번호, B.우편번호 주소, A.나머지 주소 FROM 고객 A, 우편번호 B WHERE A.우편번호 = B.우편번호; 프로그램의 편이성 때문에 이렇게 하는 사람들이 많지만, 왜 SQL문을 합쳐야 하는지를 물어보면 원리를 이해하는 사람은 많지 않다. [리스트 1]의 경우 LOOP문 안에 정말 간단한 SQL문이 인덱스를 활용하여 제대로 읽혀진다 해도 고객 수가 몇 천만 건인 경우 어떠한가? 이 SQL문은 여지없이 몇 천만번 DB 엔진에게 자료를 요청한다. SQL문이 던져지면 [그림 2]와 같이 구문해석 -> 실행계획 -> 바인드 -> 실행 -> 인출이라는 과정을 거치게 된다.
[그림 2] SQL문 해석 과정
반복 수행됨은 물론 공유 풀이라는 곳에 이미 실행된 SQL문이 존재하여 실행계획을 다시 생성하지 않더라도 계속적인 수행은 데이터베이스의 상당한 부담이 된다. 따라서 LOOP문 안에 호출되는 SQL문이 단 한 건을 요청하더라도 반드시 MAIN CURSOR에 병합하는 연습을 해야 한다. 지금은 두 개의 테이블로 간단하겠지만 여러 테이블이 되고 도중에 업무가 바뀌어 프로그램을 수정하면서 여기저기 SQL문을 추가하기 때문에 이런 형태의 프로그램이 많이 만들어지게 된다.
하지만 이 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에 있는 테이블과 조인시
대규모 배치시스템의 성공적인 구축 전략
성공시스템 구축을 위한 Enterprise Batch
대용량의 데이터, 끊임없이 이어지는 트랜잭션… 하루에도 수백만, 수천만 그리고 수억 건에 이르는 데이터 처리 작업이 금융과 통신으로 대표되는 서비스 산업에서 하루도 빠짐없이 이뤄지고 있다. 그만큼 우리의 IT 기술은 질적인 처리 능력뿐 아니라 양적인 면에서의 발전도 요구하며 하루가 다르게 변화하고 있다. 이런 흐름은 IT의 한 축인 SW 개발 영역에서도 예외는 아니다. 엔터프라이즈 환경에서 성공적인 차세대 배치시스템 구축을 고민하는 움직임이 점차 뚜렷해지면서 엔터프라이즈 배치(Enterprise Batch)의 필요성이 빠르게 대두되고 있다. 아직은 그 개념이 본격적으로 확산되진 않았지만 실무에서 혹은 컨설팅 차원에서 그 도입을 저울질하는 기업들이 늘어나는 만큼, 마소 10월호 커버스토리에서 먼저 그 실체를 점검해 본다. 이를 위해 엔터프라이즈 배치의 전체적인 이해를 돕는 배치시스템의 구축전략을 우선 설명하고 이어서 기업의 배치 처리에 활용되는 ETL 솔루션과 배치 프레임워크의 실질적인 예인 스프링 배치 2.0 등을 차례로 소개한다.
기획·정리 | 전도영 기자 mir@imaso.co.kr
1부 | 대규모 배치시스템의 성공적인 구축 전략 | 김진광 2부 | 더 견고한 PL/SQL 애플리케이션 만들기 | 조만희 3부 | 오픈소스 ETL 솔루션 활용 | 정상혁 4부 | 스프링 배치 2.0 | 최한수
엔터프라이즈 배치의 시작
대규모 배치시스템의 성공적인 구축 전략
배치시스템을 효과적으로 구축하기 위해서는 무엇보다 배치 애플리케이션이 가지고 있는 고유한 특징들을 정확히 파악하고 있어야 한다. 배치 애플리케이션의 처리 방식(Batch Processing)을 위키피디아(Wikipedia)의 표현을 빌려 정리하면, 사람의 상호 작용 없이 컴퓨터상에서 여러 프로그램을 실행시키는 것(execution of a series of programs on a computer without human interaction)이라고 할 수 있다. 이 글에서는 엔터프라이즈 배치 이해의 핵심인 배치시스템의 구축 전략에 대해 설명한다.
배치 애플리케이션은 많은 자원을 필요로 하는 대용량 작업을 정해진 시간 제약 내에 사용자와의 상호 작용을 최소화한 자동화된 형태로 수행해야 하는 처리 패턴을 갖고 있으며, 수많은 데이터 유형으로 인해 테스트가 어렵고 많은 수행 시간이 소요된다는 특징이 있다.
배치시스템의 이해
그럼 지금부터 배치 애플리케이션의 특징을 조금 더 자세히 살펴보자.
배치 애플리케이션의 네 가지 특징
● 사용자와의 상호 작용이 없다 온라인 애플리케이션과 배치 애플리케이션이 구별되는 가장 큰 특징은, 사용자에 의해 실행이 결정되지 않는다는 것이다. 배치 애플리케이션은 사용자와의 상호 작용이 없기 때문에 화면 개발로 인한 오버헤드가 없고, 사용자의 리뷰 과정에서 발생하는 요구사항 변경이 상대적으로 덜하다. 반면에 배치 애플리케이션은 사용자로부터 정확한 요구사항을 전달받기 어려우며, 불명확한 요건을 분석해 로직을 직접 설계해야 하므로 개발자/운영자의 비즈니스 이해도가 상대적으로 더 많이 요구된다.
● 정해진 시간 제약 내에 실행이 완료되어야 한다 정해진 시간 제약 내에 수행이 완료되어야 한다. 배치는 수행 결과가 필요한 시점에 의해 제약사항으로 결정된 특수한 시간 범위(Batch Window) 내에서 수행이 완료되어야 한다. 업무 로직의 개선과 프로그램 수행 성능 개선 등을 통해 Batch Window의 크기가 줄어들수록, 보다 빠른 정보 제공/마감이 가능해지기 때문에 동일 배치 애플리케이션이 제공하는 비즈니스적 가치가 높아진다.
● 많은 자원이 소모되는 대용량 작업이다 대용량 고 자원 소모의 작업을 수행한다. 온라인 실시간 처리가 힘든 대용량 처리를 수행함으로써 많은 자원을 사용하게 되므로, 한정된 자원(Connection, CPU 연산 능력, 메모리 등)을 효율적으로 관리할 수 있는 기술적인 지원 및 가이드가 필요하다. 한 번의 처리 실패로 인해 시간 제약을 지키지 못할 수 있으므로, 실패에 대한 정확한 모니터링 방법과 더불어 잘못 실행된 경우에도 효과적으로 처리할 수 있는 방안이 마련되어야 한다.
● 테스트가 어렵고 테스트에 많은 시간이 소요된다 다양한 파일과 복잡한 DB 연계가 포함되어 있어 테스트 데이터를 구성하는 것이 어렵다. 테스트 데이터를 확보하지 못한 상태에서 몇 백만 건 수준의 실제 데이터를 내려 받아 처리하므로 테스트에 많은 시간이 소요되고, 로직 오류로 인한 재 테스트 시 시간 낭비가 심할 수밖에 없다.
배치시스템에 대한 대표적인 오해(Pitfalls)
● 배치는 비교적 단순하고 덜 중요하다. 차세대 프로젝트에서 온라인/정보계 등에 비해 명확한 담당자를 찾기가 힘든 배치는 전사 아키텍처 관점에서 제대로 설계되지 못한 상태에서 프로젝트 막판에 비효율적으로 마무리되기 쉬우며, 성능에 대한 오해로 인해 균형 잡힌 최적화를 이루지 못하는 경우가 많다.
일반적으로 배치는 단순하고, 덜 중요하다고 여겨진다. 따라서 대부분 온라인/정보계 등에 집중하고 배치는 차세대 후반부에 고민하는 경향이 있다. 배치는 자동화된 대량 처리를 필요로 하는 온라인/정보계/대외계 모두와 밀접한 연관 관계를 갖고 있어, 전사 시스템이 유기적으로 업무를 처리하는 데 있어 촉매제와 같은 역할을 수행한다(직접 사용자를 만족시키는 건 적으나, 부지런히 정보가 제때 처리되도록 함).
전사 아키텍처를 설계하는 시점에서부터 배치의 역할 및 기술 구조, 연관 시스템 간의 유기적 연계 및 최적화 방안에 대한 고민이 이뤄져야 한다.
● 배치는 성능이 제일 중요하므로 C 같은 언어로 개발해야 한다 배치는 성능을 최우선으로 생각하는 경향이 있어 C나 COBOL 같은 성능이 뛰어난 언어로 개발해야 한다는 인식이 주를 이룬다. 하지만 성능에 대한 근본 요건은 주어진 Batch Window 내에서 처리할 수 있으면 된다는 것이다. 배치의 성능 최적화는 프로그래밍 언어나 프로그래밍 기법이 아닌 아키텍처 수준의 최적화를 통해 해결되어야 한다. 최적화 기법을 적용순서가 높은 순으로 나열해보면 배치 업무, 아키텍처, I/O 비용, 프로그램 수준에서 논의될 수 있다.
· 배치업무 최적화 : 유사 업무 통합, 불필요한 반복 작업 제거, 월 마감을 일 마감으로 전환, 배치 처리 요건 제거 등 · 관리 고도화 : 수작업 데이터 검증 최소화, 잘못된 결과로 인한 재작업 최소화, 중요도에 따른 자원 사용 고도화 등 · I/O 비용 최소화 : 배치 수행 시간의 80% 이상은 I/O 비용이며, I/O를 최소화해야 최적화된 성능 향상이 이뤄진다. 온라인 컴포넌트 사용 I/O 최소화, 대량 처리를 위한 DB I/O 최소화, 파일 I/O 최소화 등 · 프로그램 최적화 : 일정 사이즈로 처리 데이터를 균일하게 Fetch해서 적정 횟수만큼 묶어서 Commit하기, 프로그램 튜닝 팁 적용
배치시스템의 주요 이슈
배치 서비스 유형과 기술 유형이 정리되지 않고, 애플리케이션을 통한 유형 분석이 어려워 전사 배치 서비스에 대한 분석 및 현황 파악이 어렵다. 그리고 서비스에 대한 표준화가 이뤄지지 않고 있어서 서비스 통합 및 재활용이 되지 않아 유사 서비스가 양산되고 있다.
운영/관리 체계가 미흡해 전사 배치 서비스에 대한 현황 파악 및 효율적인 운영 및 관리가 이뤄지기 힘들며 기반 기술 구조가 취약해 적기에 고품질 서비스를 빠르고 저 비용으로 제공하지 못해 불필요한 인력 및 시스템 자원의 낭비가 존재한다.
이런 현실을 타계하기 위해 업무 애플리케이션, 애플리케이션과 기술이 연계된 표준화된 배치 모델이 필요하고 운영 및 관리 프로세스를 강화한 관리 시스템 구축 및 기반 기술 구조 설계가 필요하다.
모델 중심의 배치 서비스 표준화 미흡
배치 서비스 분류 및 유형이 비즈니스 관점, 업무 조직 관점, 업무 유형 관점에서 체계화되지 못하고 정보 항목(작업 주기/작업 유형/작업 시간 등)이 명확히 정의되지 않아 서비스에 대한 분석이 어려우며 관리 항목(서비스 대상 및 목표/상품 구분 등)이 명확히 정의되지 않아 현황 파악이 어렵다. 배치 기술 유형을 구분 짓는 기준이 불명확하고(입출력 리소스 유형, 사용 도구, 처리 흐름, 적용 기술), 각 기술 유형별로 구현 방법이 상이하며(경험한 유형이 아니면 구현 방법을 알기 어려움) 기술 유형의 확장성이 부족하다(새로운 기술 유형이 나왔을 때 유연하게 대처하기 어려움).
신규/변경 요구 발생 시 유사 서비스 파악을 통해 재활용을 극대화하는 장치가 부족하고, 업무 유형과 기술 유형을 쉽게 판단할 수 없어 배치 애플리케이션의 이해가 어려우며, 업무와 기술에 대한 이해 부족이 최적화되지 않은 배치 애플리케이션의 중복 개발로 이어진다.
관리 시스템 구축 및 기반 기술 구조 설계 필요
애플리케이션 관련 부서와 담당자 식별이 어렵고(현업 / 관리 / 개발) 장애 이력 관리 부족으로 체계적인 오류 원인 파악 및 대처가 이뤄지지 못하고 있으며, 데이터 정합성 확보를 위한 자동화된 사전 점검 기능이 부족해 담당자가 직접 검수하는 경우가 많다.
작업 신청 및 일정 관리, 수행 및 결과 모니터링, 현업 결과 제공 등 운영 업무가 효율적이지 못하고, 적절하지 못한 배치 수행 통제로 인해 전사 자원이 비효율적으로 사용되며, 보안성이 취약하다. 또한 배치 전산 자원에 대한 현황 파악이 어렵고, 지표를 활용한 자원 활용 및 관리가 어렵다. 전체 아키텍처 관점의 최적화가 미흡하다(온라인 로직 재사용, 정보계 ETL 도구의 배치 서비스 활용 등).
분할 처리 및 병렬 처리 미흡, I/O 최적화 부족 등으로 인해 적기 서비스가 지연된다. 효율적인 테스트를 위한 테스트 분류 및 각 분류에 따른 최적화된 테스트 방안이 미흡하다. 배치 개발/테스트/운영에 사용되는 데이터의 보안 요건에 대한 설계 및 빠른 반영이 어렵다.
어떤 문제들이 발생하는가?
[배치 애플리케이션 개발자 K 씨의 피곤한 일상] 현업이 변경을 요청하고 변경에 필요한 일정을 요청한다. 개발자 K 씨가 들어보니 아주 간단한 변경처럼 보인다. 하지만 자신이 없어 일단 일정을 어림잡아 일주일로 산정한다. 현업이 생각하기에도 일주일씩 걸릴 것 같지 않아 미심쩍지만, 배치 애플리케이션이므로 일단 받아들인다. 이제부터 K 씨의 돌고 도는 개발 프로세스가 진행된다.
개발자는 변경사항을 반영하기 위해 자신이 두 달 전에 개발한 배치 애플리케이션의 소스 코드를 분석해 변경 포인트를 찾는다. 본인이 개발한 코드이지만 너무 많은 중복된 코드와 반복적으로 나타나는 로깅, 트랜잭션, 예외 처리 코드 때문에 변경할 부분을 찾기가 쉽지 않다. 드디어 변경 포인트라 생각되는 부분을 찾아 현업의 요구사항을 반영한다. 이때 이미 하루를 변경 포인트를 찾는 데 허비했다.
테스트 과정에서 도출된 결과와 검증 데이터가 맞지 않는 사태가 발생한다. 도대체 어디가 잘못된 것인지 찾기가 쉽지 않다. 로깅 처리가 제대로 되어 있지 않아 이틀을 오류 찾는 데 허비하고 나서야 비로소 오류 부분을 찾았다. 오류를 수정하고 다시 테스트를 진행한다. 결과 데이터는 맞게 나왔지만 테스트 데이터의 양이 적어 테스트 결과를 확신할 수 없다. 운영서버에 적용한 후 보니, 또 다른 장애물이 나타난다. 배치 작업 중 성능이 나오지 않는 것이다. 원인을 찾아보니 오류를 찾으면서 추가한 로깅이 원인이다.
로깅을 제거한 후 다시 배치 작업을 시작한다. 이것이 끝이 아니다. 작업 중 어딘가에서 메모리 누수 현상이 발생해 배치가 중단되는 사태가 발생한다. 어느 누구도 배치가 비정상적으로 중단된 것을 알지 못했다. 다음날 K 씨가 로그를 확인한 후에야 배치가 실패했다는 것을 알게 되었고 다시 오류를 수정하고 밤새도록 K 씨가 모니터링해서 배치 작업을 완료한다.
배치 업무와 관련이 있는 곳에서는 이 사례와 같은 상황이 빈번하게 발생할 것이다. 또한 배치라는 이유로 이러한 상황을 당연하게 여겨왔을 것이다. 하지만 이러한 상황이 발생하는 원인이 무엇이고 어떻게 해결해야 할지 고민해본다면 분명히 답을 찾을 수 있을 것이다. 그럼 이 상황에서 몇 가지 핵심 질문을 유추해 보자
- 왜 간단한 요구사항을 반영하는 데 일주일이나 필요한가? - 왜 배치는 수정하기 어려운가? - 왜 배치는 개발자 스스로도 자신의 코드를 믿지 못하는가? - 왜 배치의 시작과 끝을 모니터링할 수 있는 시스템이 존재하지 않는가? - 왜 배치는 테스트하기 어려운가?
요구사항은 간단할지라도 코드 상에 CNP(Copy and Paste) 패턴으로 인한 중복된 로직들이 산재되어 있어 수정을 하기 어렵다. 배치는 대부분 대용량 데이터를 처리해야 하기 때문에 테스트 데이터를 만들기도 어렵고 테스트를 수행하는 데도 많은 시간이 소요된다. 따라서 수정한 후에 테스트를 통한 검증도 쉽지 않다. 배치는 지금까지 온라인 시스템에 비해 우선순위가 떨어진다고 생각되어 왔다. 따라서 배치시스템은 고도화되어 있지 않고 진정한 운영시스템도 존재하지 않는 경우가 많다. 이제부터 이러한 문제들을 해결하기 위한 구체적인 방법을 생각해 보자
배치시스템의 이해 및 성공 구축 전략
구축 전략을 이해하기 위해서는 배치시스템이 어떤 구조를 가지고 있는지 이해해야 할 필요가 있다.
배치시스템 구조 이해하기
<그림 1>을 보면 기업에서 사용하는 기술 구조상에서 배치는 극히 일부 요소에 불과함을 알 수 있다. 엑센츄어(Accenture) TRM 상에서 배치 서비스는 실행 환경의 인프라스트럭처 서비스에 위치하고 수많은 인프라스트럭처 서비스의 구성 요소 중 한 영역을 차지한다.
배치시스템의 구성요소를 자세히 살펴보면 배치관리 서비스, 배치 스케줄링 서비스, 대용량 배치 실행 서비스, 배치 애플리케이션 지원 서비스로 구성된다.
구성 요소별로 사용되는 도구들을 살펴보자.
● 배치 관리 서비스 일반적으로 In-House로 통합관리 시스템을 구축한다(배치 운영역량의 핵심).
● 배치 스케줄링 서비스 Control-M, TWS(Tivoli Workload Scheduler) 등 솔루션을 도입하거나 OPENSYMPHONY의 Quartz 같은 오픈소스 솔루션을 이용할 수 있다.
● 대용량 배치 실행 서비스 - 쉘 프로그래밍을 통해 유닉스 기능을 활용한다(처리 품질은 개발자 몫). - 미들웨어를 이용해 실행 컨테이너를 구축한다(기본 처리 품질 보장 및 자동화). - 서비스 프레임워크(온라인을 배치에서 사용) 또는 센터컷(배치에서 온라인을 사용)
● 배치 애플리케이션 지원 서비스 - 코어뱅킹 솔루션 등에 포함된 배치 지원 기능 또는 직접 배치 프레임워크를 구축 - 대용량 SAM 파일 처리도구(예 : Syncsort), DB 유틸리티(예 : SQL 로더) 배치 처리 결과 검증도구(예 : TeraStream) 등이 사용된다.
성공 구축 전략
배치시스템이 적시에 고품질 배치 서비스를 최소한의 비용으로 제공하기 위해서는 표준화된 배치 서비스 모델을 기반으로 배치 관리/운영 프로세스를 고도화하고, 최적화된 기술 기반 위에서 애플리케이션 프레임워크를 기반으로 이를 시스템화해야 한다.
체계적인 검증 기준의 수립과 검증을 자동화해 배치 작업의 오류를 최소화하고 데이터의 정합성을 확보한다. 하드웨어, 소프트웨어, 애플리케이션의 전체적인 최적화, 성능 향상, 안정화 등을 통해 서비스 제공시간을 단축함으로써 적시에 비즈니스 요구사항을 반영하며, 프로세스를 표준화하고 체계화해 비즈니스 변화에 일관되고 체계적인 관리를 수행함으로써 문제발생시 신속한 원인 파악 및 동일한 문제의 발생을 최소화한다. 통합된 기술표준을 확립하고, 모니터링을 통해 관리의 효율성을 높이며, 실시간으로 배치 작업을 통제해 문제 발생시 대처하는 역량을 증진시킴으로써 운영 및 관리의 효율성을 향상시킨다.
똑똑한 배치 처리를 돕는 다섯 가지 핵심 기법
배치 처리 유형을 표준화하고 이를 패턴화한 구현 방법을 제공하라
배치 기술 패턴은 반복 처리 패턴, 로직 처리 패턴, 유틸리티 패턴, 커스텀 패턴으로 크게 네 가지로 분류할 수 있다.
반복 처리 패턴은 건 단위로 반복적으로 입/출력이 이루어지고 입/출력 유형에 따라 구분된다. 반복 처리 패턴에서 입력 자원과 출력 자원은 다수가 존재할 수 있다. <표 1>에 입/출력 자원의 유형을 정리해 놓았다. 반복 처리 패턴은 ‘고정 길이 파일을 읽어서 구분자 파일로 쓴다’와 같이 표현할 수 있다.
로직 처리 패턴은 비즈니스 로직 상에서 다른 시스템의 자원을 사용하는 패턴을 뜻한다. <표 2>에 로직 처리 패턴에서 사용되는 자원을 정리했다.
반복 처리 패턴과 로직 처리 패턴은 유기적으로 연결되어 있다. 입력 자원을 이용해 처리할 데이터를 읽고 데이터베이스, 온라인 서비스, 룰 엔진 등을 이용해 비즈니스 로직을 처리한 후, 결과를 출력 자원을 이용해 저장한다. 만일 입력 자원이 고정 길이 파일이고 출력 자원이 DB이면서 비즈니스 로직에서 Dao를 이용한다면 ‘Fixedlength File To DB Using DaoConnector’와 같이 표현할 수 있다. 유틸리티 패턴은 FTP, Print, Tool을 이용한 데이터 변환, 쉘 스크립트 등을 이용하기 위한 패턴을 뜻한다. 이러한 세 가지 패턴에 포함되지 않는 패턴은 커스텀 패턴으로 정의할 수 있다.
기본 빌딩 블럭을 통해 각각의 배치 처리 유형을 동일한 개념으로 구현하라
처리 패턴은 실제로 몇 백 개나 된다. 이걸 개발자들에게 다 알라고 하는 건 지나친 학습 부담을 초래한다. 따라서 패턴은 여러 개가 되더라도 각각의 패턴은 동일한 개념으로 구현될 수 있어야 한다. 이러한 개념들을 빌딩 블럭으로 표준화함으로써 처리 패턴이 다양해질지라도 동일한 구조로 개발하는 것이 가능하도록 한다. 기본적인 빌딩 블럭의 예로는 <그림 4>에서 보는 것과 같이 ResourceReader, Transfomer, Processor, Connector, ResourceWriter 등을 들 수 있다.
SoC 원칙을 최대한 적용해 개발자의 부담을 줄여라
관심사항 분리 전략을 적용해 반복적으로 처리되는 횡단 관심사인 보안, 로깅, 예외 처리, 트랜잭션 처리 등과 같은 작업은 프레임워크에서 담당하고 개발자는 비즈니스에 관련된 관심사만을 담당한다.
● 관심사항 분리 반복적이고 공통적으로 나타나는 코드를 프레임워크에서 담당함으로써 개발자는 핵심 관심사에만 집중할 수 있도록 한다. 로깅, 메시지, 예외 처리와 관련된 사례를 보고 생각해 보자.
[사례]
어느 날 배치 애플리케이션에서 에러가 발생했다. 로그 파일을 확인해 보니 알 수 없는 에러메시지만 남아 있었다. 에러를 확인하기 위해 에러를 재현해보는 수밖에 없었다. 확인 결과 프레임워크에서 발생한 에러였다. 이 에러를 찾는 데 하루가 소요되었고 프레임워크 팀에 수정을 요청하고 프레임워크 팀에서 에러를 수정하고 다시 배치 작업을 실행하느라 프레임워크 팀을 욕하면서 또 밤샘 근무를 해야 했다. 이 일이 있은 후부터 문제가 생기면 프레임워크를 먼저 의심하게 되었다.
배치 프레임워크에서는 로깅, 메시지, 예외 처리를 표준화해 꼭 필요한 경우가 아니라면 개발자가 임의로 로그를 남기는 것을 최소화해야 한다. 배치 작업의 특성상 개발자가 임의로 남긴 로깅 한 줄도 성능에 지대한 영향을 미칠 수 있기 때문이다. 프레임워크에서 로그를 남길 때는 프레임워크 메시지와 애플리케이션 메시지를 구별할 수 있도록 해서 위 [사례]와 같은 일이 발생하지 않도록 해야 한다.
예외 처리는 무시(skip)해야 하는 예외와 배치 작업을 종료(fatal)해야 하는 예외로 구분해 처리하고, skip 예외의 경우에는 로그를 남기지 않거나 최소한의 로그만을 남겨야 하며 배치 작업을 종료해야 하는 예외의 경우에는 에러 트레이스를 적절하게 남겨야 한다. 에러 트레이스를 분석해 배치 애플리케이션에서 발생한 예외일 경우에는 애플리케이션과 관련된 트레이스만을 남기는 방법을 고려해 볼 수 있다.
[사례]와 같은 일이 자주 발생한다면 개발자들은 아무도 프레임워크를 신뢰하지 않게 될 것이고, 결국 신뢰를 잃은 프레임워크는 사장되고 말 것이다. 따라서 로깅, 메시지, 예외 처리는 별로 중요하지 않은 듯 보이지만 배치 프레임워크에서 없어서는 안 될 아주 중요한 요소이다.
● 트랜잭션 배치 작업은 하나의 트랜잭션 내에서 처리되어야 한다. 업무로직 처리 중간에 Dao를 이용하거나 온라인 서비스를 이용하는 경우에도 역시 한 트랜잭션 내에서 커밋되고 롤백되어야 데이터의 무결성이 보장된다.
배치는 특성상 대부분 대용량의 데이터를 처리해야 하고 반복적으로 DB에 입력/수정/삭제처리를 하는 경우가 많다. 만약 천만 건의 데이터를 읽어서 천만 건의 데이터를 DB에 insert하는 작업이 있다면 천만 건의 데이터를 일일이 커밋할 경우 성능에 엄청난 영향을 미치게 된다.
따라서 프레임워크에서는 커밋 사이즈를 설정할 수 있어야 하고 그 사이즈만큼씩 트랜잭션 처리를 하도록 구현해야 한다. 하지만 에러가 발생했을 때는 1건 단위로 롤백되어야 한다. 배치 작업은 주로 일과 시간 이후에 실행되는 경우가 많지만 일과시간에 수행되는 배치 작업도 존재한다. 따라서 일과시간 이후에 실행되는 배치 작업과 일과시간에 실행되는 배치 작업의 커밋 사이즈를 적절히 조정해 시스템을 좀 더 효율적으로 이용할 수 있도록 하는 전략도 필요하다.
테스트를 최적화하라
배치 테스트를 수행하기 위해서는 기본적으로 모든 경우의 수를 고려할 수 있는 데이터가 필요하다. 하지만 실제로 배치는 고려해야 하는 경우의 수가 많아 모든 경우를 고려할 수 있는 데이터를 만드는 것이 현실적으로 불가능하다. 따라서 대량의 운영 데이터를 이관해 테스트를 수행한다. 이러한 테스트용 데이터가 존재하지 않거나 빈약할 경우 개발자들은 자신들이 개발한 배치 애플리케이션을 검증하기 위해 운영 DB에 접속하려 할 것이므로 테스트용 데이터는 기본적으로 마련되어 있어야 한다.
자동화된 단위테스트를 통해 쉽고 빠르게 배치 애플리케이션의 기능을 검증하고 애플리케이션의 변경이 있을 경우 단위테스트와 회귀테스트를 통해 애플리케이션을 검증해야 한다.
배치는 일반적으로 같은 작업의 중복 실행을 허용하지 않는다. 테스트 시에는 같은 작업을 반복해서 테스트하므로 같은 배치 작업을 반복해서 실행하기 위한 방안이 있어야 한다. 테스트를 수행하는 환경에 대해서는 독립적이어야 한다. 로컬에서 테스트를 진행하거나, 서버에서 테스트를 진행하더라도 배치 애플리케이션의 수정 없이 테스트를 수행할 수 있어야 한다.
운영 시스템을 고도화하라
운영시스템은 기본적으로 모니터링, 배치 작업 실행제어, 스케줄링, 통계, 배치 작업 등록, 경보 등의 기능을 갖추어야 한다.
● 모니터링 배치의 수행 상태, 수행 시간, 메모리 사용률, CPU 사용률 등을 모니터링해서 배치의 현재 상태를 모니터링할 수 있어야 한다.
● 배치 작업 실행제어 배치 작업은 엔드 유저의 상호작용 없이 실행되므로 운영시스템에서 스케줄에 따라 자동실행 또는 긴급 배치를 위한 수동 실행 기능을 제공해야 하며, 잘못된 실행의 경우 배치 작업을 취소할 수 있는 기능이 있어야 한다.
● 스케줄링 일별, 주별, 월별, 년별, 특정한 날의 배치 작업 등을 설정할 수 있어야 하며, 특정한 날에 배치 작업이 몰려 있을 경우 배치 작업 일정을 조정해 시스템에 과부하가 걸릴 만한 상황을 미연에 방지하기 위한 배치 작업일정 조회 뷰가 있어야 한다.
● 경보 모니터링을 통해 시스템적으로 한계상황에 도달하려 하거나, 배치 작업이 비정상적으로 종료되었을 경우 해당 배치 작업 담당자에게 경보를 울려 배치 작업을 안정적으로 수행할 수 있도록 해야 한다.
조직에 제대로 적용되기 위한 주의사항
배치의 유형은 무수히 많다. 프로젝트 기간 중 배치 유형에 대해 적어도 한 개는 대표 사례가 예제 형태로 구현되어야 하고, 프레임워크 등의 변경 시마다 회귀테스트가 이뤄져야 한다.
실 가동 상황에서 세심하게 테스트되어야 한다. 실 가동 상황이란 몇 백 만건의 데이터를 가지고 성능 프로파일링을 수행하며 진행하는 테스트를 의미한다(예 : 반각 문자가 포함되어 전문의 Layout이 깨지는 경우, 특수 처리 패턴에서 메모리 누수가 발생하는 경우 점차적으로 느려짐. 동일 DB에 대한 Access가 발생하는 상황 등).
엔터프라이즈란 말에는 복잡한 조직 구조를 포함하고 있다. 조직 내에는 매뉴얼을 정말 열심히 보는 사람과 전혀 보지 않는 사람 등 다양한 유형의 사람이 존재한다. 따라서 변경된 프레임워크에 적응하기 위한 다양한 교육방법과 교육시간이 확보되어야 한다. 예를 들면, 직접적으로 대면 교육을 하고, 동영상 교육 자료를 통해 지속적으로 찾아볼 수 있고, 실제 업무를 변환한 예제를 제공해 실무적응성을 높이는 등을 예로 들 수 있다.
이러한 상황을 종합하면, 실 가동 상황에서 철저한 테스트를 수행하고, 실제 실무자들이 프로젝트 팀에 참여해서 각 팀별로 대표 사례를 수집하고, 그 사례를 실제로 구현한 예제를 만들면서 프로젝트가 진행되어 프로젝트 종료 후 자연스럽게 그 사람들이 팀의 구현 리더로서 교육 담당자가 되는 구조가 되어야 한다.
배치시스템의 중요성
지금까지 대규모 배치시스템을 성공적으로 구축하기 위한 전략을 비즈니스, 애플리케이션, 운영의 관점으로 살펴봤다. 온라인 환경과는 다르게 배치시스템에 대한 관심이 적어 현재까지 대규모 배치시스템을 성공적으로 구축한 국내 사례는 거의 없다.
따라서 프로젝트를 수행하며 얻은 필자의 경험이 독자들이 현업에서 배치시스템을 구축하는 데 조금이나마 도움이 되기를 바란다. 배치시스템의 중요성을 인식하고 개선하기 위한 노력들이 조금씩 싹트기를 희망하며 이 글을 마친다.
필자소개
김진광 kwang23@gmail.com｜기업이 겪고 있는 다양한 기술적 문제들을 아키텍처 수준에서 해결 방법을 제시하고, 그것을 실제로 실현해 낼 수 있는 수준에 도달하기 위해 열심히 노력하고 있는 개발자이다. 현재 아이티와이즈컨설팅의 전문 엔지니어 그룹에서 근무하고 있으며, 대형 보험사의 전사 배치 프레임워크를 개발했던 경험을 기반으로 그룹 계열사 간의 연결재무시스템을 구축하는 IFRS 프로젝트에서 배치 프레임워크 개발을 담당하고 있다.
시퀀스란? - 유일(UNIQUE)한 값을 생성해주는 오라클 객체입니다.
- 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할수 있습니다.
- 보통 primary key 값을 생성하기 위해 사용합니다.
- 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가 합니다.
- Sequence는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 sequence를 여러 테이블에서 쓸 수 있습니다.
START WITH : 시퀀스의 시작 값을 지정합니다. n을 1로 지정하면 1부터 순차적으로
시퀀스번호가 증가 합니다.
INCREMENT BY : 시퀀스의 증가 값을 말합니다. n을 2로 하면 2씩 증가합니다.
START WITH를 1로 하고 INCREMENT BY를 2으로 하면 1, 3, 5,7,.. 이렇게
시퀀스 번호가 증가하게 됩니다.
MAXVALUE n | NOMAXVALUE : MAXVALUE는 시퀀스가 증가할수 있는 최대값을 말합니다. NOMAXVALUE는 시퀀스의 값을 무한대로 지정합니다.
MINVALUE n | NOMINVALUE : MINVALUE는 시퀀스의 최소값을 지정 합니다.
기본값은 1이며, NOMINVALUE를 지정할 경우 최소값은 무한대가 됩니다
SQL>CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000 ;
sequence created.
시작 값이 1일고 1씩 증가하고, 최대값이 100000만이 되는 시퀀스를 생성했습니다.
SQL>INSERT INTO emp(empno, ename, hiredate ) VALUES(emp_seq.NEXTVAL, 'julia' , sysdate);
empno는 컬럼값을 입력할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여
자동으로 입력할 수 있습니다.
CURRVAL : 현재 값을 반환 합니다. .
NEXTVAL : 현재 시퀀스값의 다음 값을 반환 합니다.
SQL>SELECT emp_seq.CURRVAL FROM DUAL ;
CURRVAL
---------
1
SQL>SELECT emp_seq.NEXTVAL FROM DUAL ;
NEXTVAL
---------
2
sequence 사용
+ CREATE SEQUENCE orders_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
시퀀스의 수정 및 삭제 START WITH는 수정할수 없습니다. START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같습니다.
정규식은 보통 텍스트에 덧붙여서 확장된 검색 옵션을 지정하는 특수한 기호들을 포함합니다. 정규식을 이용하면 보다 정교하게 텍스트를 검색할 수 있습니다
예를 들어 "[0-9]"와 같은 정규식을 지정하면 숫자만을 검색합니다. 비슷한 예로 "[^0-9]"와 같은 정규식을 지정하면 숫자가 아닌 글자만을 검색합니다.
에디트플러스에서는 다음과 같은 정규식을 찾기와 바꾸기, 여러 파일에서 찾기 명령에서 지원합니다.
식 설명 \t 탭 문자를 찾습니다.
\n 줄 바꿈 문자를 찾습니다.
. 모든 문자를 찾습니다.
| 이 기호의 양쪽에 있는 두 글자를 모두 찾습니다. 예를 들어 "a|b"를 지정하면 "a"와 "b"를 모두 찾습니다.
[] 대괄호 범위의 모든 문자를 찾습니다. 예를 들어 "[ab]"를 지정하면 "a"와 "b"를 모두 찾습니다. 또 "[0-9]"를 지정하면 0부터 9까지의 모든 숫자를 찾습니다.
[^] 대괄호 범위의 모든 문자를 제외한 문자를 찾습니다. 예를 들어 "[^ab]"를 지정하면 "a" 와 "b"를 제외한 모든 문자를 찾습니다. 그리고 [^0-9]"를 지정하면 숫자가 아닌 모든 문자를 찾습니다.
* 별표 왼쪽에 있는 문자가 0번 또는 그 이상 일치하는 텍스트를 찾습니다. 예를 들어 "be*"를 지정하면 "b"와 "be", "bee"를 찾습니다.
+ 플러스 기호 왼쪽에 있는 문자가 1번 또는 그 이상 일치하는 텍스트를 찾습니다. 예를 들어 "be+"를 지정하면 "be" 와 "bee"를 찾지만 "b"는 찾지 않습니다.
? 물음표 왼쪽에 있는 문자가 0번 또는 1번 일치하는 텍스트를 찾습니다. 예를 들어 "be?"를 지정하면 "b" 와 "be"를 찾지만 "bee"는 찾지 않습니다.
^ ^기호 오른쪽에 있는 문자가 줄의 처음 글자인 경우를 찾습니다. 예를 들어 "^A"를 지정하면 줄의 처음 글자가 "A"인 경우를 찾습니다.
$ $기호 왼쪽에 있는 문자가 줄의 마지막 글자일 경우를 찾습니다. 예를 들어 "e$"를 지정하면 줄의 마지막 글자가 "e"인 경우를 찾습니다.
() 식을 해석하는 순서를 바꾸거나 식을 태그로 표시할 때 사용합니다.
\ 이스케이프 문자로 쓰입니다. "\" 문자 자체를 검색하려면 "\\"로 나타내어야 합니다.
태그로 표시된 식은 ()로 둘러 싼 식을 말합니다. 태그로 표시된 식은 \0, \1, \2, \3등과 같은 식을 사용하여 바꿀 말 항목에서 지정할 수 있습니다. \0은 일치된 텍스트 전체를 나타내고 \1은 첫 번째 태그로 표시된 부분을 나타내고 \2는 두 번째, 이런 식으로 지정할 수 있습니다. 다음의 예를 참고하십시오.
Original Search Replace Result
abc (ab)(c) \0-\1-\2 abc-ab-c
abc a(b)(c) \0-\1-\2 abc-b-c
abc (a)b(c) \0-\1-\2 abc-a-c
