Skip to content

Instantly share code, notes, and snippets.

@databasetech0073

databasetech0073/gist:4b895f5790a993ed326d389b54295f9b Secret

Created November 16, 2022 16:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save databasetech0073/4b895f5790a993ed326d389b54295f9b to your computer and use it in GitHub Desktop.
Save databasetech0073/4b895f5790a993ed326d389b54295f9b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
INSERT /*+ APPEND */
INTO BINTS_FCT (PM_PK, BI_NBR, PE_PK, CT_PK, C_2l_CD,D_PK, c_nbr, AT_CN,AT_AMT, NT_CNT, NT_AMT, CU_ID,CREATE_TS,UPDT_TS, UU_ID)
WITH C
AS (SELECT /*+ materialize */
SUBSTR (CP.CP_NBR, 1, 6) AS BI_NBR,SUBSTR (CP.CP_NBR, 1, 4) AS BNK_ID_NB_4, CP.CP_NBR,
CP.IC_2_L_CD, CP.CPCL_NBR - X.RN AS CPCL_NBR,
CP.PN_CD,
CASE CP.PANL_CNT
WHEN 0 THEN 16
ELSE CP.PANL_CNT
END AS PANL_CNT,
CASE WHEN X.RN = 0 THEN 'Y' ELSE 'N' END AS PBC_IND,
COUNT ( DISTINCT CP.IC_2_L_CD)
OVER (
PARTITION BY SUBSTR (CP.CP_NBR, 1, CP.CPCL_NBR - X.RN)) AS DC_CNT,
RPAD (
SUBSTR (CP.CP_NBR, 1, CP.CPCL_NBR - X.RN),
CASE CP.PANL_CNT
WHEN 0 THEN 16
ELSE CP.PANL_CNT
END, '0') AS LOW_VALUE,
RPAD (
SUBSTR (CP.CP_NBR, 1, CP.CPCL_NBR - X.RN),
CASE CP.PANL_CNT
WHEN 0 THEN 16
ELSE CP.PANL_CNT
END, '9') AS HIGH_VALUE
FROM USER1.CP CP
INNER JOIN ( SELECT ROWNUM - 1 AS RN
FROM DUAL
CONNECT BY LEVEL <= 18) X
ON CP.CPCL_NBR - X.RN >= 4
WHERE CP.PN_CD IN ('MC', 'VI'))
SELECT S.PM_FK, S.BI_NBR, S.PE_FK, S.CT_FK,S.C_2l_CD,S.D_PK,M.c_nbr AS c_nbr,
SUM (CASE WHEN S.RT_CD <> '3' THEN 1 ELSE 0 END) AS AT_CN,
SUM (CASE WHEN S.RT_CD <> '3' THEN S.T_AMT ELSE 0 END) AS AT_AMT,
SUM (CASE WHEN S.RT_CD = '3' THEN 1 ELSE 0 END) AS NT_CNT,
SUM (CASE WHEN S.RT_CD = '3' THEN S.T_AMT ELSE 0 END) AS NT_AMT,
SYS_CONTEXT ('USERENV', 'SESSION_USER') AS CU_ID,
SYSTIMESTAMP AS CREATE_TS,
SYSTIMESTAMP AS UPDT_TS,
SYS_CONTEXT ('USERENV', 'SESSION_USER') AS UU_ID
FROM (SELECT T.DS_NO, T.C_NBR, C.LOW_VALUE, C.HIGH_VALUE, C.CPCL_NBR, C.PANL_CNT, T.PM_FK, SUBSTR (T.C_NBR, 1, 6) AS BI_NBR, T.PE_FK, T.CT_FK,T.RT_CD,
CASE
WHEN T.CT_CD = 'CN' THEN 'US'
WHEN TRIM (C.IC_2_L_CD) IS NULL THEN '--'
WHEN C.DC_CNT > 1 THEN '--'
ELSE C.IC_2_L_CD
END
AS C_2l_CD,T.D_PK, T.T_AMT, C.DC_CNT, C.PBC_IND, T.CT_CD,
ROW_NUMBER ()
OVER (PARTITION BY T.DS_NO, T.C_NBR
ORDER BY C.CPCL_NBR DESC)
AS CP_RANK,
COUNT (*) OVER ( PARTITION BY T.DS_NO, T.C_NBR,C.CPCL_NBR) AS CL_MATCHES,
COUNT (*) OVER (PARTITION BY T.DS_NO, T.C_NBR) AS NO_MATCHES
FROM (SELECT DS_NO AS DS_NO, MP_ID AS PM_FK, TXN_PK_ID AS A_FK, TRTP_ID AS RT_FK,
CTP_ID AS CT_FK, PEMP_ID AS PE_FK, TXN_AM AS T_AMT, H_DT AS D_PK, CT_CD,CT_CD AS RT_CD, TOKN_NB AS C_NBR, C_NBR AS CN_ORG,
CASE WHEN TOKN_NB <> C_NBR THEN 'Y' ELSE 'N' END AS UPCN_IND
FROM PTFRTS_GTT
UNION
SELECT /*+ full(t) no_merge */
T.DS_NO, T.PM_FK, T.A_FK, T.RT_FK, T.CT_FK,T.PE_FK, T.T_AMT,T.H_DT AS D_PK, CT.CT_CD, R.RT_CD,
COALESCE (TP.PN, PRT.PN, T.C_NBR) AS C_NBR,
T.C_NBR AS CN_ORG,
CASE
WHEN TP.PN IS NOT NULL OR PRT.PN IS NOT NULL
THEN 'Y'
ELSE 'N'
END AS UPCN_IND
FROM T T
INNER JOIN DA A
ON T.A_FK = A.A_PK AND A.A_CD IN ('1', '2')
INNER JOIN DC_TYP CT
ON T.CT_FK = CT.CT_PK
INNER JOIN DR_TYP R
ON T.RT_FK = R.RT_PK
LEFT OUTER JOIN USER1.TP TP
ON TP.DS_NO = T.DS_NO
AND TP.TC_CD = T.TC_CD
AND TP.PM_FK = T.PM_FK
AND TP.PN_CD = 'F'
LEFT OUTER JOIN USER2.PRT PRT
ON PRT.DS_NO = T.DS_NO
AND PRT.TC_CD = T.TC_CD
AND PRT.PM_FK = T.PM_FK
AND PRT.PN_CD = 'F'
WHERE T.H_DT = :B1) T
LEFT OUTER JOIN C
ON SUBSTR (T.C_NBR, 1, 4) = C.BNK_ID_NB_4
AND LENGTH (T.C_NBR) = C.PANL_CNT
AND T.C_NBR BETWEEN C.LOW_VALUE AND C.HIGH_VALUE) S
INNER JOIN DPM M
ON S.PM_FK = M.PM_PK
WHERE S.CP_RANK = 1
GROUP BY S.PM_FK,
S.BI_NBR,
S.PE_FK,
S.CT_FK,
S.C_2l_CD,
S.D_PK,
M.c_nbr
*************************** Sql monitor + sql outline + predicate section from version 19.15 without any profile ************************
Global Information
------------------------------
Status : DONE (ERROR)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 11/11/2022 04:49:01
First Refresh Time : 11/11/2022 04:49:05
Last Refresh Time : 11/11/2022 05:04:31
Duration : 930s
PLSQL Entry Ids (Object/Subprogram) : 25723986,1
PLSQL Current Ids (Object/Subprogram) : 25723986,1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B1 | 1 | DATE | 11/02/2022 00:00:00 |
========================================================================================================================
Global Stats
=============================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Write | Write | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================================================
| 37200 | 24542 | 12616 | 0.03 | 0.02 | 42 | 278M | 2M | 911GB | 2M | 1TB | 891GB | 3TB |
=============================================================================================================================================
Parallel Execution Details (DOP=16 , Servers Allocated=32)
=========================================================================================================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Write | Write | Offload | Offload | Wait Events | |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes | (sample #) | |
=========================================================================================================================================================================================================================================================================
| PX Coordinator | QC | | 67 | 60 | 7.03 | 0.03 | 0.01 | 0.23 | 2M | 12285 | 9GB | 1297 | 849MB | 8GB | 10GB | cell multiblock physical read (6) | |
| | | | | | | | | | | | | | | | | cell smart table scan (2) | |
| p000 | Set 1 | 1 | 1364 | 736 | 626 | | 0.01 | 2.62 | 10M | 43417 | 827MB | 95532 | 47GB | . | 93GB | NaN% | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | | gc current grant 2-way (3) |
| | | | | | | | | | | | | | | | | | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (22) |
| | | | | | | | | | | | | | | | | | direct path read temp (5) |
| | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p001 | Set 1 | 2 | 1907 | 991 | 914 | | 0.00 | 2.68 | 10M | 43794 | 829MB | 137K | 67GB | . | 134GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (12) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (14) |
| | | | | | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p002 | Set 1 | 3 | 1351 | 703 | 645 | | 0.00 | 2.62 | 10M | 43151 | 824MB | 89963 | 44GB | . | 88GB | NaN% | gc cr grant 2-way (4) |
| | | | | | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (7) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (15) |
| | | | | | | | | | | | | | | | | | direct path read temp (7) |
| | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p003 | Set 1 | 4 | 1823 | 1026 | 794 | | 0.00 | 2.58 | 10M | 43142 | 824MB | 135K | 66GB | . | 132GB | NaN% | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (19) |
| | | | | | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p004 | Set 1 | 5 | 1676 | 932 | 740 | | 0.00 | 2.63 | 10M | 43303 | 826MB | 123K | 60GB | . | 120GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | latch: cache buffers chains (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (19) |
| | | | | | | | | | | | | | | | | | direct path read temp (2) |
| | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p005 | Set 1 | 6 | 2123 | 1195 | 925 | | 0.00 | 2.57 | 10M | 43232 | 825MB | 160K | 78GB | . | 156GB | NaN% | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (11) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (17) |
| | | | | | | | | | | | | | | | | | direct path read temp (3) |
| p006 | Set 1 | 7 | 1123 | 641 | 479 | | 0.00 | 2.58 | 10M | 42678 | 820MB | 80598 | 39GB | . | 79GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (19) |
| | | | | | | | | | | | | | | | | | direct path read temp (3) |
| p007 | Set 1 | 8 | 1698 | 956 | 740 | | 0.00 | 2.65 | 10M | 43540 | 828MB | 128K | 62GB | . | 125GB | NaN% | gc current grant 2-way (4) |
| | | | | | | | | | | | | | | | | | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (13) |
| | | | | | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p008 | Set 1 | 9 | 1397 | 790 | 604 | | 0.00 | 2.63 | 10M | 43223 | 825MB | 102K | 50GB | . | 99GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (12) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (16) |
| | | | | | | | | | | | | | | | | | direct path read temp (7) |
| | | | | | | | | | | | | | | | | | direct path write temp (3) |
| p009 | Set 1 | 10 | 1389 | 764 | 622 | | 0.00 | 2.62 | 10M | 43085 | 824MB | 98442 | 48GB | . | 96GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (9) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (20) |
| | | | | | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | | | | | direct path write temp (4) |
| p00a | Set 1 | 11 | 1098 | 599 | 497 | | 0.00 | 2.58 | 10M | 42637 | 820MB | 76375 | 37GB | . | 75GB | NaN% | gc cr grant 2-way (4) |
| | | | | | | | | | | | | | | | | | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (16) |
| | | | | | | | | | | | | | | | | | direct path read temp (6) |
| p00b | Set 1 | 12 | 2259 | 1250 | 1007 | | 0.00 | 2.59 | 10M | 42621 | 820MB | 167K | 82GB | . | 164GB | NaN% | gc cr grant 2-way (3) |
| | | | | | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (8) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (20) |
| | | | | | | | | | | | | | | | | | direct path read temp (7) |
| | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p00c | Set 1 | 13 | 1730 | 935 | 792 | | 0.00 | 2.60 | 10M | 42810 | 822MB | 125K | 61GB | . | 122GB | NaN% | gc cr grant 2-way (6) |
| | | | | | | | | | | | | | | | | | cell single block physical read (11) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (12) |
| | | | | | | | | | | | | | | | | | direct path read temp (7) |
| | | | | | | | | | | | | | | | | | direct path write temp (3) |
| p00d | Set 1 | 14 | 2794 | 1556 | 1235 | | 0.00 | 2.62 | 10M | 42948 | 823MB | 217K | 106GB | . | 212GB | NaN% | cell single block physical read (12) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (17) |
| | | | | | | | | | | | | | | | | | direct path read temp (2) |
| | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p00e | Set 1 | 15 | 1667 | 925 | 740 | | 0.00 | 2.56 | 10M | 42958 | 823MB | 126K | 61GB | . | 123GB | NaN% | gc cr grant 2-way (3) |
| | | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (21) |
| | | | | | | | | | | | | | | | | | direct path read temp (5) |
| | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p00f | Set 1 | 16 | 1938 | 1053 | 882 | | 0.00 | 2.59 | 10M | 42493 | 819MB | 143K | 70GB | . | 140GB | NaN% | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (22) |
| | | | | | | | | | | | | | | | | | direct path read temp (2) |
| | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p00g | Set 2 | 1 | 775 | 751 | 24 | | | 0.00 | 9M | 68985 | 66GB | 1189 | 595MB | 66GB | 67GB | cell smart table scan (5) | |
| | | | | | | | | | | | | | | | | direct path read temp (1) | |
| | | | | | | | | | | | | | | | | direct path write temp (7) | |
| p00h | Set 2 | 2 | 628 | 602 | 25 | | | 0.00 | 7M | 59696 | 57GB | 1187 | 594MB | 57GB | 58GB | cell smart table scan (10) | |
| | | | | | | | | | | | | | | | | direct path read temp (3) | |
| | | | | | | | | | | | | | | | | direct path write temp (10) | |
| p00i | Set 2 | 3 | 594 | 571 | 23 | | | | 7M | 55934 | 54GB | 1189 | 594MB | 54GB | 55GB | cell smart table scan (8) | |
| | | | | | | | | | | | | | | | | direct path read temp (5) | |
| | | | | | | | | | | | | | | | | direct path write temp (8) | |
| p00j | Set 2 | 4 | 600 | 578 | 22 | | | 0.00 | 7M | 57332 | 55GB | 1189 | 595MB | 55GB | 56GB | cell smart table scan (2) | |
| | | | | | | | | | | | | | | | | direct path read temp (6) | |
| | | | | | | | | | | | | | | | | direct path write temp (8) | |
| p00k | Set 2 | 5 | 594 | 570 | 24 | | | 0.00 | 7M | 55216 | 53GB | 1189 | 595MB | 53GB | 54GB | cell smart table scan (10) | |
| | | | | | | | | | | | | | | | | direct path read temp (5) | |
| | | | | | | | | | | | | | | | | direct path write temp (6) | |
| p00l | Set 2 | 6 | 592 | 570 | 22 | | | 0.00 | 7M | 55517 | 53GB | 1190 | 595MB | 53GB | 54GB | cell smart table scan (10) | |
| | | | | | | | | | | | | | | | | direct path read temp (1) | |
| | | | | | | | | | | | | | | | | direct path write temp (7) | |
| p00m | Set 2 | 7 | 606 | 584 | 22 | | | 0.00 | 7M | 58375 | 56GB | 1189 | 594MB | 56GB | 57GB | cell smart table scan (5) | |
| | | | | | | | | | | | | | | | | direct path read temp (3) | |
| | | | | | | | | | | | | | | | | direct path write temp (10) | |
| p00n | Set 2 | 8 | 593 | 570 | 23 | | | | 7M | 55624 | 54GB | 1187 | 594MB | 53GB | 54GB | cell smart table scan (7) | |
| | | | | | | | | | | | | | | | | direct path read temp (2) | |
| | | | | | | | | | | | | | | | | direct path write temp (10) | |
| p00o | Set 2 | 9 | 610 | 588 | 22 | | | 0.00 | 7M | 58679 | 56GB | 1189 | 595MB | 56GB | 57GB | cell smart table scan (6) | |
| | | | | | | | | | | | | | | | | direct path read temp (4) | |
| | | | | | | | | | | | | | | | | direct path write temp (8) | |
| p00p | Set 2 | 10 | 603 | 580 | 22 | | | 0.00 | 7M | 56650 | 55GB | 1188 | 595MB | 54GB | 55GB | cell smart table scan (9) | |
| | | | | | | | | | | | | | | | | direct path read temp (3) | |
| | | | | | | | | | | | | | | | | direct path write temp (6) | |
| p00q | Set 2 | 11 | 602 | 579 | 23 | | | 0.00 | 7M | 57816 | 56GB | 1187 | 594MB | 55GB | 56GB | cell smart table scan (8) | |
| | | | | | | | | | | | | | | | | direct path read temp (2) | |
| | | | | | | | | | | | | | | | | direct path write temp (12) | |
| p00r | Set 2 | 12 | 590 | 570 | 20 | | | 0.00 | 7M | 56039 | 54GB | 1189 | 595MB | 54GB | 55GB | cell smart table scan (5) | |
| | | | | | | | | | | | | | | | | direct path read temp (4) | |
| | | | | | | | | | | | | | | | | direct path write temp (8) | |
| p00s | Set 2 | 13 | 610 | 590 | 20 | | | 0.01 | 8M | 60411 | 58GB | 1189 | 594MB | 58GB | 59GB | gc cr grant 2-way (1) | |
| | | | | | | | | | | | | | | | | cell smart table scan (7) | |
| | | | | | | | | | | | | | | | | direct path read temp (3) | |
| | | | | | | | | | | | | | | | | direct path write temp (6) | |
| p00t | Set 2 | 14 | 611 | 587 | 24 | | | 0.00 | 7M | 56958 | 55GB | 1189 | 594MB | 55GB | 56GB | cell smart table scan (3) | |
| | | | | | | | | | | | | | | | | direct path read temp (2) | |
| | | | | | | | | | | | | | | | | direct path write temp (10) | |
| p00u | Set 2 | 15 | 595 | 570 | 25 | | | 0.00 | 7M | 55801 | 54GB | 1188 | 595MB | 53GB | 55GB | cell smart table scan (7) | |
| | | | | | | | | | | | | | | | | direct path read temp (3) | |
| | | | | | | | | | | | | | | | | direct path write temp (8) | |
| p00v | Set 2 | 16 | 595 | 569 | 26 | | | 0.00 | 7M | 55051 | 53GB | 1188 | 594MB | 53GB | 54GB | cell smart table scan (5) | |
| | | | | | | | | | | | | | | | | direct path read temp (4) | |
| | | | | | | | | | | | | | | | | direct path write temp (8) | |
=========================================================================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2917514310)
===================================================================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
===================================================================================================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 866 | +22 | 1 | 0 | | | | | . | . | 0.03 | Cpu (3) |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | 1 | | | | | | . | . | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7FC5_38C7BCF3 | | | 13 | +10 | 1 | 2 | | | 404 | 404MB | . | . | 0.07 | Cpu (8) |
| 3 | WINDOW SORT | | 37989 | 5371 | 20 | +3 | 1 | 4M | 2453 | 446MB | 891 | 446MB | 98MB | 231MB | 0.06 | Cpu (7) |
| 4 | NESTED LOOPS | | 37989 | 4984 | 5 | +4 | 1 | 4M | | | | | . | . | | |
| 5 | VIEW | | 1 | 2 | 5 | +4 | 1 | 18 | | | | | . | . | | |
| 6 | COUNT | | | | 5 | +4 | 1 | 18 | | | | | . | . | | |
| 7 | CONNECT BY WITHOUT FILTERING | | | | 5 | +4 | 1 | 18 | | | | | 2048 | . | | |
| 8 | FAST DUAL | | 1 | 2 | 1 | +4 | 1 | 1 | | | | | . | . | | |
| 9 | TABLE ACCESS STORAGE FULL | CP | 37989 | 4982 | 7 | +2 | 18 | 4M | 145 | 143MB | | | . | . | 0.04 | Cpu (4) |
| | | | | | | | | | | | | | | | | cell multiblock physical read (1) |
| 10 | LOAD TABLE CONVENTIONAL | BINTS_FCT | | | | | 1 | | | | | | . | . | | |
| 11 | PX COORDINATOR | | | | | | 33 | | | | | | . | . | | |
| 12 | PX SEND QC (RANDOM) | :TQ10011 | 353 | 44M | | | | | | | | | . | . | | |
| 13 | HASH GROUP BY | | 353 | 44M | | | | | | | | | . | . | | |
| 14 | PX RECEIVE | | 353 | 44M | | | | | | | | | . | . | | |
| 15 | PX SEND HASH | :TQ10010 | 353 | 44M | | | | | | | | | . | . | | |
| 16 | HASH GROUP BY | | 353 | 44M | | | | | | | | | . | . | | |
| 17 | HASH JOIN | | 5M | 44M | | | | | | | | | . | . | | |
| 18 | BUFFER SORT | | | | | | | | | | | | . | . | | |
| 19 | PX RECEIVE | | 4M | 30628 | | | | | | | | | . | . | | |
| 20 | PX SEND BROADCAST | :TQ10003 | 4M | 30628 | | | | | | | | | . | . | | |
| 21 | PARTITION HASH ALL | | 4M | 30628 | | | | | | | | | . | . | | |
| 22 | TABLE ACCESS STORAGE FULL | DPM | 4M | 30628 | | | | | | | | | . | . | | |
| 23 | VIEW | | 5M | 44M | | | | | | | | | . | . | | |
| 24 | WINDOW SORT PUSHED RANK | | 5M | 44M | | | | | | | | | . | . | | |
| 25 | PX RECEIVE | | 5M | 44M | | | | | | | | | . | . | | |
| 26 | PX SEND HASH | :TQ10009 | 5M | 44M | | | 16 | | | | | | . | . | | |
| 27 | WINDOW CHILD PUSHED RANK | | 5M | 44M | 2681 | +932 | 16 | 0 | | | 2M | 1TB | 6GB | 1TB | | |
| 28 | HASH JOIN RIGHT OUTER | | 5M | 39M | 2681 | +932 | 16 | 7G | | | | | 718MB | . | | |
| 29 | BUFFER SORT | | | | 7 | +926 | 16 | 4M | | | | | 356MB | . | 0.02 | Cpu (2) |
| 30 | PX RECEIVE | | 37989 | 62 | 7 | +926 | 16 | 4M | | | | | . | . | | |
| 31 | PX SEND HASH | :TQ10002 | 37989 | 62 | 5 | +926 | 1 | 3M | | | | | . | . | | |
| 32 | VIEW | | 37989 | 62 | 5 | +926 | 1 | 3M | | | | | . | . | | |
| 33 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D7FC5_38C7BCF3 | 37989 | 62 | 7 | +925 | 1 | 3M | 299 | 298MB | | | . | . | 0.05 | Cpu (1) |
| | | | | | | | | | | | | | | | | cell multiblock physical read (5) |
| 34 | PX RECEIVE | | 5M | 39M | 2681 | +932 | 16 | 12M | | | | | . | . | | |
| 35 | PX SEND HASH | :TQ10008 | 5M | 39M | 2681 | +931 | 16 | 12M | | | | | . | . | | |
| 36 | VIEW | | 5M | 39M | 2681 | +931 | 16 | 12M | | | | | . | . | | |
| 37 | SORT UNIQUE | | 5M | 39M | 2711 | +901 | 16 | 12M | 12198 | 6GB | 19016 | 9GB | 2GB | 5GB | 3.08 | Cpu (164) |
| | | | | | | | | | | | | | | | | direct path read temp (51) |
| | | | | | | | | | | | | | | | | direct path write temp (132) |
| 38 | PX RECEIVE | | | | 17 | +901 | 16 | 46M | | | | | . | . | 0.08 | Cpu (9) |
| 39 | PX SEND HASH | :TQ10007 | | | 25 | +900 | 16 | 46M | | | | | . | . | 0.21 | Cpu (24) |
| 40 | BUFFER SORT | | 7G | | 717 | +208 | 16 | 46M | 9511 | 5GB | 9504 | 5GB | 2GB | 5GB | 1.14 | Cpu (55) |
| | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | | | | direct path read temp (52) |
| | | | | | | | | | | | | | | | | direct path write temp (20) |
| 41 | UNION-ALL | | | | 695 | +208 | 16 | 46M | | | | | . | . | 0.40 | Cpu (45) |
| 42 | PX SELECTOR | | | | 1 | +208 | 16 | 2907 | | | | | . | . | | |
| 43 | TABLE ACCESS STORAGE FULL | PTFRTS_GTT | 2907 | 12 | 1 | +208 | 1 | 2907 | 2 | 312KB | | | . | . | | |
| 44 | NESTED LOOPS OUTER | | 5M | 39M | 83 | +820 | 16 | 46M | | | | | . | . | 0.03 | Cpu (3) |
| 45 | HASH JOIN OUTER | | 1M | 35M | 695 | +208 | 16 | 46M | | | | | 8GB | . | 0.76 | Cpu (85) |
| 46 | PX RECEIVE | | 1M | 34M | 567 | +208 | 16 | 46M | | | | | . | . | 0.07 | Cpu (8) |
| 47 | PX SEND HASH | :TQ10006 | 1M | 34M | 695 | +206 | 16 | 46M | | | | | . | . | 0.22 | Cpu (25) |
| 48 | HASH JOIN | | 1M | 34M | 879 | +22 | 16 | 46M | | | | | 83MB | . | 0.04 | Cpu (4) |
| 49 | PX RECEIVE | | 1150 | 9 | 185 | +22 | 16 | 18400 | | | | | . | . | | |
| 50 | PX SEND BROADCAST | :TQ10004 | 1150 | 9 | 1 | +206 | 16 | 18400 | | | | | . | . | | |
| 51 | PX SELECTOR | | | | 1 | +206 | 16 | 1150 | | | | | . | . | | |
| 52 | VIEW | index$_join$_012 | 1150 | 9 | 1 | +206 | 1 | 1150 | | | | | . | . | | |
| 53 | HASH JOIN | | | | 1 | +206 | 1 | 1150 | | | | | 5MB | . | | |
| 54 | INDEX STORAGE FAST FULL SCAN | DCT_PK | 1150 | 5 | 1 | +206 | 1 | 1150 | | | | | . | . | | |
| 55 | INDEX STORAGE FAST FULL SCAN | DCT_UK | 1150 | 6 | 1 | +206 | 1 | 1150 | | | | | . | . | | |
| 56 | HASH JOIN | | 1M | 34M | 879 | +22 | 16 | 46M | | | | | 58MB | . | 0.02 | Cpu (2) |
| 57 | PX RECEIVE | | 9 | 2 | 185 | +22 | 16 | 144 | | | | | . | . | | |
| 58 | PX SEND BROADCAST | :TQ10005 | 9 | 2 | 1 | +206 | 16 | 144 | | | | | . | . | | |
| 59 | PX SELECTOR | | | | 1 | +206 | 16 | 9 | | | | | . | . | | |
| 60 | VIEW | index$_join$_014 | 9 | 2 | 1 | +206 | 1 | 9 | | | | | . | . | | |
| 61 | HASH JOIN | | | | 1 | +206 | 1 | 9 | | | | | 4MB | . | | |
| 62 | INDEX STORAGE FAST FULL SCAN | DRT_PK | 9 | 1 | 1 | +206 | 1 | 9 | | | | | . | . | | |
| 63 | INDEX STORAGE FAST FULL SCAN | DRT_UK | 9 | 1 | 1 | +206 | 1 | 9 | | | | | . | . | | |
| 64 | HASH JOIN | | 1M | 34M | 879 | +22 | 16 | 46M | | | | | 19MB | . | 0.02 | Cpu (2) |
| 65 | BUFFER SORT | | | | 185 | +22 | 16 | 32 | | | | | 32768 | . | | |
| 66 | JOIN FILTER CREATE | :BF0000 | 2 | 1 | 185 | +22 | 16 | 32 | | | | | . | . | | |
| 67 | PX RECEIVE | | 2 | 1 | 185 | +22 | 16 | 32 | | | | | . | . | | |
| 68 | PX SEND BROADCAST | :TQ10000 | 2 | 1 | 1 | +22 | 1 | 32 | | | | | . | . | | |
| 69 | VIEW | index$_join$_010 | 2 | 1 | 1 | +22 | 1 | 2 | | | | | . | . | | |
| 70 | HASH JOIN | | | | 1 | +22 | 1 | 2 | | | | | 2MB | . | | |
| 71 | INLIST ITERATOR | | | | 1 | +22 | 1 | 2 | | | | | . | . | | |
| 72 | INDEX UNIQUE SCAN | DA_UK | 2 | | 1 | +22 | 2 | 2 | | | | | . | . | | |
| 73 | INDEX STORAGE FAST FULL SCAN | DA_PK | 2 | 1 | 1 | +22 | 1 | 10 | | | | | . | . | | |
| 74 | JOIN FILTER USE | :BF0000 | 6M | 34M | 695 | +206 | 16 | 46M | | | | | . | . | | |
| 75 | PX PARTITION HASH ALL | | 6M | 34M | 695 | +206 | 16 | 46M | | | | | . | . | | |
| 76 | TABLE ACCESS STORAGE FULL | T | 6M | 34M | 879 | +22 | 2048 | 46M | 912K | 883GB | | | 112MB | . | 82.76 | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | Cpu (9207) |
| | | | | | | | | | | | | | | | | cell smart table scan (107) |
| 77 | BUFFER SORT | | | | 48 | +773 | 16 | 59M | 6331 | 3GB | 6314 | 3GB | 2GB | 3GB | 0.52 | Cpu (34) |
| | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | | | | direct path read temp (20) |
| | | | | | | | | | | | | | | | | direct path write temp (4) |
| 78 | PX RECEIVE | | 56M | 278K | 43 | +774 | 16 | 59M | | | | | . | . | 0.04 | Cpu (4) |
| 79 | PX SEND HASH | :TQ10001 | 56M | 278K | 125 | +774 | 1 | 59M | | | | | . | . | 0.07 | Cpu (8) |
| 80 | PARTITION LIST ALL | | 56M | 278K | 125 | +774 | 1 | 59M | | | | | . | . | | |
| 81 | TABLE ACCESS STORAGE FULL | PRT | 56M | 278K | 126 | +773 | 3 | 59M | 9290 | 8GB | | | 7MB | . | 0.27 | Cpu (28) |
| | | | | | | | | | | | | | | | | cell smart table scan (2) |
| 82 | PARTITION LIST ITERATOR | | 4 | 3 | 83 | +820 | 46M | 44M | | | | | . | . | 0.69 | Cpu (78) |
| 83 | PARTITION HASH ITERATOR | | 4 | 3 | 83 | +820 | 46M | 44M | | | | | . | . | 0.42 | Cpu (47) |
| 84 | INDEX RANGE SCAN | TX_PN_PK | 4 | 3 | 84 | +819 | 46M | 44M | 673K | 5GB | | | . | . | 8.89 | gc cr grant 2-way (31) |
| | | | | | | | | | | | | | | | | gc current grant 2-way (12) |
| | | | | | | | | | | | | | | | | latch: cache buffers chains (1) |
| | | | | | | | | | | | | | | | | Cpu (517) |
| | | | | | | | | | | | | | | | | cell single block physical read (158) |
| | | | | | | | | | | | | | | | | cell single block physical read: flash cache (282) |
===================================================================================================================================================================================================================================================================================
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 3)
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_fix_control' '27268249:0')
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$29DBBA43")
MERGE(@"SEL$1" >"SEL$17")
OUTLINE_LEAF(@"SEL$14")
OUTLINE_LEAF(@"SEL$1BD7A054")
OUTLINE_LEAF(@"SEL$3594173E")
OUTLINE_LEAF(@"SEL$FE7673A9")
OUTLINE_LEAF(@"SEL$7EFF00A6")
MERGE(@"SEL$1E2BC615" >"SEL$15")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$13983ABD")
MATERIALIZE(@"SEL$17")
OUTLINE_LEAF(@"SEL$6C83AC1D")
MERGE(@"SEL$9834E3F4" >"SEL$16")
OUTLINE_LEAF(@"SEL$6BC70995")
MERGE(@"SEL$3" >"SEL$18")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$17")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$1E2BC615")
MERGE(@"SEL$12" >"SEL$13")
MERGE(@"SEL$243630DA" >"SEL$13")
OUTLINE(@"SEL$16")
OUTLINE(@"SEL$9834E3F4")
MERGE(@"SEL$4" >"SEL$5")
OUTLINE(@"SEL$18")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$243630DA")
MERGE(@"SEL$10" >"SEL$11")
MERGE(@"SEL$4ACDAEB1" >"SEL$11")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$4ACDAEB1")
MERGE(@"SEL$62874575" >"SEL$9")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$62874575")
MERGE(@"SEL$68B588A0" >"SEL$8")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$68B588A0")
MERGE(@"SEL$7" >"SEL$6")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$7")
FULL(@"INS$1" "BINTS_FCT"@"INS$1")
FULL(@"SEL$6BC70995" "M"@"SEL$3")
NO_ACCESS(@"SEL$6BC70995" "S"@"SEL$3")
LEADING(@"SEL$6BC70995" "M"@"SEL$3" "S"@"SEL$3")
USE_HASH(@"SEL$6BC70995" "S"@"SEL$3")
PQ_DISTRIBUTE(@"SEL$6BC70995" "S"@"SEL$3" BROADCAST NONE)
GBY_PUSHDOWN(@"SEL$6BC70995")
USE_HASH_AGGREGATION(@"SEL$6BC70995")
NO_ACCESS(@"SEL$6C83AC1D" "T"@"SEL$5")
NO_ACCESS(@"SEL$6C83AC1D" "C"@"SEL$4")
LEADING(@"SEL$6C83AC1D" "T"@"SEL$5" "C"@"SEL$4")
USE_HASH(@"SEL$6C83AC1D" "C"@"SEL$4")
PQ_DISTRIBUTE(@"SEL$6C83AC1D" "C"@"SEL$4" HASH HASH)
SWAP_JOIN_INPUTS(@"SEL$6C83AC1D" "C"@"SEL$4")
PQ_DISTRIBUTE_WINDOW(@"SEL$6C83AC1D" 2)
FULL(@"SEL$13983ABD" "T1"@"SEL$13983ABD")
PQ_CONCURRENT_UNION(@"SET$1")
INDEX_JOIN(@"SEL$7EFF00A6" "A"@"SEL$6" ("DA"."A_CD") ("DA"."A_PK"))
FULL(@"SEL$7EFF00A6" "FPT"@"SEL$7")
INDEX_JOIN(@"SEL$7EFF00A6" "R"@"SEL$9" ("DR_TYP"."RT_PK") ("DR_TYP"."RT_CD" "DR_TYP"."RS_CD"))
INDEX_JOIN(@"SEL$7EFF00A6" "CT"@"SEL$8" ("DC_TYP"."CT_PK") ("DC_TYP"."CT_CD" "DC_TYP"."PL_CD"))
FULL(@"SEL$7EFF00A6" "PRT"@"SEL$12")
INDEX(@"SEL$7EFF00A6" "TP"@"SEL$10" ("TP"."DS_NO" "TP"."PN" "TP"."PAN_CD" "TP"."TC_CD" "TP"."PM_FK"))
LEADING(@"SEL$7EFF00A6" "A"@"SEL$6" "FPT"@"SEL$7" "R"@"SEL$9" "CT"@"SEL$8" "PRT"@"SEL$12" "TP"@"SEL$10")
USE_HASH(@"SEL$7EFF00A6" "FPT"@"SEL$7")
USE_HASH(@"SEL$7EFF00A6" "R"@"SEL$9")
USE_HASH(@"SEL$7EFF00A6" "CT"@"SEL$8")
USE_HASH(@"SEL$7EFF00A6" "PRT"@"SEL$12")
USE_NL(@"SEL$7EFF00A6" "TP"@"SEL$10")
PQ_DISTRIBUTE(@"SEL$7EFF00A6" "FPT"@"SEL$7" BROADCAST NONE)
PX_JOIN_FILTER(@"SEL$7EFF00A6" "FPT"@"SEL$7")
PQ_DISTRIBUTE(@"SEL$7EFF00A6" "R"@"SEL$9" NONE BROADCAST)
PQ_DISTRIBUTE(@"SEL$7EFF00A6" "CT"@"SEL$8" NONE BROADCAST)
PQ_DISTRIBUTE(@"SEL$7EFF00A6" "PRT"@"SEL$12" HASH HASH)
PQ_DISTRIBUTE(@"SEL$7EFF00A6" "TP"@"SEL$10" NONE BROADCAST)
SWAP_JOIN_INPUTS(@"SEL$7EFF00A6" "R"@"SEL$9")
SWAP_JOIN_INPUTS(@"SEL$7EFF00A6" "CT"@"SEL$8")
FULL(@"SEL$14" "PTFRTS_GTT"@"SEL$14")
NO_ACCESS(@"SEL$29DBBA43" "X"@"SEL$1")
FULL(@"SEL$29DBBA43" "CP"@"SEL$1")
LEADING(@"SEL$29DBBA43" "X"@"SEL$1" "CP"@"SEL$1")
USE_NL(@"SEL$29DBBA43" "CP"@"SEL$1")
NO_CONNECT_BY_FILTERING(@"SEL$2")
CONNECT_BY_COMBINE_SW(@"SEL$2")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :B1 (DATE): 11/02/2022 00:00:00
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter(("CP"."CPCL_NBR"-"X"."RN">=4 AND INTERNAL_FUNCTION("CP"."PN_CD")))
17 - access("S"."PM_FK"="M"."PM_PK")
23 - filter("S"."CP_RANK"=1)
24 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DS_NO","T"."C_NBR" ORDER BY INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )<=1)
27 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DS_NO","T"."C_NBR" ORDER BY INTERNAL_FUNCTION("C"."CPCL_NBR") DESC )<=1)
28 - access("C"."PANL_CNT"=LENGTH("T"."C_NBR") AND "C"."BNK_ID_NB_4"=SUBSTR("T"."C_NBR",1,4))
filter(("T"."C_NBR"<="C"."HIGH_VALUE" AND "T"."C_NBR">="C"."LOW_VALUE"))
45 - access("PRT"."PM_FK"="FPT"."PM_FK" AND "PRT"."TC_CD"="FPT"."TC_CD" AND "PRT"."DS_NO"="FPT"."DS_NO")
48 - access("FPT"."CT_FK"="CT"."CT_PK")
53 - access(ROWID=ROWID)
56 - access("FPT"."RT_FK"="R"."RT_PK")
61 - access(ROWID=ROWID)
64 - access("FPT"."A_FK"="A"."A_PK")
69 - filter(("A"."A_CD"='1' OR "A"."A_CD"='2'))
70 - access(ROWID=ROWID)
72 - access(("A"."A_CD"='1' OR "A"."A_CD"='2'))
76 - storage((COALESCE("FPT"."DEL_FLG",'N')='N' AND TRUNC(INTERNAL_FUNCTION("FPT"."H_DT_TIM"))=:B1 AND SYS_OP_BLOOM_FILTER(:BF0000,"FPT"."A_FK")))
filter((COALESCE("FPT"."DEL_FLG",'N')='N' AND TRUNC(INTERNAL_FUNCTION("FPT"."H_DT_TIM"))=:B1 AND SYS_OP_BLOOM_FILTER(:BF0000,"FPT"."A_FK")))
81 - storage("PRT"."PN_CD"='F')
filter("PRT"."PN_CD"='F')
84 - access("TP"."DS_NO"="FPT"."DS_NO" AND "TP"."PAN_CD"='F' AND "TP"."TC_CD"="FPT"."TC_CD" AND
"TP"."PM_FK"="FPT"."PM_FK")
filter(("TP"."PM_FK"="FPT"."PM_FK" AND "TP"."TC_CD"="FPT"."TC_CD" AND "TP"."PN_CD"='F'))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment