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 처리를 위해 사용한 메모리 수치
끝.
