- Explain plan은 QL문의 엑세스 경로를 확인하고 튜닝할 수 있도록 SQL문을 분석하고 해석하여 실행계획을 수립한 후 실행계획을PLAN_TABLE에 저장하도록 해주는 명령이다.
- SQL Trace 없이 사용 할 수 있다.
- EXPLAIN Plan문법
Explain plan[set statement_id = 'identifier' ] [ Into tablename ] For sql statement |
|
Explain plan Set statement_id='a1' For Select /*+ use_nl(emp dept) */ * From emp, dept Where emp.deptno = dept.deptno And emp.deptno=10; |
|
- - statement_id = 'identifiedr' : 1-30자로 부여할 수 있는 해당 실행문의 제목
- - INTO tablename : 출력문을 저장하는 테이블명 PLAN_TABLE을 사용하지 않을경우 사용
- - FOR statement : 실행계획을 수립하고자 하는 SQL문(SELECT, INSERT, DELETE, UPDATE)
- EXPLAIN 설치
① EXPLAIN PLAN 을 설치하면 $ORACLE_HOME/rdbms/admin에 다수의 스크립트가 포함된다.
② PLAN_TALBE이라 명명된 테이블의 CREATE TABLE문을 포함하고 있는 utlxplan.sql(UTiLility
eXplain의 줄임말)
병렬 쿼리 계획에 특화된 정보를 포함하여 계획 테이블의 내용을 보여 주는 utlxplp.sql(UTiLtility eXplain Plan Parallel의 줄임말)
일반적인 직렬(비병렬) 계획에 대한 계획 테이블의 내용을 보여 주는 utlxpls.sql(UTiLity eXplain PLan으 줄임말)
- EXPLAIN PLAN을 설정하기 위해서는 먼저 PLAN_TABLE을 만들어야 한다. PLAN_TABLE 생성은 오라클에서 스크립트로 제공해 주고 있다.
SQL > @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan |
|
③ Global Temporary 생성
Gloal Temporary란?
- 임시 테이블은 사용자가 DML(update, insert, delete) 문을 실행한 후, 트랜젝션을 종료(commit)하면 변경된 데이터들이 테이블에 저장되지 않는 테이블이다
Create 문법 Create Global Temporary Table <table_name>( <column_name> <column_data_type>, <column_name> <column_data_type>, <column_name> <column_data_type>) [ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]; |
|
- on commit delete rows : 트랜젝션을 발생시킨 후, commit문을 실행할 때 없어지는 방법, 한 transaction 동안만 data를 저장(default임)
- on commit preserve rows : 트랜젝션을 종료하면(commit) 테이블 내에 데이터가 저장되었다가 세션을 종료하면 임시 테이블에 저장되었던 데이터들이 모두 없어지는 방법
한 session 동안만 data를 저장
- Temporary Table?
- 특정 Session 또는 Transaction 동안만 data를 저장함.
- TABLE의 definition 정보는 data dictionary에 영구적으로 저장되지만, data는 일시적으로 저장된다.
- CREATE로 TABLE 생성시 SEGMENT가 할당되는 것이 아니라 첫 번째 INSERT시 할당된다.
- DML 작업 시 해당 data에 대해 LOCK을 걸지 않는다.
- data 변경 시 redo log에 기록이 남지 않는다.
CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE ( ON COMMIT PRESERVE ROWS; -- 모든권한 -- 동의어 생성 : PUBLIC SYNONYM 을 생성할수 있는 권한이 있는 유저로 가야한다. |
|
- 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 |
다른 필요한 텍스트를 지정하기 위한 필트 |
- Explain Plan 실행
SQL> Explain plan Set statement_id='a1' For Select /*+ use_nl(emp dept) */ * From emp, dept Where emp.deptno = dept.deptno And emp.deptno=10; |
해당 SQL문 |
SQL> def id = a12 |
ID값 지정 |
SQL> SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) ||DECODE(id,0,'cost estimate:' || |
|
SQL> Explain plan Set statement_id='a1' For 2 Select /*+ use_nl(emp dept) */ * 3 From emp, dept Where emp.deptno = dept.deptno And emp.deptno=10;
해석되었습니다.
SQL> def id=a1
SQL> SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) ||DECODE(id,0,'cost estimate:' || 2 DECODE(position,'0','N/A',position),null) || ' ' ||options || DECODE(object_name,null,null,':') || 3 RPAD(object_owner, LENGTH(object_name)+1,',') || object_name || 4 DECODE (object_type,'UNIQUE' ,'(U) ','NON_UNIQUE','(NU)',null) || 5 DECODE(object_instance,null,null,'('||object_instance||')') "Explain Plan" 6 FROM PLAN_TABLE 7 START WITH ID= 0 and STATEMENT_ID = '&&id' 8 CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id'; 구 7: START WITH ID= 0 and STATEMENT_ID = '&&id' 신 7: START WITH ID= 0 and STATEMENT_ID = 'a1' 구 8: CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id' 신 8: CONNECT by prior ID=PARENT_ID and STATEMENT_ID='a1'
Explain Plan -------------------------------------------------------------------------------- SELECT STATEMENTcost estimate:4 NESTED LOOPS TABLE ACCESS BY INDEX ROWID:SYS,,DEPT(2) INDEX UNIQUE SCAN:SYS,,,,,DEPT_U1 TABLE ACCESS BY INDEX ROWID:SYS,EMP(1) INDEX RANGE SCAN:SYS,,,,EMP_N1
6 개의 행이 선택되었습니다.
SQL> exit |
|
- PERATION의 종류와 OPTIONS에 대한 설명
OPERATION(기능) |
OPTIONS(옵션) |
설 명 |
AGGREGATE |
GROUP BY |
그룹함수를 사용하여 하나의 로우가 추출되도록 하는 처리(버전 7에서만 표시됨) |
AND-EQUAL |
|
인덱스 머지를 이용하는 경우 |
CONNECT BY |
|
CONNECT BY를 사용하여 트리 구조로 전개 |
CONCATENATION |
|
단위 액세스에서 추출한 로우들의 합집합을 생성 |
COUNTING |
|
테이블의 로우스를 센다 |
FILTER |
|
선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업 |
FIRST ROW |
|
조회 로우 중에 첫번째 로우만 추출한다. |
FOR UPDATE |
|
선택된 로우에 LOCK을 지정한다. |
INDEX |
INQUE |
UNIQUE인덱스를 사용한다. (단 한개의 로우 추출) |
|
RANGE SCAN |
NON-UNIQUE한 인덱스를 사용한다.(한 개 이상의 로우) |
|
RANGE SCAN DESCENDING |
RANGE SCAN하고 동일하지만 역순으로 로우를 추출한다. |
NTERSECTION |
|
교집합의 로우를 추출한다. |
MERGE JOIN |
|
먼저 자신이ㅡ 조건만으로 액세스한 후 각각을 SORT하여 MERGE해 가는 조인 |
|
OUTER |
위와 동일하지만 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 |
|
어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.(주로 서브쿼리에 의해 수행된 결과) |
- DBMS_XPLAN Package
- DBMS_XPLAN은 EXPLAIN PLAN 구문보다 확장된 정보를 보여준다.
- Oracle 9.2버젼에 소개되었으며 plan_table에 저장된 실행계획을 좀 더 쉽게 출력해 볼 수 있게 되었다.
- DBMS_XPLAN Package에서 제공하는Procedure
- DISPLAY
- DISPLAY_CURSOR
- DISPLAY_AWR
- DISPLAY_SQLSET
- PREPARE_RECORDS
- VALIDATE_FORMAT
- 이 Package를 사용하기 위해서는 아래의 객체에 SELECT권한이 있어야 한다.
- V$SESSION
- V$SQL_PLAN
- V$SQL(optional)
- V$SQL_PLAN_STATISTICS_ALL
- @$ORACLE_HOME/rdbms/admin/ultxpls 스크립트를 열여보면 내부적으로 DBMS_XPLAN Package를 호출하고 있는것을 볼 수 있다.
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial')) |
|
- DBMS_XPLAN.DISPLAY
- DBMS_XPLAN.DISPLAY는 단일 SQL문에 대해 예측 실행계획을 보여주는 Function 이다.
- DBMS_XPLAN.DISPLAY는 실측 정보가 아닌 예측 정보를 제공한다.
- Function Parameter
FUNCTION DISPLAY(TABLE_NAME VARCHAR2 DEFAULT 'PLAN_TABLE', |
|
- 파라미터 설명
파라미터 |
|
설명 |
TABLE_NAME |
|
Execution Plan이 저장되는 테이블을 지정하며, 기본값은 'PLAN_TABLE' 이다. |
STATEMENT_ID |
|
Execution Plan시 SET STATEMENT_ID를 지정한 경우 이를 불러올 수 있다. 값이 NULL일 경우 마지막에 실행된 문장을 불러온다. |
FORMAT |
BASIC |
가장 기본적인 정보만 보여 준다. |
|
TYPICAL |
Format의 Default값인 Typical은 SQL 튜닝에 필요한 Normal한 정보를 보여 준다. SQL 튜닝에 가장 유용하게 사용되는 Predicate Information이 제공 된다. |
|
ALL |
Typical Format에 Query Block Name과 Column Projection Information이 추가로 제공된다 |
|
OUTLINE |
Typical Format에 추가적으로 Hidden Hint인 Outline Global Hint를 제공한다 |
|
ADVANCED |
ALL Format에 OUTLINE Format를 합친 정보를 제공한다 |
FILTER_PREDS |
|
저장된 PLAN에서 일부 Row 또는 Row Set을 제한하여 출력할 수 있다. |
- 사용방법
① Explain plan [set statement_id = 'identifier' ] [ Into tablename ] for + SQL;
- 분석하고자 하는 SQL을 'Explain plan for' 다음에 넣고 Enter
② Select * from Table(DBMS_XPLAN.DISPLAY);
- SQL이 Explain 되었으면 그 다음에 원하는 포맷으로 출력하기 위해서 위와 같이 기본적으로 'PLAN_TABLE'과 'TYPICAL' Format으로 출력.
SQL> SQL> explain plan for 2 select /*+ use_nl(emp dept) */ * from emp, dept 3 where emp.empno=dept.deptno 4 and emp.deptno=10;
해석되었습니다.
SQL> select * from table(dbms_xplan.display('plan_table',null,'typical',null));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3431005640 ---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 57 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 57 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 3 - filter("EMP"."DEPTNO"=10) 4 - access("EMP"."EMPNO"="DEPT"."DEPTNO")
17 개의 행이 선택되었습니다. |
|
여기서 잠깐~! Predicate Information에 나오는 Access,Filter Predicate에 대해 알아보자~!
- *Access Predicate*는 Access Type을 결정하는데 사용되는 Predicate(조건)을 의미한다. 더 정확하게 말하면 실제 Block을 읽기 전에 어떤 방법으로 Block을 읽을 것인가를 결정한다는 의미이다.
따라서 Index Lookup이나 Join등은 Access Predicate로 표현된다. - *Filter Predicate*는 실제 Block을 읽은 후 Data를 걸러 내기 위해 사용되는 Predicate(조건)를 의미한다.
- Predicate Information test
SQL> SQL> explain plan for 2 select /*+ use_nl(emp dept) */ * from emp, dept 3 where emp.empno=dept.deptno 4 and emp.deptno=10;
해석되었습니다.
SQL> select * from table(dbms_xplan.display('plan_table',null,'typical',null));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3431005640 ---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 57 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 57 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 3 - filter("EMP"."DEPTNO"=10) 4 - access("EMP"."EMPNO"="DEPT"."DEPTNO")
17 개의 행이 선택되었습니다. |
|
SQL> SQL> explain plan for 2 select /*+ use_hash(emp dept) */ * from emp, dept 3 where emp.empno=dept.deptno 4 and emp.deptno=10;
해석되었습니다.
SQL> select * from table(dbms_xplan.display('plan_table',null,'typical',null));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1123238657
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 57 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 57 | 7 (15)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 3 | 111 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
1 - access("EMP"."EMPNO"="DEPT"."DEPTNO") 2 - filter("EMP"."DEPTNO"=10)
16 개의 행이 선택되었습니다. |
|
- 왜 Nested Loops Join과 Hash Join에서 Access Predicate가 표현되는 방식의 차이가 발생하는가?
두 Join의 동작방식의 차이에서 비롯된다.
구분 |
방식 |
결론 |
Nested Loops Join |
선행 Table읽으면서 후행 Table을 한번씩 Access |
실제 Join은 후행 Table에 대한 Access에서 발생하기 때문에 실제 Join은 후행 Table에 대한 Acccess에서 발생한다. 그러므로 후행 Table을 읽는 단계가 Access Predicate가 된다. |
Hash Join |
선행 Table을 먼저 Build한 후, 후행 Table과 한번에 Join |
실제 Join이 발생하는 Hash Join 단계가 Access Predicat로 표현된다. |
- FORMAT
구분 |
FORMAT |
내용 |
예측내용 |
BASIC |
DBMS_XPLAN.DISPLAY 포멧과 같은 내용 |
예측내용 |
TYPICAL |
DBMS_XPLAN.DISPLAY 포멧과 같은 내용 |
예측내용 |
ALL |
DBMS_XPLAN.DISPLAY 포멧과 같은 내용 |
예측내용 |
OUTLINE |
DBMS_XPLAN.DISPLAY 포멧과 같은 내용 |
예측내용 |
ADVANCED |
DBMS_XPLAN.DISPLAY 포멧과 같은 내용 |
실측내용 |
ALLSTATS |
실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여준다. 수행횟수에 따라 *누적된 값*을 보여준다 |
실측내용 |
ALLSTATS LAST |
실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여준다. *가장 최근*에 수행된 값만 보여준다. |
실측내용 |
ADVANCED ALLSTATS LAST |
DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 Format의 정보를 보여준다. |
<참고문헌>
Oracle Database 11g : Adminstration Workshop
Oracle Database 10g Documentation Library - councepts
원본 위치 <http://www.oracleclub.com>
'1. IT Story > DB' 카테고리의 다른 글
| Oracle 10g Install 설치 후(DBCA생성 전) 바로 Upgrade(10.2.0.4) [RHEL5] (0) | 2012.03.26 |
|---|---|
| Oracle 10g Install [RHEL5] (0) | 2012.03.26 |
| Oracle 10g Upgrade 10.2.0.1 -> 10.2.0.4 (DB생성된 후 패치) [RHEL4] (0) | 2012.03.16 |
| Oracle 10g Upgrade 10.2.0.1 -> 10.2.0.4 (설치 후 바로 패치) [RHEL4] (0) | 2012.03.16 |
| Oracle SQL과 옵티마이져 (0) | 2012.02.29 |
| Oracle SQL Sub-query(기초) (0) | 2012.02.29 |
| Oracle HINT(기초) (0) | 2012.02.29 |
| Oracle SQL Driving Table & JOIN(기초) (0) | 2012.02.29 |
운명을바꾸는자
IT와 함께 살아가는 삶
