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

이병국 andongcn@dreamwiz.com 프리랜서 DB 엔지니어로서 동아제약 전산실에서 SW 개발 업무를 시작으로 프리랜서 개발자로 독립해 활동하던 중 우연한 기회에 DB와 인연을 맺게 됐다. 현재 삼성생명 전산 운영팀에서 쿼리 성능을 개선하는 DB 튜닝과 IOA 업무를 맡고 있다. 개발자 출신의 DB 엔지니어로 활동하면서 개발자에게 DB 관련 지식이 꼭 필요함을 절감했다. ‘정보의 불균형이 시장 왜곡을 가져온다’는 ‘레몬시장이론’은 중고차 거래에서 흔히 나타나기 쉽다. 좋은 차와 나쁜 차를 아는 중개인과 모르는 구매자 사이에는 정보의 비대칭 때문에 구매자가 손해를 볼 수 있다. 구매자도 차에 대해 기본적인 지식을 알고 있어야, 정보의 균형이 맞으므로 서로 손해를 보지 않고 합리적인 가격에 차를 거래할 수 있다. 마찬가지로 개발자들도 DB에 대해 기본적인 지식을 습득하여 정보의 균형을 맞추면, DB 엔지니어와 협업이 더 쉬워지고 한 단계 더 발전한 자신의 모습을 발견할 것이다.

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

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

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

이번 연재에서는 쿼리 최적화 및 튜닝을 고려한 쿼리 작성법에 대해서 소개한다. 인덱스 생성도와 공정쿼리 작성에 앞서 테이블 접근 순서를 결정할 때 고려해야 할 내용을 중심으로 알아본다.

배낭여행 스마트앱 만들기

필자는 아래 그림과 같은 배낭여행 앱을 기획해 보았다. 앱은 가로 4개 블록, 세로 3개 블록의 미로로 구성된다. 숫자가 적힌 6군데의 포지션을 모두 방문하면 게임은 종료된다. 각 포지션 방문 시 적혀 있는 숫자(kg)만큼 조약돌을 채워야 하므로 배낭은 무거워지거나 가벼워진다. 또한 깃발이 표시된 포지션에서만 출발할 수 있으며, 최종 목적지에 도착 했을 때 에너지 소모가 가장 적은 여행자가 승리를 한다. 이제 여러분은 최소 에너지를 소모하는 최적의 여행 경로를 찾아야 한다. 여기서 에너지 소모란 이동한 거리×배낭 무게의 총합을 의미한다. Let’s Go~



여기에서 우리는 다음과 같은 사실을 알 수 있다. 어떠한 경로를 선택하든지 간에 여행이 끝났을 때 배낭의 최종 무게는 10Kg으로 항상 동일할 것이다. 그러나 어떤 경로를 선택하느냐에 따라서 에너지 소모량은 다를 것이다. 필자는 아래와 같이 경로를 선택해 보았다.



각각의 구간 경로에 따라서 배낭의 무게가 달라지므로 약간의 계산이 필요하지만, 크게 어렵지 않게 구할 수 있다. 필자가 선택한 여행 경로에 따른 총 에너지 소모량은 34Cost다. 첫 번째 스테이지는 쉽게 통과했다. 2번째, 3번째 스테이지로 넘어갈수록 점점 더 복잡한 N×N 블럭맵이 나타날 것이다. 또한 포지션의 개수는 점점 더 많아질 것이고, 조약돌의 무게는 점점 더 무거워질 것이다.

배낭여행 앱 게임을 잘하기 위한 중요한 기본적인 규칙이 세 가지가 있다.

첫째, 첫 진입 포지션을 잘 선정해야 한다(출발 포지션에서 가벼운 배낭 무게로 출발해야 한다).
둘째, 진입 이후 그 다음 포지션 선정 시 가까운 거리에 있는 작은 숫자를 우선해 선택한다.
셋째, 한 번 지나간 경로는 다시 지나가지 않게 경로를 선택한다(중첩 경로 지양).

‘이러한 기초적인 앱 시나리오에 스토리를 더하고, 움직이는 이미지를 덧붙이고, 반복적인 멜로디를 가미한다면 재미있는 앱이 되지 않을까?’ 하고 나름 생각해 본다. 이 앱의 사용 대상은 덧셈, 뺄셈, 곱셈을 배우는 초등학교 저학년용으로 적합 할 듯 하다.

필자가 이번 연재에 이 앱을 소개하는 이유는 실제로 앱을 만들기 위해서가 아니다. 우리가 인덱스 생성도와 공정쿼리를 작성하기 위하여 테이블 접근 순서를 정할 때 가장 기본이 되는 기초적인 노하우와 일맥상통하기 때문이다.

오라클 CBO 방식과 통계정보

우리가 알고 있는 오라클 CBO(Cost Based Optimizer) 방식은 통계정보를 이용해 비용을 계산한 후 SQL을 수행한다. 통계정보가 존재하고 비교적 정확하다면 오라클은 대부분의 쿼리에서 올바르고 적절한 플랜을 우리에게 제공하고 쿼리를 수행할 것이다.

