ORACLE 플랜 확인 방법

※ 실행계획을 캐싱해야 하기 때문에 꼭 여러번 실행계획을 돌려본 뒤에 확인

 

 

## EXPLAIN PLAN FOR

EXPLAIN PLAN
SET STATEMENT_ID = 'PLAN1' INTO PLAN_TABLE -- PLAN ID 부여
FOR
SELECT /*+ INDEX( A SYS_C007651) */ -- SQL 입력
*
FROM DAMIN.PROFESSOR A
WHERE A.PROFNO=4006;


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY -- PLAN_TABLE에 저장된 실행계획 조회
('PLAN_TABLE','PLAN1','ALL'));

'ALL' 자리에 오는 옵션들
'BASIC' : 플랜테이블만 보여줌
'TYPICAL' : 플랜테이블 + ACCESS인지 FILTER 인지
'ALL' : 플랜테이블 + ACCESS, FILTER 정보 + 컬럼 정보

 

아래와 같은 실행계획 확인 가능

 

 

 

## AUTOTRACE ON

 

옵션

AUTOTRACE OFF : 비활성화

AUTOTRACE ON : 활성화

AUTOTRACE TRACEONLY : AUTOTRACE 활성화하고 명령문 결과를 출력하지 않음

EXPLAIN : 실행계획은 표시하지만 통계를 표시하지 않음

STATISTICS : 통계는 표시하지만 실행 계획은 표시하지 않음

 

 

SET AUTOTRACE ON
  1  SELECT  -- SQL입력
  2  *
  3  FROM DAMIN.PROFESSOR A
  4  WHERE A.PROFNO=4006
  5  AND A.ID='napeople';

    PROFNO NAME       ID              POSITION               PAY
---------- ---------- --------------- --------------- ----------
HIREDATE                BONUS     DEPTNO
------------------ ---------- ----------
EMAIL
--------------------------------------------------
HPAGE
--------------------------------------------------
      4006 전민       napeople        전임강사               220
28-JUN-10                            301
napeople@jass.com




Execution Plan
----------------------------------------------------------
Plan hash value: 482468017

--------------------------------------------------------------------------------
--------------------

| Id  | Operation                           | Name         | Rows  | Bytes | Cos
t (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------

|   0 | SELECT STATEMENT                    |              |     1 |    74 |
 2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PROFESSOR    |     1 |    74 |
 2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | PROFESSOR_01 |     1 |       |
 1   (0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------


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

   2 - access("A"."PROFNO"=4006 AND "A"."ID"='napeople')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1293  bytes sent via SQL*Net to client
        447  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SET AUTOTRACE OFF;

 

 

## V$SQL 사용

SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE '%WHERE A.PROFNO=%';



SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('b6b687nyq9zmg'));

 

 

 

 

## /* +GATHER_PLAN_STATISTICS */ 힌트 사용

 

※ 힌트 사용 전 STATISTICS_LEVEL 을 ALL로 변경 해줘야 함

ALTER SESSION SET STATISTICS_LEVEL =ALL;

SELECT /* +GATHER_PLAN_STATISTICS */
*
FROM DAMIN.PROFESSOR A
WHERE A.PROFNO=4006
AND A.ID='napeople';


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

 

 

---

※ awr를 일반적으로 많이 사용하지만 enterprise 라이센스 필요!!

STATSPACK:

• STATSPACK은 Oracle 8i부터 제공된 성능 모니터링 도구로, DB의 성능 데이터를 캡처하여 문제 분석에 도움을 줍니다.
• 주기적으로 스냅샷을 생성해 성능 관련 통계를 수집합니다.
• DB 라이선스가 필요하지 않으며, 기본적인 성능 문제를 해결하는 데 유용합니다.

AWR (Automatic Workload Repository):

• AWR은 Oracle 10g 이후에 제공된 기능으로, 성능 데이터의 자동 수집 및 저장 기능을 제공합니다.
• DBMS_WORKLOAD_REPOSITORY를 사용해 스냅샷을 관리하고 보고서를 생성합니다.
• TOP SQL, IO 통계 등을 포함한 심층적인 성능 정보를 제공하여 성능 병목을 쉽게 파악할 수 있습니다.

AWR은 더 많은 데이터를 제공하며, Tuning Pack 라이선스가 필요합니다. AWR 리포트는 GUI 기반의 Oracle Enterprise Manager에서 쉽게 조회할 수 있습니다.