전문가칼럼

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

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

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2014-12-17 00:00
조회
20796




◎ 연재기사 ◎


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

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



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

이번 연재에서는 오라클 CBO(Cost Based Optimizer) 방식과 RBO(Rule Based Optimizer) 방식에 대해 알아 본다. 옵티마이저의 의미와 그 차이점, 통계정보와 실행계획에 대해 살펴 보자.



어머니의 심부름: 두부가게와 쌀가게

만약 두부 한 모와 쌀 한 포대를 사야 한다면, 여러분은 어떤 가게를 먼저 방문할 것인가 대부분의 사람은 의심의 여지없이 두부 가게를 먼저 방문한 후 쌀가게로 향할 것이다. 어느 누구도 무거운 쌀 포대를 낑낑대며 들고서 두부 가게로 가려 하지는 않을 것이다. 사람은 본능적으로 ‘최소한의 힘’을 써서 심부름을 하려 한다. 이것은 교육 받은 것이 아니라, 경험을 바탕으로 본능적으로 판단하는 것이다.

아래 <그림 1> 동네 약도를 살펴보자. 동네 유일한 쌀가게와 3군데의 두부 가게가 있다. 일단 우리는 두부 가게를 먼저 방문하고 쌀가게를 방문할 것이다. 유일한 쌀가게는 선택의 여지없이 방문한다.

column_img_1581.jpg
<그림 1> 어머니의 심부름 하기: 두부가게와 쌀가게

두부가게는 3군데가 있으므로 상황에 따라 방문할 가게를 결정할 수 있다. 돈을 아껴야 한다면 두부가게 1을 방문할 것이고, 휴일이라면 두부가게 3을 방문할 것이고, 비가 온다면 최소 경로를 고려해 쌀가게와 가장 가까운 두부가게 2를 선택할 것이다. 특별한 고민 없이 쉽게 심부름을 하는 것처럼 보이지만 실제적으로는 수많은 정보가 제공되었고, 수많은 고민 끝에 최선의 심부름 방법이 결정된다.

우리가 인식하지 못하는 찰나에도 가게의 위치, 거리, 방향, 가격, 고객에 대한 서비스 수준, 날씨 등 수많은 정보를 제공 받았고, 제공받은 정보를 종합적으로 판단해 방문할 가게를 결정했을 것이다. 이러한 행위는 너무나 짧은 시간에 자연스레 진행돼 본능적인 것으로 간주 될 수 있으나 실제로는 아주 치밀하게 판단된 결과다. 인간의 능력에 놀라울 따름이다. 최상의 심부름을 하기 위해서는 새로 생겨나는 가게 정보에 대해서도 항상 업데이트 되어야 할 것이다.

우리가 최소한의 힘으로 혹은 최소한의 비용으로 심부름을 하듯이 오라클도 우리와 동일한 방식으로 쿼리를 분석해 가작 낮은 비용으로 결과 데이터를 가져온다. 이번 연재의 내용 중에 하나인 CBO(Cost Based Optimizer) 방식이 바로 그러하다.



CBO 방식과 RBO 방식

CBO(Cost Based Optimizer) 방식은 비용 기반 옵티마이저이고, RBO(Rule Based Optimizer) 방식은 규칙 기반 옵티마이저다. 오라클 11g부터 RBO에 대한 기술 지원이 중단됐다. 이 말은 향후 개선은 없고 현재의 기능만 유지한다는 의미다. 이런 이유로 CBO와 비교를 논한다는 것은 무의미하지만, 지나간 기술을 되짚어 보는 것도 현재의 기술을 이해하는 데 도움이 되므로 간단한 개념만 알아보자.

RBO 방식은 규칙 기반 옵티마이저로서 미리 정해진 우선 순위 규칙에 따라 접근 경로(Access Path)를 결정한다. 우선 순위가 높은 규칙이 낮은 규칙보다 우선 적용된다. 비록 잘못된 우선 순위의 규칙이 적용되더라도 예측이 가능하며, 안정적이고 플랜의 제어가 쉽다. 이런 이유 때문에 일부 DBA나 고급 개발자들은 이 방식을 선호하기도 한다. 하지만 이미 오라클에서 지원 중단을 선언하였기에 더 이상 RBO에 매달리는 것은 ‘죽은 자식 고추 만지기’와 같다. 우리는 앞으로 CBO 방식에만 집중하면 될 것이다.

반면 CBO 방식은 비용 기반 옵티마이저로서 통계정보에 따른 비용을 계산해 가장 최소한의 비용이 소모되는 접근 경로(Access Path)를 결정한다. 여기에는 I/O 비용뿐만 아니라 CPU 연산 비용 및 메모리 비용까지도 포함된다.

여기서 비용은 논리적 비용을 말하는데 논리적 비용이란 어떻게 산출되었는지 구체적으로 알 수 없다는 말과 같다. 혹은 어떻게 산출되었는지 공개할 수 없다는 말과도 동일하다. 그것은 오라클 옵티마이저의 핵심 기술에 대한 정보 보호 차원일수도 있고, 간혹 발생하는 옵티마이저의 엉뚱한 실행 계획에 대한 부끄러운 방어 차원 일수도 있겠다.

