PARALLEL SQL DBMS_XPLAN 실행통계 보기

DBMS : oracle 19.19

 

- 일반적인 DBMS_XPLAN 에서는 parallel 프로세스에 대한 통계확인이 안된다 - ALLSTATS ALL 옵션을 사용한 누적 통계 확인 시 parallel 프로세스 확인 가능  : parallel SQL 확인 시 ALLSTATS ALL 및 child_number(두번째 파라미터) 적절히 변경 - ALLSTATS ALL 은 누적치를 보여주므로 주석을 바꿔가면서 수행하여 sql_id를 변경하며 실행하는 방법으로 진행  : 모든 프로세스 통계의 합이라 실행시간은 실제 실행시간보다 많음 - child_number stats은 아래 뷰에서 확인 가능  : SELECT * FROM V$SQL_PLAN_STATISTICS_ALL WHERE SQL_ID = '0ktqj13jw7tk8' ;

---

NO PARALLEL 실행 XPLAN

SELECT COUNT(*)
  FROM TA_DD_IM_L SD INNER JOIN HR_OG_M HM ON SD.TEAM_CD = HM.ORG_CD
 WHERE SD.DT BETWEEN :1 AND :2
 ;

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |       |       |      1 |00:00:23.16 |   57772 |  46609 |       |       |          |
|   1 |  SORT AGGREGATE             |                  |      1 |       |       |      1 |00:00:23.16 |   57772 |  46609 |       |       |          |
|*  2 |   FILTER                    |                  |      1 |       |       |     12M|00:00:22.51 |   57772 |  46609 |       |       |          |
|*  3 |    HASH JOIN                |                  |      1 |       |       |     12M|00:00:21.04 |   57772 |  46609 |  1856K|  1856K| 1658K (0)|
|   4 |     INDEX FAST FULL SCAN    | PK_HR_OG_M       |      1 |       |       |   4581 |00:00:00.01 |      17 |      0 |       |       |          |
|   5 |     PARTITION RANGE ITERATOR|                  |      1 |   KEY |   KEY |     12M|00:00:17.56 |   57755 |  46609 |       |       |          |
|*  6 |      INDEX RANGE SCAN       | IDX01_TA_DD_IM_L |      2 |   KEY |   KEY |     12M|00:00:16.25 |   57755 |  46609 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------

 
PARALLEL 실행 XPLAN
-- 코디네이터 프로세스
일반적인 xplan 방법을 하면 아래와 같은 내용만 나온다

SELECT /*+ parallel(2) SK3 */ count(*)
  FROM TA_DD_IM_L SD INNER JOIN HR_OG_M HM ON SD.TEAM_CD = HM.ORG_CD
 WHERE SD.DT BETWEEN :1 AND :2
;

-- 코디네이터 xplan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0ktqj13jw7tk8',0,'ALLSTATS ALL -ROWS +OUTLINE +PARTITION +PARALLEL +HINT_REPORT'));

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Starts | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |      1 |       |       |        |      |            |      1 |00:00:19.93 |       6 |
|   1 |  SORT AGGREGATE                   |                  |      1 |       |       |        |      |            |      1 |00:00:19.93 |       6 |
|*  2 |   PX COORDINATOR                  |                  |      1 |       |       |        |      |            |      2 |00:00:19.93 |       6 |
|   3 |    PX SEND QC (RANDOM)            | :TQ10000         |      0 |       |       |  Q1,00 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |
|   4 |     SORT AGGREGATE                |                  |      0 |       |       |  Q1,00 | PCWP |            |      0 |00:00:00.01 |       0 |
|*  5 |      FILTER                       |                  |      0 |       |       |  Q1,00 | PCWC |            |      0 |00:00:00.01 |       0 |
|*  6 |       HASH JOIN                   |                  |      0 |       |       |  Q1,00 | PCWP |            |      0 |00:00:00.01 |       0 |
|   7 |        INDEX FAST FULL SCAN       | PK_HR_OG_M       |      0 |       |       |  Q1,00 | PCWP |            |      0 |00:00:00.01 |       0 |
|   8 |        PX PARTITION RANGE ITERATOR|                  |      0 |   KEY |   KEY |  Q1,00 | PCWC |            |      0 |00:00:00.01 |       0 |
|*  9 |         INDEX RANGE SCAN          | IDX01_TA_DD_IM_L |      0 |   KEY |   KEY |  Q1,00 | PCWP |            |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:2>=:1)
   5 - filter(:2>=:1)
   6 - access("SD"."TEAM_CD"="HM"."ORG_CD")
   9 - access("SD"."DT">=:1 AND "SD"."DT"<=:2)

 
-- 슬레이브 프로세스
ALLSTATS ALL을 사용하면 실제 작업을 수행한 프로세스들 통계 확인가능

-- SLAVE XPLAN
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0ktqj13jw7tk8',1,'ALLSTATS ALL -ROWS +OUTLINE +PARTITION +PARALLEL +HINT_REPORT'));

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Starts | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |      0 |       |       |        |      |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   1 |  SORT AGGREGATE                   |                  |      0 |       |       |        |      |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  2 |   PX COORDINATOR                  |                  |      0 |       |       |        |      |            |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|   3 |    PX SEND QC (RANDOM)            | :TQ10000         |      0 |       |       |  Q1,00 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   4 |     SORT AGGREGATE                |                  |      2 |       |       |  Q1,00 | PCWP |            |      2 |00:00:22.32 |   57787 |  35560 |       |       |          |
|*  5 |      FILTER                       |                  |      2 |       |       |  Q1,00 | PCWC |            |     12M|00:00:21.47 |   57787 |  35560 |       |       |          |
|*  6 |       HASH JOIN                   |                  |      2 |       |       |  Q1,00 | PCWP |            |     12M|00:00:19.69 |   57787 |  35560 |  1856K|  1856K|     2/0/0|
|   7 |        INDEX FAST FULL SCAN       | PK_HR_OG_M       |      2 |       |       |  Q1,00 | PCWP |            |   9162 |00:00:00.01 |      32 |      0 |       |       |          |
|   8 |        PX PARTITION RANGE ITERATOR|                  |      2 |   KEY |   KEY |  Q1,00 | PCWC |            |     12M|00:00:15.63 |   57755 |  35560 |       |       |          |
|*  9 |         INDEX RANGE SCAN          | IDX01_TA_DD_IM_L |      2 |   KEY |   KEY |  Q1,00 | PCWP |            |     12M|00:00:14.00 |   57755 |  35560 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:2>=:1)
   5 - filter(:2>=:1)
   6 - access("SD"."TEAM_CD"="HM"."ORG_CD")
   9 - access("SD"."DT">=:1 AND "SD"."DT"<=:2)