'Oracle Explain plan'에 해당되는 글 1건

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;

SQL > @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan

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];

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;

컬 럼 명

설 명

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

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

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:' ||
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'

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

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

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

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'))

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이 저장되는 테이블을 지정하며, 기본값은 '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을 제한하여 출력할 수 있다.

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_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

선행 Table읽으면서 후행 Table을 한번씩 Access

실제 Join은 후행 Table에 대한 Access에서 발생하기 때문에 실제 Join은 후행 Table에 대한 Acccess에서 발생한다. 

그러므로 후행 Table을 읽는 단계가 Access Predicate가 된다.

Hash Join

선행 Table을 먼저 Build한 후, 후행 Table과 한번에 Join

실제 Join이 발생하는 Hash Join 단계가 Access Predicat로 표현된다.

구분

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의

정보를 보여준다.