Optimizing Oracle Optimizer (2009년)
DBMS_XPLAN 0 0 53,654

by 구루비스터디 DBMS_XPLAN [2018.07.14]


함수가 정의되어 있는 위치 : ORA_HOME\RDBMS\ADMIN\dbmsxpln.sql


  • This package defines several table functions which can be used to display execution plans.
  • 이 패키지(DBMS_XPLAN)는 실행계획을 표시하기 위하여 사용되는 몇몇 테이블 펑션이 정의된다.


  • DBMS_XPLAN 패키지를 사용하기 위하여 다음 객체에 대한 SELECT privileges이 필요하다
    • V$SESSION
    • V$SQL_PLAN
    • V$SQL (optional)
    • V$SQL_PLAN_STATISTICS_ALL (plan statitics를 보기위하여 필수)


DBMS_XPLAN에서 표현되는 컬럼


1) Basics 항목 (Always Available)
  • Id : 각 Operation 의 ID
    • (\* 가 달려있는 경우는 predicate 정보에 access 및 filter 에 관한정보가 나옴을 표시한것)
  • Operation : (row source operation) 각각 실행되는 JOB 을 나타냄
  • Name : Operation 이 엑세스하는 테이블 및 인덱스를 나타냄


2) Query Optimizer Estimations(옵티마이져의 예상 row 수 및 bytes, cost , temp 사용량)
  • Rows (E-Rows) : 각 operation 이 끝났을 때 return되는 건수를 나타냄. 이것은 예측 건수 이므로 실제 건수와는 다를 수 있음.
  • Bytes (E-Bytes) : 각 operation 이 return 한 byte 수. 예상치 이므로 실제 받은 byte와는 다를 수 있음.
  • TempSpc : 각 operation. 이 temporary space 를 사용한 양(예상치임)
  • Cost (%CPU) : 각 operation 의 Cost. (예상치 임) 괄호안의 내용은 CPU Cost 의 백분율임.(이 값은 Child Operation 의 Cost 를 합친 누적치)
  • Time : 예측 수행시간


3) Partitioning (파티션을 엑세스 할경우만 나타남)
  • Pstart : 파티션을 엑세스 하는경우 시작파티션을 나타냄, 상수로 들어올때는 파티션 번호로 나타나며 변수로 들어올때는 KEY 로 나타남
  • Pstop : 마지막 파티션을 나타냄. 따라서 patart, pstop 를 이용하면 access 한 파티션을 알수 있음.


  • Inst : DB*LINK 명(사용하는 경우만 나타남).
  • TQ : PARALLEL SQL 사용시 table queue 명을 나타냄. TQ 는 PARALLEL SLAVE 간의 통신을 담당함.
  • IN-OUT : Parallel processing 시에 각각의 Operation 이 Serial 로 실행되는지 parallel 로 진행되는지를 나타냄.
  • PQ Distrib : Parallel processing 시에 producers 와 consumers 간의 데이터의 분배방식을 나타냄.


5) Runtime Statistics (실제 수행시간밑 실제수행건수)
  • Starts : 각 operation 을 try 한 건수(예를 들어 nested loop join 이라면 인덱스를 여러 번 scan 함)
  • A-Rows : 각 operation 이 return 한 건수
  • A-Time : 실제 실행시간( 0.1초까지 나타남 (HH:MM:SS.FF))( 이값은 Child Operation 의 Cost 를 합친 누적치)


6) I/O Statistics (I/O 관련하여 READ / WRITE 한 블록수)
  • Buffers : 각 Operation 이 메모리에서 읽은 block 수.
  • Reads : 각 Operation 이 disk 에서 읽은 block 수.
  • Writes : 각 Operation 이 disk 에 write한 block 수.


7) Memory Utilization Statistics(hash 작업이나 sort 작업시 사용한 메모리 통계)
  • OMem : optimal execution 에 필요한 메모리(예측치임).
  • 1Mem : one-pass execution. 에 필요한 메모리(예측치임)
  • O/1/M : 각 operation 이 실행한 optimal/one-pass/multipass 횟수가 순서데로 표시됨.
  • Used-Mem : 마지막 실행시의 사용한 메모리
  • Used-Tmp : 마지막 실행시 메모리가 부족하여 temporary space 를 대신 사용할 때 나타남.
    • 보이는값에 1024 를 곱해야함.
    • 예를들어 32K 로 나타나면 32MB 를 의미함.
  • Max-Tmp : 메모리가 부족하여 temporary space 를 사용할 때 최대 temp 사용량임.
    • USED-TMP 와 다른점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을경우에
    • 항상 최대값만 보인다는 것이다. (보이는값에 1024 를 곱해야함, 32K 로 나타나면 32MB 를 의미)
  • 참조 http://scidb.tistory.com/trackback/46



---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib | A-Rows |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  PX COORDINATOR              |          |      1 |        |       |            |          |       |       |        |      |            |   9999 |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |      0 |      1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |      0 |
|*  3 |    HASH JOIN SEMI            |          |      0 |      1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | PCWP |            |      0 |
|   4 |     BUFFER SORT              |          |      0 |        |       |            |          |       |       |  Q1,01 | PCWC |            |      0 |
|   5 |      PX RECEIVE              |          |      0 |  10000 | 90000 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |      0 |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 |      0 |  10000 | 90000 |     4   (0)| 00:00:01 |       |       |        | S->P | PART (KEY) |      0 |
|   7 |        TABLE ACCESS FULL     | T_PLAN   |      1 |  10000 | 90000 |     4   (0)| 00:00:01 |       |       |        |      |            |  10000 |
|   8 |     PX PARTITION RANGE ALL   |          |      0 |   9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWC |            |      0 |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |      0 |   9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWP |            |      0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------
00:00:02.08 |      55 |       |       |          |
00:00:00.01 |       0 |       |       |          |
00:00:00.01 |       0 |   921K|   921K| 1246K (0)|
00:00:00.01 |       0 | 93184 | 93184 |  104K (0)|
00:00:00.01 |       0 |       |       |          |
00:00:00.01 |       0 |       |       |          |
00:00:00.05 |      46 |       |       |          |
00:00:00.01 |       0 |       |       |          |
00:00:00.01 |       0 |       |       |          |
--------------------------------------------

"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3850

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입