전문가칼럼

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

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

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2015-05-28 00:00
조회
21761




◎ 연재기사 ◎


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

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



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

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



호미, 무시할 수 없는 그 존재감

호미는 김을 매거나 골을 낼 때, 텃밭을 가꾸거나 씨앗을 심을 때, 감자나 고구마 등 작물을 수확할 때 사용하는 우리나라 고유의 농기구다. 서유구의 「임원경제지」에서도 동서(동쪽나라의 호미)라고 했을 만큼 호미는 우리나라에서만 볼 수 있는 독특한 도구라고 한다.

호미는 부등변 삼각형 모양의 날을 나무 자루에 박아 넣은 독특한 형태인데 경주 안압지에서 출토된 통일신라 유물에서도 확인할 수 있을 만큼 오래 되었다. 호미의 종류에는 논호미와 밭호미가 있고, 밭호미는 논호미와 달리 형태가 매우 다양하다. 중부 이남 지방에서 주로 사용되었던 한쪽 날만 뽀쪽한 외귀호미, 중부 이북의 산간 지방에서 주로 사용되었던 양쪽 날이 뾰쪽한 양귀호미가 대표적이다.

column_img_1917.jpg

호미는 지방에 따라 부르는 명칭도 다양했다. 호맹이, 호무, 홈미, 호마니, 허메, 허미, 희미 등으로 불리며 모양에 따라 막지기, 경지기, 곧지기, 귀호미, 날호미, 평호미, 동자호미 등으로 헤아릴 수 없을 만큼 많다. 호미는 우리나라 농경 역사와 떼려야 뗄 수 없는 산증인이자 역사다.

호미는 하루 300평의 밭을 맬 수 있을 정도로 효율 또한 높다. 옛날 우리 조상들은 호미 한 자루로 힘든 농사일을 척척 해냈다. 그래서 덩치 큰 여러 농기구보다도 더 많이 사용되었다. 우리들의 어머니, 할머니들은 호미 한 자루로 호미처럼 등이 굽도록 일하였다. 호미는 바로 그들 삶의 일부 이기도 하다. 요즘에는 힘든 농사일을 대부분 기계로 하지만 아직도 농가에서는 호미 3~4자루씩은 갖고 있지 않을까 싶다.

이번 회는 농사를 하지 않는 사람에게는 다소 낯선 호미 이야기로부터 시작했다. 많은 사람이 비록 농사와는 무관한 삶을 살고 있지만, 각자의 분야에서 자신의 몸과 마음을 다스릴 호미 한 자루는 모두 가지고 있어야 하지 않을까 싶다. 지금도 열심히 일하는 사람들의 모습이 그 옛날 우리 어머니들의 논밭에서 호미질 하던 모습을 떠오르게 한다.



GATHER_PLAN_STATISTICS, 무시 할 수 없는 엄청난 존재감

오라클 10g부터 GATHER_PLAN_STATISTICS 힌트를 이용하면 SQL Trace를 수행하지 않고도 쿼리의 Plan 단계별로 Get Block을 알 수 있다. 쿼리 성능을 확인-비교 할 수 있기 때문에 튜닝할 때 아주 빈번하게 사용하는 유용한 힌트절이다.

대부분의 DB 관련 책에서는 GATHER_PLAN_STATISTICS 힌트절의 정의만 간략하게 기술하고 있고, DB 교육 과정에서도 내용만 간략하게 소개하는데 그치는 경우가 많아 보인다. 이 힌트절은 개발자들도 반드시 알아야 하는 중요한 내용이므로 좀 더 자세하게 알아보고 실제로 어떻게 사용되고 해석되는지에 대해 설명하고자 한다.

실행할 쿼리에 다음과 같이 GATHER_PLAN_STATISTICS 힌트절을 추가하여 사용한다.

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM 인사
WHERE 사용자명 = ‘이슬기’;



위의 대상 쿼리를 실행한 후에 아래 분석 쿼리를 곧바로 수행해야 한다.

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

왜냐하면 GATHER_PLAN_STATISTICS 힌트절을 추가해 실행한, 가장 최근의 쿼리에 대한 수행 정보를 보여주는 분석 쿼리이기 때문이다. 만약 다음과 같은 메시지가 표시된다면 접근 계정에 대한 V$SESSION 권한이 없으므로 DBA에게 요청하여 권한을 부여 받아야 한다.

User has no SELECT privilege on V$SESSION

이제 실제 쿼리를 실행하고 분석 쿼리를 수행한 내용을 자세히 살펴보자.

column_img_1918.jpg

먼저 헤더의 각 부분에 대한 설명부터 필요할 듯 하다.

Id, Operation, Name
이 부분은 우리가 흔히 봐 왔던 플랜 정보다. 자원에 대한 접근 순서와 접근 방법을 나타낸다. 참고적으로 접근 순서를 변경 할 수 있는 힌트절은 ORDERED, LEADING이 있다. 또한 접근 방법을 변경할 수 있는 힌트절은 USE_NL, USE_HASH, USE_MERGE가 있다.

Starts
오퍼레이션을 수행한 횟수를 의미한다. Starts * E-Rows 의 값이 A-Rows 값과 비슷하다면, 통계정보의 예측 Row 수와 실제 실행 결과에 따른 실제 Row 수가 유사함을 알 수 있다. 만약 값에 큰 차이가 있다면 통계정보가 실제의 정보를 제대로 반영하지 못했다고 생각할 수 있다. 이로 인해 오라클의 Optimizer가 잘못된 실행 계획을 수립할 수도 있음을 염두에 둬야 한다.

