전문가칼럼

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

오라클 플랜을 보는 법

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2015-03-04 00:00
조회
28059




◎ 연재기사 ◎


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

오라클 플랜을 보는 법



이병국 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 원리를 아는 것을 목표로 한다.



컴퓨터와 인간의 대결

1997년 세계 체스 챔피언 게리 카스파로프는 IBM 컴퓨터 딥블루와의 체스 대결에서 패배하였다. 사람들은 컴퓨터의 승리에 놀라워했지만 사실 딥블루는 카스파로프의 수많은 경기 기보를 기억하고 분석하여 만반의 준비를 하고 시합에 나선 것이었다. 즉 딥블루는 체스 경기를 사고(思考)로서 주도한 게 아니라 기억된 정보를 바탕으로 최적의 경우의 수를 찾은 것이었다.


그래서 딥블루의 승리는 컴퓨터의 승리가 아니라 알고리즘을 만든 사람의 승리였다고 할 수 있다. 미국의 체스 챔피언 출신의 조엘 벤자민이 과거 100년간의 주요 기보와 체스 대가들의 경기 스타일을 모두 프로그램에 입력한 것이 크게 기여한 결과였다. 딥블루는 세계 체스 챔피언과의 경기에서는 승리하였지만 세계적인 토너먼트 대회에서는 결과가 신통치 않았다고 한다. 그 이유는 수많은 참가자들에 대한 분석이 완벽하게 이루어지지 않았기 때문이었다. 컴퓨터가 경험하지 못한 이상한 수에 대해 제대로 대응을 할 수 없었다고 한다.


사람과 슈퍼 컴퓨터의 퀴즈 대결에서 컴퓨터가 우승한 적도 있지만, 이때도 사고에 의한 것이 아니라 방대한 데이터 축적과 이 분석에 최적화한 슈퍼 컴퓨터에 의한 것이었다. 여러 부분에서 사람을 이긴 컴퓨터지만, 바둑에서는 아직까지 사람의 능력에 미치지 못한다. 바둑에서 컴퓨터가 사람을 이기려면 최소 10~50년 이상 걸릴 것이라고 많은 전문가들은 예상하고 있다.


가로 세로 19줄 바둑의 복잡도는 체스의 ’10의 452승’배에 달한다고 한다. 아무리 게임을 많이 하더라도 같은 게임은 나올 수 없다는 의미며, 그 복잡한 정도가 마치 우주의 크기와 같다는 어느 과학자의 말도 있다. 현재 컴퓨터의 바둑 실력은 프로기사와의 시합에서 6점 접고 두어야 겨우 이길 수 있는 수준이라고 한다.


이번 연재의 내용인 오라클 플랜에서도 컴퓨터와 인간의 대결이 나타난다.



플랜이란 무엇인가

CBO 방식에서 옵티마이저는 주어진 환경(통계정보, SQL문) 하에서 최적의 실행계획(PLAN)을 우리에게 제공한다. 옵티마이저는 우리에게 어떤 경로로 테이블을 접근하는지, 어떤 방식으로 조인하는지, 어떤 인덱스 자원을 사용하는지 등에 대한 최적화한 계획(플랜)을 알려준다.


만약 옵티마이저가 제공하는 플랜이 완벽하다면 오라클은 우리에게 플랜을 제공할 필요가 없으며, 쿼리문에 힌트절을 허용할 이유도 없을 것이다. 물론 개발자들인 우리도 플랜에 대해 알아야 할 이유가 전혀 없을 것이다. 하지만 플랜은 완벽하지 않다. 오히려 간혹 잘못된 정보를 주곤 한다.


체스 챔피언과의 시합에서는 수많은 기보에 대한 충분한 분석이 이루어져서 우승하였지만, 수많은 개발자들이 작성한 무수히 많은 쿼리문에 대해 케이스별로 올바르게 분석하고 대응한다는 것은 불가능에 가깝다. 또한 퀴즈 대결에서는 방대한 데이터 축적과 빅데이터 처리에 최적화한 강력한 성능의 슈퍼 컴퓨터로 우승하였지만, 오라클 서버는 슈퍼컴퓨터가 아니라 그냥 약간 좋은 컴퓨터일 뿐이다. 그리고 쿼리를 분석하는 데 충분한 시간도 주어지지 않는다.


