10. 실행 계획 (by csahn79) [2017.12.08]
실행 계획 얻기
- EXPLAIN PLAN 구문을 실행한 후 실행 계획이 기록된 PLAN 테이블을 조회한다.
- 동적 성능 뷰를 조회하여 라이브어리 캐시에 캐싱된 실행 게획을 얻는다.
- 실시간 모니터링을 사용하여 현재 실행 중이거나 방금 전에 완료된 SQL 구문에 대한 정보를 얻는다.
- AWR이나 Statpack 리포지터리 테이블에 저장된 실행 계획을 얻는다.
- SQL 트레잇 기능을 활성화하여 실행 계획을 얻는다.
h2.EXPLAIN PLAN 구문
 |
SQL> SELECT count(*) FROM user_objects;
COUNT(*)
----------
51131
SQL> EXPLAIN PLAN FOR SELECT count(*) FROM user_objects;
해석되었습니다.
SQL> EXPLAIN PLAN FOR SELECT count(*) FROM user_objects;
EXPLAIN PLAN FOR SELECT count(*) FROM user_objects *
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view
h3.plan 테이블
h4.plan 테이블에 질의하기
SQL> EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10 ORDER BY ename;
해석되었습니다.
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 261 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 261 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("DEPTNO"=10)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 행이 선택되었습니다.
h4.바인드 변수로 인한 함정
CREATE OR REPLACE PROCEDURE p (p_value IN NUMBER) IS
BEGIN
FOR i IN (SELECT * FROM emp WHERE empno = p_value)
LOOP
NULL; -- do something
END LOOP;
END;
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = 7788
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = :B1
리터럴과 바인드 변수는 동일하게 여기지 않는다. 올바르게 사용하기 위해서는 바인드 변수와 함께 사용한다.
그럼에도 불구하고 두 가지 문제점이 존재한다.
- 기본적으로 바인드 변수가 VARCHAR2로 선언된다.
- 바인드 변수 피킹이 사용되지 않는다. 즉 바인드 변수를 사용할 경우 EXPLAIN PLAN 구문에서 생성된 출력은 신뢰할 수 없다.
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("EMPNO"=TO_NUMBER(:B1))
13 행이 선택되었습니다.
h2.동적 성능 뷰
4개의 성능 뷰를 통해 라이브러리 캐시의 커서에 대한 정보를 살펴볼 수 있다.
- v$sql_plan은 기본적으로 plan 테이블과 동일한 정보를 제공한다.
- v$sql_plan_statistics는 v$sql_plan 뷰의 각 오퍼레이션에 대한 경과시간 및 생성된 로우의 개수 등의 실행 통계를 제공한다.
- v$sql_workare는 커서를 실행하는데 필요한 메모리 작업 영역에 대한 정보를 제공한다.
- v$sql_plan_statistics_all은 v$sql_plan, v$sql_plan_statistics, v$sql_workarea 뷰에서 제공하는 모든 정보를 한 번에 볼 수 있게 해준다.
지식 커서 식별하기
SQL> SELECT status, sql_id, sql_child_number
2 FROM v$session
3 WHERE username = 'CURTIS';
STATUS SQL_ID SQL_CHILD_NUMBER
------- ------------- ---------------
ACTIVE 1scu79x31qavt 1
SQL> SELECT sql_id, child_number, sql_text
2 FROM v$sql
3 WHERE sql_fulltext LIKE '%online discount%'
4 AND sql_text NOT LIKE '%v$sql%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------
1hqjydsjbvmwq 0 SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P
WHERE S.PROMO_ID = P.PROMO_ID AND PROMO_SUBCATEGORY
= 'online discount'
동적 성능 뷰 조회하기
SQL> SELECT * FROM table(dbms_xplan.display_cursor('1hqjydsjbvmwq', 0));
PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------
SQL_ID 1hqjydsjbvmwq, child number 0 ------------------------------------
SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID =
P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount'
Plan hash value: 265338492
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | HASH JOIN | | 913K| 26M| 139 (33)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 4 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 918K| 8075K| 123 (27)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 123 (27)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("S"."PROMO_ID"="P"."PROMO_ID")
3 - filter("PROMO_SUBCATEGORY"='online discount')
AWR과 Statpack
SQL> SELECT * FROM table(dbms_xplan.display_awr('1hqjydsjbvmwq'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 1hqjydsjbvmwq
-------------------
SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID
= P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount'
Plan hash value: 265338492
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | HASH JOIN | | 913K| 26M| 139 (33)| 00:00:01 |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 4 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 918K| 8075K| 123 (27)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 123 (27)| 00:00:01 |
------------------------------------------------------------------------------------
SQL ID: 1hqjydsjbvmwq DB/Inst: DBM11203/DBM11203 Snaps: 576-577
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID = ...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- -------------
1 2446651477 16,577 2 577 577
2 265338492 14,736 4 577 577
--- ---------------------------------------------------------------------------
Plan 1(PHV: 2446651477)
----------------------
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- ------
Elapsed Time (ms) 16,577 8,288.6 50.2
CPU Time (ms) 16,071 8,035.3 50.9
Executions 2 N/A N/A
Buffer Gets 163,606 81,803.0 90.1
Disk Reads 161,900 80,950.0 96.0
Parse Calls 2 1.0 1.0
Rows 2 1.0 N/A
-------------------------------------------------------------------------
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2798 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | NESTED LOOPS | | 913K| 26M| 2798 (27)| 00:00:12 |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 4 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 39950 | 351K| 121 (27)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 39950 | 351K| 121 (27)| 00:00:01 |
-----------------------------------------------------------------------------------
Plan 2(PHV: 265338492)
---------------------
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- ------
Elapsed Time (ms) 14,736 3,684.0 44.6
CPU Time (ms) 14,565 3,641.2 46.1
Executions 4 N/A N/A
Buffer Gets 6,755 1,688.8 3.7
Disk Reads 6,485 1,621.3 3.8
Parse Calls 1 0.3 0.5
Rows 4 1.0 N/A
-------------------------------------------------------------------------
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | HASH JOIN | | 913K| 26M| 139 (33)| 00:00:01 |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 4 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 918K| 8075K| 123 (27)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 123 (27)| 00:00:01 |
-----------------------------------------------------------------------------------
트레이스 기능
h4.10053 이벤트
ALTER SESSION SET events '10053 trace name context forever'
ALTER SESSION SET events '10053 trace name context off'
ALTER SYSTEM SET events 'trace[rdbms.SQL_Optimizer.*][sql:9s5u1k3vshsw4]'
ALTER SYSTEM SET events 'trace[rdbms.SQL_Optimizer.*][sql:9s5u1k3vshsw4] off'
dbms_sqldiag.dump_trace(
p_sql_id => '30g1nn8wdymh3',
p_child_number => 0,
p_component => 'Optimizer',
p_file_id => 'test'
);
- p_sql_id는 처리될 부모 커서를 지정한다.
- p_child_number는 처리할 자식 커서를 식별할 수 있도록 p_sql_id와 함께 자식 커서의 번호를 지정한다. 이 파라미터는 선택사항이며 기본값은 0이다.
- p_component는 프로시저가 Optimizer를 덤프할지 아니면 Compiler 트레이스를 덤프할지 지정한다.
- p_file_id는 tracefile_identified 초기화 파라미터에 대한 값을 지정한다.
h4.10032 이벤트
----- Current SQL Statement for this session (sql_id=gbxvdrz7jvt80) ----
SELECT count(n) FROM t WHERE n BETWEEN 6 AND 19
----- Explain Plan Dump ----
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL | T | 14 | 182 | 2 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information: ---------------------2 - filter(("N">=6 AND "N"<=19))
Content of other_xml column
===========================
db_version : 11.2.0.3
parse_schema : CHRIS
dynamic_sampling: 2
plan_hash : 2966233522
plan_hash_2 : 1071362934
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$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
...
_px_numa_support_enabled = true
total_processor_group_count = 1
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
...
End of Optimizer State Dump
- 현재 세션에 대해 이벤트를 활성화 하거나 비활성화하는 방법은 각각 다음과 같다.
ALTER SESSION SET events '10132 trace name context forever'
ALTER SESSION SET events '10132 trace name context off'
- 전체 데이터베이스에 대해 활성화 하거나 비활성화 할 수 있다. 그러나 이러한 설정은 즉시 효력을 발휘하는 것이 아니라 변경 후 새로 생성된 세션에 대해서만 적용된다.
ALTER SYSTEM SET events '10132 trace name context forever'
ALTER SYSTEM SET events '10132 trace name context off
dbms_xplan 패키지
h3.출력
SQL_ID dwnnunj9nuztb, child number 0
------------------------------------
SELECT t2.* FROM t1, t2 WHERE t1.n = t2.n AND t1.id > :t1_id AND
t2.id BETWEEN :t2_id_min AND :t2_id_max
다음과 같은 정보를 제공한다.
- sql_id는 부모 커서를 식별한다.
- sql_id와 함께 child number는 자식 커서를 식별한다.
- SQL 구문의 텍스트는 display_cursor 및 display_awr 함수를 사용하여 출력을 생성했을 경우에만 사용할 수 있다.
Plan hash value: 2539808735
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 14 | 7756 | 15 (7)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 14 | 7392 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_PK | 14 | | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 876 | 22776 | 23 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 |
 |
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T2@SEL$1
4 - SEL$1 / T2@SEL$1
5 - SEL$1 / T1@SEL$1
Outline Data ------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
-------------------------------------
1 - :T1_ID (NUMBER): 6
2 - :T2_ID_MIN (NUMBER): 6
3 - :T2_ID_MAX (NUMBER): 19
Predicate Information (identified by operation id):
--------------------------------------------------
1 - filter(:T2_ID_MIN<=:T2_ID_MAX)
2 - access("T1"."N"="T2"."N")
4 - access("T2"."ID">=:T2_ID_MIN AND "T2"."ID"<=:T2_ID_MAX)
5 - filter("T1"."ID">:T1_ID)
Column Projection Information (identified by operation id):
----------------------------------------------------------
1 - "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
2 - (#keys=1) "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
3 - "T2"."ID"[NUMBER,22], "T2"."N"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
4 - "T2".ROWID[ROWID,10], "T2"."ID"[NUMBER,22]
5 - "T1"."N"[NUMBER,22]
Note
----
- dynamic sampling used for this statement (level=2)
h3.display 함수
 |
 |
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
8 행이 선택되었습니다.
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("EMPNO"=TO_NUMBER(:B1))
13 행이 선택되었습니다.
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "EMP"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
DB_VERSION('12.2.0.1')
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=TO_NUMBER(:B1))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
"EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
40 행이 선택되었습니다.
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic +predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("EMPNO"=TO_NUMBER(:B1))
13 행이 선택되었습니다.
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'typical -bytes -note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 3 (0)| 00:00:01 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("EMPNO"=TO_NUMBER(:B1))
13 행이 선택되었습니다.
SQL> ALTER SESSION SET current_schema = franco;
SQL> EXPLAIN PLAN FOR SELECT * FROM t;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE
SQL> EXPLAIN PLAN INTO franco.plan_table FOR SELECT * FROM t;
SQL> SELECT * FROM table(dbms_xplan.display(table_name=>'franco.plan_table'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 3956160932
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 14 | 1218 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL> SELECT /*+ gather_plan_statistics */ count(*) FROM t;
COUNT(*)
---------
1000
SQL> CREATE TABLE my_plan_table
2 AS
3 SELECT cast(1 AS VARCHAR2(30)) AS plan_id, p.*
4 FROM v$sql_plan_statistics_all p
5 WHERE (sql_id, child_number) = (SELECT prev_sql_id, prev_child_number
6 FROM v$session
7 WHERE sid = sys_context('userenv','sid'));
SQL> SELECT * FROM table(dbms_xplan.display('my_plan_table', NULL, 'iostats'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 2 |00:00:00.01 | 10 | 4 |
| 1 | SORT AGGREGATE | | 2 | 1 | 2 |00:00:00.01 | 10 | 4 |
| 2 | TABLE ACCESS FULL| T | 2 | 1000 | 2000 |00:00:00.01 | 10 | 4 |
---------------------------------------------------------------------------------------------
 |
display_cursor함수
SQL> SELECT /*+ gather_plan_statistics */ count(pad)
2 FROM (SELECT rownum AS rn, pad FROM t ORDER BY n)
3 WHERE rn = 1;
COUNT(PAD)
---------
1
SQL> SELECT * FROM table(dbms_xplan.display_cursor('d5v0dt28fp5fh', 0, 'iostats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID d5v0dt28fp5fh, child number 0
------------------------------------
SELECT /*+ gather_plan_statistics */ count(pad) FROM (SELECT rownum AS rn, pad FROM t ORDER BY n) WHERE rn = 1
Plan hash value: 2545006537
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 147 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 147 |
|* 2 | VIEW | | 1 | 1000 | 1 |00:00:00.02 | 147 |
| 3 | SORT ORDER BY | | 1 | 1000 | 1000 |00:00:00.02 | 147 |
| 4 | COUNT | | 1 | | 1000 |00:00:00.01 | 145 |
| 5 | TABLE ACCESS FULL| T | 1 | 1000 | 1000 |00:00:00.01 | 145 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------
2 - filter("RN"=1)
display_awr함수
SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
----------------------------------
SQL_ID 48vuyqjwpf9wg
-------------------
SELECT COUNT(N) FROM T
Plan hash value: 2966233522
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T | ----------------------------------
SQL_ID 48vuyqjwpf9wg
-------------------
SELECT COUNT(N) FROM T
Plan hash value: 3776247601
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX FAST FULL SCAN| I |
-------------------------------------
SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', 2966233522, NULL, 'basic'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 48vuyqjwpf9wg
-------------------
SELECT COUNT(N) FROM T
Plan hash value: 2966233522
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
----------------------------------
실행 계획 해석하기
부모 자식 관계
 |
 |
h3.오퍼레이션 유형
h3.독립형 오퍼레이션
SELECT deptno, count(*)
FROM emp WHERE job = 'CLERK'
AND sal < 1200 GROUP BY deptno
-------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | HASH GROUP BY | | 1 | 2 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 3 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_I | 1 | 4 |
-------------------------------------------------------------------
2 - filter("SAL"<1200) 3 - access("JOB"='CLERK')
 |