오라클 성능 고도화 원리와 해법 I (2012년)
- Introduction
- (1) 예상 실행계획 출력
- (2) 캐싱된 커서의 실제 실행계획 출력
- (3) 캐싱된 커서의 Row Source별 수행 통계 출력
- (4) 실습 : DBMS_XPLAN.DISPLAY
- (4) 실습 : DBMS_XPLAN.DISPLAY_CURSOR
- (4) 실습 : ROW-Source 별 수행 통계
Introduction
- dbms_xplan 패키지를 통해 Plan_Table에 저장된 실행계획을 좀 더 쉽게 출력 할 수 있고, 10g부터는 실행계획은 물론 Row Source별 수행 통계까지 출력 가능함
(1) 예상 실행계획 출력
- 첫번째 인자에는 실행 계획이 저장된 Plan_Table 명을 입력하고, 두번째 인자가 NULL일 경우 가장 마지막 explain_plan을 보여주며
- 세번째 인자를 통해 5(Basic, Typical, All , Outline, Advanced)가지 포맷 옵션을 선택할 수 있음
SQL> select plan_table_output
from table (dbms_xplan.display('plan_table',null,'all'));
(2) 캐싱된 커서의 실제 실행계획 출력
- 1)커서란 하드파싱 과정을 거쳐 메모리에 적재된 SQL과 Parse Tree,실행 계획 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말함
- 2)오라클은 라이브러리 캐시에 캐싱되어 있는 수행 통계를 볼 수 있도록 v$sql 뷰를 제공함
- 3)활용도가 높은 뷰는 v$sql_plan과 v$sql_plan_statistics와 두개를 합친 v$sql_plan_statistics_all 임
- 4)dbms_xplan.display_cursor함수를 이용해 조회 가능
SQL> select *
from table (dbms_xplan.display_cursor('sql_id',child_no,'format'));
- 참고로 ms_xplan.display_awr 함수를 이용하면 AWR에 수집된 과거 수행SQL에 대해서도 분석 작업을 할 수 있음
(3) 캐싱된 커서의 Row Source별 수행 통계 출력
1) 수행 통계 출력 방법
- /*\+ gather_plan_statistics \*/힌트를 사용 (set serveroutput off)
- 시스템 또는 세션 레벨에서 statisticts_level 파라미터를 All로 설정-->운영DB에서는 삼가해야함
- \_rowsource_execution_statisticts 파라미터를 True로 설정
- SQL 트레이스 수행
- v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를 이용하여 조회
- dbms_xplan.display_cursor함수를 이용해 조회 가능
2) 항목 설명
- E-Rows는 SQL을 수행하기 전 옵티마이저가 각 Row Source별 예상했던 로우 수로서 v$sql_plan에서 읽어온 값
- A-Rows는 실제 수행 시 읽었던 로우 수로서 v$sql_plan_statistics에서 읽어온 값
- 기본적으로 누적값을 보여주며, 아래 처럼 Format에 last를 추가해주면 마지막 수행했을 때의 일량을 보여줌
SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aduuuwpa8f64v', 0, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID aduuuwpa8f64v, child number 0
-------------------------------------
select * from scott.emp e, scott.dept d where d.deptno = e.deptno and e.sal >= 1000
Plan hash value: 615168685
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-
----------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 24 | 24 |00:00:00.01 | 16 | 825K| 825K| 679K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 24 | 24 |00:00:00.01 | 9 | | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
3 - filter("E"."SAL">=1000)
(4) 실습 : DBMS_XPLAN.DISPLAY
SQL> explain plan set statement_id = 'manon94'
2 for
3 SELECT *
4 FROM emp e, dept d
5 WHERE d.deptno = e.deptno
6 AND e.sal >= 1000
7 ;
해석되었습니다.
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'basic'))
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 3 | INDEX FULL SCAN | PK_DEPT |
| 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL | EMP |
------------------------------------------------
12 개의 행이 선택되었습니다.
Statistics
----------------------------------------------------------
40 recursive calls
12 db block gets
158 consistent gets
0 physical reads
0 redo size
1045 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed
SQL>
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'basic rows bytes cost'))
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)|
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
| 4 | SORT JOIN | | 14 | 532 | 4 (25)|
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)|
-----------------------------------------------------------------------------
12 개의 행이 선택되었습니다.
Statistics
----------------------------------------------------------
29 recursive calls
12 db block gets
150 consistent gets
0 physical reads
0 redo size
1335 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed
SQL>
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'typical'))
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
19 개의 행이 선택되었습니다.
Statistics
----------------------------------------------------------
29 recursive calls
12 db block gets
156 consistent gets
0 physical reads
0 redo size
1819 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
19 rows processed
SQL>
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'serial'))
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
19 개의 행이 선택되었습니다.
Statistics
----------------------------------------------------------
29 recursive calls
12 db block gets
156 consistent gets
0 physical reads
0 redo size
1819 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
19 rows processed
SQL>
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'all'))
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
43 개의 행이 선택되었습니다.
Statistics
----------------------------------------------------------
29 recursive calls
12 db block gets
156 consistent gets
0 physical reads
0 redo size
3176 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
43 rows processed
SQL>
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'outline'))
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
USE_MERGE(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
37 개의 행이 선택되었습니다.
Statistics
----------------------------------------------------------
157 recursive calls
12 db block gets
383 consistent gets
0 physical reads
0 redo size
2422 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
37 rows processed
SQL>
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'advanced'))
3 ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
USE_MERGE(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
61 개의 행이 선택되었습니다.
Statistics
----------------------------------------------------------
124 recursive calls
12 db block gets
1323 consistent gets
0 physical reads
0 redo size
3875 bytes sent via SQL*Net to client
460 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
61 rows processed
SQL>
(4) 실습 : DBMS_XPLAN.DISPLAY_CURSOR
SQL> set serveroutput off
2 SELECT *
3 FROM emp e, dept d
4 WHERE d.deptno = e.deptno
5 AND e.sal >= 1000
6 ;
12 개의 행이 선택되었습니다.
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
6 ;
PREV_SQL_ID PREV_CHILD_NUMBER
------------- -----------------
chbh66q2vddw3 0
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'iostats'))
3 ;
SQL_ID chbh66q2vddw3, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE d.deptno = e.deptno AND e.sal
>= 1000
Plan hash value: 844388907
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | MERGE JOIN | | 14 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 |
|* 4 | SORT JOIN | | 14 |
|* 5 | TABLE ACCESS FULL | EMP | 14 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
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
31 개의 행이 선택되었습니다.
SQL>
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'memstats'))
3 ;
SQL_ID chbh66q2vddw3, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE d.deptno = e.deptno AND e.sal
>= 1000
Plan hash value: 844388907
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | MERGE JOIN | | 14 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | | |
|* 4 | SORT JOIN | | 14 | 2048 | 2048 | 2/0/0|
|* 5 | TABLE ACCESS FULL | EMP | 14 | | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
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
31 개의 행이 선택되었습니다.
SQL>
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'allstats'))
3 ;
SQL_ID chbh66q2vddw3, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE d.deptno = e.deptno AND e.sal
>= 1000
Plan hash value: 844388907
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | MERGE JOIN | | 14 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | | |
|* 4 | SORT JOIN | | 14 | 2048 | 2048 | 2/0/0|
|* 5 | TABLE ACCESS FULL | EMP | 14 | | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
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
31 개의 행이 선택되었습니다.
SQL>
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'allstats last'))
3 ;
SQL_ID chbh66q2vddw3, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE d.deptno = e.deptno AND e.sal
>= 1000
Plan hash value: 844388907
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | MERGE JOIN | | 14 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | | |
|* 4 | SORT JOIN | | 14 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS FULL | EMP | 14 | | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
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
31 개의 행이 선택되었습니다.
SQL>
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'advanced allstats last'))
3 ;
SQL_ID chbh66q2vddw3, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE d.deptno = e.deptno AND e.sal
>= 1000
Plan hash value: 844388907
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
USE_MERGE(@"SEL$1" "E"@"SEL$1")
PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14],
"E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22]
5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
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
71 개의 행이 선택되었습니다.
SQL>
(4) 실습 : ROW-Source 별 수행 통계
SQL> set pagesize 0
SQL> set linesize 200
SQL> set serveroutput off
SQL> SELECT /*+ gather_plan_statistics */ *
2 FROM emp e, dept d
3 WHERE d.deptno = e.deptno
4 AND e.sal >= 1000
5 ;
12 개의 행이 선택되었습니다.
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
6 ;
4tbrp92uug9v8 0
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display_cursor('4tbrp92uug9v8', '0', 'advanced allstats last'))
3 ;
SQL_ID 4tbrp92uug9v8, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM emp e, dept d WHERE
d.deptno = e.deptno AND e.sal >= 1000
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6 (100)| | 12 |00:00:00.01 | 11 | | | |
| 1 | MERGE JOIN | | 1 | 14 | 812 | 6 (17)| 00:00:01 | 12 |00:00:00.01 | 11 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 80 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 4 | 14 | 532 | 4 (25)| 00:00:01 | 12 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 12 |00:00:00.01 | 7 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
USE_MERGE(@"SEL$1" "E"@"SEL$1")
PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
63 개의 행이 선택되었습니다.
SQL>
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3086
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.