이와 같이 한정된 분석자료, 한정된 서버자원, 한정된 처리시간 하에서 오라클 옵티마이저가 우리에게 완벽한 플랜을 제공하리라는 어떠한 희망도 근거도 없다. 단지 대체로 괜찮은 플랜을 제공할 뿐이다. 수시로 올바르지 않은 플랜을 제공하기도 한다. 이와 같은 이유 때문에 우리는 플랜을 볼 줄 알아야 하고, 이해할 줄 알아야 하고, 비교할 줄 알아야 하며, 변경할 줄 알아야 한다.


이번 연재 목표는 플랜을 쉽게 보고 쉽게 이해하는 것이다. 또한 플랜을 비교하는 것이다. 비교하기 위해서는 오라클 옵티마이저가 제공하는 플랜 외에 개발자 자신이 생각하는 플랜이 있어야 한다. 플랜을 비교할 때는 개발자가 생각하는 플랜이 항상 올바른 것이고 옵티마이저가 제공하는 플랜은 틀렸다고 전제하고 시작해야 한다. 대부분은 일치하겠지만 만약 서로 다르다면 개발자 플랜을 우선해야 한다. 옵티마이저의 플랜은 의심스러운 눈길로 바라봐야 한다.


연재의 말미에는 개발자가 생각하는 플랜을 필자는 어떤 방법으로 쉽게 표현하는지에 대한 내용도 있으며, 쿼리 제작 시 개발자의 생각(플랜)을 어떤 방법으로 쿼리에 포함 시키는지에 대한 내용도 있다.



오라클 플랜 보는 법

플랜에 대한 내용은 기본적인 내용이라 일반적인 DB 도서에서는 자세한 내용을 설명하지 않는다. 하지만 필자가 경험하기로는 상당수 개발자들이 의외로 모르고 있는 경우를 많이 보았다. 심지어 쿼리 작성 후 데이터 결과만 확인할 뿐 플랜을 아예 보지 않는 개발자도 많았다. 쿼리 제작 후에는 반드시 플랜을 확인하는 습관을 가져야 한다. 아래 플랜을 보면서 하나씩 배워 보자.


column_img_1604.jpg

위 플랜을 해석하면 다음과 같다.


ACCESS FULL : 고객 테이블 FULL SCAN(전체 접근)

Cost=633K : 633,000 비용발생(논리적 비용 = IO + MEM + CPU + NET + …)

Card=42M : 42,000,000건(접근하는 레코드 수)

Bytes=15G : 15,000,000,000(42,000,000 * 1 ROW의 총 길이)


ACCESS FULL은 고객 테이블을 FULL SCAN하겠다는 의미다. 어떠한 인덱스도 통하지 않고 테이블을 직접 접근해 전체 데이터를 읽는다는 의미다. 플랜에서 이 용어가 보인다는 것은 다음의 3가지 경우에 해당한다.


첫째, 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로써 필요한 인덱스를 생성함으로써 해결 가능하다.

둘째, 인덱스는 존재하지만 부정확한 통계정보로 인하여 인덱스를 타지 않는 경우로써, 최신의 통계 정보를 구성하거나 힌트절을 사용해 해결할 수 있다.

셋째, 테이블 FULL SCAN하는 것이 인덱스를 통한 랜덤 엑세스보다 유리한 경우로써, 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별로 효용성이 없을 때다. 위의 쿼리는 조회조건이 없으므로 바로 이 경우에 해당한다.


Cost는 비용을 의미하는데 해당 쿼리가 동작되었을 때 소요하는 비용을 말한다. 비용이 크면 클수록 오라클이 많은 일을 하고 있다고 생각하면 된다. 즉 무거운 쿼리인 것이다. 여기서 비용은 물리적 비용이 아니라, 논리적 비용을 의미한다. 논리적 비용이란 직접적이고 구체적인 수치에 의해 명확하게 알 수 있는 비용이 아니다. 오라클 옵티마이저가 산출한 비용에 대해 우리는 왜 그러한 비용값이 계산되었는지 이해할 필요까지는 없다. 단지 플랜 비교 시 비교 기준 값으로는 삼을 수 있다.