E-Rows (Estimated Rows)
통계정보에 근거한 예측 Row 수를 의미한다. 통계정보를 갱신할수록 값이 매번 다를 수 있으며, 대부분의 DB 운영에서는 통계정보를 수시로 갱신하지 않으므로 해당 값에 큰 의미를 둘 필요는 없다. 하지만 E-Rows 값과 A-Rows 값이 현격하게 차이가 있다면 오라클이 잘못된 실행 계획을 세울 수도 있음을 인지해야 하며 통계정보 생성을 검토해 보아야 한다.

A-Rows (Actual Rows)
쿼리 실행 결과에 따른 실제 Row 수를 의미한다. 우리는 A-Rows 에서 중요한 여러 정보를 추정 할 수 있다. 이에 대한 부분은 이번 연재에서 계속 설명이 이어진다(이번 호 문제 부분에서).

A-Time (Actual Elapsed Time)
쿼리 실행 결과에 따른 실제 수행 시간을 의미한다. 하지만 실행 시점의 여러 상황이 늘 가변적이고 또한 메모리에 올라온 Block의 수에 따라서 수행 시간이 달라지므로 해당 값에 큰 의미를 둘 필요는 없다.

Buffers (Logical Reads)
논리적인 Get Block 수를 의미한다. 해당 값은 오라클 옵티마이저가 일한 총량을 의미하므로, 튜닝을 진행할 때 필자가 가장 중요하게 생각하는 요소 중 하나다.

Reads (Physical Reads)
물리적인 Get Block 수를 의미한다. 동일한 쿼음이 아닌 경우에는 값이 0인 것을 보면 알 수 있듯이 메모리에서 읽어온 Block은 제외된다. 해당 값에 큰 의미를 둘 필요는 없다.

위의 헤더에서 튜닝 시 가장 중요하게 활용되는 부분은 Buffers와 A-Rows다. Buffers 값을 통해서 Get Block의 총량을 알 수 있고, A-Rows를 통해 플랜 단계별로 실제 Row 수를 알 수 있기 때문이다. 이제 동일한 쿼리를 접근 순서를 달리해서 실행한 후 분석 쿼리를 통해서 성능상의 차이를 살펴보도록 하자.
실습 쿼리에 힌트절을 추가해 테이블 접근 순서를 다음과 같이 하여 실행하였다(인사 → 실적).



SELECT /*+ GATHER_PLAN_STATISTICS LEADING(A B) */
*
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));



column_img_1919.jpg

이번에는 힌트절을 달리하여 테이블 접근 순서를 다음과 같이 변경하였다. (실적 → 인사)



SELECT /*+ GATHER_PLAN_STATISTICS LEADING(B A) */
*
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));



column_img_1920.jpg

우리는 동일한 쿼리에 대해서 서로 다른 힌트절을 추가하여 접근 순서를 달리하여 실행한 결과 다음과 같은 Buffers 값을 얻었다.

인사 → 실적 : 14 Buffers
실적 → 인사 : 393 Buffers

같은 쿼리라도 어떤 순서대로 테이블을 접근하느냐에 따라서 일량이 28배 가량 차이가 났음을 알 수 있다. 다음과 같이 인덱스 생성도를 이용하면 더욱 직관적으로 이해할 수 있다.

column_img_1921.jpg

이번에는 접근 순서가 아닌 접근 방법을 달리해서 실행한 후 분석 쿼리를 통해서 성능상의 차이를 살펴보도록 하자. 다음과 같이 USE_HASH 힌트절을 추가했다.



SELECT /*+ GATHER_PLAN_STATISTICS LEADING(A B) USE_HASH(B) */
*
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));



column_img_1922.jpg

우리는 동일한 쿼리에 대해서 서로 다른 힌트절을 추가하여 접근 순서를 달리하거나 혹은 접근 방법을 달리하여 실행한 결과, 다음과 같은 Buffers 값을 얻었다.

접근순서 : 인사 → 실적, 접근방법 : For문 형식의 순차적 접근 : 14 Buffers
접근순서 : 실적 → 인사, 접근방법 : For문 형식의 순차적 접근 : 393 Buffers
접근순서 : 인사 → 실적, 접근방법 : Hash 함수를 이용한 접근 : 29 Buffers

결국 동일한 쿼리라도 어떤 순서대로 테이블을 접근하느냐 혹은 어떤 방법으로 접근하느냐에 따라서 일량이 서로 다름을 알 수 있으며, 우리는 올바른 선택을 위하여 결정 해야 한다.



튜닝을 위한 최고의 힌트절

오라클이 GATHER_PLAN_STATISTICS 힌트절을 제공하기 이전에는 개발자들이 튜닝을 하기가 무척 곤란했다. 단지 플랜만을 보면서 자원에 대한 접근 순서와 접근 방법에 대한 정보만을 갖고 성능을 예측해야만 했기 때문이다.

오라클 10g부터 제공된 GATHER_PLAN_STATISTICS 힌트절 덕분에 우리는 예측정보 및 실제정보를 바탕으로 성능 이슈에 대해 효과적으로 대응할 수 있게 되었다. SQL Trace를 수행하지 않고도 쿼리의 Plan 단계별로 Get Block을 알 수 있으므로 DBA에게 의존적인 부분을 상당부분 벗어날 수 있게 되었다.

필자가 튜닝을 하면서 가장 빈번히 사용한 힌트절이고, 가장 유용한 힌트절임을 절실히 느꼈기 때문에 이번 연재에 구체적으로 설명하였다.

다음 연재에서도 계속해서 알면 유용한 오라클의 여러 가지 기능들에 대해 알아 보겠다.



용기를 갖자
오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로 쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다.

이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...



[지난 문제의 정답과 풀이]원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제



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

column_img_1923.jpg



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



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

column_img_1924.jpg