- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/CORE/Plan+Statistics?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
Display_cursor
 | DISPLAY_CURSOR displays the execution plans for one or several cursors DISPLAY_CURSOR는 shared SQL area안에 있는 하나또는 몇몇 커서에 대한 실행 계획을 표시한다. |
Display_cursor Table Function 함수설명
function display_cursor(sql_id varchar2 default null,
cursor_child_no integer default 0,
format varchar2 default 'TYPICAL')
- sql_id:
해당 SQL의 SQL_ID를 지정한다.
(V$SQL.SQL_ID, V$SESSION.SQL_ID, V$SESSION.PREV_SQL_ID 참조)
SQL_ID를 명시하지 않으면 해당 세션의 마지막 실행문장을 의미한다.
- cursor_child_no:
해당 SQL CURSOR의 child number를 지정한다.
(V$SQL.CHILD_NUMBER, V$SESSION.SQL_CHILD_NUMBER, V$SESSION.PREV_CHILD_NUMBER 참조)
SQL_ID의 설정에 따라 고려되어야 한다.
- format:
실행된 Plan을 어떻게 보여줄지 결정한다.
Display_cursor의 포멧
Basic
가장 기본적인 포맷이다
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'basic')); PLAN_TABLE_OUTPUT --------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 ------------------------------------------------- | Id | Operation | Name | ------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | PX COORDINATOR | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | | 3 | HASH JOIN SEMI | | | 4 | BUFFER SORT | | | 5 | PX RECEIVE | | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | | 7 | TABLE ACCESS FULL | T_PLAN | | 8 | PX PARTITION RANGE ALL | | | 9 | TABLE ACCESS FULL | T_PLAN2 | ------------------------------------------------- 24 개의 행이 선택되었습니다. 경 과: 00:00:00.21
Serial
Cost정보와 예측정보과 함께 파티션 정보가 표시된다.
가장 중요한 Predicate정보가 나타난다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'serial')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 29 | 7 (15)| 00:00:01 | | | |* 3 | HASH JOIN SEMI | | 1 | 29 | 7 (15)| 00:00:01 | | | | 4 | BUFFER SORT | | | | | | | | | 5 | PX RECEIVE | | 10000 | 90000 | 4 (0)| 00:00:01 | | | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 | 4 (0)| 00:00:01 | | | | 7 | TABLE ACCESS FULL | T_PLAN | 10000 | 90000 | 4 (0)| 00:00:01 | | | | 8 | PX PARTITION RANGE ALL | | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | | 9 | TABLE ACCESS FULL | T_PLAN2 | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Note ----- - dynamic sampling used for this statement 32 개의 행이 선택되었습니다. 경 과: 00:00:00.23
Typical
Default포멧이다. 파티션, 패러럴정보가 표시된다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'typical')); PLAN_TABLE_OUTPUT ---------------------------------------------------------- SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN SEMI | | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 5 | PX RECEIVE | | 10000 | 90000 | 4 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 | 4 (0)| 00:00:01 | | | | S->P | PART (KEY) | | 7 | TABLE ACCESS FULL | T_PLAN | 10000 | 90000 | 4 (0)| 00:00:01 | | | | | | | 8 | PX PARTITION RANGE ALL | | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWC | | | 9 | TABLE ACCESS FULL | T_PLAN2 | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Note ----- - dynamic sampling used for this statement 32 개의 행이 선택되었습니다. 경 과: 00:00:00.16
Cost
All
Typical정보와 함께 Query Block Name과 Column Projection이 표시된다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'all')); PLAN_TABLE_OUTPUT ------------------------------------------------- SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN SEMI | | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 5 | PX RECEIVE | | 10000 | 90000 | 4 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 | 4 (0)| 00:00:01 | | | | S->P | PART (KEY) | | 7 | TABLE ACCESS FULL | T_PLAN | 10000 | 90000 | 4 (0)| 00:00:01 | | | | | | | 8 | PX PARTITION RANGE ALL | | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWC | | | 9 | TABLE ACCESS FULL | T_PLAN2 | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$DBE3B336 7 - SEL$DBE3B336 / A@MAIN_QUERY 9 - SEL$DBE3B336 / B@SUB_QUERY Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 2 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 3 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 4 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 5 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 6 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] 9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] Note ----- - dynamic sampling used for this statement 52 개의 행이 선택되었습니다. 경 과: 00:00:00.24
Outline
Typical정보와 함께 아우트라인정보를 확인할 수 있다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'outline')); PLAN_TABLE_OUTPUT ------------------------------------------------------- SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN SEMI | | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 5 | PX RECEIVE | | 10000 | 90000 | 4 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 | 4 (0)| 00:00:01 | | | | S->P | PART (KEY) | | 7 | TABLE ACCESS FULL | T_PLAN | 10000 | 90000 | 4 (0)| 00:00:01 | | | | | | | 8 | PX PARTITION RANGE ALL | | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWC | | | 9 | TABLE ACCESS FULL | T_PLAN2 | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.3') OUTLINE_LEAF(@"SEL$DBE3B336") UNNEST(@"SUB_QUERY") OUTLINE(@"MAIN_QUERY") OUTLINE(@"SUB_QUERY") FULL(@"SEL$DBE3B336" "A"@"MAIN_QUERY") FULL(@"SEL$DBE3B336" "B"@"SUB_QUERY") LEADING(@"SEL$DBE3B336" "A"@"MAIN_QUERY" "B"@"SUB_QUERY") USE_HASH(@"SEL$DBE3B336" "B"@"SUB_QUERY") PQ_DISTRIBUTE(@"SEL$DBE3B336" "B"@"SUB_QUERY"PARTITION NONE) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Note ----- - dynamic sampling used for this statement 51 개의 행이 선택되었습니다. 경 과: 00:00:00.29
Advanced
All포멧 + Outline포멧 형식이다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------- SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN SEMI | | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 5 | PX RECEIVE | | 10000 | 90000 | 4 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 | 4 (0)| 00:00:01 | | | | S->P | PART (KEY) | | 7 | TABLE ACCESS FULL | T_PLAN | 10000 | 90000 | 4 (0)| 00:00:01 | | | | | | | 8 | PX PARTITION RANGE ALL | | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWC | | | 9 | TABLE ACCESS FULL | T_PLAN2 | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$DBE3B336 7 - SEL$DBE3B336 / A@MAIN_QUERY 9 - SEL$DBE3B336 / B@SUB_QUERY Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.3') OUTLINE_LEAF(@"SEL$DBE3B336") UNNEST(@"SUB_QUERY") OUTLINE(@"MAIN_QUERY") OUTLINE(@"SUB_QUERY") FULL(@"SEL$DBE3B336" "A"@"MAIN_QUERY") FULL(@"SEL$DBE3B336" "B"@"SUB_QUERY") LEADING(@"SEL$DBE3B336" "A"@"MAIN_QUERY" "B"@"SUB_QUERY") USE_HASH(@"SEL$DBE3B336" "B"@"SUB_QUERY") PQ_DISTRIBUTE(@"SEL$DBE3B336" "B"@"SUB_QUERY"PARTITION NONE) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 2 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 3 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 4 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 5 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 6 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] 9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] Note ----- - dynamic sampling used for this statement 71 개의 행이 선택되었습니다. 경 과: 00:00:00.24
Iostats
io와 관련된 buffer, pysical read pysical write정보가 표시된다. Plan Statistics정보가 출력된다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'iostats')); PLAN_TABLE_OUTPUT ----------------------------------------------------- SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------- | 1 | PX COORDINATOR | | 1 | | 9999 |00:00:02.08 | 55 | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 1 | 0 |00:00:00.01 | 0 | |* 3 | HASH JOIN SEMI | | 2 | 1 | 9999 |00:00:06.28 | 42 | | 4 | BUFFER SORT | | 3 | | 10000 |00:00:06.11 | 0 | | 5 | PX RECEIVE | | 3 | 10000 | 10000 |00:00:06.05 | 0 | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 0 | 10000 | 0 |00:00:00.01 | 0 | | 7 | TABLE ACCESS FULL | T_PLAN | 1 | 10000 | 10000 |00:00:00.05 | 46 | | 8 | PX PARTITION RANGE ALL | | 3 | 9999 | 9999 |00:00:00.09 | 42 | | 9 | TABLE ACCESS FULL | T_PLAN2 | 2 | 9999 | 9999 |00:00:00.03 | 42 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Note ----- - dynamic sampling used for this statement 31 개의 행이 선택되었습니다. 경 과: 00:00:00.30
Memstats
Sort 및 Hast 영역을 사용할 때의 메모리정보를 표시한다. Plan Statistics정보가 출력된다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'memstats')); PLAN_TABLE_OUTPUT ---------------------------------------------------- SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | O/1/M | -------------------------------------------------------------------------------------------------------------------- | 1 | PX COORDINATOR | | 1 | | 9999 |00:00:02.08 | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 1 | 0 |00:00:00.01 | | | | |* 3 | HASH JOIN SEMI | | 2 | 1 | 9999 |00:00:06.28 | 921K| 921K| 3/0/0| | 4 | BUFFER SORT | | 3 | | 10000 |00:00:06.11 | 93184 | 93184 | 3/0/0| | 5 | PX RECEIVE | | 3 | 10000 | 10000 |00:00:06.05 | | | | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 0 | 10000 | 0 |00:00:00.01 | | | | | 7 | TABLE ACCESS FULL | T_PLAN | 1 | 10000 | 10000 |00:00:00.05 | | | | | 8 | PX PARTITION RANGE ALL | | 3 | 9999 | 9999 |00:00:00.09 | | | | | 9 | TABLE ACCESS FULL | T_PLAN2 | 2 | 9999 | 9999 |00:00:00.03 | | | | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Note ----- - dynamic sampling used for this statement 31 개의 행이 선택되었습니다. 경 과: 00:00:00.18
Allstats
Hash Value가 같은 SQL의 누적통계를 표시한다. Plan Statistics정보가 출력된다.
Allstats Last
해당 SQL이 마지막 실행된 통계정보를 표시한다. Plan Statistics정보가 출력된다.
WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'advanced allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------ SQL_ID 6mq5vfpcam4jm, child number 0 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b) Plan hash value: 641105310 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | A-Rows | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | PX COORDINATOR | | 1 | | | | | | | | | | 9999 | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | 0 | |* 3 | HASH JOIN SEMI | | 0 | 1 | 29 | 7 (15)| 00:00:01 | | | Q1,01 | PCWP | | 0 | | 4 | BUFFER SORT | | 0 | | | | | | | Q1,01 | PCWC | | 0 | | 5 | PX RECEIVE | | 0 | 10000 | 90000 | 4 (0)| 00:00:01 | | | Q1,01 | PCWP | | 0 | | 6 | PX SEND PARTITION (KEY)| :TQ10000 | 0 | 10000 | 90000 | 4 (0)| 00:00:01 | | | | S->P | PART (KEY) | 0 | | 7 | TABLE ACCESS FULL | T_PLAN | 1 | 10000 | 90000 | 4 (0)| 00:00:01 | | | | | | 10000 | | 8 | PX PARTITION RANGE ALL | | 0 | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWC | | 0 | | 9 | TABLE ACCESS FULL | T_PLAN2 | 0 | 9999 | 195K| 2 (0)| 00:00:01 | 1 | 3 | Q1,01 | PCWP | | 0 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------- 00:00:02.08 | 55 | | | | 00:00:00.01 | 0 | | | | 00:00:00.01 | 0 | 921K| 921K| 1246K (0)| 00:00:00.01 | 0 | 93184 | 93184 | 104K (0)| 00:00:00.01 | 0 | | | | 00:00:00.01 | 0 | | | | 00:00:00.05 | 46 | | | | 00:00:00.01 | 0 | | | | 00:00:00.01 | 0 | | | | -------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$DBE3B336 7 - SEL$DBE3B336 / A@MAIN_QUERY 9 - SEL$DBE3B336 / B@SUB_QUERY Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.3') OUTLINE_LEAF(@"SEL$DBE3B336") UNNEST(@"SUB_QUERY") OUTLINE(@"MAIN_QUERY") OUTLINE(@"SUB_QUERY") FULL(@"SEL$DBE3B336" "A"@"MAIN_QUERY") FULL(@"SEL$DBE3B336" "B"@"SUB_QUERY") LEADING(@"SEL$DBE3B336" "A"@"MAIN_QUERY" "B"@"SUB_QUERY") USE_HASH(@"SEL$DBE3B336" "B"@"SUB_QUERY") PQ_DISTRIBUTE(@"SEL$DBE3B336" "B"@"SUB_QUERY"PARTITION NONE) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 2 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 3 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 4 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 5 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 6 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10] 7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] 9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] Note ----- - dynamic sampling used for this statement 69 개의 행이 선택되었습니다. 경 과: 00:00:00.29
+Peeked binds
해당 SQL에서 사용된 Bind변수값을 추출할 수 있다.
WOONG@ORCL>var ag_bind varchar2(5); WOONG@ORCL>exec :ag_bind := 'Many2'; PL/SQL 처리가 정상적으로 완료되었습니다. 경 과: 00:00:00.22 WOONG@ORCL>select /*+ gather_plan_statistics qb_name(main_query)*/ 2 a.col1, a.col2 3 from t_plan a 4 where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ 5 b.col1, b.col2 6 from t_plan2 b 7 where col1 = :ag_bind 8 and col2 <= 100); 선택된 레코드가 없습니다. 경 과: 00:00:02.13 WOONG@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all allstats last +peeked_binds')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- SQL_ID cjtzqad54bcmy, child number 2 ------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ b.col1, b.col2 from t_plan2 b where col1 = :ag_bind and col2 <= 100) Plan hash value: 1032089627 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- | 1 | PX COORDINATOR | | 1 | | | | | | | | | | 0 |00:00:02.12 | 32 | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 1 | 18 | 5 (20)| 00:00:01 | | | Q1,02 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | | |* 3 | HASH JOIN SEMI BUFFERED | | 0 | 1 | 18 | 5 (20)| 00:00:01 | | | Q1,02 | PCWP | | 0 |00:00:00.01 | 0 | 1068K| 1068K| | | 4 | BUFFER SORT | | 0 | | | | | | | Q1,02 | PCWC | | 0 |00:00:00.01 | 0 | 73728 | 73728 | | | 5 | PX RECEIVE | | 0 | 1 | 9 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | | 0 |00:00:00.01 | 0 | | | | | 6 | PX SEND HASH | :TQ10000 | 0 | 1 | 9 | 2 (0)| 00:00:01 | | | | S->P | HASH | 0 |00:00:00.01 | 0 | | | | |* 7 | TABLE ACCESS BY INDEX ROWID| T_PLAN | 1 | 1 | 9 | 2 (0)| 00:00:01 | | | | | | 0 |00:00:00.01 | 29 | | | | |* 8 | INDEX RANGE SCAN | T_PLAN_IDX | 1 | 1 | | 1 (0)| 00:00:01 | | | | | | 0 |00:00:00.01 | 29 | | | | | 9 | PX RECEIVE | | 0 | 1 | 9 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | | 0 |00:00:00.01 | 0 | | | | | 10 | PX SEND HASH | :TQ10001 | 0 | 1 | 9 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH | 0 |00:00:00.01 | 0 | | | | | 11 | PX BLOCK ITERATOR | | 0 | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWC | | 0 |00:00:00.01 | 0 | | | | |* 12 | TABLE ACCESS FULL | T_PLAN2 | 0 | 1 | 9 | 2 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$DBE3B336 7 - SEL$DBE3B336 / A@MAIN_QUERY 8 - SEL$DBE3B336 / A@MAIN_QUERY 12 - SEL$DBE3B336 / B@SUB_QUERY Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=846): 'Many2' Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2") 7 - filter("A"."COL2"<=100) 8 - access("A"."COL1"=:AG_BIND) 12 - access(:Z>=:Z AND :Z<=:Z) filter(("COL1"=:AG_BIND AND "COL2"<=100)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 2 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 3 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 4 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 5 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 6 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 8 - "A".ROWID[ROWID,10], "A"."COL1"[VARCHAR2,10] 9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] 10 - (#keys=2) "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] 11 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22] 12 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22] 62 개의 행이 선택되었습니다. 경 과: 00:00:03.31 추가TEST : Peeked binds는 Bind Peeking을 사용하는 경우만 의미가 있다.(바인드피킹 enable = false하면 안된다는 뜻??) WOONG@ORCL>alter session set "_optim_peek_user_binds" = false; 세션이 변경되었습니다. 경 과: 00:00:00.07 WOONG@ORCL>var ag_bind varchar2(5); WOONG@ORCL>exec :ag_bind := 'Many2'; PL/SQL 처리가 정상적으로 완료되었습니다. 경 과: 00:00:00.00 WOONG@ORCL>select /*+ gather_plan_statistics qb_name(main_query)*/ 2 a.col1, a.col2 3 from t_plan a 4 where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */ 5 b.col1, b.col2 6 from t_plan2 b 7 where col1 = :ag_bind 8 and col2 <= 100); 선택된 레코드가 없습니다. 경 과: 00:00:02.42 WOONG@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all allstats last +peeked_binds')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- SQL_ID cjtzqad54bcmy, child number 19 -------------------------------------- select /*+ gather_plan_statistics qb_name(main_query)*/ a.col1, a.col2 from t_plan a where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_n b.col1, b.col2 from t_plan2 b where col1 = :ag_bind Plan hash value: 2253543560 ------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | A-Rows | ------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | PX COORDINATOR | | 1 | | | | | | | | | | 0 |0 | 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 1 | 18 | 9 (12)| 00:00:01 | | | Q1,02 | P->S | QC (RAND) | 0 |0 |* 3 | HASH JOIN SEMI BUFFERED| | 0 | 1 | 18 | 9 (12)| 00:00:01 | | | Q1,02 | PCWP | | 0 |0 | 4 | BUFFER SORT | | 0 | | | | | | | Q1,02 | PCWC | | 0 |0 | 5 | PX RECEIVE | | 0 | 100 | 900 | 6 (0)| 00:00:01 | | | Q1,02 | PCWP | | 0 |0 | 6 | PX SEND HASH | :TQ10000 | 0 | 100 | 900 | 6 (0)| 00:00:01 | | | | S->P | HASH | 0 |0 |* 7 | TABLE ACCESS FULL | T_PLAN | 1 | 100 | 900 | 6 (0)| 00:00:01 | | | | | | 0 |0 | 8 | PX RECEIVE | | 0 | 100 | 900 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | | 0 |0 | 9 | PX SEND HASH | :TQ10001 | 0 | 100 | 900 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH | 0 |0 | 10 | PX BLOCK ITERATOR | | 0 | 100 | 900 | 2 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWC | | 0 |0 |* 11 | TABLE ACCESS FULL | T_PLAN2 | 0 | 100 | 900 | 2 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWP | | 0 |0 ------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$DBE3B336 7 - SEL$DBE3B336 / A@MAIN_QUERY 11 - SEL$DBE3B336 / B@SUB_QUERY Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2") 7 - filter(("A"."COL2"<=100 AND "A"."COL1"=:AG_BIND)) 11 - access(:Z>=:Z AND :Z<=:Z) filter(("COL2"<=100 AND "COL1"=:AG_BIND)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 2 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 3 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 4 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 5 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 6 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22] 8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] 9 - (#keys=2) "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22] 10 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22] 11 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22] 53 개의 행이 선택되었습니다. 경 과: 00:00:00.45 WOONG@ORCL>
 | 바인드변수의 확인 Trace10053, v$sql_bind_capture정보를 통해서도 확인할 수 있다.