Card(Cardinality)는 쿼리 조건에 맞는 레코드 건수를 의미한다. 참고로 우리는 K는 10의 3승을 의미하고, M은 10의 6승을 의미하고, G는 10의 9승을 의미함을 이미 알고 있다. 위의 플랜에서 Card 값은 42M이므로 고객 테이블의 데이터 건수가 4,200만임을 알 수 있다.


Bytes는 쿼리 실행,시 발생하는 네트워크 트레픽을 의미한다. 즉 I/O 발생량이다. 1Row를 구성하는 컬럼의 길이 총 합을 구한 후 Card 값을 곱하면 된다. 결국 위의 플랜에서는 15,000,000,000 Byte라는 어마어마한 네트워크 트레픽이 발생함을 알 수 있다.


위의 플랜을 종합하면, 해당 쿼리는 고객 테이블을 FULL SCAN으로 접근해 4,200만 건의 데이터를 읽어오며, 이때 15,000,000,000Byte의 네트워크 트래픽을 유발한다. 그리고 이때 비용은 633K가 발생함을 오라클 옵티마이저가 알려 주고 있다.


그럼, 계속해서 다음 플랜을 살펴보자. 아래 플랜에서 우리는 플랜을 해석하는 순서를 알아볼 것이다. 또한 ORDER BY 절이 있는 쿼리의 비용(Cost) 부분에 주목해서 살펴볼 것이다.


column_img_1605.jpg


플랜을 해석하는 순서는 다음과 같다.


첫째, 레벨(깊이)이 다른 경우에는 안쪽 레벨부터 해석한다.

둘째, 레벨(깊이)이 같은 경우에는 위에서 아래로 해석한다.


따라서 위 플랜은 1번(가져와서), 2번(SORT해서), 3번(보여준다)순으로 해석하면 된다.


플랜을 자세히 보면, 고객 테이블을 FULL SCAN하는 1번의 비용(Cost)은 633K인데 반해, SORT하는 2번의 비용(Cost)은 6M임을 알 수 있다. 결국 4,200만 건의 데이터를 가져오는 비용보다, 가져온 데이터를 SORT하는 비용이 10배 가량 높음을 알 수 있다. 대부부분의 개발자들은 튜닝 시 조건절 컬럼의 인덱스 유무에 관심을 갖지만, SORT를 없애는 것도 튜닝에서 중요한 부분임을 알 수 있다.


계속해서 다음 2개의 플랜을 동시에 살펴보자.

column_img_1605_01.jpg


column_img_1606.jpg



위 2개의 플랜에서 쿼리의 차이점은 ROWNUM < = 1 혹은 2 부분이다. 테이블을 FULL SCAN하기 위해 접근하지만 COUNT(STOPKEY) 부분에서 레코드 건수가 1 혹은 2가 되었을 때 SCAN을 중지하고 빠져 나옴을 알 수 있다. 2개의 플랜에서 ROWNUM 값이 1 혹은 2에 따라서 Card 값과 Bytes 값이 배수가 됨을 알 수 있다. 그런데 비용(Cost) 값은 왜 같을까 그것은 고객 테이블의 첫 번째 레코드와 두 번째 레코드가 동일 블록에 저장돼 있기 때문일 것이다. 참고로 오라클은 최소 운반 단위인 블록 단위로 데이터를 운반한다.


이번엔 좀더 복잡한 플랜을 인덱스 생성도와 같이 비교해 살펴보자.


column_img_1607.jpg


플랜의 해석 순서는, 깊이가 다른 경우에는 안쪽에서 바깥쪽으로, 깊이가 같은 경우에는 위에서 밑으로 해석한다고 이미 설명하였다. 따라서 위 플랜은 1 → 2 → 3 → 4 → 5 → 6순으로 해석한다. 그리고 플랜의 내용에서 스캔은 UNIQUE SCAN임을 알 수 있고, 두 테이블의 조인 방식은 NESTED LOOP JOIN임을 알 수 있다. 즉, 순차적 루프에 의한 접근 방식이다. 조인 방식에 대한 더 자세한 내용은 지난 연재인 ‘오라클 조인의 방식’에서 확인할 수 있다.


