View Source

h1. 04.DBMS_XPLAN 패키지
* 사용가능 버전 : oracle 9.2
* 10g부터 라이브러리 캐시에 캐싱된 SQL 커서에 대한 실행계획, Row Source별 수행통계까지도 출력 가능

h3. (1)예상 실행계획 출력
{code:title=정의}
DBMS_XPLAN.DISPLAY(table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,--생략시 최근 수행 statement_id
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);
{code}
{info:title=Format 옵션}
||옵션||표시정보||
|BASIC|* Id, Operation, Name|
|TYPICAL|* Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time
* Predicate Information
|ALL|* Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time
* Query Block Name / Object Alias
* Predicate Information
* Column Projection Information|
|OUTLINE|* Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time
* Outline Data
* Predicate Information|
|ADVANCED|* Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time
* Query Block Name / Object Alias
* Outline Data
* Predicate Information
* Column Projection Information|
|SERIAL|* TYPICAL과 동일하나 병렬 수행 정보(parallel) 정보를 표시하지 않음|

* Query Block Name - 쿼리 블록 명칭을 보여줌
* Predicate Information - 데이터 접근 방법을 보여줌
* Outline Data - 실제 옵티마이저가 활용하는 힌트로써 이 정보를 이용하면 기존 힌트로 구현하기 어려운 튜닝을 가능하게 해줌
* Column Projection Information - 각 Operation 단계에서 어떤 Column을 추출하는지에 대한 정보

* 추가 가능한 세부 옵션들 :
** ROWS
** BYTES
** COST
** PARTITION
** PARALLEL(TQ, IN-OUT, PQ Distrib 정보)
** PREDICATE
** PROJECTION
** ALIAS(Query Block Name)
** REMOTE
** NOTE
{info}
{code:title=사용예}
SQL> explain plan for
2 select count(1) from test_table2 where col1 like '2016%';

해석되었습니다.

SQL> select plan_table_output
2 from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

Plan hash value: 3110898069

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 17 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 10389 | 182K| 17 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

2 - filter("COL1" LIKE '2016%')

14 개의 행이 선택되었습니다.

SQL> select plan_table_output
2 from table(dbms_xplan.display('plan_table',null));

결과동일

SQL> select plan_table_output
2 from table(dbms_xplan.display('plan_table',null,'typical'));

결과 동일

