전문가칼럼

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

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

전문가칼럼
DBMS별 분류
Etc
작성자
dataonair
작성일
2016-11-25 00:00
조회
7252




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘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 이야기: 튜닝(34회)

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



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

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

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



튜닝실전 1편에서는 대용량 데이터를 처리하는 배치 쿼리에 있어서 인덱스를 사용하지 말아야 하는 경우에 대해서 중요하게 설명하였고, 튜닝실전 2편에서는 소규모 데이터를 처리하는 온라인 쿼리에 있어서 인덱스를 정확히 사용해야 하는 경우에 대하여 주로 설명하였다. 튜닝실전 3편에서는 공정쿼리 작성에 따른 튜닝의 선제적이고 예방적인 효과에 대해서 설명하였는데 이번 연재 및 다음 연재에서는 필자가 필드에서 경험한 내용을 바탕으로 하여 구체적이고 실무적인 튜닝 방법에 대해 설명할 예정이다.



뭉치면 살고 흩어지면 죽는다.

쿼리의 조건들을 가능하면 선행 테이블로 유도하는 것이다. 만약 [그림 1]에서와 같이 후행 테이블에서 사용하는 조건을 선행 테이블의 조건으로 대체가 가능하다면 쿼리의 성능 개선에 큰 도움이 된다.

column_img_2628.jpg

[그림 1] 선행 테이블의 조건 추가

선행 테이블에서 검색되고 필터 처리된 데이터 건수만큼 후행 테이블로 For문 방식으로 접근하는 온라인 쿼리의 Nested Loop 조인에서는 아주 큰 효과를 볼 수 있다. 하지만 배치 쿼리의 Hash Join에서는 효과를 기대하기 곤란하다. 그럼에도 개발자가 실제로 접하는 대부분의 쿼리는 온라인 쿼리의 Nested Loop 조인 방식이므로 꼭 알아 두어야 할 튜닝 규칙임에는 틀림이 없다.



줄이고 줄이고 또 줄이자

다이어트에 성공하려면 먹는 것을 줄이고 입는 것을 줄이고 자는 것을 줄여야 한다는 말이 있다. 튜닝에서도 이 말은 동일하게 적용된다. 오라클 튜닝을 극단적으로 말하면 데이터 정보가 들어 있는 블록의 운반을 줄이는 것이라 할 수 있다. 여기서 블록이란 오라클 I/O의 가장 작은 단위, 즉 최소 운반 단위를 말하며 오라클 성능과 밀접한 관련이 있다.

오라클 블록은 사용자가 입력한 데이터를 하드디스크에 저장하거나 혹은 저장된 데이터를 작업하려고 메모리로 로드할 때 처리하는 최소 작업 단위이자 최소 운반 단위다. 오라클 블록은 데이터 블록 또는 페이지라고 불리기도 한다. 한마디로 블록이란 물리적 데이터가 저장되는 디스크 공간이라고 이해하면 된다.

그렇다면 블록의 운반을 어떤 방법으로 줄일 것인가 그것은 바로 오라클 쿼리에서 사용하는 연산자를 쓰임새에 맞게 잘 선택하는 것이다.

column_img_2629.jpg

[그림 2] 연산자와 블록의 연관성

위의 [그림 2]는 서로 다른 연산자를 사용하는 쿼리이지만 수치의 값에 따라서는 동일한 결과를 나타내는 쿼리일 수도 있다. 만약 동일한 결과를 나타낸다면 블록의 운반을 최소한으로 요구하는 연산자를 우선적으로 사용해야 한다. 결과로 리턴되는 블록 수는 비록 동일할지라도 그 과정에서 어떠한 연산자를 사용하는가에 따라서는 운반되는 블록 수가 다르기 때문이다. 구체적인 예를 살펴보자.



AND (컬럼 = ‘AAA’ OR 컬럼 = ‘AAB’)
AND 컬럼 BETWEEN ‘AAA’ AND ‘AAB’
AND 컬럼 LIKE ‘AA%’
AND 컬럼 IN (‘AAA’, ‘AAB’)



위의 쿼리 구문은 모두 동일한 결과를 나타낸다. 하지만 검색되고 필터되는 과정에서 서로 다른 개수의 블록이 운반-사용되었다. 우리는 항상 최소한의 블록을 필요로 하는 연산자를 사용하도록 노력해야 한다. 쿼리의 실행 결과가 동일하더라도 그것의 성능이 동일하다는 것을 보장하지는 않는다. 그렇다면 우선적으로 사용해야 하는 연산자의 순서는 어떻게 될까



= IN BETWEEN LIKE



필자가 경험한 바로는 위 순서대로 우선 순위를 정할 수 있다. 어떤 상황에서나 항상 100%로 적용 가능하지는 않지만 99% 이상이라고 생각한다.



