DATOR


시스템 성능 개선 어디에 중점을 두어야 할까? . . . (주)엔코아 조상윤 수석 연구원 커버 스토리


coverstory.jpg



하수가 고수에게 묻는다. 시스템 성능을 개선하고자 할 때 어떤 부분에 중점을 두고 개선하여야 하는가? 고수는 답한다. 전략적으로 인덱스를 생성해야 한다. 곧 최소의 인덱스로 최대의 효과를 낼 수 있도록 하여야 한다는 것이다. 아울러 SQL 문이 인덱스를 최대한 활용할 수 있는 방향으로 작성되어야 한다는 말과 같다. 하수는 고개를 갸우뚱거리며 되묻는다. 최소의 인덱스로 최대의 효과를 낼 수 있도록 한다는 말엔 동감한단다. 하지만 SQL 문이 인덱스를 최대한 활용할 수 있도록 작성함이란 무엇을 의미하냐고 되묻는다. 여기에서 하수에게 무엇을 얘기하여야 하는가? 필자라면 옵티마이저의 SQL 문의 처리 과정과 CBO 개념을 얘기하고 싶어진다.


● SQL문 처리 과정: SELELCT * FROM DEPT;


1. 쿼리 구문 분석

A. SQL파싱 (SQL파서)

i. 검색 단계: 동일한 SQL문이 SGA SHARED POOL내의 LIBRARY CACHE 안에 존재하는가?
(
존재하지 않으면 구문 분석, ROW-SOURCE 생성을 수행한다. 하드 파싱)

ii. 문법 체크 단계

iii. 시멘틱 체크 단계: 쿼리문에 사용된 오브젝트(테이블, 칼럼)가 실제 존재?

iv. 권한 확인 단계: 쿼리 사용자가 테이블, 칼럼 등을 검색할 권한 여부

v. 테이블 관리 락 수행


B. SQL 최적화 (옵티마이저)

i. QUERY TRANSFORMER: 쿼리 변환 -> 뷰 병합, 서브 쿼리 머징, 이행성(처리범위 감소)

ii. Estimator : 선택도, 카디널리티, 비용 등의 예상치 계산

iii. PLAN GENERATOR: PLAN 생성


2. ROW-SOURCE 생성(ROW SOURCE GENERATOR): 실행 계획을 실행 가능한 코드 또는
프로시저 형태로 포맷팅

3. SQL 실행 (SQL ENGINE): 검색에서 해당 SQL 문이 존재한다면 바로 실행 단계로 넘어온다. (소프트 파싱)

4. 결과 인출



실행 계획

실행 계획이 진정 얘기하고 싶은 주제이다. 실행 계획이야말로 CBO의 진정한 산출물이다.

고수가 성능 문제에 대한 해답으로 얘기한 인덱스 활용 파악을 위해선 반드시 실행 계획을 가지고 얘기할 수 밖엔 없다. DBMSSQL 문을 실행함이란 앞서 만든 실행 계획을 통해 수행된다는 사실을 잊지 말아야 한다. 성능이란 실행 계획 분석으로 좌우된다. 또한, 실행 계획을 통해 해당 SQL의 수행 경로 및 성능을 파악함으로 관련 테이블()의 인덱스 활용도를 분석할 수 있다.


<실행 계획 문법>

EXPLAIN PLAN

SET STATEMENT_ID = ‘사용자 지정 ID’  /* 옵션 */

INTO 플랜 테이블 명 /* 옵션 */

FOR 수행 SQL 문장;


기본적으로 위 실행 계획 발생 쿼리 문장을 실행하면 실행 계획은 PLAN_TABLE이란 테이블에 쌓인다. 당부하지만 이 테이블 생성 작업이 필요하다면 반드시 최신 PLAN_TABLE 생성 스크립트로 테이블을 생성하길 바란다. 또한, 10G이상에선 자동으로 PLAN_TABLE PUBLIC SYNONYM이 생성되므로 사용자 별 PLAN_TABLE을 따로 만들 필요가 없음을 기억해 주길 바란다.


실행 계획을 만들었으니 어떤 실행 계획이 세워졌는지 볼 차례이다. 간단하게 본다면 “SELECT * FROM PLAN_TABLE;” 문으로 볼 수도 있지만, 오라클은 좀더 다양하게 실행 계획을 볼 수 있는 도구를 제공한다. 바로 그 유명한 DBMS_XPLAN 패키지이다. 이 패키지는 점점 발전하고 있고 영원히 깨질 것 같지 않던 TRACE + TKPROF 보고서를 대체해도 될 정도로 발전된 상황이다. 개인적으로 10046 TRACE보다는 DBMS_XPLAN을 많이 이용하는데 그 이유는 읽기 편하다는 점을 들고 싶다.


<DBMS_XPLAN 패키지 관련함수>

- DISPLAY (예측 실행 계획)

- DISPLAY_CURSOR (캐싱된 커서의 실제 실행된 실행 계획)

- DISPLAY_AWR (AWR에 담겨진 실제 실행된 실행 계획)

- DISPLAY_SQLSET (ALL_SQLSET을 통한 실행 계획)


DBMS_XPLAN 패키지를 통해 옵티마이저의 SQL 문에 대한 실행 계획을 분석함으로써 사용된 인덱스의 활용 유무 및 비용산정의 적합성을 체크하여 최적의 SQL 문 작성을 기대 할 수 있다. 그러므로 하수에서 고수로의 성장은 옵티마이저의 실행 계획의 이해를 통한 문제점 진단 및 그 해결을 통해 이룰 수 있다.


다시 말하지만, 오늘의 주제는 실행 계획이다. 지겹도록 반복적으로 얘기하고 싶은 얘기는 실행 계획에 대한 완전 정복이다.


이것은 오라클이 무얼 생각하고 있으며, 어떤 판단을 내리고 있는지를 알 수 있는 X-RAY 검사이다. 성능 문제가 발생한다면 먼저 관련 SQL 문을 찾고 해당 실행 계획을 분석하여 JOIN 형태가 적당한지 인덱스를 태울 것인지 아니면 그 반대로 풀 스캔 처리할 것인지 또는 통계 정보 수집이 필요할 것인지 판단 내리게 되는 것이다.


실행 계획 완전 정복! 잊지 말길 바란다.





Tag :

Leave Comments