튜닝의 도구 – Explain Plan -oracle Tuning 강좌 Tip

[정민이]

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

튜닝의 도구 – Explain Plan

Explain Plan은 실행 계획을 사용자에게 제공하는 명령 입니다. SQL문이 처음 실행될 때 서버 프로세스가 파싱을 한 후 SQL문법에 대한 오류를 확인하고 데이터딕셔너리를 통해 권한이나 객체에 대한 확인 작업을 거친 후 해당 SQL문장이 어떻게 실행 될 것인가에 대해 결정을 하게 되는데 이 결과를 실행 계획(execution Plan)이라 하며 이 결과를 확인 할 수 있는 도구가 Explain Plan 입니다.

Explain Plan을 통해 SQL문이 실행 될 때의 상태 정보를 확인 할 수 있으며 실행 계획은 실행 계획 테이블(PLAN_TABLE)에 저장 됩니다.

아래의 예문을 통해 쓰임새를 알아 보도록 합니다.

Explain Plan 명령을 사용 하기 위해서는 SQL을 분석한 결과를 저장 할 테이블(PLAN_TABLE)이 있어야 합니다. C 드라이브에 오라클이 설치되었다고 한다면 C:\oracle\ora92\rdbms\admin\Utlxplan.sql 이라는 파일을 통해 생성을 하면 됩니다.

기본 문법은 다음과 같습니다.

Explain Plan
[Set statement_id = ‘사용자가 지정한 아이디’]
[into 테이블명]
For SQL문장…

statement_id : SQL문에 대한 아이디를 줌으로써 다른 사용자의 것과 구분이 가능
데이블명 : 실행 계획을 저장 할 테이블 명, 별도로 지정 하지 않으면 PLAN_TABLE
SQL문장 : 실행 계획을 분석 하고자 하는 SQL문

SQL> @C:\oracle\ora92\rdbms\admin\Utlxplan.sql

테이블이 생성되었습니다.

SQL>  explain plan
  2  set statement_id='1'
  3  for select ename, sal, deptno
  4      from emp
  5      where empno = 7369;

해석되었습니다.

SQL> select rtrim(lpad(' ',2*level) ||
  2        rtrim(operation) || ' ' ||
  3        rtrim(options) || ' ' || object_name) query_plan ,
  4        cost,
  5        cardinality
  6  from plan_table
  7  start with id=0 and statement_id='1'
  8  connect by prior id = parent_id and statement_id = '1';

QUERY_PLAN                                    COST CARDINALITY
---------------------------------------- ---------- -----------
  SELECT STATEMENT                                2          1
    TABLE ACCESS BY INDEX ROWID EMP              2          1
      INDEX UNIQUE SCAN EMP_PK                    1          14

물론 아래처럼 Oracle에서 제공하는 utlxplp.sql 파일을 이용하여 PLAN_TABLE의 내용을 확인 할 수도 있습니다.

SQL> @C:\oracle\ora92\rdbms\admin\utlxplp.sql

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------


---------------------------------------------------------------------------
| Id  | Operation                  |  Name      | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |            |    1 |    17 |    2 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMP        |    1 |    17 |    2 |
|*  2 |  INDEX UNIQUE SCAN        | EMP_PK      |    14 |      |    1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------


  2 - access("EMP"."EMPNO"=7369)

Note: cpu costing is off

15 개의 행이 선택되었습니다.

실행 계획의 해석

SELECT STATEMENT                        2      1
    TABLE ACCESS BY INDEX ROWID EMP    2      1
      INDEX UNIQUE SCAN EMP_PK          1    14

들여 쓰기가 가장 많이 된 부분이 실행 되며 그래서 맨 아래 부분이 먼저 실행 된다는 겁니다. EMP_PK 인덱스를 이용하여 인덱스 Unique 검색을 수행하며 찾은 rowid로 TABLE ACCESS가 이루어지므로 2번째와 3번째는 한단계로 볼 수 있습니다. 즉 같은 수준이 되는 것입니다. 즉 위의 SQL 문장은 Primary Key 인덱스를 이용하여 찾은 데이터의 rowid를 이용하여 검색을 수행 한다는 것입니다.

다른 예제를 해보겠습니다.

SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select count(*) from dept;

  COUNT(*)
----------
        4

혹시 이전의 실행 계획이 저장 되어 있을지 몰라 삭제 합니다.
SQL> delete from plan_table;
SQL> commit;

커밋이 완료되었습니다.

EMP Table의 deptno는 인덱스가 결려 있습니다. 오라클은 기본적으로  COST BASED OPTIMIZER 이므로 비용(COST)를 따지게 됩니다. 현재 데이터의 건수가 얼마 되지 않아 인덱스를 이용하는 것보다 테이블을 FULL SCAN하는 것이 효율이 좋아 이를 선택하게 됩니다.

SQL>  explain plan
  2  set statement_id = '1'
  3  for
  4  select emp.ename, dept.dname
  5  from emp, dept
  6  where emp.deptno = dept.deptno;

해석되었습니다.


