컴퓨터 공부/Database

[Oracle] Explain plan 사용하기

나정_ 2013. 6. 4. 20:31

Oracle에서 Explain plan을 통해 실행계획을 확인해보자!!!


오늘은 오라클에서 Explain plan(실행계획)을 보는 방법에 대하여 정리해 보도록 하겠다. 


우선 오라클 Explain plan에 대하여 설명을 하기에 앞서 실행계획을 왜 사용하는지, 실행계획을 보고 분석할 수 있어야 하는지에 대하여 알아보도록 하겠다. 

간단한 예를 통해 설명하자면 같은 결과를 산출해 나가는 2개의 상의한 쿼리가 잇다고 하자. 그런데 이 두 쿼리를 수행하면 결과가 나오기까지의 시간이 각각 다르다. 물론 늦게 결과를 추출한 쿼리가 성능이 좋지 않은건 사실이지만 왜 성능이 좋지 않은지를 알기위해서는 작성된 쿼리에 대한 옵티마이저가 생성한 실행계획을 해석할 수 있어야만 그 원인을 찾을 수 있다. 



1. Explain plan 이란 


Explain plan(실행계획)이란 이론적으로 실행하기에 앞서 성능을 확인해보고자 도와주는 것이 explain plan 이다. 

그렇다면 문법적으로 들어가보자. 

"explain plan for ~ " 바로 이것이 Explain plan(실행계획)을 확인할 수 있게 도와주는 문법이다. 즉 SQL문의 엑세스 경로를 확인하고 튜닝할 수 있도록 SQL문을 분석하고 해석하여 실행계획을 수립한 후 PLAN_TABLE에 저장하도록 해주는 명령어이라고 할 수 있다..

유의해야 할 사항은, 실행계획을 수행하기 앞서 plan_table 존재여부를 판단한 후 없을 경우 plan_table을 생성해주어야 한다. 하지만 대다수 설치했었을 때 default로 plan_table이 생성되니 너무 걱정하지 말자.~ 

 


문법

EXPLAIN PLAN [ SET statement_id = ‘’ INTO tablename] FOR

SELECT u.user_name,

c.CODE_NAME

FROM tb_common_code c

JOIN tb_user_m u

ON u.USER_POSITION_CODE = c.CODE

ORDER BY u.user_name desc



statement_id

1-30자로 부여할 수 있는 해당 실행문의 제목으로 여러명 의 사용자가 분석 작업을 하게 될 때 분석 결과를 서로 구분하기 위한 식별자를 의미함

 into tablename

출력문을 저장하는 테이블 명으로 plan_table을 사용하지 않을 경우 사용함 

for~ 

실행계획을 수립하고자 하는 sql문을 입력  


2. plan table 생성


plan_table이 없을 경우를 대비해서 plan_table을 생성하는 방법을 정리해보았다. 

오라클은 plan_table을 설치시에 스크립트로 따로 저장을 해주는 것같다. 경로  $ORACLE_HOME\product

\11.2.0\dbhome_1\RDBMS\ADMIN 아래에 utlxplan.sql 파일이 있는데 이 파일을 cmd창에서 아래대로 입력하면 plan_table이 생성되고 만약 이게 실패한다면  sql파일을 메모장으로 연 뒤 안에 들어있는 스크립트를 그대로 복사해서 테이블을 생성해주면 된다. 


아래와 같이 입력하여 실행하면 plan_table을 생성할 수 있다.


@D:\app\HWAJUNG\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql


테이블 생성 후 수행속도 향상과 동일 statement_id가 생성되는 것을 막기 위하여 statement_id에 대한 index를 생성하는 것이 좋다. 필자는 index를 생성하지 않았더니 .. 이것저것 수행결과를 확인해보고 나니 중복된 내용이 너무 많아 알아보기가 불편했다. 그러니 다들 아래의 문법도 꼭 같이 실행해주길 바란다. 


CREATE UNIQUE INDEX plan_index ON PLAN_TABLE(statement_id.id);



3. Explain plan(실행계획) 사용방법


explain_plan을 본떠서 plan_table에 집어넣는 방법까지 해보았다. 그렇다면 이제는 실행계획을 확인해보아야할 차례이다. 

열심히 본뜨기만 하고 보지못한다면 그건 무슨 소용인가 

실행계획은 sql툴마다 조금씩 다른 듯하나, 주로 필자가 자주 쓰는 툴인 sqlplus와 sqlgate 두종류로 준비해보았다. 


1. sqlplus 실행계획 


SET AUTOTRACE TRACEONLY EXPLAIN;


위 명령어를 실행할 후 실행계획을 보고자 하는 문장을 실행하면 옵티마이저가 작성하고 선택한 실행계획을 볼 수 있다





2. sqlgate 실행계획 


EXPLAIN PLAN SET statement_id = 'users' FOR

SELECT u.user_seq,

u.user_name,

c.code_name

FROM tb_common_code c

JOIN tb_user_m u

ON u.user_position_code = c.code

WHERE u.user_seq = '12'

ORDER BY u.user_name DESC


결과 


SELECT * FROM plan_table ;





* plan table 컬럼명 




4. DBMS_XPLAN_PACKAGE


DBMS_XPLANEXPLAN PLAN 구문보다 확장된 정보를 보여준다

Oracle 9.2버전에 소개되었으며, plan_table에 저장된 실행계획을 좀 더 쉽게 출력해 볼 수 있게 되었다.

 

- DISPLAY (예측결과에 대한 sql문장 실행 계획을 보여줌)

- DISPLAY_CURSOR (실행결과에 대한 sql문장 실행계획을 보여줌)

- DISPLAY_AWR ( 특정 과거시점의 문제되는 sql문장에 대한 실행계획을 보여줌 )

- DISPLAY_SQLSET(Tuning Advisor에서 튜닝된 sql의 실행계획을 보여줌) --- 

- VALIDATE_FORMAT

 

위의 Package를 사용하기 위해서는 아래의 객체에 SELECT 권한이 있어야 한다.

 

- V$SESSION

- V$SQL_PLAN

- V$SQL(optional)

- V$SQL_PLAN_STATISTICS_ALL

 


ㄱ . DBMS.XPLAN,DISPLAY 파라미터 


DBMS_XPLAN.DISPLAY는 단일 SQL문에 대해 예측 실행계획을 보여주는 Function이다.

DBMS_XPLAN.DISPLAY는 실측 정보가 아닌 예측정보를 제공한다





ㄴ. FORMAT 종류

 

format의 종류로는 basic, typical, all, outline, advance 5가지 종류가 있다.

EXPLAIN PLAN FOR

SELECT *

FROM tb_menu_m

START WITH menu_parent_seq = '0'

CONNECT BY PRIOR menu_seq = menu_parent_seq

ORDER SIBLINGS BY menu_parent_seq ASC



basic : 가장 기본적인 정보만 보여줌 





-typical : basic보다 rows byte, cost, time등이 추가되었으며 predicate information정보가 추가되었다.




-all : query block정보와 column projection information정보가 추가되었다. Query block nameoracle에서 임의로 이름을 생성하며 QB_NAME hint로 지정해 사용할 수 있다





-advanced : basic + typical + all + outline에 포함된 모든 정보를 다보여준다.


ㄷ. 사용방법 


sqlexplain되었으면 아래와 같이 입력하여 실행계획을 확인해보도록 한다.

 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

혹은

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,“basic”,null));