Oracle 에서 explain plan 쿼리 계획

Mysql에서 쿼리 튜닝을 위해 혹은 인덱스를 걸기 위해 자주 사용했던 explain.

Oracle에서는 문법이 다른데 어떻게 다른지 사용법을 알아보자

---

Mysql

EXPLAIN
SELECT * 
FROM {table_name};
-- update, insert, delete 문에도 확인 가능

 

Oracle

-- 실행 계획 저장
EXPLAIN PLAN 
	SET STATEMENT_ID = '{PLAN value}'
	FOR
SELECT * 
FROM {table_name};

-- 저장된 실행 계획 보기
SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','{PLAN value}','ALL'));

 

Oracle 결과

EXPLAIN PLAN
    SET STATEMENT_ID ='test'
    FOR
SELECT * FROM dual;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'test', 'ALL'));

---

plan 보는 법

• Id : 실행계획의 오퍼레이션 Id
• Operation : 해당단계에 수행한 작업 내용
• Name : 해당단계에 작업을 수행한 대상 오브젝트(테이블 or 인덱스)
• Rows : 해당 단계 수행 시 조회될 예상 데이터 건수
• Bytes : 해당 단계까지 사용될 예상 데이터 양(누적)
• Cost : 해당 단계까지 사용될 예상 비용(누적)
• Time : 해당 단계까지 사용될 예상 시간(누적)

 

plan 실행 순서

부모보다 자식이 먼저 실행된다.

가장 안에서 위에서 를 기억하면 편하다.

위 결과의 순서는 5 -> 4 -> 6 -> 3 -> 7 -> 2 -> 8 -> 1 -> 0 순으로 진행된다.

그렇다면 아래 plan의 실행 순서는 어떻게 될까?

정답은 3 -> 5 -> 4 -> 2 -> 7 -> 6 -> 1 -> 0

 

plan table 구조

CREATE 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 NUMBER,
  OBJECT_TYPE VARCHAR2(30),
  OPTIMIZER VARCHAR2(255),
  SEARCH_COLUMNS NUMBER,
  ID NUMBER,
  PARENT_ID NUMBER,
  DEPTH NUMBER,
  POSITION NUMBER,
  COST NUMBER,
  CARDINALITY NUMBER,
  BYTES NUMBER,
  OTHER_TAG VARCHAR2(255),
  PARTITION_START VARCHAR2(255),
  PARTITION_STOP VARCHAR2(255),
  PARTITION_ID NUMBER,
  OTHER LONG,
  DISTRIBUTION VARCHAR2(30),
  CPU_COST NUMBER,
  IO_COST NUMBER,
  TEMP_SPACE NUMBER,
  ACCESS_PREDICATES VARCHAR2(4000),
  FILTER_PREDICATES VARCHAR2(4000),
  PROJECTION VARCHAR2(4000),
  TIME NUMBER,
  QBLOCK_NAME VARCHAR2(30),
  OTHER_XML CLOB
);

 

plan table 구조 설명

 

항목	설명
STATEMENT_ID	쿼리를 고유하게 식별하는 ID
PLAN_ID	실행 계획의 고유한 ID
TIMESTAMP	실행 계획이 생성된 시간
REMARKS	실행 계획에 대한 추가 정보
OPERATION	실행 계획에서 수행되는 연산
OPTIONS	연산에 대한 추가 옵션
OBJECT_NODE	연산이 수행되는 노드
OBJECT_OWNER	연산이 수행되는 객체의 소유자
OBJECT_NAME	연산이 수행되는 객체의 이름
OBJECT_ALIAS	연산이 수행되는 객체의 별칭
OBJECT_INSTANCE	연산이 수행되는 객체의 인스턴스
OBJECT_TYPE	연산이 수행되는 객체의 타입
OPTIMIZER	사용된 옵티마이저
SEARCH_COLUMNS	검색된 칼럼 수
ID	노드의 고유 ID
PARENT_ID	부모 노드의 ID
DEPTH	노드의 깊이
POSITION	노드의 위치
COST	노드의 비용
CARDINALITY	노드가 반환하는 행 수
BYTES	노드가 반환하는 바이트 수
OTHER_TAG	다른 정보
PARTITION_START	파티션 범위의 시작
PARTITION_STOP	파티션 범위의 끝
PARTITION_ID	파티션 ID
OTHER	다른 정보
DISTRIBUTION	노드의 분배 방법
CPU_COST	노드의 CPU 비용
IO_COST	노드의 IO 비용
TEMP_SPACE	노드에서 사용하는 임시 공간
ACCESS_PREDICATES	액세스 수행 시 사용되는 조건
FILTER_PREDICATES	필터링 시 사용되는 조건
PROJECTION	노드에서 사용되는 프로젝션
TIME	노드에서 소요된 시간
QBLOCK_NAME	블록 이름
OTHER_XML	다른 정보