홍길동을 찾아라

사람을 쉽게 찾으려면 주소를 알아야 한다. 주소를 모른다면 우리는 전국 방방곡곡을 찾아 다녀야 할 것이다. 참고로 현재의 도로명 주소 체계가 나오기 전의 주소는 시군구 + 읍면동 + 번지수 로 구성되었다. 만약 주소를 알더라도 일부분만 안다면 어떻게 될까



용상동 7142 번지에 사는 홍길동 → 시군구를 모르는 경우
안동시 7142 번지에 사는 홍길동 → 읍면동을 모르는 경우
안동시 용상동 번지에 사는 홍길동 → 번지수를 모르는 경우



위의 3가지 경우에서 홍길동을 가장 찾기 쉬운 경우는 어떤 경우인가 또한 가장 찾기 어려운 경우는 어떤 경우인가 아마도 홍길동을 가장 찾기 쉬운 경우는 번지수를 모르는 경우일 것이다. 또한 가장 찾기 어려운 경우는 시군구를 모르는 경우일 것이다. 찾기 쉬운 순서대로 나열하면 아래와 같다.



번지수를 모르는 경우 : 읍면동을 모르는 경우 : 시군구를 모르는 경우



결론은 주소의 앞부분을 모르는 경우보다 뒷부분을 모르는 경우가 사람 찾기엔 휠씬 수월하다는 것을 알 수 있다. 주소의 뒷부분을 모른다면 한정된 지역만 찾으면 되지만 주소의 앞부분을 모른다면 전국을 찾아 다녀야 할지도 모른다. 오라클의 튜닝에서도 이와 크게 다르지 않다. 지금까지의 상황을 오라클에 빗대어 재해석 해보자.

데이터를 쉽게 찾으려면 인덱스가 존재해야 한다. 인덱스가 없다면 우리는 테이블 전체를 FULL SCAN 해야 할 것이다. 참고로 예전의 주소 테이블의 인덱스는 시군구 + 읍면동 + 번지로 구성됨을 우리는 이미 알고 있다. 만약 인덱스가 있더라도 일부분만 사용 한다면 어떻게 될까



AND 시군구 LIKE → 시군구를 잘 모르는 경우(EQUAL 미사용)
AND 읍면동 =
AND 번지 =
AND 시군구 =
AND 읍면동 LIKE → 읍면동을 잘 모르는 경우(EQUAL 미사용)
AND 번지 =
AND 시군구 =
AND 읍면동 =
AND 번지 LIKE → 번지를 잘 모르는 경우(EQUAL 미사용)



위의 3가지 쿼리에서 가장 빠른 쿼리는 어떤 쿼리인가 또한 가장 느린 쿼리는 어떤 쿼리인가

결론은 결합인덱스의 앞부분을 모르는 쿼리보다 뒷부분을 모르는 쿼리가 휠씬 더 빠르다는 것을 알아야 한다. 결합 인덱스의 뒷부분을 모른다면 한정된 Filter만 일어나지만 결합 인덱스의 앞부분을 모른다면 전체 인덱스를 Filter 해야 할 것이다.



두부가게와 쌀가게

지난 연재에서 쌀 한 포대와 두부 한 모를 사야 하는 심부름에 대해 언급한 적이 있다. 이에 약간의 부연 설명을 추가 하고자 한다. 대부분의 사람들은 두부가게부터 먼저 방문해야 함을 잘 알고 있다. 계산을 통해 증명할 필요는 없지만, 굳이 계산식을 통한 이해가 필요하다면 아래와 같다.



W = F * S 일 = 힘 * 거리
F = M * A 힘 = 무게 * 가속도
W = M * A * S 일 = 무게 * 가속도 * 거리



결국 심부름은 일이고, 일은 거리나 무게에 비례함을 우리는 알 수 있다. 아래 [그림 3]을 보면 두부가게를 먼저 방문 하는 경우보다 쌀가게를 먼저 방문 하는 경우가 2배 가까이 일이 많음을 알 수 있다.

column_img_2630.jpg

[그림 3] 심부름의 일의 양 계산

우리는 심부름을 할 때 일의 양을 계산하여 행동하지는 않는다. 아무리 복잡하고 힘든 심부름도 본능적으로 최적의 방법으로 수행한다. 오라클 튜닝도 마찬가지다. 필자는 지금까지 계산하여 튜닝한 적이 거의 없다. 그냥 본능적으로 심부름 하듯이 튜닝을 했을 뿐이다. 단지 심부름에서는 무게와 거리와 방문 순서가 중요하지만, 튜닝에서는 블록과 인덱스와 접근 방향이 중요한 요소임을 잊지 말자. (다음 회에 계속)