CBO는 비용 방식이므로 통계정보가 잘못되었거나 최신 정보를 제대로 반영하지 못한다면 잘못된 접근 경로(Access Path)를 결정할 것이다. 또한 예측 불가능하고 플랜의 제어가 쉽지 않다. 간혹 잘못된 실행 계획을 제공하는 경우도 있다. 통계정보의 부실 때문 일수도 있고, 옵티마이저의 성능 문제일 수도 있다. 옵티마이저를 너무 믿어서는 안된다. 그럼에도 CBO 방식을 사용하는 이유는 대용량 시스템에선 CBO가 최선이라기보다는 RBO가 대안이 될 수 없기 때문이다. 아래 <그림 2>는 RBO 방식과 CBO 방식에 대한 비교 설명이다.



column_img_1582.jpg
<그림 2> RBO 방식과 CBO 방식

CBO 방식: 옵티마이저와 통계정보 그리고 실행계획

CBO 방식에서 우리는 옵티마이저와 통계정보, 실행계획에 대해 알아야 한다. 옵티마이저는 사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적의 처리 경로를 제공하는 오라클 엔진이다. 옵티마이저는 주어진 환경(통계정보, SQL)하에서 최적의 실행계획(Plan)을 우리에게 제공한다. 그렇다고 항상 최적의 실행계획을 제공하지는 않는다. 이와 같은 경우 우리는 힌트절을 통해 잘못된 실행계획을 바로 잡을 수 있다.

우리는 옵티마이저에게 너무 큰 기대를 해서는 안된다. 옵티마이저는 옵티마이저이지 사람이 아니다. 옵티마이저가 제공하는 최적의 경로는 알고리즘에 의한 하나의 판단일 뿐, 실제로 최적의 실행계획을 보장하지는 않는다. 완벽한 옵티마이저는 존재하지 않는다. 그럼 최적의 실행계획은 누가 찾아야 하나 바로 개발자다. 개발자가 최적의 실행계획을 찾아야 한다.

요즘 유행하는 청소 로봇을 구매한다고 해서 집안이 깔끔하게 청소되지는 않는다. 청소 로봇은 내장된 알고리즘에 따라 혼자 청소만 할 뿐이다. 책상 밑의 의자에 갇혀서 빠져 나오지 못하는 경우도 있고, 그리 높지 않은 턱도 넘지 못해 제자리만 맴돌 때도 있다. 청소 로봇이 청소를 잘하기 위해서는 주변 환경도 갖추어져야 한다. 전선과 턱 등 장애가 될 만한 부분이 없어야 한다. 또한 내장된 알고리즘도 좋아야 한다. 아무리 청소 로봇이라도 사람의 도움이 필요하다.

잘못된 SQL 문이나 부정확한 통계정보에 따라 옵티마이저는 부실한 실행계획을 우리에게 제공할 수 있다. 설사 올바른 SQL이나 정확한 통계정보를 제공했다고 해도 옵티마이저가 우리에게 최적의 실행계획을 제공한다고 믿어서는 안된다. 옵티마이저는 전지전능하지 않다. 여러분이 관심있게 실행계획을 살펴본다면 의외로 옵티마이저의 ‘단순 무식함’에 놀랄 경우도 많을 것이다. 그런데 이와 같은 때에도 원인을 추적하면, 우리의 잘못인 경우가 대부분이다. 옵티마이저가 올바른 판단을 하도록 우리는 다음과 같은 부분을 올바르게 제공하거나 제어해야 한다.

첫째, 최적의 인덱스 구성하기
둘째, 올바른 SQL문 작성하기
셋째, 주기적이거나 혹은 최신의 통계정보 갱신하기
넷째, 힌트절 추가해 옵티마이저 제어하기

이와 같은 노력들이 튜닝에 대한 개발자들의 직관성을 길러줄 것이다.

아래 <그림 3>은 옵티마이저와 통계정보, 실행계획에 대해 한눈에 이해할 수 있는 그림이다. 통계정보는 그림에 표시된 내용 외에도 옵티마이저 버전, 네트워크 정보, 서버 정보, SQL 구문, 수집된 테이블과 인덱스 정보, 컬럼의 분포도, 옵티마이저 모드 등 많은 부분이 있다. 이 모든 수집정보를 통해 옵티마이저는 최소한의 운반 비용이 드는 실행계획을 세운다.

column_img_1583.jpg
<그림 3> 옵티마이저와 통계정보 그리고 실행계획

요즘, 뛰어난 DB 성능에 따라 웬만한 비효율적인 튜닝 대상 쿼리들이 묻히는 경우가 많다. 또한 개발자들도 민감하게 인식하지 못하고 있다. 하지만 평소에 성능 향상에 대한 노력을 하지 않으면 정작 필요한 시점에 낭패를 당하기 쉽다. 조금씩 조금씩 노력하고 전진 했으면 좋겠다.

오늘 연재를 통해 우리는 CBO 방식과 RBO 방식에 대해서 알아 보았다. 다음 연재에서는 오라클 플랜정보에 대해 자세히 알아볼 예정이다.



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

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

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

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



column_img_1584.jpg

[이번 호 문제]

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

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