EXPLAIN PLAN(실행계획) 이란

ORACLE/TUNING2010. 1. 12. 15:45

EXPLAIN PLAN(실행계획) 이란

☞ EXPLAIN PLAN(실행계획) 이란?

◈ SQL문의 엑세스 경로를 확인하고 튜닝할 수 있도록 SQL문을 분석하고 해석하여

실행계획을 수립한 후 실행계획을 PLAN_TABLE에 저장하도록 해주는 명령 입니다.

◈ SQL Trace없이 사용 할 수 있습니다.

◈ ORACLE_HOME/rdbms/admin/utlxplan.sql실행

◈ statement_id컬럼에 인덱스를 생성해주는것이 수행속도를 향상시켜주고

id값이 중복되는 것을 막을 수 있습니다.

[Syntax]

- statement_id = 'identifiedr' : 1-30자로 부여할 수 있는 해당 실행문의 제목

- INTO tablename : 출력문을 저장하는 테이블명 PLAN_TABLE을 사용하지 않을경우 사용

- FOR statement : 실행계획을 수립하고자 하는 SQL문(SELECT, INSERT, DELETE, UPDATE)

1. Plan_table 생성

◈ Explain plan을 sql에 포함해서 수행하면 옵티마이저가 실행 계획까지만 수립하여

plan_table에 저장해 둡니다.

◈ 이 table을 생성하기 위한 script는 ORACLE_HOME/rdbms/admin/utlxplan.sql 입니다.

SQL>@C:\oracle\ora81\rdbms\admin\utlxplan.sql

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

2. Index 생성

◈ 테이블 생성 후 수행 속도 향상과 동일한 statement_id가 생성되는 것을 방지하기 위해

index를 생성 합니다.

SQL> CREATE UNIQUE INDEX plan_index ON PLAN_TABLE(statement_id,id);

인덱스가 생성되었습니다.

3. SQL 문 사용

◈ FOR 뒷 부분에 확인하고자 하는 sql을 입력 합니다.

SQL>EXPLAIN PLAN SET STATEMENT_ID='a1' FOR

SELECT /*+ index(emp emp_pk) */ * FROM emp WHERE empno > 0;

해석되었습니다.

4. PLAN_TABLE 을 SELECT 하는 SQL 문을 실행

◈ plan.sql이라고 저장해서 사용하면 편리합니다.

SQL>SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) ||DECODE(id,0,'cost estimate:' ||

DECODE(position,'0','N/A',position),null) || ' ' ||options || DECODE(object_name,null,null,':') ||

RPAD(object_owner, LENGTH(object_name)+1,',') || object_name ||

DECODE (object_type,'UNIQUE' ,'(U) ','NON_UNIQUE','(NU)',null) ||

DECODE(object_instance,null,null,'('||object_instance||')') "Explain Plan"

FROM PLAN_TABLE

START WITH ID= 0 and STATEMENT_ID = '&&id'

CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id'

a1을 입력하면 아래와 같은 실행계획을 볼 수 있습니다.

Explain Plan

-----------------------------------------------

SELECT STATEMENTcost estimate:1

TABLE ACCESS BY INDEX ROWID:TESTEMP(1)

INDEX RANGE SCAN:TEST,,,EMP_PK

☞ PLAN_TABLE 컬럼 설명

------------------------------------------------------------------------------------------------

컬 럼 명 설 명

------------------------------------------------------------------------------------------------

STATEMENT_ID EXPLAIN PLAN문에서 사용자가 지정한 제목

TIMESTAMP 실행계획이 수립된 날짜와 시간

REMARKS 사용자가 부여한 주석(COMMENT)

OPERATION 아래 표에 자세히 설명 되어 있습니다.

OPTIONS 아래 표에 자세히 설명 되어 있습니다.

OBJECT_NODE 사용한 데이터베이스 링크

OBJECT_OWNER 해당 라인의 오브젝트를 생성한 사용자 그룹

OBJECT_NAME 테이블이나 인덱스, 클러스터등의 이름

OBJECT_INSTANCE SQL의 FROM절에 기술된 오브젝트를 좌에서 우로 부여한 번호

OBJECT_TYPE 오브젝트의 종류(예 non-unique index)

ID 수립된 각 실행단계에 붙여진 일련번호

PARENT_ID 해당 ID의 부모가 가진 ID

POSITION 같은 부모 ID를 가지고 있는 자식 ID간의 처리 순서

OTHER 다른 필요한 텍스트를 지정하기 위한 필트

------------------------------------------------------------------------------------------------

☞ OPERATION의 종류와 OPTIONS에 대한 설명

OPERATION(기능) OPTIONS(옵션) 설 명

---------------------------------------------------------------------------------------------------------------------------------

AGGREGATE GROUP BY 그룹함수를 사용하여 하나의 로우가 추출되도록 하는 처리(버전 7에서만 표시됨)

AND-EQUAL 인덱스 머지를 이용하는 경우

CONNECT BY CONNECT BY를 사용하여 트리 구조로 전개

CONCATENATION 단위 액세스에서 추출한 로우들의 합집합을 생성

COUNTING 테이블의 로우스를 센다

FILTER 선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업

FIRST ROW 조회 로우 중에 첫번째 로우만 추출한다.

FOR UPDATE 선택된 로우에 LOCK을 지정한다.

INDEX UINQUE UNIQUE인덱스를 사용한다. (단 한개의 로우 추출)

RANGE SCAN NON-UNIQUE한 인덱스를 사용한다.(한 개 이상의 로우)

RANGE SCAN RANGE SCAN하고 동일하지만 역순으로 로우를 추출한다.

DESCENDING

