이펙티브 오라클 (2009년)
Introduction
EXPLAIN PLAN은 주어진 SQL 쿼리를 지금 실행한다면 이 쿼리의 쿼리 계획이 어떻게 될지를 오라클에게 알려달라고 하는 데 이용될 수 있는 SQL 명령이다.
EXPLAIN PLAN 설치
- 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 생성은 오라클에서 스크립트로 제공해 주고 있다.
- 오라클 제공 스크립트 모음 폴더 : $ORACLE_HOME\RDBMS\ADMIN
SQL > @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan
테이블이 생성되었습니다.
경 과: 00:00:00.75
SQL > desc plan_table
이름 널? 유형
----------------------------------------------------------------------------------- -------- ------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
~~~~~~~ (이하 생략)
- 책의 설명은.... 잘 이해가 안간다... 그냥 자체 이해한 내용이니... 틀린점있으면 지적해 주면 매우매우 좋다^^.
- PLAN TABLE을 공유하여 사용하자~ 인것 같다..
- 스크립트 실행을 위해 특정 유저로 접속하여 스크립트를 실행시키면 테이블의 OWNER는 특정 유저가 된다. 모.. 자체적으로 일정 유저에 공유 시켜도 될거 같은데... 이 책에서는 UTILS 혹은 TOOLS라는 유저를 생성하여 공유할 것을 권한다.
- UTILS 혹은 TOOLS라고 불리는 스키마 생성 : SQL> CREATE USER UTILS IDENTIFIED BY UTILS;
- CREATE SESSION과 CREATE TABLE 권한을 부여한다. : SQL> CREATE USER UTILS IDENTIFIED BY UTILS;
- 스키마를 이용하여 "ON COMMIT PRESERVE ROWS"옵션을 가진 글로벌 임시 테이블 형태로 계획 테이블을 생성한후 PLAN_TALBE의 모든 권한(ALL privilege)을 일반(public)에게 부여한다.
글로벌 임시 테이블
- 임시 테이블은 사용자가 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 (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob
)
ON COMMIT PRESERVE ROWS;
-- 모든권한
grant ALL on PLAN_TABLE 모든유저;
-- 동의어 생성 : PUBLIC SYNONYM 을 생성할수 있는 권한이 있는 유저로 가야한다.
CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
- 계획 테이블의 내용을 보여 주는 utlxpls.sql 스크립트의 내용을보면 결국은 DBMS_XPLAN 패키지를 불러서 실행계획을 보여준다.
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'serial'));
- DBMS_XPLAN 패키지가 10g부터는 많은 업그레이드로 인해 요즘의 튜닝도구로 널리 사용되고 있다.
- 우리는 이제 약간 구식인 그리고 자료가 아주 많은 EXPLAIN PLAN 보다 좀더 세련되게 DBMS_XPLAN 패키지에 대해 심도있게 탐구해 보자~~
DBMS_XPLAN Package
- Oracle에서 실행 계획을 분석하기 위한 Tool을 세가지 들라면?
- DBMS_XPLAN Package
- DBMS_XPLAN Package
- DBMS_XPLAN Package
- 오타가 아니다. DBMS_XPLAN Package가 CBO, 그리고 Query 튜닝의 시작과 끝을 담당하는 유일무이한Tool이라는 것을 강조하기 위한 것이다.
- 이라고.. Oracle Optionizing Oracle Optimizer에서 조동욱씨가 표현하였다.
- 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 Package에 대해 알아볼까요~~~
DBMS_XPLAN.DISPLAY
- 단일 SQL문에 대해 예측 실행계획을 보여주는 Function
Function Parameter
FUNCTION DISPLAY(TABLE_NAME VARCHAR2 DEFAULT 'PLAN_TABLE',
STATEMENT_ID VARCHAR2 DEFAULT NULL,
FORMAT VARCHAR2 DEFAULT 'TYPICAL',
FILTER_PREDS VARCHAR2 DEFAULT NULL)
파라미터 설명
| 파라미터 | 설명 |
|---|---|
| TABLE_NAME | Execution Plan이 저장되는 테이블을 테이블을 지정하며, Defalut는 'PLAN_TABLE'이다. |
| STATEMENT_ID | Execution Plan시 SET STATEMENT_ID를 지정한 경우 이를 불러올 수 있다. 값이 NULL일 경우 마지막에 실행된 문장을 불러온다. |
| FORMAT(BASIC) | 가장 기본적인 정보만 보여줌 |
| FORMAT(TYPICAL) | Format의 Default값인 Typical은 SQL 튜닝에 필요한 Normal한 정보를 보여줌 SQL 튜닝에 가장 유용하게 사용되는 Predicate Information이 제공된다 |
| FORMAT(ALL) | Typical Format에 Query Block Name과 Column Projection Information이 추가로 제공된다 |
| FORMAT(OUTLINE) | Typical Format에 추가적으로 Hidden Hint인 Outline Global Hint를 제공한다 |
| FORMAT(ADVANCED) | ALL Format에 OUTLINE Format를 합친 정보를 제공한다 |
| FILTER_PREDS | 저장된 PLAN에서 일부 Row 또는 Row Set을 제한하여 출력할 수 있다. |
사용방법
- SQL*PLUS open
- Oracle Client인SQL*PLUS를실행한다.
- EXPLAIN PLAN FOR + SQL;
- 분석하고자 하는 SQL을'EXPLAIN PLAN FOR' 다음에넣고 Enter
- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
- SQL이 EXPLAIN 되었으면 그다음에 원하는 포멧으로 출력한다.
- 위와같이 하였으면 기본적으로 'PLAN_TABLE' 과 'TYPICAL' Format으로 출력하겠다는의미이다.
- 여기서 잠깐~! 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 > create table t1(c1 int, c2 int);
테이블이 생성되었습니다.
경 과: 00:00:00.28
SQL > create table t2(c1 int, c2 int);
테이블이 생성되었습니다.
경 과: 00:00:00.00
SQL > create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.
경 과: 00:00:00.07
SQL > create index t2_n1 on t2(c1)
2 ;
인덱스가 생성되었습니다.
경 과: 00:00:00.01
SQL > insert into t1
2 select level,level from dual connect by level <= 1000;
1000 개의 행이 만들어졌습니다.
경 과: 00:00:00.06
SQL > insert into t2
2 select level,level from dual connect by level <= 1000
3 ;
1000 개의 행이 만들어졌습니다.
경 과: 00:00:00.01
SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS('HNJ','T1', METHOD_OPT =>'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:01.03
SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS('HNJ','T2', METHOD_OPT =>'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.06
SQL >explain plan for
2 select /*+ use_nl(t1 t2) */ *
3 from t1, t2
4 where t1.c1 = t2.c2
5 and t1.c1 = 1;
해석되었습니다.
경 과: 00:00:00.12
SQL > select * from table(dbms_xplan.display('plan_table',null,'typical',null));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1755794669
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 14 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"=1)
4 - filter("T2"."C2"=1)
17 개의 행이 선택되었습니다.
경 과: 00:00:00.09
SQL > explain plan for
2 select /*+ use_hash(t1 t2) */ *
3 from t1, t2
4 where t1.c1 = t2.c2
5 and t1.c2 = 1;
해석되었습니다.
경 과: 00:00:00.00
SQL > select * from table(dbms_xplan.display('plan_table',null,'typical',null));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 14 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1000 | 7000 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C2")
2 - filter("T1"."C2"=1)
16 개의 행이 선택되었습니다.
경 과: 00:00:00.09
SQL >
- 왜 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
- DBMS_XPLAN 패키지의 파라미터중 Format의 옵션5가지(BASIC,TYPICAL,ALL,OUTLINE,ADVANCED)에 대하여 알아본다. 자세한 내용은 아래 링크 클릭~
- DBMS_XPLAN Format
DBMS_XPLAN.DISPLAY_CURSOR
- 단일 SQL문에 대해 실제 수행된 실행계획을 보여주는 Function
Function Parameter
FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
CURSOR_CHILD_NO INTEGER DEFAULT 0,
FORMAT VARCHAR2 DEFAULT 'TYPICAL'))
파라미터 설명
| 파라미터 | 설명 |
|---|---|
| TABLE_NAME | 실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다. SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다. SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다. |
| STATEMENT_ID | 해당 SQL_ID의 CHILD NUMBER 값을 지정한다. CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다. |
| FORMAT | 저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터 |
사용조건
- Oracle 10g에 추가된 기능이다.
- 이 Package를 사용하기 위해서는 아래의 객체에SELECT 권한이있어야 한다.
- V$SESSION
- V$SQL_PLAN
- V$SQL(optional)
- V$SQL_PLAN_STATISTICS_ALL
오라클은 라이브러리 캐시에 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공한다. 이것과 함께 _id 값으로 조인해서 사용할 수 있도록 제공되는 뷰가 몇 가지 더 있는데, 그 중 활용도가 가장 높은 것이 v$sql_plan과 v$sql_plan_statistics다. 그리고 이 두 뷰를 합쳐서 보여주는 것이 V$SQL_PLAN_STATISTICS_ALL 이다.
- PLAN_STATISTICS 정보는 다음의 조건 중 하나를 만족해야 기록된다.
- 'STATISTICS_LEVEL' Parameter 값을 ALL로변경한경우
- '_ROWSOURCE_EXECUTION_STATISTICS' Parameter 값을TRUE로변경한경우
- GATHER_PLAN_STATISTICS HINT를 사용할때, 10g부터지원
- 쿼리 실행전에 SERVEROUTPUT을 OFF하는것을 잊지말자.
- DISPLAY_CURSOR 을 조회하려면 sql_id와 child_number를 알아야한다. SERVEROUTPUT을 OFF하지 않으면 dbms_output.disable프로시저를 호출하는 커서의 sql_id를 찾게 된다.
- SQL> SET SERVEROUTPUT OFF;
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의 정보를 보여준다. |
Runtiime Executiion Plan 확인방법
- 예측실행계획과 실제실행계획을 비교하여 본다.
예측실행계획
- SQL 실행
SQL > set serveroutput off
SQL > select *
2 from scott.emp e, scott.dept d
3 where d.deptno = e.deptno
4 and e.sal >= 1000;
~~~~~~~~~~~~~
24 개의 행이 선택되었습니다.
- V$SESSION에서 SQL_ID 확인
SQL > SELECT PREV_SQL_ID, PREV_CHILD_NUMBER
2 FROM V$SESSION
3 WHERE SID = USERENV('sid')
4 AND USERNAME IS NOT NULL
5 AND PREV_HASH_VALUE <> 0;
PREV_SQL_ID PREV_CHILD_NUMBER
------------- -----------------
2ja7z9r1v93a2 0
1 개의 행이 선택되었습니다.
- DBMS_XPLAN.DISPLAY_CURSOR 조회
SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2ja7z9r1v93a2', 0, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 2ja7z9r1v93a2, child number 0
-------------------------------------
select * from scott.emp e, scott.dept d where d.deptno = e.deptno
and e.sal >= 1000
Plan hash value: 615168685
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
|* 1 | HASH JOIN | | 24 | 825K| 825K| 659K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 4 | | | |
|* 3 | TABLE ACCESS FULL| EMP | 24 | | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
3 - filter("E"."SAL">=1000)
Note
-----
- dynamic sampling used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
28 개의 행이 선택되었습니다.
경 과: 00:00:00.81
수행한SQL이 다음의 조건을 충족하지 않았기 때문에 기록이 안됨
- 'STATISTICS_LEVEL' Parameter 값을 ALL로변경한경우
- '_ROWSOURCE_EXECUTION_STATISTICS' Parameter 값을TRUE로변경한경우
- GATHER_PLAN_STATISTICS HINT를사용할때, 10g부터지원
실제실행계획
SQL > ALTER SESSION SET "_ROWSOURCE_EXECUTION_STATISTICS" = TRUE;
세션이 변경되었습니다.
SQL > set serveroutput off
-- ALTER SESSION SET "_ROWSOURCE_EXECUTION_STATISTICS" = TRUE; 대신
-- /*+ GATHER_PLAN_STATISTICS */ 힌트를 사용해도 된다.
SQL > select *
2 from scott.emp e, scott.dept d
3 where d.deptno = e.deptno
4 and e.sal >= 1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- ----
8499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 30 SALES CHICAG
8521 WARD SALESMAN 7698 81/02/22 1250 500 30 30 SALES CHICAG
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
24 개의 행이 선택되었습니다.
경 과: 00:00:00.25
SQL > SELECT PREV_SQL_ID, PREV_CHILD_NUMBER
2 FROM V$SESSION
3 WHERE SID = USERENV('sid')
4 AND USERNAME IS NOT NULL
5 AND PREV_HASH_VALUE <> 0;
PREV_SQL_ID PREV_CHILD_NUMBER
------------- -----------------
aduuuwpa8f64v 0
1 개의 행이 선택되었습니다.
경 과: 00:00:00.11
SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aduuuwpa8f64v', 0, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID aduuuwpa8f64v, child number 0
-------------------------------------
select * from scott.emp e, scott.dept d where d.deptno = e.deptno and e.sal >= 1000
Plan hash value: 615168685
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-
----------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 24 | 24 |00:00:00.01 | 16 | 825K| 825K| 679K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 24 | 24 |00:00:00.01 | 9 | | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
3 - filter("E"."SAL">=1000)
Note
-----
- dynamic sampling used for this statement
24 개의 행이 선택되었습니다.
경 과: 00:00:00.45
SQL >
Note
: 부가적으로 필요한 정보를 제공한다. 위 예문에서 는 dynamic sampling 이 사용되었음 알려준다.
Oracle 10g에서는 통계 정보가 없는 Table 에 대해서 dynamic sampling 을 수행한다.
예측실행계획과 실제실행계획 비교
- A-Rows, A-Time, Buffer 추가정보보여줌
- E : Estimate, A : Actual
DBMS_XPLAN Package의 실행계획의 항목
SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7ww3qp7ahvbkk', 0, 'BASIC ROWS BYTES COST PREDICATE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from scott.emp e, scott.dept d where d.deptno = e.deptno
and e.sal >= 1000
Plan hash value: 615168685
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)|
|* 1 | HASH JOIN | | 28 | 1876 | 7 (15)|
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)|
|* 3 | TABLE ACCESS FULL| EMP | 28 | 1036 | 3 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
3 - filter("E"."SAL">=1000)
22 개의 행이 선택되었습니다.
경 과: 00:00:01.57
SQL >
이문서의 내용은 아래 책,문서를 참고했습니다.
- THOMAS KYTE 저, 박민호 역의 이펙티브오라클
- 강정식군 오라클클럽 세미나자료, 오라클클럽세미나_4.DMBS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝
- 조동욱저, Oracle Optionizing Oracle Optimizer
- 조시형저, 오라클 성능 고도화 원리와 해법I
"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3461
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.