플랜의 해석 순서를 그림으로 변환하면 인덱스 생성도와 동일하다는 것을 우리는 알 수 있다.

(주문번호 인덱스 → 주문 테이블 → 고객번호 인덱스 → 고객 테이블)


이번에는 고객 테이블에 인덱스가 없는 경우를 가정해 보았다. 인덱스 생성도와 같이 살펴보자.
column_img_1608.jpg



고객 테이블의 고객번호 컬럼에 인덱스가 없어서 고객 테이블에서 FULL SCAN이 발생하고 있다. 실제 리턴 결과 건수는 1건이지만, 인덱스가 존재하지 않음에 따라 고객 테이블의 전체 데이터를 FULL SCAN하고 있는 것이다. 여기에서 우리는 Card=1이라는 것에 주목할 필요가 있다. 비록 인덱스는 없지만 고객 테이블에 통계정보가 구성돼 있음을 유추할 수 있고, 고객번호는 UNIQUE함을 추정할 수 있다. 따라서 고객번호 컬럼을 인덱스로 생성해야 함을 알 수 있다. 플랜에서는 인덱스를 생성해야 할 컬럼을 직관적으로 바로 알기는 어려우나, 인덱스 생성도를 함께 이용하면 어떤 위치에 어떤 인덱스를 생성해야 하는지 한눈에 알 수 있다.


덧붙여, 테이블을 FULL SCAN한다는 의미는 다음의 3가지 경우에 해당함을 다시 한번 강조한다.


첫째, 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로서 필요한 인덱스를 생성해 해결 가능하다.
둘째, 인덱스는 존재하나 부정확한 통계정보로 인하여 인덱스를 타지 않는 경우로서 최신의 통계 정보를 구성하거나 힌트절을 사용해서 해결 가능하다.
셋째, 테이블을 FULL SCAN하는 것이 인덱스를 통한 랜덤 엑세스보다 유리한 경우로써 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별 효용성이 없을 때다.


이번에는 조인절 양방향 모두에 인덱스가 없는 경우를 가정해 보았다.


column_img_1609.jpg


위 쿼리의 문제점은 이런 경우 두 테이블 간 조인 방식은 예전에는 Sort Merge Join 방식으로 풀리는 경우가 많았다. 처리 순서는 다음과 같다.


첫째, 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한 후 고객번호 순으로 정렬한다(SORT).

둘째, 주문 테이블에서 주문일자가 ‘20150112’인 주문을 구한 후 고객번호 순으로 정렬한다(SORT).

셋째, 정렬된 고객 정보와 주문 정보를 고객번호 컬럼으로 결합한다(MERGE).


하지만 Sort Merge Join 방식은 성능상 문제가 많은 조인 방식이다. 해결 방안은 조인절 컬럼에 인덱스를 생성하여 Nested Loop Join 방식으로 플랜이 풀리게 해야 한다. 만약에 고객 테이블의 고객번호를 인덱스로 생성한다면 주문 테이블에서 고객 테이블로 순차적으로 접근할 것이고, 주문 테이블의 고객번호를 인덱스로 생성한다면 고객 테이블에서 주문 테이블로 순차적으로 접근할 것이다.


만약, 업무 로직상 조인절에 인덱스를 생성하기 곤란한 상황이라면 힌트절을 추가해 Hash Join 방식으로 접근하는 것도 좋은 방법이다. 대부분 Hash Join 방식이 Sort Merge Join 방식보다 성능이 더 좋다. 그래서 요즘 Sort Merge Join 방식은 거의 볼 수 없고 Hash Join 방식을 많이 사용한다.


아래의 경우가 힌트절을 추가해Hash Join 방식을 설명한 것이다.


column_img_1610.jpg


Hash Join 방식은 해시 함수를 이용한 접근 방식인데, 대량의 데이터 처리에 효율적인 조인 방식이다. Nested Loop Join 방식에서 처리 범위가 부담스럽거나, Sort Merge Join 방식에서 정렬(Sort)이 부담스러울 때 사용한다. Hash Join 방식의 처리 순서는 다음과 같다.


첫째, 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 분류해해시 테이블을 생성한다(해시 함수를 이용해 해시 테이블 생성).

