Optimizing Oracle Optimizer (2009년)
DISPLAY_CURSOR
- DISPLAY_CURSOR displays the execution plans for one or several cursors
- in the shared SQL area
- 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
- 가장 기본적인 포맷이다
SQL>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정보가 나타난다.
SQL>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포멧이다. 파티션, 패러럴정보가 표시된다.
SQL>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이 표시된다.
SQL>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정보와 함께 아우트라인정보를 확인할 수 있다.
SQL>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포멧 형식이다.
SQL>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정보가 출력된다.
SQL>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정보가 출력된다.
SQL>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정보가 출력된다.
SQL>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변수값을 추출할 수 있다.
SQL>var ag_bind varchar2(5);
SQL>exec :ag_bind := 'Many2';
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.22
SQL>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
SQL>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하면 안된다는 뜻??)
SQL>alter session set "_optim_peek_user_binds" = false;
세션이 변경되었습니다.
경 과: 00:00:00.07
SQL>var ag_bind varchar2(5);
SQL>exec :ag_bind := 'Many2';
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.00
SQL>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
SQL>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
SQL>
바인드변수의 확인
- Trace10053, v$sql_bind_capture정보를 통해서도 확인할 수 있다.
v$sql_bind_capture저장량을 조절하는 방법
- _cursor_bind_capture_area_size : 일정량의 bind변수 저장공간을 조절하여 보다 많은 변수를 저장할 수 있다.
- _cursor_bind_capture_interval : 값을 줄여 Bind Capture의 주기를 좀더 짧은 간격으로 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
- the Automatic Workload Repository (AWR)
- DISPLAY_AWR는 Automatic Workload Repository (AWR)에 저장된 SQL문장의 실행계획을 표시한다.
- DISPLAY_AWR Function를 사용하기 위하여 다음 객체에 대한 SELECT privileges이 필요하다
- DBA_HIST_SQL_PLAN
- DBA_HIST_SQLTEXT
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
- in a SQL tuning set
- DISPLAY_SQLSET은 SQL tuning set에 저장되어 있는 SQL문장에 대한 실행계획을 표시한다.
- DISPLAY_AWR Function를 사용하기 위하여 다음 객체에 대한 SELECT privileges이 필요하다
- ALL_SQLSET_PLANS
- ALL_SQLSET_STATEMENTS
- dbms_sqltune 패키지에 대한 설정이 되어야함
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년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3852
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.