DBMS_XPLAN Format (by treeone) [2009.10.08]
DBMS_XPLAN Format
Table of Contents
DBMS_XPLAN Format
BASIC Format
실행계획의 단계별 Operation과 Object 이름만을 보여주는 말 그대로 매우 기본적인 Format이다.
- sample
- 설명
| 구분 | 항목 | 설명 |
|---|---|---|
| PLAN 정보 | ID | 각 Operation의 ID. *가 있는 경우 추가 정보란에 해당 ID의 정보를 보여줌 |
| PLAN 정보 | Operation | 로우 단위의 액세스 정보를 보여줌 |
| PLAN 정보 | Name | 액세스 하는 Object의 정보를 보여줌 |
TYPICAL Format
가장 일반적인 용도로 고안되었다. 단계별 Operation 과 Object명, 예측 Row수와 Result Set의 크기(Byte), Cost와 예측 실행시간등의 정보를 보여준다. 무엇보다 Predicate 정보를 보여준다는 사실이 중요하다.
- sample
- 설명
| 구분 | 항목 | 설명 |
|---|---|---|
| PLAN 정보 | ID | 각 Operation의 ID. *가 있는 경우 추가 정보란에 해당 ID의 정보를 보여줌 |
| PLAN 정보 | Operation | 로우 단위의 액세스 정보를 보여줌 |
| PLAN 정보 | Name | 액세스 하는 Object의 정보를 보여줌 |
| PLAN 정보 | Rows | 각 Operation에서 예상하는 Rows |
| PLAN 정보 | Bytes | 각 Operation에서 예상하는 Byte |
| PLAN 정보 | Cost(% CPU) | 각 Oepration에서 예상하는 Cost(괄호안의 내용은 CPU Cost의 예상치) |
| PLAN 정보 | Time | 각 Operation에서 예상하는 수행시간 |
| 추가 정보 | {color:orange}*Predicate* Information{color} | {color:orange}*SQL에서 WHERE절에 사용되는 조건들이 각 Operation에 적용되는 정보를 보여줌* SQL 튜닝 시 가장 필요한 정보이며 가장 깅력한 정보임 개발자들이 반드시 알아야 하고 이해하고 있어야 하는 정보 10046 Trace에서도 보여주지 못하는 정보임{color} |
ALL Format
ALL Format은 실행 계획을 분석하는데 있어서 없어서는 안될 중요한 두가지 정보를 추가적으로 제공해 준다.
- Query Block 정보를 제공한다.
Oracle은 우리가 수행 요청한 SQL문장을 Query Block 이라는 단위로 나눈다. Query Block은 Transformation 및 Optimization의 기본 단위가 된다.
Query Block명은 Inline View와 Subquery가 많이 사용되는 복잡한 Query를 해석할 때 특히 유용하다. - Column Projection 정보를 제공한다. Column Projection이란 실행 계획의 특정 단계에서 어떤 Column을 추출하는가를 의미한다.
Column Projection 정보는 또한 특별한 유형의 Query Transformation을 Troubleshooting 할 때 유용한 정보가 된다.
- sample
QB_NAME Hint를 사용하면 Query Block 명을 직접 조작할 수 있다. 복잡한 Query의 실행 계획을 해석할 때 매우 유용한 기능이다.
아래예 예제를 보면 QB_NAME을 'x'로 변경하였으며 Query Block 명이 변경된 것을 확인할 수 있다.
SQL > explain plan for
2 select /*+ qb_name(x) */ *
3 from t1
4 where c1=1 and c2='dummy';
해석되었습니다.
경 과: 00:00:00.03
SQL >
SQL > select * from table(dbms_xplan.display('plan_table',null,'all'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1420382924
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - X / T1@X
2 - X / T1@X
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"=TO_NUMBER('dummy'))
2 - access("C1"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[NUMBER,22], "C2"[NUMBER,22]
2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
27 개의 행이 선택되었습니다.
경 과: 00:00:00.25
- 설명
| 구분 | 항목 | 설명 |
|---|---|---|
| PLAN 정보 | ID | 각 Operation의 ID. *가 있는 경우 추가 정보란에 해당 ID의 정보를 보여줌 |
| PLAN 정보 | Operation | 로우 단위의 액세스 정보를 보여줌 |
| PLAN 정보 | Name | 액세스 하는 Object의 정보를 보여줌 |
| PLAN 정보 | Rows | 각 Operation에서 예상하는 Rows |
| PLAN 정보 | Bytes | 각 Operation에서 예상하는 Byte |
| PLAN 정보 | Cost(% CPU) | 각 Oepration에서 예상하는 Cost(괄호안의 내용은 CPU Cost의 예상치) |
| PLAN 정보 | Time | 각 Operation에서 예상하는 수행시간 |
| 추가 정보 | Predicate Information | SQL에서 WHERE절에 사용되는 조건들이 각 Operation에 적용되는 정보를 보여줌 SQL 튜닝 시 가장 필요한 정보이며 가장 깅력한 정보임 개발자들이 반드시 알아야 하고 이해하고 있어야 하는 정보 10046 Trace에서도 보여주지 못하는 정보임 |
| 추가 정보 | Query Block | SQL에서 View, Inline View 등 각각의 집합을 Query Block으로 해석하고 그 이름을 부여함 |
| 추가 정보 | {color:orange}*Column* Projection Information{color} | {color:orange}*Predicate Information이 WHERE절에 사용되는 조건컬럼만 보여주는 것이라면* Column Projection Information은 해당 Operation에서 사용되는 모든 컬럼을 보여줌{color} |
OUTLINE Format
OUTLINE Format은 매우 재미있는 추가적인 정보를 제공한다. Outline은 실행 계획을 수립하는데 필요한 Hint들의 목록을 의미한다. 특정 실행계획을 재현하기 위해 어떤 Hint가 필요한지 확인하기 위한 용도로 사용 가능하다.
- sample
- 설명
| 구분 | 항목 | 설명 |
|---|---|---|
| PLAN 정보 | ID | 각 Operation의 ID. *가 있는 경우 추가 정보란에 해당 ID의 정보를 보여줌 |
| PLAN 정보 | Operation | 로우 단위의 액세스 정보를 보여줌 |
| PLAN 정보 | Name | 액세스 하는 Object의 정보를 보여줌 |
| PLAN 정보 | Rows | 각 Operation에서 예상하는 Rows |
| PLAN 정보 | Bytes | 각 Operation에서 예상하는 Byte |
| PLAN 정보 | Cost(% CPU) | 각 Oepration에서 예상하는 Cost(괄호안의 내용은 CPU Cost의 예상치) |
| PLAN 정보 | Time | 각 Operation에서 예상하는 수행시간 |
| 추가 정보 | Predicate Information | SQL에서 WHERE절에 사용되는 조건들이 각 Operation에 적용되는 정보를 보여줌 SQL 튜닝 시 가장 필요한 정보이며 가장 깅력한 정보임 개발자들이 반드시 알아야 하고 이해하고 있어야 하는 정보 10046 Trace에서도 보여주지 못하는 정보임 |
| 추가 정보 | Outline Data | {color:orange}*SQL 튜닝시 가장 강력한 정보 제공* SQL을 수행할 때 옵티마이저가 참고하는 Hidden Hint SQL에 Hint를 따로 주지 않아도 부여를 하며 여기서 사용되는 대부분의 Hint들은 Query Block Name을 바탕으로 구성함 이 정보가 제공되기 전까지는 기존에 알려진 힌트로만 튜닝을 할 수 있었으나 이를 사용하게 되면서 기존에 튜닝이 불가능하였던 부분까지 가능해짐 Undocument Format이기 때문에 아직까지 활용범위는 적으나 점차 사용할 수 있는 범위가 확대되고 있음{color} |
ADVANCED Format
All Format + Outline Format
- sample
문서에 대하여
최초작성자 : 한남주
최초작성일 : 2009년 10월 08일
이문서의 내용은 아래 문서로 대부분(거의다 ㅋ) 작성하였습니다. 물론 강식이에게 허락받음^\^
- 강정식군 오라클클럽 세미나자료, 오라클클럽세미나_4.DMBS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝
- 조동욱저, Oracle Optionizing Oracle Optimizer 도요.. (이건 책내용... 슬쩍..)