SQL> col  query_plan format a40
SQL> select rtrim(lpad(' ',2*level) ||
  2            rtrim(operation) || ' ' ||
  3            rtrim(options) || ' ' || object_name) query_plan ,
  4            cost,
  5            cardinality
  6      from plan_table
  7      start with id=0 and statement_id='1'
  8  connect by prior id = parent_id and statement_id = '1';

QUERY_PLAN                                    COST CARDINALITY
---------------------------------------- ---------- -----------
  SELECT STATEMENT                                5          14
    HASH JOIN                                    5          14
      TABLE ACCESS FULL DEPT                      2          4
      TABLE ACCESS FULL EMP                      2          14

이 경우엔 DEPT 테이블을 FULL SCAN 후 EMP 테이블의 FULL SCAN된 데이터와 HASH JOIN하여 결과를 얻어내게 됩니다.

이번에 옵티마이저 모드를 RULE BASED OPTIMIZER로 변경해 보겠습니다. 이 경우엔 전체적인 수행의 비용보다 SQL 문법에 의존적이며 15개 정도 미리 정해져 있는 공식에 따라 가장 빠른 실행 방법이 결정 됩니다. 인덱스가 걸려 있으면 우선적으로 이용하게 되는 거죠… RBO(RULE BASED OPTIMIZER)에서 최 상위의 검색 방법은 ROWID를 이용하는 겁니다, 옵티마이저에 대해서는 추후 강좌에서 자세히 살펴 볼겁니다.

SQL> alter session set optimizer_goal = RULE
  2  ;

세션이 변경되었습니다.

SQL> explain plan
  2  set statement_id = '2'
  3  for
  4  select emp.ename, dept.dname
  5  from emp, dept
  6  where emp.deptno = dept.deptno;

해석되었습니다.

SQL> col  query_plan format a40
SQL> select rtrim(lpad(' ',2*level) ||
  2            rtrim(operation) || ' ' ||
  3            rtrim(options) || ' ' || object_name) query_plan ,
  4            cost,
  5            cardinality
  6      from plan_table
  7      start with id=0 and statement_id='2'
  8  connect by prior id = parent_id and statement_id = '2';

QUERY_PLAN                                    COST CARDINALITY
---------------------------------------- ---------- -----------
  SELECT STATEMENT
    TABLE ACCESS BY INDEX ROWID EMP
      NESTED LOOPS
        TABLE ACCESS FULL DEPT
        INDEX RANGE SCAN EMP_DEPTNO

이 경우엔 DEPT TABLE을 FULL SCAN(DEPT 테이블이 드라이빙 테이블임)후 EMP Table의 인덱스인 EMP_DEPTNO를 이용하여 RANGE SCAN한 후 NESTED LOOP이므로 DEPT 테이블의 전체 레코드를 한건식 Access하여  EMP Table의 데이터를 ROWID로 얻어 내는 과정을 반복하게 됩니다.

다음은 PLAN_TABLE의 컬럼에 대한 설명이니 참고 바랍니다.

STATEMENT_ID  explain plan에서 지정한 SQL문을 구별하기 위한 식별자(여러 개를 분석 할 수 있으므로)
TIMESTAMP  explain plan 구분을 실행한 시간
REMARKS  주석
OPERATION  각 단계별로 수행할 연산자(HASH JOIN, TABLE ACCESS 등등)
OPTIONS  연산자에 대한 옵션(FULL SACN, RANGE SCAN등등)
OBJECT_NODE  사용된 객체가 참고하는데 사용된 데이터베이스 링크
OBJECT_OWNER  사용된 객체의 소유자
OBJECT_NAME  객체 이름
OBJECT_INSTANCE  원래 SQL문에 있는 FROM절의 객체의 위치를 정수화 한 값
OBJECT_TYPE  객체의 유형
OPTIMIZER  현재의 Optimizer Mode(RULE, CHOOSE, FIRST_ROW…)
SEARCH_COLUMNS  시작과 끝 키를 가진 인덱스 컬럼의 수
ID  실행 계획 각 단계의 ID
PARENT_ID  각 단계의 결과를 가지고 하는 상위 단계 ID
POSITION  두개의 간계가 같은 상위 ID를 가질 때 가장 낮은 ID가 먼저 실행
COST  CBO 에 의해 추정된 현재의 연산 비용
CARDINALITY  현재 연산을 통해 추출 될 레코드 수
BYTES  현재 연산을 통해 추출된 바이트
OTHER_TAG  OTHER 컬럼에서  SQL 텍스트의 기능을 표현
PARTITION_START  파티션 범위 검색 시 시작 파티션
PARTITION_STOP  파티션 범위 검색 시 종료 파티션
PARTITION_ID  파티션 범위 검색 시 시작 파티션과 파티션 범위 검색 시 종료 파티션 컬럼의 값을 계산
OTHER  병렬 실행 슬레이브와 병렬 쿼리에 대한 정보
DISTRIBUTION  병렬 실행 슬레이브가 어떻게 레코드를 추출하는가의 방법
CPU_COST  사용자 정의 CPU 비용
IO_COST  사용자 정의 IO 비용 

출저: 개발자를위하여 커뮤니티 카페
http://cafe.naver.com/fordeveloper