DISPLAY_CURSOR

DBMS_XPLAN.DISPLAY_CURSOR

 : 10g 부터 지원

 : 9i 에서는 trace 10046 사용해야함

 

해당 패키지를 사용하기 위해서는 일반 DB유저에게 아래와 같은 권한을 부여해야한다. 

GRANT SELECT ON V_$SESSION TO 사용자;

GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO 사용자;

GRANT SELECT ON V_$SQL TO 사용자;

 

사용법. 

1. 실행 계획을 검토할 문장에 힌트 /*+ gather_plan_statistics */ 삽입 후 실행

2.   select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST')); 실행

 

예)

SELECT /*+ gather_plan_statistics */ * 
FROM TUNING.ORDERS
WHERE CODE = 10;

 

 

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST')); 

 

PLAN_TABLE_OUTPUT
SQL_ID  1xsrwsu9684v1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ *  FROM TUNING.ORDERS WHERE CODE = 
10
 
Plan hash value: 231671837
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |       |  8863 (100)|          |   8856 |00:00:00.01 |    8891 |   8776 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS          |      1 |   8911 |   452K|  8863   (1)| 00:00:01 |   8856 |00:00:00.01 |    8891 |   8776 |
|*  2 |   INDEX RANGE SCAN                  | ORDERS_CODE_IDX |      1 |   8911 |       |    31   (0)| 00:00:01 |   8856 |00:00:00.01 |     120 |      0 |
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CODE"=10)

 

PLAN_TABLE_OUTPUT

SQL ID = 힌트를 넣은 SQL 의 ID, Shared Pool 에 저장이 되어 있다면 추후에도 확인이 가능

PLAN HASH VALUE : 실행계획에 종속적인 값

                            SQL ID는 같아도 PLAN HASH VALUE 값은 다를 수 있음, -> 실행계획이 변경 된 것

 

 

 

항목	설명
Id	일련번호
Operation	작업명
Name	사용되는 객체명
Starts	실행 횟수
	일반적으로 NESTED LOOP JOIN이나 SORT MERGE JOIN에서 선행 데이터 건수가 해당 값이 됨
E-Rows	실행 단계별 예상 건수
	옵션에 -rows를 사용하면 나타나지 않음
A-Rows	실행 단계별 실제 건수
A-Time	실행 단계별 걸린 시간
	누적 값이며 1/100초까지 측정 된다. 누적 값이므로 해당 작업에서 소요된 시간을 구하려면 이전 단계의 값을 차감해주어야 한다
Buffers	Logical I/O(BUFFER CACHE에서 읽음)에 의해 읽은 블록 수. 누적 값이므로 해당 작업에서 읽은 블록 수를 구하려면 이전 단계의 값을 차감해주어야 한다
Reads	I/O(DISK에서 읽음)
	실행 계획을 비교할 때는 Reads 횟수를 비슷하게 하여 Buffers 항목을 비교하여야 한다. 마찬가지로 누적 값이다.
0Mem	PGA를 사용하는 작업(Sort Merge Join, Hash Join 등)이 메모리에서 모두 처리(OPTIMAL PASS) 되었을 때, 예상되는 PGA 사용량
1Mem	PGA를 사용하는 작업이 PGA 메모리에서 한번에 처리되지 못하고, 디스크를 한번 사용(ONE PASS)했을 때, 예상되는 PGA 사용량
Used-Mem	PGA를 사용하는 작업에서 실제 사용된 PGA 사용량. OPTIMAL PASS이면 (0), ONE PASS이면 (1), MULTI PASS이면 (2)가 표시

 

항목	설명
E-Time	작업 별 예상 실행 시간. 누적값
Cost	작업 별 비용. 비용이 높을수록 성능을 저하시키는 작업일 가능성이 높음
Writes	ONE PASS 이상으로 문장이 실행되어 디스크에 기록하는 블록수
	WITH이 MATERIALIZE로 동작하는 경우에도 나타남
Used-Tmp	ONE PASS 이상으로 SQL이 실행 될 때 나타남
Pstart	파티션 테이블을 스캔할 때 나타나며, 스캔이 시작되는 파티션 번호가 표시
	ADVANCED 또는 PARTITION 옵션을 사용해야 나타남
Pstop	파티션 테이블을 스캔할 때 나타나며, 스캔이 종료되는 파티션 번호가 표시
TQ	PARALLEL로 SQL을 실행했을 때 나타남. ADVANCED 옵션을 사용해야 표시
IN-OUT	PARALLEL로 SQL을 실행했을 때 나타남. PARALLEL 처리 방식이 표시
Distrib	PARALLEL 처리 분배 방식이 표시

 

 

DBMS_XPLAN.DISPLAY_CURSOR 사용법

 

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST')); 

 

첫번째 인자 : SQL ID or Null 

                  null : 방금 전에 수행 했던 SQL을 따라감

 

두번째 인자 : PLAN HASH VALUE 값 or Null

                  동일한 SQL ID 에 다른 PLAN HASH VALUE 값을 가지고 있을 경우 확인

세번째 인자 

DBMS_XPLAN.DISPLAY_CURSOR 함수의 세번째 인자에 지정 가능한 항목은 다음과 같음

항목	설명
BASIC	작업명과 해당 옵션만 표시
TYPICAL	파티션과 PARALLEL 사용과 같은 다양한 정보를 표시. 디폴트
SERIAL	TYPICAL과 같지만, PARALLEL 정보는 제외
ALL	표시 가능한 정보는 모두 표시
ADVANCED	ALL과 OUTLINE 및 바인드 변수 정보를 표시
ALLSTATS	IOSTATS LAST와 동일
MEMSTATS	해시 조인, 정렬 등과 같은 메모리 사용 작업에 대한 정보를 표시
IOSTATS	커서 실행에 대한 I/O 통계 표시
LAST	최근에 실행된 커서에 대한 통계를 표시
OUTLINE	OUTLINE 정보와 힌트를 표시
ALIAS	쿼리 블럭명 및 객체의 별칭을 표시
PREDICATE	PREDICATE를 표시
PEEKED_BINDS	바인드 변수 값을 표시
PARALLEL	병렬 실행 정보를 표시
PARTITION	파티션 프루닝 정보를 표시
BYTES	계산된 바이트 수를 표시
COST	옵티마이저에 의해 계산된 비용