전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)

전문가칼럼
DBMS별 분류
DB일반
작성자
dataonair
작성일
2016-10-04 00:00
조회
8968




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’


이산가족 찾기 생방송을 통해 배우는 DB 원리


개발자에게 맞는 DB 공부방법 찾기: 물리적 분류와 논리적 분류 그리고 인덱스


데이터베이스 인덱스의 오해와 진실


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (하)


누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀


누구도 알려주지 않았던 ‘오라클 쿼리 작성의 비법’


퀴리 최적화 및 튜닝을 위한 오라클 공정쿼리 작성법


만능 쿼리와 한 방 쿼리


오라클 옵티마이저 ‘CBO와 RBO’ 이해하기


재미있는 DB 이야기 ‘60갑자와 쿼리’


그림으로 배우는 ‘오라클 조인의 방식’ 이야기


반드시 알아야 하는 오라클 힌트절 7가지


오라클 플랜을 보는 법


개발자들의 영원한 숙제 ‘NULL 이야기’


알면 유용한 오라클 기능 ‘GATHER_PLAN_STATISTICS’


알면 유용한 오라클 기능들


오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’


이제는 말할 수 있다: 주식 자동매매 프로그램(상)


이제는 말할 수 있다: 주식 자동매매 프로그램(하)


개발자들이 자주 접하는 오라클 에러 메세지


재미있는 DB 이야기 ‘사라진 날짜를 찾아라’


오라클 랜덤 함수와 사용자 정의 함수


그림으로 배우는 ‘공정쿼리와 인덱스 생성도’


이병국의 개발자를 위한 DB 이야기: 디폴트 세팅의 함정과 오라클 파라미터


재미있는 DB 이야기 ‘놀라운 마방진의 세계’


오라클 운반 최소 단위 BLOCK


이병국의 개발자를 위한 DB 이야기: 이세돌과 알파고의 세기의 대결


이병국의 개발자를 위한 DB 이야기(30회) : DB 엔지니어의 가볍게 읽는 세상 이야기


이병국의 개발자를 위한 DB 이야기: 튜닝(31회) : 개발자를 위한 DB 튜닝 실전(1편)


이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)


이병국의 개발자를 위한 DB 이야기: 튜닝(33회) : 개발자를 위한 튜닝 실전(3편)


이병국의 개발자를 위한 DB 이야기: 튜닝(34회) : 개발자를 위한 DB 튜닝 실전(4편)


이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)


이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)


보기 좋은 떡이 먹기도 좋다 - 좋은 쿼리 좋은 성능


테이블의 수직분할과 수평분할에 대한 이해


DB 성능 제고를 위한 채번의 이해와 방식별 장단점 비교


이병국의 개발자를 위한 DB 이야기: 마지막회 : ‘개발자를 위한 DB 이야기’ 연재를 마치며



이병국의 개발자를 위한 DB 이야기: 튜닝(32회)

개발자를 위한 튜닝 실전(2편)



성공과 실패의 경험을 나누자, 용기와 희망을 나누자

개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서DB 분야에 입문한지 1~2년 된 기입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.

전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.



인덱스 생성도를 이용한 튜닝 방법

온라인 쿼리에서의 튜닝은 대부분 인덱스와 연관되어 있다. 쿼리 성능과 가장 밀접한 관계가 있는 것이 바로 인덱스다. 인덱스의 올바른 생성 규칙과 관련해서는 지난 연재인 ‘인덱스 끝장리뷰’를 참고하기 바란다. 이번 연재는 인덱스 생성도를 통한 튜닝 방법에 대하여 설명 하고자 한다. 필자의 경험을 따르면 온라인 쿼리의 튜닝은 대부분 인덱스 생성도만으로 해결 가능하였다.

인덱스 생성도는 원과 선과 점으로 구성되는데 간략히 설명하면 다음과 같다.

column_img_2537.jpg

인덱스 생성도를 활용하면 다음과 같은 여러 가지 이점이 있다.

1. 인덱스 생성도는 복잡한 쿼리를 단순하게 보여준다.
2. 인덱스 생성도는 테이블의 접근 방향과 순서를 쉽게 알 수 있다.
3. 오라클 플랜을 이해할 수 있고 더 나아가 올바른 플랜을 수립할 수 있다.
4. 인덱스를 생성해야 할 위치를 알 수 있으며 인덱스 구성 컬럼을 알 수 있다.

