oracle - 개발자가 범하기 쉬운 튜닝 이슈

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’인 것을 찾으면 된다.

인덱스가 존재하는데도 인덱스를 활용 못하는 경우

◆ 첫 번째 컬럼에 조건을 주지 않은 경우
지난 호에도 설명했듯 첫 번째 컬럼에 = 또는 LIKE, > 조건 등을 기술해야 한다. 10개 컬럼으로 구성된 결합 인덱스가 있어 9개 컬럼에 = 조건을 주더라도 첫 번째 컬럼 조건이 빠진다면 이 인덱스는 있으나마나 한 것이다. 인덱스란 B-트리 형태로 정렬된 순서를 가지고 있다. 이 순서를 가지고 액세스를 하여 해당 범위가 초과되면 검색을 중단한다. 그러므로 인덱스를 구성하는 컬럼 순서대로 조건이 많이 주어질수록 좋은 결과를 낸다. 중간에 하나라도 빠지게 되면 뒤 순서의 조건은 인덱스와는 무관하게 체크 조건이 될 뿐이다.

인덱스를 통해 검색 조건을 줄여주는 컬럼을 ‘인덱스 활용 컬럼’이라 하고, 검색 조건을 줄여주지 못하고 데이터를 추출하는 체크 조건만 될 때 이를 ‘데이터 체크 컬럼’이라 정의한다(<표 1>).

<표 1> 데이터 체크 컬럼이 되는 경우

<표 1>
구성 칼럼	조건	비고
인덱스 컬럼   1 매장 코드  2 판매 일자  3 제품 코드	* 첫 번째 컬럼 조건이 누락된 경우 WHERE 판매 일자 = ‘20030618’ AND 제품 코드 = ‘50001’	인덱스 활용 못함 판매 일자, 제품 코드는 단지 데이터 체크 컬럼 => 전체 데이터 다 읽음
	* 중간 컬럼 조건이 누락된 경우 WHERE 매장코드 = ‘R2001’ AND 제품 코드 = ‘50001’	인덱스 활용 가능 => 매장 코드 : 인덱스 활용 컬럼 => 제품 코드 : 데이터 체크 컬럼 판매 일자의 조건이 누락됨에 따라 매장 코드 조건에 의해 모두 데이터가 읽어지면서 제품 코드는 데이터를 가지고 올 것인가를 체크하는 조건만 된다.
	* 첫 번쨰 컬럼 조건이 ‘=’이 아닌 경우 WHERE 매장 코드 = ‘R2%’ AND 판매 일자 = ‘20030618’ AND 제품 코드 = ‘50001’	인덱스 활용 가능 => 매장코드 : 인덱스 활용 컬럼 => 판매일자 : 데이터 체크 컬럼 => 제품코드 : 데이터 체크 컬럼 하지만 첫 번째 컬럼이 LIKE 조건이므로 판매 일자와 제품 코드는 단지 데이터를 가지고 오기 위한 체크 컬럼만 된다. 즉, 우선 순서를 가진 컬럼이 = 조건이 아닌 경우는 뒤 컬럼은 모두 데이터 체크 컬럼이다.

결합 인덱스를 사용할 때에는 인덱스 활용 컬럼이 데이터 범위를 충분히 좁혀질 때 유용한 것이다. <그림 1>과 같이 데이터가 인덱스 활용 컬럼에 의해 점점 좁혀지는 구조가 돼야 한다. 데이터 체크 컬럼은 데이터를 좁혀주는 것이 아니라 읽은 후 버려지는 경우가 많기 때문이다.

<그림 1>
　　　　　　　　　
◆ 인덱스를 활용하지 못하도록 SQL을 작성한 경우
SQL문의 WHERE 조건절 기술시 해당하는 인덱스가 있어도 힌트를 강제적으로 사용하지 않으면 인덱스를 활용 못하는 경우는 <표 2>와 같다.

<표 2> 인덱스 활용을 못하는 유형

