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

Explain Plan란?
  • 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 (
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;

  

   

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

  

   

  • 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',
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을 제한하여 출력할 수 있다.

   

  • 사용방법

       

    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>

원본 위치 <http://www.oracleclub.com/lecture/1017>

원본 위치 <http://www.gurubee.net/display/DBSTUDY/EXPLAIN+PLAN>

블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,