column_img_2538.jpg

성별 컬럼의 분포도는 50%이며, 나이 컬럼의 분포도는 1% 내외이다. 따라서 성별과 나이 컬럼은 인덱스 컬럼으로 사용하기에는 부적합하다. 그러므로 조건절에 사용되는 컬럼 중에서 분포도가 좋은 고객명 컬럼만 인덱스 생성도에 표시하였다. 현재 고객명 컬럼은 인덱스가 없으므로 인덱스 생성이 필요함을 알 수 있다.

column_img_2539.jpg

성별 컬럼의 분포도는 50%이며 나이 컬럼의 분포도는 1% 내외이다. 따라서 성별과 나이 컬럼은 인덱스 컬럼으로 사용하기에는 부적합하다. 그러므로 조건절에 사용되는 컬럼 중에서 분포도가 좋은 고객명 컬럼만 인덱스 생성도에 표시하였다. 조인절에서 사용하고 있는 고객번호 컬럼은 분포도의 좋고 나쁨을 떠나서 무조건 표시해야 한다. 위의 인덱스 생성도를 보면 쿼리에서 사용되는 인덱스 컬럼을 쉽게 알 수 있다. 바로 고객명(고객.고객명)과 고객번호(주문.고객번호)다. 그리고 고객테이블, 주문테이블 순으로 접근함을 알 수 있다.

column_img_2540.jpg

조건절에서 사용하는 컬럼 중에서 분포도가 좋은 고객명 컬럼과 주문일자 컬럼만 인덱스 생성도에 표시하였다. 조인절에서 사용하고 있는 고객번호 컬럼은 분포도의 좋고 나쁨을 떠나서 무조건 표시해야 한다. 위의 인덱스 생성도를 보면 고객명 조건절을 통하여 고객 테이블을 먼저 접근하고, 이후 고객번호 조인절에 의해서 주문 테이블을 접근함을 알 수 있다. 주문테이블의 주문일자 컬럼은 인덱스가 없기 때문에 컬럼값에 의한 필터만 발생한다.

column_img_2541.jpg

만약 위의 인덱스 생성도처럼 조건절에서 사용하는 컬럼 중에서 분포도가 좋은 주문일자 컬럼에 인덱스를 추가로 생성한다면 어떠한 문제가 발생할지 생각해 보자. 오라클은 일단 아래 2가지 접근 방향으로 나누어 판단할 것이다.



column_img_2542.jpg

첫 번째는 고객명 조건절을 통하여 고객 테이블을 먼저 접근하고, 이후 고객번호 조인절에 의해서 주문 테이블을 접근하는 경우인데 이때 주문일자 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않고 컬럼값에 의한 필터만 발생한다.

두 번째는 주문일자 조건절을 통하여 주문 테이블을 먼저 접근하고 이후 고객번호 조인절에 의해서 고객 테이블을 접근하는 경우이다. 이때 고객명 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않고 컬럼값에 의한 필터만 발생한다.

이와 같이 양방향 접근이 가능하므로 만약 오라클이 CBO 방식을 따른다면, 최소 비용이 소요되는 방향(플랜)으로 결정할 것이다. 하지만 부정확한 통계정보로 인하여 오라클 옵티마이저가 잘못된 방향을 결정할 수도 있다. 이때 통계정보를 재생성 하거나 혹은 아래와 같이 힌트절을 통하여 직접 방향을 결정해 해결하기도 한다.

column_img_2543.jpg

고객명 조건절을 통하여 고객 테이블을 먼저 접근하고 이후 고객번호 조인절에 의해 주문 테이블을 접근하는 경우이다. 이때 인덱스를 타는 컬럼은 접근 방향의 목적지(화살표)인 1번과 3번 컬럼이며, 2번과 4번 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않는다. 특히 4번 컬럼은 컬럼값에 의한 필터만 발생한다.

column_img_2544.jpg

위의 쿼리는 Outer Join 쿼리이다. 따라서 오라클 옵티마이저는 주문일자 조건절을 통하여 주문 테이블을 먼저 접근하고, 이후 고객번호 조인절에 의하여 고객 테이블을 접근할 것이다. 이때 인덱스 타는 컬럼은 접근 방향의 목적지(화살표)인 4번과 2번 컬럼이며, 3번과 1번 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않는다. 특히 1번 컬럼은 컬럼값에 의한 필터만 발생한다.