|
예제 및 응용
--특정 SID를 가진 세션의 직전 Query의 실행계획을 추출 select t.* from v$session s, table(dbms_xplan.display_cursor(s.prev_sql_id, s.prev_child_number)) t where s.sid=9; --SH유저가 파싱한 sql문장 중 'sAleS'문자열이 있는 내용있는 Query의 실행계획을 추출 select t.* from v$sql s, dba_users u, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'RUNSTATS_TOT')) t where s.sql_text like '%sAleS%' and u.user_id=s.parsing_user_id and u.username='SH'; --Shared Pool(v$sql)에 Cache되어 있는 Query들 중 buffer_gets(logical reads) 수치가 높은 순으로 Runtime 실행 계획을 추출한다. select plan_table_output from (select * from (select s.sql_id, s.child_number from v$sql s where exists(select 1 from v$sql_plan p where p.plan_hash_value = s.plan_hash_value) order by s.buffer_gets desc) where rownum <= 10 ) s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last'));
Display_awr
| DISPLAY_AWR displays the execution plans for SQL statements stored in DISPLAY_AWR는 Automatic Workload Repository (AWR)에 저장된 SQL문장의 실행계획을 표시한다. |
| DISPLAY_AWR Function를 사용하기 위하여 다음 객체에 대한 SELECT privileges이 필요하다
|
Display_awr Table Function 함수설명
function display_awr(sql_id varchar2,
plan_hash_value integer default null,
db_id integer default null,
format varchar2 default 'TYPICAL')
- sql_id:
AWR에 저장되어 있는 SQL_ID를 지정한다.
(DBA_HIST_SQL_PLAN.SQL_ID 참조)
- plan_hash_value:
저장된 SQL의 식별자인 hash_value를 지정한다.(Optional Parameter)
- db_id:
특정 database id를 지정한다.
생략 시 Default로 local database id가 지정된다.
- format:
Display Table Function과 같다.
DBMS_XPLAN.DISPLAY_AWR() 사용예
select t.\* from dba_hist_sqltext ht, table(dbms_xplan.display_awr(ht.sql_id, null, null, '-PREDICATE \+ALIAS')) t where ht.sql_text like '%sAleS%';
Display_sqlset
| DISPLAY_SQLSET displays the execution plans for SQL statements stored DISPLAY_SQLSET은 SQL tuning set에 저장되어 있는 SQL문장에 대한 실행계획을 표시한다. |
| DISPLAY_AWR Function를 사용하기 위하여 다음 객체에 대한 SELECT privileges이 필요하다
|
Display_awr Table Function 함수설명
function display_sqlset(sqlset_name varchar2,
sql_id varchar2,
plan_hash_value integer default null,
format varchar2 default 'TYPICAL',
sqlset_owner varchar2 default null)
- sqlset_name:
SQL tuning set에 지정된 이름
- sql_id:
SQL tuning set에 저장된 SQL_ID를 지정
- plan_hash_value:
저장된 SQL의 식별자인 hash_value를 지정한다.(Optional Parameter)
- format:
Display Table Function과 같다.
- sqlset_owner:
해당 SQL tuning set의 Owner를 지정한다.
Default는 현재 User이다
DBMS_XPLAN.DISPLAY_SQLSET사용예
select * from table(dbms_xplan.display_sqlset('my_sts', 'gcfysssf6hykh', null, 'ALL -NOTE -PROJECTION')) t
문서에 대하여
- 최초작성자 : 이지웅
- 최초작성일 : 2009년 2월 21일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/CORE/Plan+Statistics?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.