[Tuning] SQL Plan 해석

해석 방법

• 가장 안쪽 ➡ 밖
• 위 ➡ 아래
• Join Pair 구조 시 Join 순서에 의해 분석

테스트 쿼리

 

select STADIUM_ID,
       GUBUN,
       HOMETEAM_ID,
       AWAYTEAM_ID
  from (select *
          from SCHEDULE left join player on TEAM_ID=HOMETEAM_ID
         where STADIUM_ID=:1
       )
 where SCHE_DATE like '201205%'
 group by STADIUM_ID,
       GUBUN,
       HOMETEAM_ID,
       AWAYTEAM_ID;

 

해당쿼리 실행 계획

SQL_ID  19dhxb0hv46bn, child number 0
-------------------------------------
select STADIUM_ID,        GUBUN,        HOMETEAM_ID,        AWAYTEAM_ID 
  from (select *           from SCHEDULE left join player on 
TEAM_ID=HOMETEAM_ID          where STADIUM_ID=:1        )  where 
SCHE_DATE like '201205%'  group by STADIUM_ID,        GUBUN,        
HOMETEAM_ID,        AWAYTEAM_ID
 
Plan hash value: 3310106657
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |       |     6 (100)|          |      2 |00:00:00.01 |       9 |       |       |          |
|   1 |  HASH GROUP BY                        |             |      1 |    99 |     6  (17)| 00:00:01 |      2 |00:00:00.01 |       9 |  1422K|  1422K|  671K (0)|
|*  2 |   HASH JOIN OUTER                     |             |      1 |    99 |     5   (0)| 00:00:01 |     98 |00:00:00.01 |       9 |  1538K|  1538K|  477K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SCHEDULE    |      1 |    28 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | SCHEDULE_PK |      1 |       |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       1 |       |       |          |
|   5 |    TABLE ACCESS FULL                  | PLAYER      |      1 |  2400 |     3   (0)| 00:00:01 |    480 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
..(생략)..
 
Outline Data
-------------
..(생략)..
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TEAM_ID"="HOMETEAM_ID")
   4 - access("SCHEDULE"."STADIUM_ID"=:1 AND "SCHEDULE"."SCHE_DATE" LIKE '201205%')
       filter("SCHEDULE"."SCHE_DATE" LIKE '201205%')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------

..(생략)..
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

 

실행 계획 분석

 

1. 실행계획순서 확인

-------------------------------------------------------------
| Id  | Operation                             | Name        |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |
|   1 |  HASH GROUP BY                        |             |
|*  2 |   HASH JOIN OUTER                     |             |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SCHEDULE    |
|*  4 |     INDEX RANGE SCAN                  | SCHEDULE_PK |
|   5 |    TABLE ACCESS FULL                  | PLAYER      |
-------------------------------------------------------------

 

2. Predicate Information

• Execution Plan 지표에서 Predicate Information 탭은 컬럼에 대한 Access 정보/Join 정보/Filter 정보를 보여줌
• DBMS_XPLAN 패키지를 이용하여 실행계획 출력시 각 단계별 액세스 조건과 필터 조건을 상세히 분석 가능
• Access - 일반적으로 Index Scan 단계에서 표시
               - Index 스캔 범위를 결정
               - 실제 Block을 읽기 전 사용되는 조건
• Filter     - Index Scan, Table Access 단계에 표시
               - 처리 일양에는 영향이 없는 필터링 조건
               - 실제 block을 읽은 후 data를 걸러냄

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TEAM_ID"="HOMETEAM_ID")
   4 - access("SCHEDULE"."STADIUM_ID"=:1 AND "SCHEDULE"."SCHE_DATE" LIKE '201205%')
       filter("SCHEDULE"."SCHE_DATE" LIKE '201205%')