이와 같은 방식으로 인덱스 생성도를 작성한다면 아무리 복잡한 쿼리라도 단순하게 도식화할 수 있고, 또한 테이블 접근 순서 및 인덱스 생성 위치에 대해서도 정확하게 이해할 수 있다. 이러한 방법으로 Heavy 쿼리를 분석한다면 대부분의 튜닝은 쉽게 해결된다.

column_img_2545.jpg

우리가 온라인에서 흔히 접할 수 있는 복잡한 쿼리라도 위의 인덱스 생성도 그림처럼 대부분은 5~6개 미만의 테이블로 구성된다. 이와 같이 간략히 구성된 인덱스 생성도를 통해 우리는 테이블 접근 순서를 알 수 있으며, 그에 따른 인덱스 생성 위치를 알 수 있다.



조건1로 진입시 테이블 접근 순서: A → B→ E→ D→ C
조건1로 진입시 인덱스 생성 포인트: 1번, 3번, 8번, 11번, 5번
조건3로 진입시 테이블 접근 순서: D→ E→ B→ A→ C
조건3로 진입시 인덱스 생성 포인트: 12번, 10번, 7번, 2번, 5번
조건4로 진입시 테이블 접근 순서: E→ B→ A→ D→ C 혹은 E→ D→ B→ A→ C
조건4로 진입시 인덱스 생성 포인트: 9번, 7번, 2번, 11번, 5번
조건2로 진입 시 테이블 접근 순서: Outer Join 테이블을 먼저 접근하지는 않는다.
조건2로 진입시 인덱스 생성 포인트: Outer Join 테이블을 먼저 접근하지는 않는다.



테이블 접근 순서는 오라클 옵티마이저가 결정한다. 그 결정이 올바르기 위해서는 현 시점의 DB 정보가 정확하게 통계 정보로 구축되어 있어야 하며, 필요한 인덱스도 미리 만들어져 있어야 한다는 전제가 필요하다. 그러나 현실은 그러하지 못하므로 개발자가 직접 테이블 접근 순서를 판단할 수 있어야 하며, 그에 따른 필요한 인덱스 생성 위치를 알고 있어야 한다. 그러기 위해서는 인덱스 생성도에 대한 이해는 꼭 필요하다. 필자는 현업에서 경험한 다양한 온라인 쿼리에 대한 튜닝을 이러한 인덱스 생성도를 통해 대부분 해결하였다. 인덱스 생성도는 크게 수평형, 수직형, 혼합형 3가지 형태로 구분된다.

column_img_2546.jpg

인덱스 생성도가 어떠한 형태이든 우리는 테이블 접근 순서를 스스로 결정할 수 있어야 하며, 인덱스 생성 위치에 대해서도 정확하게 알고 있어야 한다. 만약 필요한 위치에 인덱스가 없다면 인덱스를 생성해야 한다. 또한 오라클 옵티마이저에 의한 테이블 접근 순서가 틀리다고 판단되면 힌트절을 사용하여 접근 순서를 바로 잡아야 한다.

지금까지 인덱스 생성도를 이용한 튜닝 방법에 대하여 그림을 통하여 자세히 설명을 하였다. 인덱스 생성도는 복잡한 쿼리를 단순하게 도식화할 수 있고, 인덱스 생성 포인트에 대하여 정확하게 이해할 수 있는 좋은 방법임을 다시 한 번 강조한다.

인덱스 생성도는 개발자가 흔히 접하는 OLTP 쿼리의 대부분에 적용 가능하다. 하지만 배치성 쿼리, OLTP High Concurrency Table, Critical SQLs들에 빈번히 사용되는 Table의 Index 생성 시에는 충분한 영향도 검증을 해야 함에 유의하자.

튜닝실전 1편에서는 대용량 데이터를 처리하는 배치 쿼리에 있어서 인덱스를 사용하지 말아야 하는 경우에 대하여 중요하게 설명하였다. 튜닝실전 2편에서는 소규모 데이터를 처리하는 온라인 쿼리에 있어서 인덱스를 정확히 사용해야 하는 경우에 대해서 주로 설명하였다. 다음 연재인 튜닝실전 3편에서는 튜닝이 잘 된 기본적인 쿼리 작성에 대하여 설명하고자 한다. (다음 회에 계속)