Explain plan이 머요?

Explain plan은 실제로 SQL을 수행하지 않고, 해당 SQL을 처리하는데 어떤 방법으로, 얼마의 비용(시간, 자원 등등)이 드는지 미리 예측 하기 위해 사용한다.

• 이 명령어를 사용하기 위해서는 PLAN_TABLE이 존재해야 하고, PLAN_TABLE을 만들려면?
  • 10g 이전 : utlxplan.sql 스크립트를 이용하여 생성
  • 10g 이후 : 설치시 자동적으로 sys.plan_table$이 만들어지고, 이 테이블을 가리키는 public synonym을 생성해 두므로 \*이미 존재*한다.

Explain plan은 어떻게 사용하는 거요?

실행계획만들기

SQL> explain plan for
  2  select *
  3  from emp
  4  where empno=7900;

해석되었습니다.

=> 이렇게 하면, SELECT * FROM EMP SQL문을 직접 수행하지 않고, 예측한 실행계획정보를 PLAN_TABLE에 저장해 놓는다.

실행계획보기

• UTLXPLS.SQL
• UTLXPLP.SQL
• DBMS_XPLAN.DISPLAY procedure

SQL> set linesize 200
SQL> @utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   2 - access("EMPNO"=7900)

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

9i 이전에는 만든 실행계획정보를 예쁘게 보기 위해서 스크립트를 따로 작성해서 보곤 했지만, (p159 스크립트 참고)
9i부터는 오라클이 제공하는 utlxpls.sql 또는 utlxplp.sql 스크립트를 이용하면 알아서 이쁘게 보여준다. (line size좀 조정해서 쓰시고..)

요즘은 SQL을 프로그램안에 하드코딩하지 않고 SQL문을 따로 Repository에 저장해 놓고 사용하므로, 매일 저장된 SQL들의 실행계획을 별도 테이블에 저장해 놓았다가, 특별한 이슈(통계정보 변경 등등)로 인하여 갑자기 성능이 나빠진 SQL이 생겼을 경우, 여러가지 방법을 동원하여, 괜찮은 성능을 보일 때의 실행계획처럼 나오도록 빠르게 튜닝할 수도 있겠다.

문서에 대하여

• 최초작성자 : 오정희
• 최초작성일 : 2009년 10월 23일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*