SQL 스터디 4주차!
EXPLAIN PLAN
예상 실행계획
예상 실행계획 만들기
EXPLAIN PLAN FOR
SELECT * FROM T_ORD WHERE ORD_SEQ = 4;
예상 실행계획 확인하기
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
결과
Plan hash value: 2027818626
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_ORD | 1 | 44 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T_ORD | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORD_SEQ"=4)
- Id: 실행계획의 오퍼레이션 id
- Name: 해당 단계에 작업을 수행한 대상 오브젝트 (테이블 또는 인덱스)
- Rows: 해당 단계 수행시 조회될 예상 데이터 건수
- Bytes: 해당 단계까지 사용될 예상 데이터양(누적)
- Cost: 해당 단계까지 사용될 예상 비용(누적)
-
Time: 해당 단계까지 사용될 예상 시간(누적)
TABLE ACCESS BY INDEX ROWID: 인덱스에 저장된 데이터의 주소(ROWID)를 이용해 실제 데이터를 찾는 과정
실행계획 (단계별)
EXPLAIN PLAN FOR
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';
Plan hash value: 3240201901
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 170 | 20740 | 11 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 170 | 20740 | 11 (10)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| M_CUS | 60 | 4680 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_ORD | 252 | 11088 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------
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'))
- 부모는 여러 자식을 가질 수 있다 (오퍼레이션 1 : 오퍼레이션2, 3의 부모)
- 자식이 부모보다 먼저 수행된다.
- 같은 들여쓰기 수준인 형제는 위쪽 오퍼레이션(형)이 먼저 실행된다.
실제 실행계획
- 실제 실행계획에는 오퍼레이션별로 실제 소모한 실행과 실제 수행한 IO횟수 등이 나온다.
- 성능 개선 전과 후를 정확히 비교하려면 실제 실행계획을 이용해야 한다.
실행계획 보는 방법
GATHER_PLAN_STATISTICS힌트를 사용하면 자세한 실행 정보가 오라클 내에 모두 저장된다.- 실행계획을 확인하는 방법:
DBMS_XPLAN.DISPLAY_CURSOR를 이용 -> 실제 실행계획을 만든 SQL의 SQL_ID찾아내어서 확인- 이용하려면 아래의 VIEW들에 SELECT 권한 필요
- V_$SQL
- V_$SQL_PLAN_STATISTICS_ALL
- V_$SQL_PLAN
- V_$SESSION
- 이용하려면 아래의 VIEW들에 SELECT 권한 필요
- 힌트를 사용하기 위해서는 주석의 시작을 타내는
/*바로 뒤에+표시 추가
-- 실제 실행계획 만들기
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';
-- 실제 실행계획을 만든 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;
실행 결과
Plan hash value: 3240201901
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 165 |00:00:00.01 | 31 | 27 | | | |
|* 1 | HASH JOIN | | 1 | 170 | 165 |00:00:00.01 | 31 | 27 | 779K| 779K| 1235K (0)|
|* 2 | TABLE ACCESS FULL| M_CUS | 1 | 60 | 60 |00:00:00.01 | 6 | 6 | | | |
|* 3 | TABLE ACCESS FULL| T_ORD | 1 | 252 | 243 |00:00:00.01 | 25 | 21 | | | |
--------------------------------------------------------------------------------------------------------------------------
- Starts: 해당 단계를 수행한 횟수
- E-Rows: 해당 단계의 예상 데이터 건수
- A-Rows: 해당 단계의 실제 데이터 건수
- A-Time: 해당 단계까지 수행된 실제 시간(누적) - 복잡한 실행계획이나 병렬쿼리에서는 부정확하게 나올 수도 있음
- Buffers: 해당 단계까지 메모리 버퍼에서 읽은 블록 수(논리적 IO횟수, 누적으로)
- Reads: 해당 단계가지 디스크에서 읽은 블록 수(물리적 IO횟수, 누적으로)
- OMemm, 1Mem, Used-Mem : SQL처리를 위해 사용한 메모리 수치
성능 개선 중요한 수치
아래 항목들이 눈에 띄게 수치가 높아진 단꼐가 있으면 해당 부분의 원인을 찾아 성능 개선을 하면 된다.
- A-Rows
- A-Time
- Buffers
주의 사항
- 운영에서 서비스될 SQL에는
GATHER_PLAN_STATISTICS힌트가 포함되지 않아야 한다. - 불필요한 수치 수집으로 성능상의 손해를 볼 수 있다.
reference
SQL BOOSTER