둘째, 주문 테이블에서 주문일자가 ‘20150112’인 주문을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 변환해해시 테이블로 순차적으로 접근한다(해시 함수를 통해 해시 테이블 탐색).


메모리에 해시 테이블을 생성하고 해시 함수를 이용하여 연산 조인을 하기 때문에 CPU 사용이 증가할 수 있으므로 조회 빈도가 높은 온라인 프로그램에는 적합하지 않는 조인 방식이다.


끝으로, UNION ALL과 UNION 관련하여 다음 2개 플랜을 동시에 살펴보자.


column_img_1611.jpg



쿼리에서 UNION ALL 구문을 사용하면 중복되는 데이터를 있는 그대로 모두 보여준다. 하지만 UNION 구문을 사용하면 중복되는 데이터를 제거하고 UNIQUE하게 보여준다는 것을 우리는 이미 알고 있다. 위의 2개 플랜의 차이점은 SORT (UNIQUE) 부분이다. 이것의 의미는 데이터를 정렬한 후에 중복된 데이터를 제거하고 UNIQUE하게 보여 준다는 의미다.


그런데 SORT는 왜 하는가 왜냐하면 SORT는 중복된 데이터를 제거하는 가장 단순하고 쉬운 방법이기 때문이다. SORT 이후에 동일한 값이 연속 되어 있으면 제거하면 되기 때문이다.


예전에는 화투 1~2세트는 갖고 있는 집이 많았다. 화투는 기본적으로 1월(솔)부터 12월(비)까지 총 48장으로 구성되는데, 만약 화투 개수가 48장 이상이라면 중복된 화투장을 찾아 빼야 할 것이다. 이때 화투를 모두 펼쳐놓고 같은 모양을 찾아서 빼기란 쉬운 일이 아니다. 아마도 대부분의 사람들은 1월부터 12월까지 화투를 정렬(SORT)한 후, 중복되는 화투장을 찾아내는 방법을 쓸 것이다. UNION 구문 사용 시에 오라클 옵티마이저도 우리와 동일한 방법으로 중복된 데이터를 제거한다. 바로 SORT(정렬)을 이용하고 있는 것이다.



오라클 옵티마이저의 실행계획과 개발자의 실행계획

오라클 옵티마이저의 실행계획(Plan)은 통계정보(과거+현재)를 기반으로 한다. 그에 반해 개발자의 실행계획(Plan)은 과거 + 현재 + 미래 정보를 기반으로 한다. 당연히 개발자의 실행 계획이 더 옳을 것이다. 개발자들은 향후 데이터 증감 추이가 어떻게 될 것인지, 생성 예정인 인덱스는 무엇인지, 또 프로그램을 누가 어떤 용도로 얼마나 빈번하게 사용하는지 등, 오라클 옵티마이저보다 더 많은 정보를 얻을 수 있기 때문에 더 좋은 플랜을 계획할 수 있다.


오라클 옵티마이저 실행계획과 개발자의 실행계획이 일치하면 좋겠지만 항상 일치하진 않는다. 지금까지의 경험상으로 90% 이상은 일치했고, 10% 미만은 일치하지 않았다. 그렇다면 왜 일치하지 않는 걸까 다음과 같은 여러 가지 이유로 인하여 일치하지 않았다.


첫째, 통계정보 구성이 실제 데이터를 반영하지 못하거나 없는 경우

둘째, 적절한 인덱스가 존재하지 않거나 부적절한 경우

셋째, 쿼리가 최적화 되어 있지 않는 경우나 잘못 사용된 경우

넷째, 오라클 옵티마이저의 알고리즘이 완벽하지 않다는 현실적인 문제


실행계획이 일치하지 않는 경우에는 통계정보를 재구성하거나 필요한 인덱스 생성 및 쿼리 최적화로 어느 정도 해결할 수 있다. 하지만 옵티마이저의 알고리즘 기술수준 문제에 있어서는 해결 방법이 없다. 이때에는 힌트절을 추가하여 인위적으로 플랜을 변경해야 한다. 오라클이 힌트절 기능을 제공한다는 의미는 곧 오라클 옵티마이저의 알고리즘이 완벽하지 않다는 의미다. 향후 오라클 버전에서 성능 개선이 점차 이루어진다면 힌트절 기능이 축소되거나 동작에 제한이 걸릴 수도 있다. 이는 개발자 중심의 실행계획에서 오라클 중심의 실행계획으로 주도권이 넘어간다는 의미다. 만약 그런 상황이 실제로 온다면 개발자들에게 좋은 상황인지 나쁜 상황인지 고민스럽다.