SQL> select plan_table_output
2 from table(dbms_xplan.display('plan_table',null,'basic rows bytes cost predicate'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3110898069

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 17 (0)|
| 1 | SORT AGGREGATE | | 1 | 18 | |
|* 2 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 10389 | 182K| 17 (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

2 - filter("COL1" LIKE '2016%')

14 개의 행이 선택되었습니다.

SQL> select plan_table_output
2 from table(dbms_xplan.display('plan_table',null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

Plan hash value: 3110898069

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 17 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 10389 | 182K| 17 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

1 - SEL$1
2 - SEL$1 / TEST_TABLE2@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
INDEX_FFS(@"SEL$1" "TEST_TABLE2"@"SEL$1" ("TEST_TABLE2"."COL1"
"TEST_TABLE2"."COL2"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("COL1" LIKE '2016%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

40 개의 행이 선택되었습니다.
{code}
{code:title=filter_preds}
filter_preds - plan_table에서 출력되어야할 row를 지정할 수 있다.

SQL> select plan_id from plan_table where statement_id = 'query';

PLAN_ID
----------
2836
2836
2836
3341
3341
3341

6 개의 행이 선택되었습니다.

SQL> select plan_table_output
2 from table(dbms_xplan.display(null,'query','BASIC','plan_id = 2836'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2346248975

------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | MMORDRCT |
| 2 | INDEX UNIQUE SCAN| MMORDRCT_PK |
------------------------------------------

9 개의 행이 선택되었습니다.

SQL> select plan_table_output
2 from table(dbms_xplan.display(null,'query','BASIC','plan_id = 3341'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 647164865

--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN| MMMEDORT_IDX15 |
--------------------------------------------

9 개의 행이 선택되었습니다.

SQL> select plan_table_output
2 from table(dbms_xplan.display(null,'query ','BASIC','depth < 2'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 647164865

---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
---------------------------------

8 개의 행이 선택되었습니다.
{code}

h3. (2)캐싱된 커서의 실제 실행계획 출력
* 커서 : 하드파싱 과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area
* 오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql, v$sql_plan, v$sql_plan_statistics, v$sql_plan_statistics_all 뷰를 제공
* v$sql_plan 뷰를 통해 원하는 형식으로 포맷팅할 수 있지만 다음 패키지를 사용하면 편하게 포맷팅된 실행계획을 볼 수 있다.

{code:title=정의}
DBMS_XPLAN.DISPLAY_CURSOR(sql_id IN VARCHAR2 DEFAULT NULL,--생략시 최근 sql_id
child_number IN NUMBER DEFAULT NULL,--생략시 최근 sql_id의 child_number
format IN VARCHAR2 DEFAULT 'TYPICAL');
{code}
{info:title=Format 옵션}
dbms_xplan.display 함수에 사용했던 포맷을 그대로 사용할 수 있으며 다음 세부 format 옵션이 추가된다.
||옵션||설명||
|IOSTATS|IO관련 분석정보를 보여준다|
|MEMSTATS|메모리관련 분석정보를 보여준다(해시조인,소트,비트맵오퍼레이션등)|
|ALLSTATS|IOSTATS + MEMSTATS|
|LAST|마지막으로 수행된 분석정보만 보여준다|
{info}
{code:title=사용예}
SQL> select count(1)
2 from test_table1
3 where exists(select 1 from test_table2 where col1 = test_table1.col1);

COUNT(1)
----------
294

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

SQL_ID a8fs5xmvhz2hb, child number 0
-------------------------------------
select count(1) from test_table1 where exists(select 1 from
test_table2 where col1 = test_table1.col1)

Plan hash value: 4055452834

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 19 (100)| |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| 2 | NESTED LOOPS | | 302 | 10872 | 19 (11)| 00:00:01 |
| 3 | SORT UNIQUE | | 11985 | 210K| 17 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 11985 | 210K| 17 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | TEST_TABLE1_PK | 1 | 18 | 0 (0)| |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("COL1"="TEST_TABLE1"."COL1")

23 개의 행이 선택되었습니다.

SQL> select count(1)
2 from test_table1
3 where exists(select 1 from test_table2 where col1 = test_table1.col1);

COUNT(1)
----------
294

SQL> column prev_sql_id new_value sql_id
SQL> column prev_child_number new_value child_no
SQL> select prev_sql_id, prev_child_number
2 from v$session
3 where sid=userenv('sid')
4 and username is not null
5 and prev_hash_value <> 0;

PREV_SQL_ID PREV_CHILD_NUMBER
-------------------------- -----------------
avmgkkjah7pbm 0

SQL> select * from table(dbms_xplan.display_cursor('avmgkkjah7pbm',0,'iostats'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------

SQL_ID avmgkkjah7pbm, child number 0
-------------------------------------
select count(1) from test_table1 where exists(select 1 from
test_table2 where col1 = test_table1.col1)

Plan hash value: 4055452834

-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | 302 |
| 3 | SORT UNIQUE | | 11985 |
| 4 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 11985 |
|* 5 | INDEX UNIQUE SCAN | TEST_TABLE1_PK | 1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("COL1"="TEST_TABLE1"."COL1")

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level


29 개의 행이 선택되었습니다.

SQL> select * from table(dbms_xplan.display_cursor('avmgkkjah7pbm',0,'memstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

SQL_ID avmgkkjah7pbm, child number 0
-------------------------------------
select count(1) from test_table1 where exists(select 1 from
test_table2 where col1 = test_table1.col1)

Plan hash value: 4055452834

--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | O/1/M |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | NESTED LOOPS | | 302 | | | |
| 3 | SORT UNIQUE | | 11985 | 22528 | 22528 | 1/0/0|
| 4 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 11985 | | | |
|* 5 | INDEX UNIQUE SCAN | TEST_TABLE1_PK | 1 | | | |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("COL1"="TEST_TABLE1"."COL1")

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level


29 개의 행이 선택되었습니다.

SQL> select * from table(dbms_xplan.display_cursor('avmgkkjah7pbm',0,'advanced allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------

SQL_ID avmgkkjah7pbm, child number 0
-------------------------------------
select count(1) from test_table1 where exists(select 1 from
test_table2 where col1 = test_table1.col1)

Plan hash value: 4055452834

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 19 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 36 | | | | | |
| 2 | NESTED LOOPS | | 302 | 10872 | 19 (11)| 00:00:01 | | | |
| 3 | SORT UNIQUE | | 11985 | 210K| 17 (0)| 00:00:01 | 22528 | 22528 |20480 (0)|
| 4 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 11985 | 210K| 17 (0)| 00:00:01 | | | |
|* 5 | INDEX UNIQUE SCAN | TEST_TABLE1_PK | 1 | 18 | 0 (0)| | | | |
----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5DA710D3
4 - SEL$5DA710D3 / TEST_TABLE2@SEL$2
5 - SEL$5DA710D3 / TEST_TABLE1@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_FFS(@"SEL$5DA710D3" "TEST_TABLE2"@"SEL$2" ("TEST_TABLE2"."COL1" "TEST_TABLE2"."COL2"))
INDEX(@"SEL$5DA710D3" "TEST_TABLE1"@"SEL$1" ("TEST_TABLE1"."COL1"))
LEADING(@"SEL$5DA710D3" "TEST_TABLE2"@"SEL$2" "TEST_TABLE1"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "TEST_TABLE1"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("COL1"="TEST_TABLE1"."COL1")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]
3 - (#keys=1) "COL1"[VARCHAR2,18]
4 - "COL1"[VARCHAR2,18]

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level


63 개의 행이 선택되었습니다.
{code}

h3. (3)캐싱된 커서의 Row Source별 수행 통계 출력
* Row Source별 수행 통계 수집
** gather_plan_statistics 힌트 사용
** 시스템 또는 세션 레벨에서 statistics_level 파라미터를 all로 설정 - ALTER SESSION SET STATISTICS_LEVEL = ALL;
** _rowsource_execution_statistics 파라미터를 true로 설정 - ALTER SESSION SET "_ROWSOURCE_EXECUTION_STATISTICS" = TRUE;
** SQL Trace 걸기

* 해당 통계는 v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를 이용하면 된다.
{code:title=사용예}

SQL> select /*+gather_plan_statistics*/ count(1)
2 from test_table1
3 where exists(select 1 from test_table2 where col1 = test_table1.col1);

COUNT(1)
----------
294

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 0zf6jkp8n4fjv, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(1) from test_table1
where exists(select 1 from test_table2 where col1 =
test_table1.col1)

Plan hash value: 4055452834

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 19 (100)| | 1 |00:00:00.01 | 161 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 36 | | | 1 |00:00:00.01 | 161 | | | |
| 2 | NESTED LOOPS | | 1 | 302 | 10872 | 19 (11)| 00:00:01 | 294 |00:00:00.01 | 161 | | | |
| 3 | SORT UNIQUE | | 1 | 11985 | 210K| 17 (0)| 00:00:01 | 302 |00:00:00.01 | 64 | 2528 | 22528 |20480 (0)|
| 4 | INDEX FAST FULL SCAN| TEST_TABLE2_PK | 1 | 11985 | 210K| 17 (0)| 00:00:01 | 11985 |00:00:00.01 | 64 | | | |
|* 5 | INDEX UNIQUE SCAN | TEST_TABLE1_PK | 302 | 1 | 18 | 0 (0)| | 294 |00:00:00.01 | 97 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5DA710D3
4 - SEL$5DA710D3 / TEST_TABLE2@SEL$2
5 - SEL$5DA710D3 / TEST_TABLE1@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_FFS(@"SEL$5DA710D3" "TEST_TABLE2"@"SEL$2" ("TEST_TABLE2"."COL1" "TEST_TABLE2"."COL2"))
INDEX(@"SEL$5DA710D3" "TEST_TABLE1"@"SEL$1" ("TEST_TABLE1"."COL1"))
LEADING(@"SEL$5DA710D3" "TEST_TABLE2"@"SEL$2" "TEST_TABLE1"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "TEST_TABLE1"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("COL1"="TEST_TABLE1"."COL1")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]
3 - (#keys=1) "COL1"[VARCHAR2,18]
4 - "COL1"[VARCHAR2,18]


58 개의 행이 선택되었습니다.

{code}
{info:title=Row Source 분석 항목}
||항목||설명||
|Id|Operation ID|
|Operation|로우 단위의 액세스 정보|
|Name|액세스하는 오브젝트의 정보|
|Starts|각 Operation을 수행한 횟수|
|E-Rows|예측 Row수|
|E-Bytes|예측 byte|
|Cost (%CPU)|Cost|
|E-Time|예측 수행시간|
|A-Rows|실측 Row수 |
|A-Time|실측 수행시간|
|Buffers|실측 Logical Block|
|OMem|Optimal Execution에 필요한 메모리(예측) - 메모리에서만 작업 가능한 메모리 크기|
|1Mem|One-Pass Execution에 필요한 메모리(예측) - 디스크를 1회라도 했을 때 필요한 메모리 크기|
|Used-Mem |마지막 실행시 사용한 메모리|
{info}
{tip:title=Query Block Name 활용}
Query Block Name 을 지정하여 실행계획 수정에 사용할 수 있다.
{code}
억지이긴 하지만 테스트해본다.
SQL> select /*+no_unnest(@sonexists)*/ count(1) from test_table1 a
2 where exists(select /*+ qb_name(sonexists) */ 1 from test_table2 b
3 where col1 = a.col1);

COUNT(1)
----------
294

SQL> select * from table(dbms_xplan.display_cursor(null,0,'all last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
SQL_ID 8fuyum6qfw3h8, child number 0
-------------------------------------
select /*+no_unnest(@sonexists)*/ count(1) from test_table1 a
where exists(select /*+ qb_name(sonexists) */ 1 from test_table2
b where col1 = a.col1)

Plan hash value: 767868621

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 76648 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | FILTER | | | | | |
| 3 | INDEX FAST FULL SCAN| TEST_TABLE1_PK | 41722 | 733K| 57 (2)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST_TABLE2_PK | 2 | 36 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
3 - SEL$1 / A@SEL$1
4 - SONEXISTS / B@SONEXISTS

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( IS NOT NULL)
4 - access("COL1"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]
3 - "A"."COL1"[VARCHAR2,18]
4 - "COL1"[VARCHAR2,18]


38 개의 행이 선택되었습니다.
{code}
{tip}