[Oracle Tunning] DBMS_XPLAN.DISPLAY_CURSOR

 DBMS_XPLAN.DISPLAY_CURSOR는 오라클에서 SQL 실행 계획을 조회하기 위해 사용되는 함수이다.

 

이 함수는 특히 실행된 SQL 문에 대한 실행 계획과 그에 대한 성능 통계를 제공하는 데 유용하다.

 

이를 통해 SQL 문이 어떻게 실행되었는지, 그리고 성능을 개선할 여지가 있는지 확인할 수 있다.

 

• 실행 계획 조회: DBMS_XPLAN.DISPLAY_CURSOR는 커서와 관련된 실행 계획을 보여준다. 이를 통해 쿼리가 실행된 방식과 각 단계에서 사용된 접근 방법을 확인할 수 있다.
• 실행 통계 제공: 실행된 쿼리에 대한 통계 정보를 제공한다. 이 통계에는 실제로 읽은 블록 수, 사용된 CPU 시간, 실행된 행의 수 등이 포함된다. 이러한 정보는 쿼리 성능을 분석하고 병목 지점을 찾아내는 데 도움이 된다.
• ACTIVE SESSION HISTORY (ASH)와 통합: DISPLAY_CURSOR는 ASH와 통합되어 실행 중이거나 완료된 쿼리에 대한 심층 분석을 수행할 수 있다. 이를 통해 쿼리의 성능 문제를 더욱 정확하게 진단할 수 있다.

---

필요 권한

GRANT SELECT ON V_$SQL TO dev;
GRANT SELECT ON V_$SESSION TO dev;
GRANT SELECT ON V_$SQL_PLAN TO dev;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS TO dev;

ALTER SESSION SET STATISTICS_LEVEL = ALL;

사용법

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));

params: 
1. SQL_ID
2. CHILD NUMBER
3. 출력 옵션

1, 2번 인자에 NULL을 넣으면 가장 마지막에 실행된 SQL에 대한 정보를 의미한다.

---

실행 계획 주요 내용

구분	내용
기본항목	Id: 각 Operation ID. 별(*) 표시가 있으면 Predicate Information에 추가 정보를 표시함 Operation: 각각 실행 작업 Name: Operation이 액세스하는 테이블 또는 인덱스
옵티마이저의 예상값	E-Rows: 각 Operation이 끝났을 때 반환되는 예상 건수 E-Bytes: 각 Operation이 반환한 예상 바이트 수 E-Temp: 각 Operation이 temporary space를 사용하는 예상 양 Cost(%CPU): 각 Operation의 코스트(CPU 코스트의 백분율) E-Time: 예측 수행시간
실제 실행 정보	Starts: 각 Operation을 시도한 건수(예를 들어 NL 조인이라면 조인 시도 횟수) A-Rows: 각 Operation이 실제 반환한 건수 A-Time: 실제 실행시간 Buffers: 각 Operation이 메모리에서 읽은 블록 수 Reads: 각 Operation이 디스크에서 읽은 블록 수 Writes: 각 Operation이 디스크에 쓴 블록 수
PGA 사용*	OMem: optimal 실행에 필요한 메모리(예측치) 1Mem: one-pass 실행에 필요한 메모리(예측치) Used-Mem: 마지막 실행 시 사용한 메모리

 

* PGA는 group by 작업, 정렬, 해시 조인 시 사용

---

DBMS_XPLAN.DISPLAY_CURSOR를 출력했을 때, 나오는 정보들에 대한 세부적인 설명은 아래와 같다.

 

Outline Data 