<표 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을 사용한 경우	조건절에 NULL을 사용한 경우 WHERE 연체금액 IS NULL 기본적으로 인덱스를 구성한 컬럼 값이 전부 NULL이라면 인덱스는 이런 값을 저장하지 않는다. 따라서 NULL인 값이 많지 않아 인덱스를 통해 액세스를 하고자 한다면 데이터 생성시 디폴트로 0과 같이 데이터를 만들어 주는 것이 낫다. 반대로, 만약 연체금액이 NULL인 사람이 많고 연체금액이 없는 사람은 별로 분석 대상이 아니고 연체금액이 NOT NULL인 사람이 분석 대상이라면 연체금액 컬럼을 NULL 허용 컬럼으로 두는 것이 좋다. => NULL이 적으면 default로  0을 넣어 index를 타게하고 NULL이 많고 쓸일이 없다면 걍 NULL값 사용. WHERE 연체금액 IS NOT NULL => WHERE 연체금액 > 0 앞서 말한 것처럼 인덱스에는 연체금액 NULL인 사람은 존재하지 않기 때문에, 연체금액 있는 사람만을 추출하고자 할 때에는 해당 인덱스를 활용하는 것이 훨씬 유리하다.
부정형으로 조건을 사용한 경우	WHERE 연체코드 != ‘부분납’ 부정문은 인덱스를 활용하지 못한다. WHERE 연체코드 < ‘부분납’ OR 연체코드 > ‘부분납’ 또는 테이블을 한번 더 읽어 NOT EXISTS를 사용하라 WHERE NOT EXISTS (SELECT ‘X’ FROM 체납 WHERE 연체코드=’부분납’)
LIKE 연산자를 맨 앞에 사용하는 경우	WHERE 주소 LIKE ‘%신림’ 이 경우에는 인덱스를 사용할 수 없다. 이런 이유로 컬럼을 시도, 군구, 동읍으로 주소 컬럼을 나누어 생성하는 것이 좋다.

역으로 인덱스를 사용하는 것을 방해하는 원리를 이용하여 인덱스가 불리하다고 생각되는 경우 일부러 앞의 예에 기술된 것처럼 써 주는 기법도 많이 사용된다. 하지만 필자 의견으로는 적절하게 힌트를 구사하여 제어를 하는 것이 가독성 측면에서 더 나은 방법이라 생각한다.

◆ 옵티마이저의 선택(힌트 이용)
룰 기반 옵티마이저가 아닌 경우 인덱스를 활용하고자 하나 옵티마이저가 판단하여 자신이 생각하는 인덱스를 활용하지 않고 풀(FULL) 스캔이나 다른 인덱스를 사용하는 경우가 있다. 이는 옵티마이저가 자체적으로 판단하여 SQL 실행계획을 작성하기 때문이다. 하지만 아직 최적화된 SQL 실행 계획을 제시해 주지 못하고 있다. 따라서 이런 경우 힌트를 사용한다면 자신이 생각하는 인덱스를 사용하라고 지시를 내릴 수 있다.

힌트 사용 문법은 /*+ INDEX(테이블명 인덱스명) */이다. 힌트는 /* */이라는 주석 문구에 해당하므로 다르더라도 컴파일이나 실행시 오류는 내지 않는다. 따라서 문제가 있어 수정했는데 잘 못 사용하여 힌트 문장이 제대로 수행되지 않을 수 있으니 반드시 플랜을 작성해 제대로 활용되었는지를 확인해야 한다.

 

 

2 인덱스를 타는데 왜 느리죠

(데이터 분포도와 SQL문 호출 횟수를 고려)

데이터 분포도가 낮은 경우
과도한 소트를 방지하기 위해 인덱스를 활용하는 경우도 있지만 인덱스 활용의 주 목적은 수많은 데이터 중 자신이 읽고자 하는 부분이 적은 경우 해당 부분만을 읽고자 할 때 사용되는 것이다. 따라서 인덱스로 지정한 컬럼에 주어진 조건이 전체 데이터의 10% 미만이지 않을 때에는 되도록 풀 스캔을 하는 것이 일반 룰로 되어 있다. 이는 인덱스와 데이터 파일을 읽어야 하기 때문에 2배의 노력이 드는 것이고 멀티 블럭 Read를 하지 않기 때문에 5개 이상의 블럭을 한 번에 읽어오는 풀 스캔에 비해 5배, 즉 최소 10배 이상의 노력이 수반된다고 보기 때문이다. 따라서 조건이 인덱스로 읽혀진다면 반드시 데이터 분포도를 생각해야 한다.

