☞ 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 문의 액세스 경로를 확인하고 Explain plan을 sql 에 포함해서 수행하면 옵티마이저가 실행 계획까지만 PLAN을 사용하고자 하는 USER로 SQLPLUS LOGIN한 후 SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 16:41:26 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> @C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql 테이블이 생성되었습니다. SQL> 2. PLUSTRACE ROLE의 생성 C:\>sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 17:01:26 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> @C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql 롤이 생성되었습니다. SQL> 권한이 부여되었습니다. 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> 3. PLUSTRACE Role의 부여 PLUSTRACE ROLE을 plan을 사용하고자 하는 유저에게 부여 합니다. SQL> GRANT plustrace TO scott; 권한이 부여되었습니다. SQL> conn scott/tiger SQL> SET AUTOTRACE ON ; SQL> SELECT a.ename, a.sal, b.dname ENAME SAL DNAME ENAME SAL DNAME 14 개의 행이 선택되었습니다. -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 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 | -------------------------------------------------------------------------- 4 - access("A"."DEPTNO"="B"."DEPTNO") SQL> ☞ 10g ⊙ Golden(골든)의 결과 - Ctrl + P 키를 누루면 결과 나온다.
튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후
실행 계획을 테이블(plan_table)에 저장하도록 해주는 명령 입니다.
1. PLAN TABLE의 생성
수립하여 plan_table에 저장해 둡니다.
ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql을 수행하여 plan_table을 생성 합니다.
C:\>SQLPLUS scott/tiger
다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sqlplus "/ as sysdba"로 접속하여 PLUSTRACE ROLE을 생성 합니다.
ORACLE_HOME/sqlplus/admin/plustrce.sql을 수행하여 plustrace role을 생성 합니다.
다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> drop role plustrace;
drop role plustrace
*
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다
SQL> create role plustrace;
SQL> grant select on v_$sesstat to plustrace;
SQL> set echo off
SQL>
권한을 부여 한다음.
다시 plan을 사용하는 유저로 접속을 합니다.
연결되었습니다.
autotrace 상태를 on으로 바꿉니다.
SQL>
SQL문을 실행 합니다.
2 FROM emp a, dept b
3 WHERE a.deptno = b.deptno;
---------- ---------- --------------
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
---------- ---------- --------------
JAMES 950 SALES
FORD 3000 RESEARCH
MILLER 1300 ACCOUNTING
Execution Plan
----------------------------------------------------------
Plan hash value: 351108634
Predicate Information (identified by operation id):
---------------------------------------------------
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
⊙ 참고
☞ 8.17
@C:\oracle\ora81\RDBMS\ADMIN\utlxplan.sql;
@C:\oracle\ora81\sqlplus\admin\plustrce.sql;
@C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql
@C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql
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 |