만약, 통계정보가 존재하지 않는다면 혹은 존재하지만 실제와는 차이가 있는 통계정보를 가지고 있다면 오라클은 우리에게 정확한 플랜을 제공하지도 않을뿐더러 쿼리 수행도 최적이지 않다. 실제로 운영을 하다 보면, 이런 경우가 빈번히 발생함을 알 수 있을 것이다. 설사 통계정보가 존재하고 정확하다고 해도 항상 올바른 플랜을 제공한다고 보장하지는 못한다.

오라클 CBO 방식에서 비용이란 물리적인 비용이 아니라 논리적인 비용을 의미한다. 여기서 논리적인 비용이란 어떤 근거로 비용이 산출되었는지 명확하게 알 수 없다는 말과 같을 수도 있다. 혹은 오라클에서 명시적으로 비용 산출 계산 방식을 공개하지 않는다는 말과도 동일할 것이다.

이와 같은 여러 가지 이유 때문에 우리는 알고 있는 업무 지식을 바탕으로 스스로 비용을 예측하고 플랜을 예상할 수 있어야 한다. 이번 연재의 핵심은 테이블 접근 순서를 정하고 인덱스 생성도를, 그리고 공정 쿼리를 작성하는 것에 있다.

테이블 접근 순서 규칙 1: 진입형 테이블을 결정하라!

쿼리의 조건 중에서 가장 선택도가 좋은 컬럼의 테이블을 최초 진입형 테이블로 결정한다.



위의 쿼리에서 교육평가 테이블의 평점 컬럼을 최초 진입형 조건으로 결정 했을 때 인덱스 생성도는 아래와 같다.

테이블 접근 순서 규칙 1: 진입형 테이블을 결정하라!

쿼리의 조건 중에서 가장 선택도가 좋은 컬럼의 테이블을 최초 진입형 테이블로 결정한다.



위의 쿼리에서 과목 테이블의 과목명 컬럼을 최초 진입형 조건으로 결정 했을 때, 인덱스 생성도는 아래와 같다.

테이블 접근 순서 규칙 1: 진입형 테이블을 결정하라!

쿼리의 조건 중에서 가장 선택도가 좋은 컬럼의 테이블을 최초 진입형 테이블로 결정한다.



위의 쿼리에서 사원 테이블의 성명 컬럼을 최초 진입형 조건으로 결정 했을때 인덱스 생성도는 아래와 같다.

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

테이블 접근 순서 규칙 2: OUTER JOIN보다 INNER JOIN을 우선하라!

쿼리의 조인절에서 OUTER JOIN 보다 INNER JOIN 테이블을 먼저 접근한다.



위의 쿼리의 조인절에서 강사 테이블보다는 과목 테이블이나 교육평가 테이블의 접근을 우선으로 한다.

테이블 접근 순서 규칙 3: 연결 확장형보다는 연결 축소형 테이블을 우선하라!

쿼리의 조인절에서 연결 확장형보다 연결 축소형 테이블을 먼저 접근한다.



위의 쿼리의 조인절 테이블인 과목과 교육평가 테이블 중에서 조건(A+) 필터로 인하여 레코드 축소가 일어날 가능성이 더 높은 교육평가 테이블의 접근을 우선으로 한다.

인덱스 생성도와 공정쿼리 재작성하기

인덱스 생성도를 통하여 테이블 접근 순서와 각 테이블의 인덱스 생성 포인트를 알 수 있다.



위의 인덱스 생성도 최종본을 통하여 공정 쿼리를 재작성해 보자.



이번 연재를 통해 우리는 오라클 CBO 방식 하에서 테이블 접근 순서에 대한 기본 규칙을 배웠다. 그리고 인덱스 생성도와 공정 쿼리를 통해 플랜 정보를 알 수 있었고, 인덱스 생성 포인트를 알 수 있게 되었다. 더블어 오라클에서 우리에게 일방적으로 제공하는 플랜 정보가 아닌, 우리 스스로 비용을 예측하고 플랜을 예상하는 방식에 대해 배웠다. 다음 연재에서는 만능쿼리와 한방쿼리에 대한 내용으로 여러분을 다시 찾아 뵙고자 한다.

용기를 갖자 오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로 쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다. 이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...

[지난 문제의 정답과 풀이]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.



해설 : FROM절, 조인절, 조건절 모두에 테이블 접근 순서에 따라서 쿼리를 작성한다. 이렇게 작성한 공정쿼리에서, 누구라도 플랜을 알 수 있고 인덱스 생성 포인트를 쉽게 알 수 있다.

[이번 호 문제]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

각 연재의 말미에 간단하면서도 재미있고 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에서 한다.





 

출처 : 한국데이터베이스진흥원

제공 : 데이터전문가 지식포털 DBguide.net

[출처] http://www.dbguide.net/knowledge.db?cmd=view&boardUid=182744&boardConfigUid=19&boardStep=&categoryUid=196