INTERSECTION 교집합의 로우를 추출한다.

MERGE JOIN OUTER 먼저 자신이ㅡ 조건만으로 액세스한 후 각각을 SORT하여

MERGE해 가는 조인

위와 동일한 방법이지만 outer join을 사용한다.

MINUS MINUS 함수를 사용한다.

NESTED LOOPS OUTER 먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를

이용해 다른 테이블을 연결하는 조인

위와 동일하지만 outer join을 사용한다.

PROJECTION 내부적인 처리의 일종

REMOTE 다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해

DATABASE LINK를 사용하는 경우

SEQUENCE 시퀀스를 액세스 한다.

SORT UNIQUE 같은 로우를 제거하기 위한 SORT

GROUP BY 액세스 결과를 GROUP BY 하기 위한 SORT

JOIN MERGE JOIN을 하기 위한 SORT

ORDER BY ORDER BY를 위한 SORT

TABLE ACCESS FULL 전체 테이블을 스캔한다.

CLUSTER CLUSTER를 액세스 한다.

HASH 키값에 대한 해쉬 알고리즘을 사용(버전 7에서만)

BY ROWID ROWID를 이용하여 테이블을 추출한다.

UNION 두 집합의 합집합을 구한다.(중복없음)

항상 전체 범위 처리를 한다.

UNION ALL 두 집합의 합집합을 구한다.(중복가능)

UNION과는 다르게 부분범위 처리를 한다.

VIEW 어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.(주로 서브쿼리에 의해 수행된 결과)

=======================================================================================================

출처 : http://jaehan.tistory.com/92?srchid=BR1http%3A%2F%2Fjaehan.tistory.com%2F92

EXPLAIN PLAN 이란?

사용자들이 SQL 문의 액세스 경로를 확인하고
튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후
실행 계획을 테이블(plan_table)에 저장하도록 해주는 명령 입니다.

1. PLAN TABLE의 생성

Explain plan을 sql 에 포함해서 수행하면 옵티마이저가 실행 계획까지만
수립하여 plan_table에 저장해 둡니다.

PLAN을 사용하고자 하는 USER로 SQLPLUS LOGIN한 후
ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql을 수행하여 plan_table을 생성 합니다.

C:\>SQLPLUS scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 16:41:26 2006

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql

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

SQL>

2. PLUSTRACE ROLE의 생성
sqlplus "/ as sysdba"로 접속하여 PLUSTRACE ROLE을 생성 합니다.
ORACLE_HOME/sqlplus/admin/plustrce.sql을 수행하여 plustrace role을 생성 합니다.

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 17:01:26 2006

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다

SQL> create role plustrace;

롤이 생성되었습니다.

SQL>
SQL> grant select on v_$sesstat to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$mystat to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL>
SQL> set echo off
SQL>

3. PLUSTRACE Role의 부여

PLUSTRACE ROLE을 plan을 사용하고자 하는 유저에게 부여 합니다.

SQL> GRANT plustrace TO scott;

권한이 부여되었습니다.

권한을 부여 한다음.
다시 plan을 사용하는 유저로 접속을 합니다.

SQL> conn scott/tiger
연결되었습니다.

autotrace 상태를 on으로 바꿉니다.

SQL> SET AUTOTRACE ON ;
SQL>

SQL문을 실행 합니다.

SQL> SELECT a.ename, a.sal, b.dname
2 FROM emp a, dept b
3 WHERE a.deptno = b.deptno;

ENAME             SAL DNAME
---------- ---------- --------------
SMITH             800 RESEARCH
ALLEN            1600 SALES
WARD             1250 SALES
JONES            2975 RESEARCH
MARTIN           1250 SALES
BLAKE            2850 SALES
CLARK            2450 ACCOUNTING
SCOTT            3000 RESEARCH
KING             5000 ACCOUNTING
TURNER           1500 SALES
ADAMS            1100 RESEARCH

ENAME             SAL DNAME
---------- ---------- --------------
JAMES             950 SALES
FORD             3000 RESEARCH
MILLER           1300 ACCOUNTING

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

Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

--------------------------------------------------------------------------

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

4 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
        626 recursive calls
          0 db block gets
        134 consistent gets
         10 physical reads
          0 redo size
        856 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         10 sorts (memory)
          0 sorts (disk)
         14 rows processed

SQL>

⊙ 참고

☞ 8.17
@C:\oracle\ora81\RDBMS\ADMIN\utlxplan.sql;
@C:\oracle\ora81\sqlplus\admin\plustrce.sql;

☞ 10g
@C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql
@C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql

⊙ Golden(골든)의 결과

- Ctrl + P 키를 누루면 결과 나온다.

⊙ TOAD(토드)에서 설정방법

- Ctrl + E 키를 누루면 결과 나온다.

PLAN 테이블(TOAD_PLAN_TABLE)이 존재한다면

TOAD 상단 메뉴에서
View 메뉴 > Options 메뉴 내용중 "Oracle > General" 에 Explain Plan Table Name 이 있습니다. 이곳에 "TOAD_PLAN_TABLE" 이라 명시하시고 사용하면 됩니다

저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

Oracle Hidden Parameter 란 (0)	2010.01.26
FAST_START_MTTR_TARGET (0)	2010.01.18
Statspack Report 간단 분석 방법 (0)	2009.12.17
cursor_sharing 파라미터에 대한 테스트 (0)	2009.12.16
Literal SQL 조회하는 방법 (0)	2009.12.05

Posted by [PineTree]

About DATABASE

EXPLAIN PLAN(실행계획) 이란

'ORACLE > TUNING' 카테고리의 다른 글

카테고리

공지사항

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바