예를 들어 전국 국민을 조회하는데 이중 서울에 사는 사람만 조회를 한다면 서울에 사는 인구 분포는 반 정도이다. 이를 인덱스를 활용한다면 분명 효과적인 인덱스 활용이 아니다.

SQL문이 과도하게 호출되는 경우
만약 대용량 데이터를 활용하는 사람은 반드시 숙독하기 바란다. 전형적인 배치 프로그램을 예로 든다면 <리스트 1>과 같이 프로그램이 수행된다.

<리스트 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문은 조회조차 되지 않는다. 이제 느리다고 다른 사람의 프로세스 탓이라든지, 데이터베이스가 느리다든지 하는 오류는 범하지 않길 바란다.

 

 

온라인 프로그램에서 목표 시간내 프로그램이 수행되지 않아요

정신없이 설명하다 보니 잠시 머리를 식히라는 의미에서 적었다. 온라인 프로그램인 경우 조회 조건이 까다롭고 건수가 많은 테이블은 속도를 줄이는데 한계가 있다. 심지어 일부 프로젝트에서는 ‘온라인 프로그램 수행 목표시간을 3초 이내’라고 절대적인 수치를 정해 놓고 무조건 시간 초과 시에는 불합격을 선언하기도 한다. 하지만 필자는 이런 경우도 무난히 통과했다.

대개 튜닝을 해도 안 되는 조회 화면은 대개 건수가 많은 테이블을 다양한 검색 조건을 통해 분석용 프로그램이 많다. 이는 특정 사용자가 일 몇회 미만으로 사용하는 것이 대부분이다. 이를 억지로 튜닝하느라 다른 프로세스를 추가하거나 인덱스를 추가한다면 이는 득보다 실이 많다.

온라인 프로그램은 사용자와의 대화이다. 이들이 갑갑하게 여기는 것은 엔터를 친 다음 PC가 다운된 것처럼 아무런 메시지 없이 마우스의 모래시계만 돌다가 수십 초 경과 후 조회되는 것이다. 그리고 건수가 많은 테이블을 분석용으로 사용하는 것은 월 1～2회의 한정된 사용자만이 사용한다. 이런 프로그램은 조회 버튼을 누르자마자 ‘잠시만 기다리세요’라는 메시지 박스를 띄워라. 한 메시지 박스를 띄우는 것만으로는 부족하다. 왜냐하면 또 화면이 가만히 있을 테니까. ‘데이터를 처리 중입니다’ 계속 3초 간격으로 번갈아 메시지를 보여주라. 남은 시간을 보여주거나 남은 처리 건수를 보여준다면 더욱 효과적이다. 물론 증권회사의 주문 등록 같은 온라인 프로그램을 이렇게 해서는 안 된다. 하지만 입찰 안내서나 요구사항이 명시되어 절대적인 기준을 삼거나 사용자가 프로그램이 느리다고 불평한다면 이렇게 해보라. 신기하게도 사용자는 아무 말 안한다.
　　　　
　　　　
7 패러럴 옵션을 주었는데 속도가 나지 않아요
이론상으로는 알고 있었지만 언뜻 상황이 되면 떠오르지 않는 때가 있다. 데이터 이행을 하였을 때 경험한 것이다. 백업 서버에서 몇일 간을 테스트하면서 하루 이상 걸리던 프로세스를 2시간 이내로 줄이는데 성공을 했다. 실행서버에서 새로 유저를 만들어 프로젝트 오픈 몇 일전 최종 리허설을 하게 되었다. 그런데 이게 웬일인가. 2시간이면 끝났던 프로세스가 5시간을 넘어도 끝나지 않는 것이었다. 모니터링 해보니 패러럴 옵션이 먹히지 않은 것이었다. “실행 서버는 CPU도 백업 서버보다 많은데…” 문제는 MAX_PARALLEL_SERVER 개수가 백업 서버보다 작게 잡혀져 있었다. 공교롭게도 실행 서버에서 다른 프로세서는 다 중단시켰는데 데이터 백업 프로세서가 수행되면서 10개 정도를 사용하고 있었던 것이다. 패러럴 옵션은 앞에서 말한대로 CPU 개수를 초과하면 그리 효력이 없다. 또한 총 수행되는 패러럴 서버 개수가 MAX_PARALLEL_SERVER 개수를 초과한다면 병렬 서버는 수행되지 않는다. 만약 이것이 실제 이행시 일어났다면 분명 필자는 해고되었을 것이다. 문제는 예기치 않은 곳에서 특히 자신하고 체크하지 않는 부분에서 일어난다는 사실을 기억하기 바란다.