======================== Outline Data ===================================
- 오라클 내부적으로 사용한 힌트와 사용자가 사용한 힌트 정보를 제공한다.
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      DB_VERSION('21.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$58A6D7F6" "I"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "P"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "I"@"SEL$1" "P"@"SEL$1")
      USE_HASH(@"SEL$58A6D7F6" "P"@"SEL$1")
      END_OUTLINE_DATA
  */
===========================================================

 

쿼리 Block info

======================== 쿼리 Block info =======================
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$58A6D7F6
   2 - SEL$58A6D7F6 / "I"@"SEL$1"
   3 - SEL$58A6D7F6 / "P"@"SEL$1"

 

Predicate Information(중요)

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("I"."ITEM_ID"="P"."ITEM_ID")

 

Column Projection Information

Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "I"."ITEM_ID"[NUMBER,22], "P"."ITEM_ID"[NUMBER,22], "P"."MOD_DTS"[DATE,7], 
       "I"."UITEM_ID"[NUMBER,22], "I"."UITEM_NM"[VARCHAR2,100], "I"."SALE_YN"[VARCHAR2,1], 
       "I"."UITEM_DESC"[VARCHAR2,500], "I"."REG_ID"[VARCHAR2,20], "I"."REG_DTS"[DATE,7], 
       "I"."MOD_ID"[VARCHAR2,20], "I"."MOD_DTS"[DATE,7], "P"."ITEM_ID"[NUMBER,22], 
       "P"."UITEM_ID"[NUMBER,22], "P"."APPLY_DT"[VARCHAR2,8], "P"."SALE_PRICE"[NUMBER,22], 
       "P"."DISCOUNT_PRICE"[NUMBER,22], "P"."COST_PRICE"[NUMBER,22], "P"."REG_ID"[VARCHAR2,20], 
       "P"."REG_DTS"[DATE,7], "P"."MOD_ID"[VARCHAR2,20], "P"."MOD_DTS"[DATE,7], "P"."UITEM_ID"[NUMBER,22], 
       "P"."APPLY_DT"[VARCHAR2,8], "P"."SALE_PRICE"[NUMBER,22], "P"."DISCOUNT_PRICE"[NUMBER,22], 
       "P"."COST_PRICE"[NUMBER,22], "P"."REG_ID"[VARCHAR2,20], "P"."REG_DTS"[DATE,7], 
       "P"."MOD_ID"[VARCHAR2,20]
   2 - "I"."ITEM_ID"[NUMBER,22], "I"."UITEM_ID"[NUMBER,22], "I"."UITEM_NM"[VARCHAR2,100], 
       "I"."SALE_YN"[VARCHAR2,1], "I"."UITEM_DESC"[VARCHAR2,500], "I"."REG_ID"[VARCHAR2,20], 
       "I"."REG_DTS"[DATE,7], "I"."MOD_ID"[VARCHAR2,20], "I"."MOD_DTS"[DATE,7]
   3 - "P"."ITEM_ID"[NUMBER,22], "P"."UITEM_ID"[NUMBER,22], "P"."APPLY_DT"[VARCHAR2,8], 
       "P"."SALE_PRICE"[NUMBER,22], "P"."DISCOUNT_PRICE"[NUMBER,22], "P"."COST_PRICE"[NUMBER,22], 
       "P"."REG_ID"[VARCHAR2,20], "P"."REG_DTS"[DATE,7], "P"."MOD_ID"[VARCHAR2,20], "P"."MOD_DTS"[DATE,7]
 
Note
-----
   - this is an adaptive plan
   - 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
 
Query Block Registry:
---------------------
 
  SEL$1 (PARSER)
  SEL$2 (PARSER)
    SEL$58A6D7F6 (VIEW MERGE SEL$2 ; SEL$1) [FINAL]

---

튜닝에 자주 사용되는 실행 계획 출력 방식

- Buffers 컬럼이 안보일 때
ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL;
ALTER SESSION SET "_rowsource_execution_statistics" = TRUE;
---
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +OUTLINE +ALIAS'));

========== 제일 많이 사용 =============
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));

---

DBMS_XPLAN.DISPLAY_CURSOR는 쿼리 튜닝 작업에서 많이 사용된다.

 

예를 들어, 예상치 못한 쿼리 성능 저하가 발생했을 때, 이 함수를 사용해 실제 실행된 계획과 그 성능 통계를 분석함으로써 문제를 진단할 수 있다.

 

또한, DISPLAY_CURSOR는 실행 계획이 예상과 다른지 확인하는 데 유용하다.

 

만약 옵티마이저가 비효율적인 계획을 선택했다면, 이 함수를 통해 그 이유를 파악하고, 필요한 경우 힌트 등을 사용해 계획을 수정할 수 있다.

 

 

끝!