오라클 옵티마이저의 실행계획은 어떤 내용인지 이미 알고 있다. 그렇다면 개발자의 실행계획은 무엇인가 개발자의 실행계획은 개발자의 마음속에 있는 실행 계획이다. 쿼리를 제작할 때 대부분의 개발자들은 이미 마음속에 실행계획(접근순서, 접근방법, 자원선택)을 갖고 있다. 하지만 그러한 실행계획은 어느 순간이 지나면 잊혀지는 찰나적인 생각일 뿐이다. 이러한 고민을 해결하고자 필자는 쿼리를 제작할 때 실행계획의 내용을 쿼리에 포함시키는 방법을 사용한다. 바로 공정 쿼리다.


column_img_1612.jpg




우리는 인덱스 생성도에서 테이블 접근 순서 및 인덱스 생성 위치를 알 수 있다. 또한 공정쿼리 작성법에 의해 작성된 쿼리에서도 테이블 접근 순서 및 인덱스 생성 위치를 쉽게 알 수 있다. 작성된 쿼리에 개발자가 생각하는 실행 계획이 내포되어 있어서, 오라클 옵티마이저가 제시하는 플랜과의 비교 작업이 한결 쉬워졌고 언제든지 가능해졌다.


공정쿼리 방식으로 제작한 쿼리는 다른 개발자가 처음 보더라도 쉽게 플랜을 유추해 낼 수 있고 인덱스 생성 위치를 바로 알 수 있어서 좋다. 더 자세한 내용은 지난 연재인 ‘공정쿼리 작성법’을 참고하기 바란다. 결론적으로 인덱스 생성도와 공정쿼리와 플랜은 형태는 틀리지만 본질적으로 동일한 의미를 지니고 있으므로 삼위일체라 생각할 수 있다.



바인드 변수와 하드 파싱

대부분의 개발자들은 튜닝시 플랜을 상수값으로 테스트하지만, 실제로 바인드 변수로 운영되는 경우에는 플랜이 다를 수 있다. 프로그램에 바인드 변수로 되어 있다면 반드시 바인드 변수로 플랜을 확인해야 한다. 아래 두 개의 쿼리를 살펴보자.


SELECT * FROM 고객 WHERE 고객명 = ‘홍길동’ → 상수값

SELECT * FROM 고객 WHERE 고객명 = :NAME → 바인드 변수


위의 두 개의 쿼리는 동일한 결과를 조회하지만 플랜은 다른 수 있다. 이와 같은 쿼리들은 대부분의 경우 플랜이 동일하지만 간혹 다른 경우도 있으므로, 쿼리가 어떤 방식으로 운영되는지에 따라 플랜을 구분-확인해야 한다.


그럼, 바인드 변수를 사용하는 이유는 무엇일까 바인드 변수 사용은 하드 파싱을 줄이기 위한 수단이다. 오라클 옵티마이저는 상수값이 다르면 서로 다른 쿼리로 인식해 파싱을 새로 한다. 특히 실행 횟수가 많고 컬럼의 distinct 값이 크다면, 하드 파싱이 자주 발생해 시스템 전반적으로 많은 메모리를 사용할 것이고 CPU 부하도 높을 것이다. 반면에 바인드 변수를 사용한다면 오라클 옵티마이저는 동일한 쿼리로 인식하기 때문에 파싱을 매번 하지는 않는다. OLTP 프로그램은 데이터베이스 성능을 고려해 바인드 변수 사용을 권고한다.


이번 연재에서는 오라클 옵티마이저가 제공하는 플랜과 개발자가 생각하는 플랜을 어떠한 방법으로 쿼리문에 구현해야 하는지에 대해 배웠다. 다음 연재에서는 개발자들의 영원한 숙제인 ‘NULL’에 대해 자세히 알아본다.



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



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

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



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



column_img_1613.jpg


[이번 호 문제]

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



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



column_img_1614.jpg