EXPLAIN PLAN
- EXPLAIN PLAN은 주어진 SQL 쿼리를 지금(현재의 세션에서 현재의 설정으로) 실행한다면 이 쿼리의 쿼리 계획이 어떻게 될지를 알 수 있는 SQL 명령이다.
- EXPLAIN PLAN은 주어진 쿼리를 실행하기 위하여 실제로 사용하였던 계획이 무엇인지를 말해 줄 수 없다.
EXPLAIN PLAN 설치
- $ORACLE_HOME/rdbms/admin
- utlxplan.sql (UTiLity eXplain) : PLAN_TABLE이라 명명된 테이블의 CREATE TABLE 문을 포함
- utlxplp.sql (UTiLity eXplain PLan Parallel) : 병렬 쿼리 계획에 특화된 정보를 포함하여 계획 테이블의 내용을 보여 준다.
- select * from table(dbms_xplan.display());
- utlxpls.sql(UTiLity eXplain PLan Serial) : 일반적인 직렬(비병렬) 계획에 대한 계획 테이블의 내용을 보여준다.
- select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
- EXPLAIN PLAN을 설정하기 위해서는 먼저 계획 테이블 자체를 만들어야 한다.
- UTILS 혹은 TOOLS라는 스키마를 생성 후 "ON COMMIT PRESERVE ROWS"옵션을 가진 글로벌 임시 테이블 생성 후 모든권한(ALL privilege)을 일반(public)에 부여한 후 일반 동의어(public synonym) PLAN_TABLE을 생성하여 계획 테이블을 공유할 수 있다.
- 10G부터 GLOBAL TEMPORARY TABLE인 PLAN_TABLE$을 제공한다. (PUBLIC SYNONYM PLAN_TABLE, CRUD 권한 부여)
EXPLAIN PLAN을 사용하라
explain plan
[set statement_id = 'text']
[into [owner.]table_name]
for statement;
- statement_id는 여러 계획을 계획 테이블에 저장할 수 있도록 해 준다.
- owner.table_name은 PLAN_TABLE이 아닌 다른 테이블을 사용할 수 있도록 해 준다.
- Pstart와 Pstop 값을 출력해 줌으로서 테이블을 통째로 읽지 않고 오직 한 파티션만 액세스될 것이라는 것을 알려준다.
- Oracle9i 릴리스 2 이상에서만 쿼리 계획의 각 단계에 적용할 술어의 정확한 부분을 나타낸다.
쿼리 계획을 읽는 방법
- 실행 계획을 읽는 방법 : 위에서 아래로, 안쪽에서 바깥쪽으로
EXPLAIN PLAN의 함정 회피
- EXPLAIN PLAN은 주어진 쿼리를 현재의 환경에서 즉시 실행해야 하는 경우 이 SQL 쿼리의 쿼리 계획을 얻기 위한 수단이지 이 쿼리르르 어제 수행하였을 때 어떤 계획이 사용되었는지 또는 앞으로 다른 세션을 이용하여 이 쿼리를 수행할 때 어떤 계획이 사용될지를 보여 주지는 못한다.
- 9i 이후 버젼에서는 이미 실행된 쿼리의 실제 계획을 얻기가 어렵지 않다(V$SQL_PLAN 뷰를 통해 조회 가능)
- p.153 ~ p.155 참조
DBMS_XPLAN과 V$SQL_PLAN의 사용
- DBMS_XPLAN.DISPLAY : 보고서의 말미에 상당한 양의 정보를 포함(Oracle9i 릴리스 2부터 도입)하고 있고, 절차에 따라 일정한 형식을 갖춘 EXPLAIN PLAN의 출력을 반환하는 함수
select * from table(dbms_xplan.display);