Oracle 예상 실행계획과 실제 실행계획 사용 방법(툴 상관없음)

Oracle에서 성능 개선을 위해 실행계획을 사용한다.

아래와 같이 실행계획은 예상 실행계획과 실제 실행계획 방법을 살펴보자

 

예상 실행계획

Toad, Orange와 같은 Tool에서는 "ctrl+e" 눌러서 예상 실행계획을 편하게 확인하는 경우가 있으나 자세한 정보를 보기 위해서는 아래와 같이 따라한다.

 

SELECT * FROM T_ORD WHERE ORD_SEQ = 4;

위 쿼리를 예상 실행계획으로 실행하고 싶은 경우엔 아래와 같이 실행한다.

 

-- 실행계획 만들기
EXPLAIN PLAN FOR
SELECT * FROM T_ORD WHERE ORD_SEQ = 4;

-- 실행계획 확인하기
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

위와 같이 쿼리 2문장을 순서대로 각각 실행하면 아래와 같이 예상 실행계획 결과가 나온다.

 

PLAN_TABLE_OUTPUT
 
-----------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    44 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD    |     1 |    44 |     2   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_T_ORD |     1 |       |     1   (0)|
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ORD_SEQ"=4)

-- Id : 실행계획의 오퍼레이션 ID (구분자, 순서아님)
-- Operation : 해당 단계에 수행한 작업 내용
-- Name : 해당 단계에 작업을 수행한 대상 오브젝트(테이블 또는 인덱스)
-- Rows : 해당 단계 수행 시 조회될 예상 데이터 건수
-- Bytes : 해당 단계까지 사용될 예상 데이터양(누적)
-- Cost (%CPU) : 해당 단계까지 사용될 예상 비용(누적)

 

실제 실행계획

SELECT *
  FROM T_ORD T1
     , M_CUS T2
 WHERE T1.CUS_ID = T2.CUS_ID
   AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
   AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
   AND T2.CUS_GD = 'A';

위 쿼리를 실제 실행계획으로 실행하고 싶은 경우엔 아래와 같이 실행한다.

 

1번 : 실제 실행계획 만들기(hint 추가)

SELECT /*+ GATHER_PLAN_STATISTICS */
       *
  FROM T_ORD T1
     , M_CUS T2
 WHERE T1.CUS_ID = T2.CUS_ID
   AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
   AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
   AND T2.CUS_GD = 'A';

 

2번 : 실제 실행계획을 만든 SQL의 SQL_ID찾아내기

SELECT T1.SQL_ID ,T1.CHILD_NUMBER ,T1.SQL_TEXT
  FROM V$SQL T1
 WHERE T1.SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
 ORDER BY T1.LAST_ACTIVE_TIME DESC;

 

3번 : 실제 실행계획 조회하기(각자의 SQL_ID, CHILD_NUMBER를 사용할 것)

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3ykn80uy9n32d',0,'ALLSTATS LAST'));

 

위와 같이 1~3번 쿼리를 순서대로 실행하면 아래와 같이 실제 실행계획 결과가 나온다.

PLAN_TABLE_OUTPUT
SQL_ID  3ykn80uy9n32d, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        *   FROM T_ORD T1      , 
M_CUS T2  WHERE T1.CUS_ID = T2.CUS_ID    AND T1.ORD_DT >= 
TO_DATE('20170101','YYYYMMDD')    AND T1.ORD_DT < 
TO_DATE('20170201','YYYYMMDD')    AND T2.CUS_GD = 'A'
 
Plan hash value: 3240201901
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    101 |00:00:00.01 |      12 |       |       |          |
|*  1 |  HASH JOIN         |       |      1 |    170 |    101 |00:00:00.01 |      12 |   779K|   779K| 1234K (0)|
|*  2 |   TABLE ACCESS FULL| M_CUS |      1 |     60 |     60 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T_ORD |      1 |    252 |    146 |00:00:00.01 |       5 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."CUS_ID"="T2"."CUS_ID")
   2 - filter("T2"."CUS_GD"='A')
   3 - filter(("T1"."ORD_DT"<TO_DATE(' 2017-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "T1"."ORD_DT">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

-- Starts : 해당 단계를 수행한 횟수
-- E-Rows : 해당 단계의 예상 데이터 건수
-- A-Rows : 해당 단계의 실제 데이터 건수
-- A-Time : 해당 단계까지 수행된 실제 시간(누적)
-- Buffers : 해당 단계까지 메모리 버퍼에서 읽은 블록 수(논리적 IO 횟수, 누적)
-- Reads : 해당 단계까지 디스크에서 읽은 블록 수(물리적 IO 횟수, 누적)
-- OMem : SQL 처리를 위해 사용한 메모리 수치
-- 1Mem : SQL 처리를 위해 사용한 메모리 수치
-- Used-Mem : SQL 처리를 위해 사용한 메모리 수치

끝.