=> select  name, value from    v$parameter

8 예전에는 빨리 수행되었는데 지금은 속도가 안나요

인덱스의 추가나 변경
프로그램이 작성된 이후 신규로 추가되거나 컬럼이 변경된 인덱스가 영향을 미치는 것이다. 예를 들어 WHERE 고객ID LIKE ‘200310% AND 고객유형 = ‘기업’이란 조건 절이 있다면 이 SQL문이 작성되기 전에 없었던 고객 유형이 첫 컬럼으로 시작하는 인덱스가 신규로 추가되었다고 가정하자. 이런 경우 ‘=’ 조건이 우선할 수 있으므로 고객 ID를 컬럼으로 한 인덱스를 사용하다가 고객 유형으로 시작하는 인덱스를 사용하도록 바뀔 수 있다.

그러므로 전체 프로젝트의 관점에서 볼 때 자기 혼자서 잘한다고 되는 것이 아니다. 다른 팀과 공유해서 사용하는 테이블에 대해서는 스키마 변동 내역을 반드시 공유해야 한다.
　
데이터 건수 증가, 분포도 변경
이는 개발환경에서는 몇 건 안 되는 시험용 데이터를 사용하며, 프로젝트 초기에는 데이터가 많이 누적되지 않아 시간이 지나면 지날수록 속도가 점점 저하되어 나중엔 하드웨어 증설이라든지 대대적인 애플리케이션 튜닝을 하는 경우가 많다. 이는 정말로 심각한 문제다.

이는 각각의 테이블에 대해 디스크 보관 주기라는 개념이 없이 계속 데이터를 누적해 나가는 현실과도 무관하지 않다. 업무 설계시 시간 개념과 마감 개념이 도입되지 않는다면 이 문제는 쉽사리 해결되지 않는다. 즉, 장기간 보관해서 사용할 데이터를 따로 어떻게 관리할 것인지 전략을 수립해야 한다. 통계 테이블을 만들고 일정 기간이 지난 상세 테이블은 따로 백업 테이블로 옮긴다든지 하여 현재 업무를 진행하고 있는 데이터가 과거 데이터와 같이 섞여 있어 현재 업무가 지연되는 것을 막아야 한다.
예를 들어 매출 테이블을 들자면 최근 3년치만 보관하고 3년이 지난 데이터는 백업 매출 데이터로 옮겨라. 그리고 장기 분석하는 데이터를 분석하는 프로그램은 실제 매출 테이블과 백업 매출 테이블을 묶어 조회하도록 해라. 분석은 어느 정도 시간이 걸려도 크게 문제가 안 되지만 실제 이번 달 매출을 처리하기 위한 테이블은 훨씬 가벼워지기 때문에 상당한 성능 개선효과가 나타난다.
　
9 파티션 테이블의 사용
건수가 많은 테이블은 파티션 테이블을 고려해 주어야 한다. 파티션 테이블은 오라클 8 버전부터 지원된다. 필자는 오라클이 파티션 테이블 기능으로 인하여 명실상부하게 대용량 데이터베이스의 발판을 이룩하였다고 본다. 파티션 테이블은 테이블을 파티션이란 개념으로 쪼개어 놓아 WHERE 조건절을 해석하여 해당 조건에 맞는 파티션만 액세스할 수 있도록 한 것이다. 따라서 8번에서 기술한 것처럼 굳이 테이블을 나누지 않아도 파티션 기능을 이용한다면 효과적으로 최신 정보에 대한 관리가 가능하다.


파티션 테이블을 사용함으로써 얻는 큰 이점 중 하나는 작업을 병렬처리 할 수 있다는 것이다. 특히 이행이나 큰 배치 작업시 파티션 별로 프로세스를 동시에 수행하여 효과를 볼 수 있다는 것이다.

파티션 테이블을 만드는 방법은 ADMINISTRATOR’S GUIDE를 참고하기 바란다. 특히 EXCHANGE PARATITION 이란 기능은 큰 데이터를 지닌 테이블을 관리하는 DBA라면 눈여겨 보길 바란다.