Skip to content

Instantly share code, notes, and snippets.

@databasetech0073

databasetech0073/gist:714263bce477f624763f757e457cb861 Secret

Created November 12, 2022 20:36
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/714263bce477f624763f757e457cb861 to your computer and use it in GitHub Desktop.
Save databasetech0073/714263bce477f624763f757e457cb861 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.CRD_PFX 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.PAN_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.PAN_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
********************* Execution which was running fine in 19.11 version **********************
Global Information
------------------------------
Status : DONE
Instance ID : 2
SQL Execution ID : 33554457
Execution Started : 10/04/2022 06:50:25
First Refresh Time : 10/04/2022 06:50:35
Last Refresh Time : 10/04/2022 07:50:45
Duration : 3620s
PLSQL Entry Ids (Object/Subprogram) : 25723986,1
PLSQL Current Ids (Object/Subprogram) : 25723986,1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B1 | 1 | DATE | 02/10/2018 00:00:00 |
========================================================================================================================
Global Stats
====================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
====================================================================================================================================
| 17561 | 13564 | 3968 | 0.01 | 8.31 | 16 | 4.18 | 298M | 1M | 662GB | 289K | 50GB | 71.43% |
====================================================================================================================================
Parallel Execution Details (DOP=16 , Servers Allocated=32)
=================================================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | (sample #) |
=================================================================================================================================================================================================================
| PX Coordinator | QC | | 1642 | 1619 | 12 | 0.01 | 3.34 | 3.84 | 3.72 | 115M | 96720 | 1GB | 418 | 104MB | -13.64% | gc cr multi block request (1) |
| | | | | | | | | | | | | | | | | gc current multi block request (1) |
| | | | | | | | | | | | | | | | | os thread startup (3) |
| | | | | | | | | | | | | | | | | log file switch completion (1) |
| | | | | | | | | | | | | | | | | enq: FB - contention (1) |
| | | | | | | | | | | | | | | | | sql_id: g0p6hupu5my6d (1) |
| | | | | | | | | | | | | | | | | cell list of blocks physical read (2) |
| | | | | | | | | | | | | | | | | cell multiblock physical read (2) |
| | | | | | | | | | | | | | | | | cell single block physical read (5) |
| p000 | Set 1 | 1 | 228 | 32 | 196 | | 0.16 | 0.00 | | 5M | 52768 | 40GB | 10533 | 2GB | 83.69% | cell smart table scan (138) |
| | | | | | | | | | | | | | | | | direct path read temp (46) |
| | | | | | | | | | | | | | | | | direct path write temp (7) |
| p001 | Set 1 | 2 | 229 | 31 | 198 | | 0.17 | 0.00 | | 5M | 50741 | 38GB | 10517 | 2GB | 82.88% | cell smart table scan (134) |
| | | | | | | | | | | | | | | | | direct path read temp (56) |
| | | | | | | | | | | | | | | | | direct path write temp (9) |
| p002 | Set 1 | 3 | 230 | 31 | 198 | | 0.16 | 0.00 | | 5M | 52598 | 40GB | 10528 | 2GB | 83.63% | cell smart table scan (133) |
| | | | | | | | | | | | | | | | | direct path read temp (57) |
| | | | | | | | | | | | | | | | | direct path write temp (8) |
| p003 | Set 1 | 4 | 227 | 31 | 195 | | 0.15 | 0.00 | | 5M | 52646 | 40GB | 10522 | 2GB | 83.63% | cell smart table scan (137) |
| | | | | | | | | | | | | | | | | direct path read temp (47) |
| | | | | | | | | | | | | | | | | direct path write temp (6) |
| p004 | Set 1 | 5 | 226 | 32 | 194 | | 0.14 | 0.07 | | 5M | 52605 | 40GB | 10500 | 2GB | 83.63% | cell smart table scan (130) |
| | | | | | | | | | | | | | | | | direct path read temp (54) |
| | | | | | | | | | | | | | | | | direct path write temp (8) |
| p005 | Set 1 | 6 | 232 | 31 | 200 | | 0.16 | 0.07 | | 5M | 52900 | 40GB | 10536 | 2GB | 83.74% | cell smart table scan (136) |
| | | | | | | | | | | | | | | | | direct path read temp (48) |
| | | | | | | | | | | | | | | | | direct path write temp (12) |
| p006 | Set 1 | 7 | 229 | 32 | 197 | | 0.16 | | | 5M | 52534 | 40GB | 10519 | 2GB | 83.61% | cell smart table scan (136) |
| | | | | | | | | | | | | | | | | direct path read temp (54) |
| | | | | | | | | | | | | | | | | direct path write temp (4) |
| p007 | Set 1 | 8 | 228 | 31 | 197 | | 0.15 | | | 5M | 53783 | 41GB | 10505 | 2GB | 84.08% | cell smart table scan (135) |
| | | | | | | | | | | | | | | | | direct path read temp (48) |
| | | | | | | | | | | | | | | | | direct path write temp (10) |
| p008 | Set 1 | 9 | 231 | 31 | 200 | | 0.16 | | | 5M | 52571 | 40GB | 10551 | 2GB | 83.61% | cell smart table scan (134) |
| | | | | | | | | | | | | | | | | direct path read temp (48) |
| | | | | | | | | | | | | | | | | direct path write temp (12) |
| p009 | Set 1 | 10 | 230 | 32 | 198 | | 0.17 | | | 5M | 52772 | 40GB | 10521 | 2GB | 83.66% | cell smart table scan (135) |
| | | | | | | | | | | | | | | | | direct path read temp (49) |
| | | | | | | | | | | | | | | | | direct path write temp (15) |
| p010 | Set 1 | 11 | 232 | 32 | 200 | | 0.16 | | | 5M | 52107 | 39GB | 10473 | 2GB | 83.47% | cell smart table scan (139) |
| | | | | | | | | | | | | | | | | direct path read temp (56) |
| | | | | | | | | | | | | | | | | direct path write temp (13) |
| p011 | Set 1 | 12 | 229 | 31 | 197 | | 0.15 | | | 5M | 53881 | 41GB | 10492 | 2GB | 84.10% | cell smart table scan (134) |
| | | | | | | | | | | | | | | | | direct path read temp (48) |
| | | | | | | | | | | | | | | | | direct path write temp (7) |
| p012 | Set 1 | 13 | 224 | 32 | 192 | | 0.16 | | | 5M | 52631 | 40GB | 10550 | 2GB | 83.61% | cell smart table scan (131) |
| | | | | | | | | | | | | | | | | direct path read temp (45) |
| | | | | | | | | | | | | | | | | direct path write temp (11) |
| p013 | Set 1 | 14 | 225 | 32 | 193 | | 0.15 | 0.00 | | 5M | 52207 | 39GB | 10525 | 2GB | 83.47% | cell smart table scan (133) |
| | | | | | | | | | | | | | | | | direct path read temp (52) |
| | | | | | | | | | | | | | | | | direct path write temp (9) |
| p014 | Set 1 | 15 | 227 | 32 | 195 | | 0.16 | | | 5M | 52980 | 40GB | 10488 | 2GB | 83.77% | cell smart table scan (137) |
| | | | | | | | | | | | | | | | | direct path read temp (50) |
| | | | | | | | | | | | | | | | | direct path write temp (7) |
| p015 | Set 1 | 16 | 227 | 31 | 196 | | 0.17 | | | 5M | 50182 | 38GB | 10494 | 2GB | 82.70% | cell smart table scan (134) |
| | | | | | | | | | | | | | | | | direct path read temp (56) |
| | | | | | | | | | | | | | | | | direct path write temp (11) |
| p016 | Set 2 | 1 | 328 | 298 | 29 | | 0.15 | 0.78 | | 6M | 12139 | 724MB | 3368 | 671MB | -96.08% | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | direct path read temp (26) |
| p017 | Set 2 | 2 | 1168 | 1109 | 59 | | 0.00 | 0.70 | 0.02 | 6M | 19588 | 2GB | 8542 | 2GB | -100.00% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | direct path read temp (46) |
| | | | | | | | | | | | | | | | | direct path write temp (5) |
| p018 | Set 2 | 3 | 334 | 299 | 34 | | 0.15 | 0.85 | | 6M | 14772 | 1GB | 4590 | 901MB | -96.08% | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | direct path read temp (28) |
| | | | | | | | | | | | | | | | | direct path write temp (3) |
| p019 | Set 2 | 4 | 1039 | 959 | 79 | | 0.16 | 0.82 | | 6M | 27631 | 3GB | 13732 | 3GB | -100.00% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | direct path read temp (65) |
| | | | | | | | | | | | | | | | | direct path write temp (3) |
| p020 | Set 2 | 5 | 849 | 819 | 29 | | 0.16 | 0.73 | 0.05 | 6M | 12411 | 727MB | 3364 | 673MB | -96.08% | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | direct path read temp (17) |
| | | | | | | | | | | | | | | | | direct path write temp (1) |
| p021 | Set 2 | 6 | 513 | 470 | 42 | | 0.14 | 0.92 | | 6M | 17066 | 1GB | 5859 | 1GB | -96.08% | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | direct path read temp (31) |
| | | | | | | | | | | | | | | | | direct path write temp (2) |
| p022 | Set 2 | 7 | 372 | 334 | 37 | | 0.15 | 0.73 | | 6M | 14813 | 1GB | 5010 | 1GB | -96.08% | gc cr block busy (1) |
| | | | | | | | | | | | | | | | | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (7) |
| | | | | | | | | | | | | | | | | direct path read temp (30) |
| | | | | | | | | | | | | | | | | direct path write temp (2) |
| p023 | Set 2 | 8 | 697 | 632 | 64 | | 0.16 | 0.74 | | 6M | 22337 | 2GB | 10407 | 2GB | -100.00% | cell single block physical read (8) |
| | | | | | | | | | | | | | | | | direct path read temp (50) |
| | | | | | | | | | | | | | | | | direct path write temp (6) |
| p024 | Set 2 | 9 | 1122 | 1070 | 51 | | 0.16 | 0.85 | | 6M | 18632 | 1GB | 7342 | 1GB | -96.08% | cell single block physical read (10) |
| | | | | | | | | | | | | | | | | direct path read temp (44) |
| | | | | | | | | | | | | | | | | direct path write temp (6) |
| p025 | Set 2 | 10 | 1767 | 1684 | 81 | | 0.16 | 0.82 | 0.38 | 6M | 27926 | 3GB | 14010 | 3GB | -100.00% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | direct path read temp (72) |
| | | | | | | | | | | | | | | | | direct path write temp (3) |
| | | | | | | | | | | | | | | | | read by other session (1) |
| p026 | Set 2 | 11 | 822 | 773 | 48 | | 0.16 | 0.78 | | 6M | 16989 | 1GB | 6657 | 1GB | -96.08% | gc current grant 2-way (2) |
| | | | | | | | | | | | | | | | | cell single block physical read (6) |
| | | | | | | | | | | | | | | | | direct path read temp (38) |
| | | | | | | | | | | | | | | | | direct path write temp (4) |
| p027 | Set 2 | 12 | 505 | 461 | 43 | | 0.16 | 0.72 | | 6M | 16014 | 1GB | 6201 | 1GB | -96.08% | cell single block physical read (8) |
| | | | | | | | | | | | | | | | | direct path read temp (34) |
| | | | | | | | | | | | | | | | | direct path write temp (3) |
| | | | | | | | | | | | | | | | | read by other session (1) |
| p028 | Set 2 | 13 | 1002 | 941 | 59 | | 0.16 | 0.76 | | 6M | 19785 | 2GB | 8537 | 2GB | -100.00% | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | cell single block physical read (9) |
| | | | | | | | | | | | | | | | | direct path read temp (55) |
| | | | | | | | | | | | | | | | | direct path write temp (1) |
| p029 | Set 2 | 14 | 631 | 587 | 43 | | 0.16 | 0.89 | | 6M | 17071 | 1GB | 6004 | 1GB | -96.08% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (5) |
| | | | | | | | | | | | | | | | | direct path read temp (30) |
| | | | | | | | | | | | | | | | | direct path write temp (6) |
| p030 | Set 2 | 15 | 644 | 588 | 55 | | 0.16 | 0.70 | | 6M | 19257 | 2GB | 8526 | 2GB | -100.00% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (8) |
| | | | | | | | | | | | | | | | | direct path read temp (46) |
| | | | | | | | | | | | | | | | | direct path write temp (5) |
| p031 | Set 2 | 16 | 471 | 416 | 54 | | 0.16 | 0.73 | | 6M | 18998 | 2GB | 8125 | 2GB | -100.00% | gc current grant 2-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read (9) |
| | | | | | | | | | | | | | | | | direct path read temp (32) |
| | | | | | | | | | | | | | | | | direct path write temp (6) |
=================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2056239125)
=================================================================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | (Max) | (Max) | (%) | (# samples) |
=================================================================================================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 2844 | +196 | 1 | 0 | | | | | | | | 0.02 | Cpu (3) |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | 1 | | | | | | | | | | |
| 2 | LOAD AS SELECT | | | | 8 | +3 | 1 | 1 | | | | | | 543K | | 0.01 | Cpu (2) |
| 3 | WINDOW SORT | | 10647 | 1653 | 7 | +4 | 1 | 1M | | | | | | 74M | | 0.01 | Cpu (2) |
| 4 | VIEW | | 10647 | 1652 | 1 | +10 | 1 | 1M | | | | | | | | | |
| 5 | NESTED LOOPS | | 10647 | 1652 | 1 | +10 | 1 | 1M | | | | | | | | | |
| 6 | VIEW | | 1 | 2 | 1 | +10 | 1 | 18 | | | | | | | | | |
| 7 | COUNT | | | | 1 | +10 | 1 | 18 | | | | | | | | | |
| 8 | CONNECT BY WITHOUT FILTERING | | | | 1 | +10 | 1 | 18 | | | | | | | | | |
| 9 | FAST DUAL | | 1 | 2 | 1 | +10 | 1 | 1 | | | | | | | | | |
| 10 | TABLE ACCESS STORAGE FULL | CP | 10647 | 1650 | 10 | +1 | 18 | 1M | | | | | | | | 0.01 | Cpu (2) |
| 11 | LOAD TABLE CONVENTIONAL | | | | 1618 | +2003 | 1 | 0 | 12331 | 96MB | | | | | | 9.14 | gc current multi block request (1) |
| | | | | | | | | | | | | | | | | | log file switch completion (1) |
| | | | | | | | | | | | | | | | | | Cpu (1593) |
| | | | | | | | | | | | | | | | | | enq: FB - contention (1) |
| | | | | | | | | | | | | | | | | | cell single block physical read (4) |
| 12 | PX COORDINATOR | | | | 3614 | +7 | 33 | 12M | | | | | | | | 0.09 | os thread startup (3) |
| | | | | | | | | | | | | | | | | | Cpu (13) |
| 13 | PX SEND QC (RANDOM) | :TQ10013 | 359 | 3M | 1618 | +2003 | 16 | 12M | | | | | | | | 0.01 | Cpu (2) |
| 14 | HASH GROUP BY | | 359 | 3M | 1619 | +2002 | 16 | 12M | | | | | | 2G | | 0.05 | Cpu (8) |
| 15 | PX RECEIVE | | 359 | 3M | 1417 | +2002 | 16 | 12M | | | | | | | | 0.01 | Cpu (1) |
| 16 | PX SEND HASH | :TQ10012 | 359 | 3M | 1 | +2002 | 16 | 12M | | | | | | | | 0.04 | Cpu (7) |
| 17 | HASH GROUP BY | | 359 | 3M | 24 | +1979 | 16 | 12M | | | | | | 2G | | 0.23 | Cpu (41) |
| 18 | HASH JOIN | | 10M | 3M | 24 | +1979 | 16 | 37M | | | | | | 447M | | 0.07 | Cpu (12) |
| 19 | BUFFER SORT | | | | 9 | +1972 | 16 | 4M | | | | | | 151M | | | |
| 20 | PX RECEIVE | | 4M | 30619 | 9 | +1972 | 16 | 4M | | | | | | | | | |
| 21 | PX SEND HASH | :TQ10006 | 4M | 30619 | 32 | +1970 | 1 | 4M | | | | | | | | | |
| 22 | PARTITION HASH ALL | | 4M | 30619 | 32 | +1970 | 1 | 4M | | | | | | | | | |
| 23 | TABLE ACCESS STORAGE FULL | DPM | 4M | 30619 | 32 | +1970 | 1024 | 4M | 83652 | 665MB | | | | | | 0.03 | Cpu (3) |
| | | | | | | | | | | | | | | | | | cell list of blocks physical read (2) |
| | | | | | | | | | | | | | | | | | cell single block physical read (1) |
| 24 | PX RECEIVE | | 9M | 3M | 24 | +1979 | 16 | 37M | | | | | | | | 0.05 | Cpu (8) |
| 25 | PX SEND HASH | :TQ10011 | 9M | 3M | 25 | +1978 | 16 | 37M | | | | | | | | 0.05 | Cpu (8) |
| 26 | VIEW | | 9M | 3M | 25 | +1978 | 16 | 37M | | | | | | | | 0.12 | Cpu (21) |
| 27 | WINDOW SORT PUSHED RANK | | 9M | 3M | 110 | +1893 | 16 | 70M | 162K | 19GB | 120K | 19GB | | 2G | 11G | 4.68 | Cpu (234) |
| | | | | | | | | | | | | | | | | | direct path read temp (478) |
| | | | | | | | | | | | | | | | | | direct path write temp (108) |
| 28 | PX RECEIVE | | 9M | 3M | 86 | +1893 | 16 | 70M | | | | | | | | 0.11 | Cpu (19) |
| 29 | PX SEND HASH | :TQ10010 | 9M | 3M | 77 | +1893 | 16 | 70M | | | | | | | | 0.25 | Cpu (43) |
| 30 | WINDOW CHILD PUSHED RANK | | 9M | 3M | 1752 | +218 | 16 | 70M | 146K | 19GB | 101K | 19GB | | 4G | 10G | 41.97 | Cpu (6748) |
| | | | | | | | | | | | | | | | | | direct path read temp (553) |
| | | | | | | | | | | | | | | | | | direct path write temp (47) |
| 31 | HASH JOIN RIGHT OUTER | | 9M | 2M | 1676 | +218 | 16 | 7G | | | | | | 217M | | 23.08 | Cpu (4041) |
| 32 | BUFFER SORT | | | | 4 | +216 | 16 | 1M | | | | | | 97M | | | |
| 33 | PX RECEIVE | | 10647 | 24 | 4 | +216 | 16 | 1M | | | | | | | | | |
| 34 | PX SEND HASH | :TQ10005 | 10647 | 24 | 1633 | +216 | 1 | 1M | | | | | | | | 0.01 | Cpu (2) |
| 35 | VIEW | | 10647 | 24 | 1632 | +217 | 1 | 1M | | | | | | | | | |
| 36 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FDA1E71E_D71F1BDE | 10647 | 24 | 1632 | +217 | 1 | 1M | 106 | 104MB | | | | | | 0.01 | cell multiblock physical read (1) |
| 37 | PX RECEIVE | | 9M | 2M | 1676 | +218 | 16 | 37M | | | | | | | | 0.05 | Cpu (9) |
| 38 | PX SEND HASH | :TQ10009 | 9M | 2M | 1677 | +217 | 16 | 37M | | | | | | | | 0.13 | Cpu (23) |
| 39 | VIEW | | 9M | 2M | 1677 | +217 | 16 | 37M | | | | | | | | | |
| 40 | SORT UNIQUE | | 9M | 2M | 1701 | +193 | 16 | 37M | 48155 | 7GB | 47883 | 7GB | | 2G | 4G | 2.84 | Cpu (121) |
| | | | | | | | | | | | | | | | | | direct path read temp (336) |
| | | | | | | | | | | | | | | | | | direct path write temp (41) |
| 41 | PX RECEIVE | | | | 15 | +193 | 16 | 37M | | | | | | | | 0.07 | Cpu (12) |
| 42 | PX SEND HASH | :TQ10008 | | | 23 | +193 | 16 | 37M | | | | | | | | 0.13 | Cpu (22) |
| 43 | BUFFER SORT | | 7G | | 197 | +19 | 16 | 37M | 33844 | 4GB | 18803 | 4GB | | 2G | 4G | 0.77 | Cpu (35) |
| | | | | | | | | | | | | | | | | | direct path read temp (91) |
| | | | | | | | | | | | | | | | | | direct path write temp (9) |
| 44 | UNION-ALL | | | | 177 | +19 | 16 | 37M | | | | | | | | 0.18 | Cpu (32) |
| 45 | BUFFER SORT | | | | 3 | +18 | 16 | 3M | | | | | | 443M | | | |
| 46 | PX RECEIVE | | | | 3 | +18 | 16 | 3M | | | | | | | | | |
| 47 | PX SEND ROUND-ROBIN | :TQ10003 | | | 4 | +17 | 1 | 3M | | | | | | | | 0.01 | Cpu (1) |
| 48 | TABLE ACCESS STORAGE FULL | PTFRTS_GTT | 3M | 12005 | 3 | +18 | 1 | 3M | | | | | | | | | |
| 49 | NESTED LOOPS OUTER | | 6M | 2M | 36 | +160 | 16 | 33M | | | | | | | | | |
| 50 | HASH JOIN OUTER | | 2M | 2M | 178 | +18 | 16 | 33M | | | | | | 6G | | 0.42 | Cpu (74) |
| 51 | PX RECEIVE | | 2M | 2M | 138 | +19 | 16 | 33M | | | | | | | | 0.06 | Cpu (11) |
| 52 | PX SEND HASH | :TQ10007 | 2M | 2M | 176 | +18 | 16 | 33M | | | | | | | | 0.13 | Cpu (22) |
| 53 | HASH JOIN | | 2M | 2M | 181 | +13 | 16 | 33M | | | | | | 27M | | 0.03 | Cpu (6) |
| 54 | BUFFER SORT | | | | 6 | +13 | 16 | 18352 | | | | | | 918K | | | |
| 55 | PX RECEIVE | | 1147 | 9 | 6 | +13 | 16 | 18352 | | | | | | | | | |
| 56 | PX SEND BROADCAST | :TQ10000 | 1147 | 9 | 1 | +12 | 1 | 18352 | | | | | | | | | |
| 57 | VIEW | index$_join$_012 | 1147 | 9 | 1 | +12 | 1 | 1147 | | | | | | | | | |
| 58 | HASH JOIN | | | | 1 | +12 | 1 | 1147 | | | | | | 2M | | | |
| 59 | INDEX STORAGE FAST FULL SCAN | DCT_PK | 1147 | 5 | 1 | +12 | 1 | 1147 | | | | | | | | | |
| 60 | INDEX STORAGE FAST FULL SCAN | DCT_UK | 1147 | 6 | 1 | +12 | 1 | 1147 | | | | | | | | | |
| 61 | HASH JOIN | | 2M | 2M | 181 | +13 | 16 | 33M | | | | | | 20M | | 0.04 | Cpu (7) |
| 62 | BUFFER SORT | | | | 6 | +13 | 16 | 144 | | | | | | 32768 | | | |
| 63 | PX RECEIVE | | 9 | 2 | 6 | +13 | 16 | 144 | | | | | | | | | |
| 64 | PX SEND BROADCAST | :TQ10001 | 9 | 2 | 1 | +12 | 1 | 144 | | | | | | | | | |
| 65 | VIEW | index$_join$_014 | 9 | 2 | 1 | +12 | 1 | 9 | | | | | | | | | |
| 66 | HASH JOIN | | | | 1 | +12 | 1 | 9 | | | | | | 1M | | | |
| 67 | INDEX STORAGE FAST FULL SCAN | DRT_PK | 9 | 1 | 1 | +12 | 1 | 9 | | | | | | | | | |
| 68 | INDEX STORAGE FAST FULL SCAN | DRT_UK | 9 | 1 | 1 | +12 | 1 | 9 | | | | | | | | | |
| 69 | HASH JOIN | | 2M | 2M | 181 | +13 | 16 | 33M | | | | | | 14M | | 0.03 | Cpu (6) |
| 70 | BUFFER SORT | | | | 6 | +13 | 16 | 32 | | | | | | 32768 | | | |
| 71 | PX RECEIVE | | 2 | 1 | 6 | +13 | 16 | 32 | | | | | | | | | |
| 72 | PX SEND BROADCAST | :TQ10002 | 2 | 1 | 1 | +12 | 1 | 32 | | | | | | | | | |
| 73 | VIEW | index$_join$_010 | 2 | 1 | 1 | +12 | 1 | 2 | | | | | | | | | |
| 74 | HASH JOIN | | | | 1 | +12 | 1 | 2 | | | | | | 883K | | | |
| 75 | INLIST ITERATOR | | | | 1 | +12 | 1 | 2 | | | | | | | | | |
| 76 | INDEX UNIQUE SCAN | DA_UK | 2 | | 1 | +12 | 2 | 2 | | | | | | | | | |
| 77 | INDEX STORAGE FAST FULL SCAN | DA_PK | 2 | 1 | 1 | +12 | 1 | 10 | | | | | | | | | |
| 78 | PX PARTITION HASH ALL | | 8M | 2M | 181 | +13 | 16 | 39M | | | | | | | | | |
| 79 | TABLE ACCESS STORAGE FULL | T | 8M | 2M | 184 | +10 | 2048 | 39M | 630K | 610GB | | | 99.66% | 118M | | 12.41 | Cpu (17) |
| | | | | | | | | | | | | | | | | | cell smart table scan (2156) |
| 80 | BUFFER SORT | | | | 7 | +155 | 16 | 5M | | | | | | 412M | | 0.01 | Cpu (1) |
| 81 | PX RECEIVE | | 46M | 184K | 6 | +155 | 16 | 5M | | | | | | | | | |
| 82 | PX SEND HASH | :TQ10004 | 46M | 184K | 36 | +156 | 1 | 5M | | | | | | | | | |
| 83 | PARTITION LIST ALL | | 46M | 184K | 36 | +156 | 1 | 5M | | | | | | | | | |
| 84 | TABLE ACCESS STORAGE FULL | TP_RT | 46M | 184K | 38 | +154 | 3 | 5M | 525 | 514MB | | | | | | 0.02 | gc cr multi block request (1) |
| | | | | | | | | | | | | | | | | | Cpu (2) |
| | | | | | | | | | | | | | | | | | cell multiblock physical read (1) |
| 85 | PARTITION LIST ITERATOR | | 4 | 3 | 36 | +160 | 33M | 33M | | | | | | | | 0.14 | Cpu (24) |
| 86 | PARTITION HASH ITERATOR | | 4 | 3 | 36 | +160 | 33M | 33M | | | | | | | | 0.15 | Cpu (26) |
| 87 | INDEX RANGE SCAN | TX_PN_PK | 4 | 3 | 37 | +159 | 33M | 33M | 116K | 906MB | | | | | | 2.32 | gc cr block busy (1) |
| | | | | | | | | | | | | | | | | | gc cr grant 2-way (8) |
| | | | | | | | | | | | | | | | | | gc current grant 2-way (6) |
| | | | | | | | | | | | | | | | | | Cpu (274) |
| | | | | | | | | | | | | | | | | | cell single block physical read (116) |
| | | | | | | | | | | | | | | | | | read by other session (2) |
=================================================================================================================================================================================================================================================================================
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('optimizer_dynamic_sampling' 3)
OPT_PARAM('star_transformation_enabled' 'true')
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"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")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$13983ABD")
OUTLINE_LEAF(@"SEL$6C83AC1D")
MERGE(@"SEL$9834E3F4")
OUTLINE_LEAF(@"SEL$6BC70995")
MERGE(@"SEL$3")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$1E2BC615")
MERGE(@"SEL$12")
MERGE(@"SEL$243630DA")
OUTLINE(@"SEL$17")
OUTLINE(@"SEL$16")
OUTLINE(@"SEL$9834E3F4")
MERGE(@"SEL$4")
OUTLINE(@"SEL$18")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$243630DA")
MERGE(@"SEL$10")
MERGE(@"SEL$4ACDAEB1")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$4ACDAEB1")
MERGE(@"SEL$62874575")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$62874575")
MERGE(@"SEL$68B588A0")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$68B588A0")
MERGE(@"SEL$7")
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" HASH HASH)
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")
FULL(@"SEL$13983ABD" "T1"@"SEL$13983ABD")
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)
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$17" "from$_subquery$_005"@"SEL$17")
NO_ACCESS(@"SEL$1" "X"@"SEL$1")
FULL(@"SEL$1" "CP"@"SEL$1")
LEADING(@"SEL$1" "X"@"SEL$1" "CP"@"SEL$1")
USE_NL(@"SEL$1" "CP"@"SEL$1")
NO_CONNECT_BY_FILTERING(@"SEL$2")
CONNECT_BY_COMBINE_SW(@"SEL$2")
END_OUTLINE_DATA
*/
********************* Execution which is failing in 19.15 version with high tempspace consumption**********************
Global Information
------------------------------
Status : DONE (ERROR)
Instance ID : 1
SQL Execution ID : 16777233
Execution Started : 10/06/2022 12:03:33
First Refresh Time : 10/06/2022 12:03:39
Last Refresh Time : 10/06/2022 12:18:47
Duration : 914s
PLSQL Entry Ids (Object/Subprogram) : 25723986,1
PLSQL Current Ids (Object/Subprogram) : 25723986,1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B1 | 1 | DATE | 10/05/2022 00:00:00 |
========================================================================================================================
Global Stats
========================================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes |
========================================================================================================================================================
| 40830 | 33370 | 7451 | 0.07 | 1.32 | 6.98 | 0.17 | 269M | 1M | 904GB | 3M | 1TB | 891GB | 4TB |
========================================================================================================================================================
Parallel Execution Details (DOP=16 , Servers Allocated=32)
===================================================================================================================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Offload | Offload | Wait Events | |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes | (sample #) | |
===================================================================================================================================================================================================================================================================================
| PX Coordinator | QC | | 61 | 49 | 12 | 0.07 | 0.00 | 0.28 | | 2M | 6355 | 4GB | 1274 | 835MB | 3GB | 5GB | cell multiblock physical read (5) | |
| | | | | | | | | | | | | | | | | | cell smart table scan (1) | |
| | | | | | | | | | | | | | | | | | direct path read temp (5) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01c | Set 1 | 1 | 802 | 779 | 23 | | | 0.00 | | 9M | 68338 | 66GB | 1197 | 599MB | 65GB | 66GB | ASM IO for non-blocking poll (1) | |
| | | | | | | | | | | | | | | | | | cell smart table scan (11) | |
| | | | | | | | | | | | | | | | | | direct path read temp (8) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01d | Set 1 | 2 | 664 | 643 | 21 | | | 0.01 | | 7M | 60061 | 58GB | 1197 | 598MB | 57GB | 58GB | cell smart table scan (10) | |
| | | | | | | | | | | | | | | | | | direct path read temp (9) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01e | Set 1 | 3 | 657 | 635 | 22 | | | 0.01 | 0.12 | 7M | 59641 | 57GB | 1195 | 598MB | 57GB | 58GB | cell smart table scan (6) | |
| | | | | | | | | | | | | | | | | | direct path read temp (6) | |
| | | | | | | | | | | | | | | | | | direct path write temp (4) | |
| p01f | Set 1 | 4 | 630 | 610 | 20 | | | 0.00 | | 7M | 57234 | 55GB | 1197 | 599MB | 55GB | 56GB | cell smart table scan (8) | |
| | | | | | | | | | | | | | | | | | direct path read temp (6) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01g | Set 1 | 5 | 637 | 617 | 20 | | | 0.00 | 0.05 | 7M | 57474 | 55GB | 1195 | 598MB | 55GB | 56GB | cell smart table scan (11) | |
| | | | | | | | | | | | | | | | | | direct path read temp (6) | |
| | | | | | | | | | | | | | | | | | direct path write temp (2) | |
| p01h | Set 1 | 6 | 615 | 595 | 20 | | | 0.00 | | 7M | 54957 | 53GB | 1197 | 598MB | 52GB | 54GB | cell smart table scan (13) | |
| | | | | | | | | | | | | | | | | | direct path read temp (5) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01i | Set 1 | 7 | 630 | 608 | 22 | | | 0.00 | | 7M | 56643 | 55GB | 1199 | 599MB | 54GB | 55GB | cell smart table scan (15) | |
| | | | | | | | | | | | | | | | | | direct path read temp (8) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01j | Set 1 | 8 | 638 | 619 | 19 | | | 0.00 | | 7M | 58257 | 56GB | 1197 | 598MB | 56GB | 57GB | cell smart table scan (6) | |
| | | | | | | | | | | | | | | | | | direct path read temp (4) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01k | Set 1 | 9 | 628 | 608 | 20 | | | | | 7M | 57537 | 55GB | 1197 | 599MB | 55GB | 56GB | cell smart table scan (7) | |
| | | | | | | | | | | | | | | | | | direct path read temp (4) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01l | Set 1 | 10 | 628 | 607 | 20 | | | 0.00 | | 7M | 56535 | 54GB | 1195 | 598MB | 54GB | 55GB | cell smart table scan (10) | |
| | | | | | | | | | | | | | | | | | direct path read temp (6) | |
| | | | | | | | | | | | | | | | | | direct path write temp (2) | |
| p01m | Set 1 | 11 | 616 | 597 | 19 | | | 0.00 | | 7M | 56525 | 54GB | 1195 | 598MB | 54GB | 55GB | cell smart table scan (5) | |
| | | | | | | | | | | | | | | | | | direct path read temp (6) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01n | Set 1 | 12 | 636 | 617 | 19 | | | 0.01 | | 7M | 59653 | 57GB | 1196 | 598MB | 57GB | 58GB | cell smart table scan (3) | |
| | | | | | | | | | | | | | | | | | direct path read temp (5) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01o | Set 1 | 13 | 637 | 617 | 19 | | | 0.00 | | 7M | 57147 | 55GB | 1197 | 598MB | 55GB | 56GB | cell smart table scan (7) | |
| | | | | | | | | | | | | | | | | | direct path read temp (7) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01p | Set 1 | 14 | 634 | 615 | 19 | | | | | 7M | 57479 | 55GB | 1197 | 598MB | 55GB | 56GB | cell smart table scan (10) | |
| | | | | | | | | | | | | | | | | | direct path read temp (4) | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | |
| p01q | Set 1 | 15 | 617 | 596 | 21 | | | 0.00 | | 7M | 55259 | 53GB | 1197 | 598MB | 53GB | 54GB | ASM IO for non-blocking poll (1) | |
| | | | | | | | | | | | | | | | | | cell smart table scan (10) | |
| | | | | | | | | | | | | | | | | | direct path read temp (7) | |
| | | | | | | | | | | | | | | | | | direct path write temp (3) | |
| p01r | Set 1 | 16 | 637 | 617 | 20 | | | | | 7M | 57704 | 56GB | 1197 | 599MB | 55GB | 56GB | cell smart table scan (6) | |
| | | | | | | | | | | | | | | | | | direct path read temp (6) | |
| | | | | | | | | | | | | | | | | | direct path write temp (2) | |
| p01s | Set 2 | 1 | 1508 | 1154 | 353 | | 0.00 | 0.42 | | 9M | 11840 | 387MB | 148K | 72GB | . | 145GB | NaN% | cell single block physical read: flash cache (5) |
| | | | | | | | | | | | | | | | | | | direct path read temp (3) |
| p01t | Set 2 | 2 | 2166 | 1641 | 525 | | 0.23 | 0.41 | | 9M | 11740 | 386MB | 224K | 110GB | . | 219GB | NaN% | cell single block physical read: flash cache (6) |
| | | | | | | | | | | | | | | | | | | direct path read temp (5) |
| p01u | Set 2 | 3 | 1419 | 1069 | 350 | | | 0.41 | | 9M | 11927 | 388MB | 135K | 66GB | . | 132GB | NaN% | cell single block physical read: flash cache (4) |
| | | | | | | | | | | | | | | | | | | direct path read temp (5) |
| | | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p01v | Set 2 | 4 | 2197 | 1675 | 522 | | 0.00 | 0.42 | | 9M | 12036 | 389MB | 217K | 106GB | . | 212GB | NaN% | cell single block physical read: flash cache (6) |
| | | | | | | | | | | | | | | | | | | direct path read temp (4) |
| p01w | Set 2 | 5 | 1995 | 1516 | 478 | | 0.10 | 0.41 | | 9M | 11463 | 384MB | 194K | 95GB | . | 190GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (5) |
| | | | | | | | | | | | | | | | | | | direct path read temp (4) |
| p01x | Set 2 | 6 | 2475 | 1890 | 584 | | 0.00 | 0.44 | | 9M | 12006 | 388MB | 240K | 118GB | . | 236GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (5) |
| | | | | | | | | | | | | | | | | | | direct path read temp (5) |
| | | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p01y | Set 2 | 7 | 1257 | 956 | 301 | | 0.00 | 0.42 | | 9M | 11931 | 388MB | 118K | 58GB | . | 116GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (7) |
| | | | | | | | | | | | | | | | | | | direct path read temp (6) |
| | | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p01z | Set 2 | 8 | 1934 | 1472 | 461 | | 0.21 | 0.43 | | 9M | 12067 | 388MB | 187K | 93GB | . | 186GB | NaN% | cell single block physical read: flash cache (2) |
| | | | | | | | | | | | | | | | | | | direct path read temp (1) |
| p020 | Set 2 | 9 | 1624 | 1222 | 402 | | 0.00 | 0.41 | | 9M | 11488 | 385MB | 154K | 76GB | . | 151GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (5) |
| | | | | | | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p021 | Set 2 | 10 | 1521 | 1163 | 357 | | 0.23 | 0.41 | | 9M | 11617 | 385MB | 146K | 72GB | . | 143GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (3) |
| | | | | | | | | | | | | | | | | | | direct path read temp (5) |
| | | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p022 | Set 2 | 11 | 1198 | 920 | 278 | | 0.10 | 0.42 | | 9M | 11933 | 388MB | 116K | 57GB | . | 114GB | NaN% | cell single block physical read: flash cache (6) |
| | | | | | | | | | | | | | | | | | | direct path read temp (6) |
| p023 | Set 2 | 12 | 2499 | 1920 | 578 | | 0.01 | 0.46 | | 9M | 12284 | 390MB | 253K | 124GB | . | 247GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (4) |
| | | | | | | | | | | | | | | | | | | direct path read temp (4) |
| p024 | Set 2 | 13 | 1940 | 1501 | 439 | | 0.10 | 0.41 | | 9M | 11561 | 385MB | 193K | 94GB | . | 189GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (4) |
| | | | | | | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p025 | Set 2 | 14 | 3093 | 2465 | 627 | | 0.00 | 0.39 | | 9M | 11658 | 386MB | 325K | 159GB | . | 318GB | NaN% | cell single block physical read: flash cache (8) |
| | | | | | | | | | | | | | | | | | | direct path read temp (3) |
| | | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p026 | Set 2 | 15 | 1401 | 1076 | 325 | | 0.10 | 0.41 | | 9M | 11536 | 385MB | 140K | 68GB | . | 137GB | NaN% | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (4) |
| | | | | | | | | | | | | | | | | | | direct path read temp (8) |
| | | | | | | | | | | | | | | | | | | direct path write temp (2) |
| p027 | Set 2 | 16 | 2239 | 1700 | 538 | | 0.23 | 0.41 | | 9M | 11957 | 388MB | 226K | 110GB | . | 221GB | NaN% | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | | | | | | cell single block physical read: flash cache (3) |
| | | | | | | | | | | | | | | | | | | direct path read temp (4) |
===================================================================================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3957617523)
===================================================================================================================================================================================================================================================================================
| 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 | | | | | | 1 | | | | | | . | . | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | 1 | | | | | | . | . | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6D1B_38C7BCF3 | | | 25 | +12 | 1 | 2 | | | 397 | 397MB | . | . | 0.07 | Cpu (8) |
| 3 | WINDOW SORT | | 30888 | 4547 | 35 | +2 | 1 | 4M | 2412 | 439MB | 877 | 439MB | 98MB | 226MB | 0.14 | Cpu (10) |
| | | | | | | | | | | | | | | | | direct path read temp (5) |
| | | | | | | | | | | | | | | | | direct path write temp (1) |
| 4 | VIEW | | 30888 | 4152 | 3 | +6 | 1 | 4M | | | | | . | . | | |
| 5 | NESTED LOOPS | | 30888 | 4152 | 5 | +6 | 1 | 4M | | | | | . | . | | |
| 6 | VIEW | | 1 | 2 | 5 | +6 | 1 | 18 | | | | | . | . | | |
| 7 | COUNT | | | | 5 | +6 | 1 | 18 | | | | | . | . | | |
| 8 | CONNECT BY WITHOUT FILTERING | | | | 5 | +6 | 1 | 18 | | | | | 2048 | . | | |
| 9 | FAST DUAL | | 1 | 2 | 1 | +6 | 1 | 1 | | | | | . | . | | |
| 10 | TABLE ACCESS STORAGE FULL | CP | 30888 | 4150 | 8 | +1 | 18 | 4M | 147 | 141MB | | | . | . | 0.04 | Cpu (4) |
| 11 | LOAD TABLE CONVENTIONAL | BINTS_FACT | | | | | 1 | | | | | | . | . | | |
| 12 | PX COORDINATOR | | | | | | 33 | | | | | | . | . | | |
| 13 | PX SEND QC (RANDOM) | :TQ10013 | 364 | 4M | | | | | | | | | . | . | | |
| 14 | HASH GROUP BY | | 364 | 4M | | | | | | | | | . | . | | |
| 15 | PX RECEIVE | | 364 | 4M | | | | | | | | | . | . | | |
| 16 | PX SEND HASH | :TQ10012 | 364 | 4M | | | | | | | | | . | . | | |
| 17 | HASH GROUP BY | | 364 | 4M | | | | | | | | | . | . | | |
| 18 | HASH JOIN | | 10M | 4M | | | | | | | | | . | . | | |
| 19 | BUFFER SORT | | | | | | | | | | | | . | . | | |
| 20 | PX RECEIVE | | 4M | 30627 | | | | | | | | | . | . | | |
| 21 | PX SEND HASH | :TQ10006 | 4M | 30627 | | | | | | | | | . | . | | |
| 22 | PARTITION HASH ALL | | 4M | 30627 | | | | | | | | | . | . | | |
| 23 | TABLE ACCESS STORAGE FULL | DPM | 4M | 30627 | | | | | | | | | . | . | | |
| 24 | PX RECEIVE | | 9M | 4M | | | | | | | | | . | . | | |
| 25 | PX SEND HASH | :TQ10011 | 9M | 4M | | | | | | | | | . | . | | |
| 26 | VIEW | | 9M | 4M | | | | | | | | | . | . | | |
| 27 | WINDOW SORT PUSHED RANK | | 9M | 4M | | | | | | | | | . | . | | |
| 28 | PX RECEIVE | | 9M | 4M | | | | | | | | | . | . | | |
| 29 | PX SEND HASH | :TQ10010 | 9M | 4M | | | 16 | | | | | | . | . | | |
| 30 | WINDOW CHILD PUSHED RANK | | 9M | 4M | 3112 | +915 | 16 | 0 | | | 3M | 1TB | 9GB | 1TB | 0.06 | Cpu (7) |
| 31 | HASH JOIN RIGHT OUTER | | 9M | 3M | 3112 | +915 | 16 | 10G | | | | | 648MB | . | 0.08 | Cpu (9) |
| 32 | BUFFER SORT | | | | 8 | +908 | 16 | 4M | | | | | 350MB | . | 0.01 | Cpu (1) |
| 33 | PX RECEIVE | | 30888 | 66 | 7 | +909 | 16 | 4M | | | | | . | . | | |
| 34 | PX SEND HASH | :TQ10005 | 30888 | 66 | 7 | +908 | 1 | 4M | | | | | . | . | 0.01 | Cpu (1) |
| 35 | VIEW | | 30888 | 66 | 7 | +908 | 1 | 4M | | | | | . | . | | |
| 36 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D6D1B_38C7BCF3 | 30888 | 66 | 7 | +908 | 1 | 4M | 396 | 395MB | | | . | . | 0.05 | Cpu (1) |
| | | | | | | | | | | | | | | | | cell multiblock physical read (5) |
| 37 | PX RECEIVE | | 9M | 3M | 3112 | +915 | 16 | 18M | | | | | . | . | | |
| 38 | PX SEND HASH | :TQ10009 | 9M | 3M | 3113 | +914 | 16 | 18M | | | | | . | . | | |
| 39 | VIEW | | 9M | 3M | 3113 | +914 | 16 | 18M | | | | | . | . | | |
| 40 | SORT UNIQUE | | 9M | 3M | 3143 | +884 | 16 | 18M | 13636 | 7GB | 19145 | 9GB | 2GB | 5GB | 2.78 | Cpu (191) |
| | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (2) |
| | | | | | | | | | | | | | | | | direct path read temp (97) |
| | | | | | | | | | | | | | | | | direct path write temp (24) |
| 41 | PX RECEIVE | | | | 16 | +884 | 16 | 47M | | | | | . | . | 0.07 | Cpu (8) |
| 42 | PX SEND HASH | :TQ10008 | | | 25 | +883 | 16 | 47M | | | | | . | . | 0.29 | Cpu (33) |
| 43 | BUFFER SORT | | 7G | | 681 | +227 | 16 | 47M | 9574 | 5GB | 9570 | 5GB | 2GB | 5GB | 1.36 | Cpu (69) |
| | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | | | | direct path read temp (71) |
| | | | | | | | | | | | | | | | | direct path write temp (13) |
| 44 | UNION-ALL | | | | 659 | +227 | 16 | 47M | | | | | . | . | 0.30 | Cpu (34) |
| 45 | BUFFER SORT | | | | 3 | +225 | 16 | 3M | | | | | 427MB | . | | |
| 46 | PX RECEIVE | | | | 3 | +225 | 16 | 3M | | | | | . | . | | |
| 47 | PX SEND ROUND-ROBIN | :TQ10003 | | | 16 | +224 | 1 | 3M | | | | | . | . | 0.02 | Cpu (2) |
| 48 | TABLE ACCESS STORAGE FULL | PTFRTS_GTT | 3M | 12152 | 16 | +224 | 1 | 3M | 351 | 348MB | | | 7MB | . | | |
| 49 | NESTED LOOPS OUTER | | 6M | 3M | 51 | +835 | 16 | 43M | | | | | . | . | 0.02 | Cpu (2) |
| 50 | HASH JOIN OUTER | | 2M | 3M | 659 | +227 | 16 | 43M | | | | | 7GB | . | 0.74 | Cpu (83) |
| 51 | PX RECEIVE | | 2M | 2M | 587 | +227 | 16 | 43M | | | | | . | . | 0.18 | Cpu (20) |
| 52 | PX SEND HASH | :TQ10007 | 2M | 2M | 659 | +225 | 16 | 43M | | | | | . | . | 0.20 | Cpu (23) |
| 53 | HASH JOIN | | 2M | 2M | 853 | +31 | 16 | 43M | | | | | 83MB | . | 0.03 | Cpu (3) |
| 54 | BUFFER SORT | | | | 1 | +31 | 16 | 18400 | | | | | 896KB | . | | |
| 55 | PX RECEIVE | | 1150 | 9 | 1 | +31 | 16 | 18400 | | | | | . | . | | |
| 56 | PX SEND BROADCAST | :TQ10000 | 1150 | 9 | 1 | +36 | 1 | 18400 | | | | | . | . | | |
| 57 | VIEW | index$_join$_012 | 1150 | 9 | 1 | +36 | 1 | 1150 | | | | | . | . | | |
| 58 | HASH JOIN | | | | 1 | +36 | 1 | 1150 | | | | | 5MB | . | | |
| 59 | INDEX STORAGE FAST FULL SCAN | DCT_PK | 1150 | 5 | 1 | +36 | 1 | 1150 | 8 | 520KB | | | . | . | | |
| 60 | INDEX STORAGE FAST FULL SCAN | DCT_UK | 1150 | 6 | 1 | +36 | 1 | 1150 | 13 | 752KB | | | . | . | | |
| 61 | HASH JOIN | | 2M | 2M | 853 | +31 | 16 | 43M | | | | | 59MB | . | 0.03 | Cpu (3) |
| 62 | BUFFER SORT | | | | 1 | +31 | 16 | 144 | | | | | 32768 | . | | |
| 63 | PX RECEIVE | | 9 | 2 | 1 | +31 | 16 | 144 | | | | | . | . | | |
| 64 | PX SEND BROADCAST | :TQ10001 | 9 | 2 | 1 | +36 | 1 | 144 | | | | | . | . | | |
| 65 | VIEW | index$_join$_014 | 9 | 2 | 1 | +36 | 1 | 9 | | | | | . | . | | |
| 66 | HASH JOIN | | | | 1 | +36 | 1 | 9 | | | | | 4MB | . | | |
| 67 | INDEX STORAGE FAST FULL SCAN | DRT_PK | 9 | 1 | 1 | +36 | 1 | 9 | 1 | 8192 | | | . | . | | |
| 68 | INDEX STORAGE FAST FULL SCAN | DRT_UK | 9 | 1 | 1 | +36 | 1 | 9 | 2 | 16384 | | | . | . | | |
| 69 | HASH JOIN | | 2M | 2M | 853 | +31 | 16 | 43M | | | | | 34MB | . | 0.04 | Cpu (5) |
| 70 | BUFFER SORT | | | | 1 | +31 | 16 | 32 | | | | | 32768 | . | | |
| 71 | PX RECEIVE | | 2 | 1 | 1 | +31 | 16 | 32 | | | | | . | . | | |
| 72 | PX SEND BROADCAST | :TQ10002 | 2 | 1 | 1 | +36 | 1 | 32 | | | | | . | . | | |
| 73 | VIEW | index$_join$_010 | 2 | 1 | 1 | +36 | 1 | 2 | | | | | . | . | | |
| 74 | HASH JOIN | | | | 1 | +36 | 1 | 2 | | | | | 2MB | . | | |
| 75 | INLIST ITERATOR | | | | 1 | +36 | 1 | 2 | | | | | . | . | | |
| 76 | INDEX UNIQUE SCAN | DA_UK | 2 | | 1 | +36 | 2 | 2 | 1 | 8192 | | | . | . | | |
| 77 | INDEX STORAGE FAST FULL SCAN | DA_PK | 2 | 1 | 1 | +36 | 1 | 10 | 1 | 8192 | | | . | . | | |
| 78 | PX PARTITION HASH ALL | | 9M | 2M | 713 | +171 | 16 | 53M | | | | | . | . | | |
| 79 | TABLE ACCESS STORAGE FULL | T | 9M | 2M | 854 | +30 | 2048 | 53M | 917K | 887GB | | | 112MB | . | 87.53 | Cpu (9743) |
| | | | | | | | | | | | | | | | | cell smart table scan (138) |
| 80 | BUFFER SORT | | | | 23 | +813 | 16 | 20M | | | | | 1GB | . | 0.11 | Cpu (12) |
| 81 | PX RECEIVE | | 57M | 281K | 23 | +813 | 16 | 20M | | | | | . | . | 0.01 | Cpu (1) |
| 82 | PX SEND HASH | :TQ10004 | 57M | 281K | 70 | +812 | 1 | 20M | | | | | . | . | 0.02 | Cpu (2) |
| 83 | PARTITION LIST ALL | | 57M | 281K | 70 | +812 | 1 | 20M | | | | | . | . | | |
| 84 | TABLE ACCESS STORAGE FULL | TP_RT | 57M | 281K | 70 | +812 | 3 | 20M | 3009 | 3GB | | | 7MB | . | 0.18 | Cpu (19) |
| | | | | | | | | | | | | | | | | cell smart table scan (1) |
| 85 | PARTITION LIST ITERATOR | | 3 | 3 | 51 | +835 | 43M | 41M | | | | | . | . | 0.45 | Cpu (51) |
| 86 | PARTITION HASH ITERATOR | | 3 | 3 | 51 | +835 | 43M | 41M | | | | | . | . | 0.37 | Cpu (42) |
| 87 | INDEX RANGE SCAN | TX_PN_PK | 3 | 3 | 51 | +835 | 43M | 41M | 179K | 1GB | | | . | . | 4.82 | gc cr grant 2-way (8) |
| | | | | | | | | | | | | | | | | Cpu (459) |
| | | | | | | | | | | | | | | | | cell single block physical read: flash cache (77) |
===================================================================================================================================================================================================================================================================================
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('19.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 3)
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_fix_control' '27268249:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"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$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" HASH HASH)
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")
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)
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$17" "from$_subquery$_005"@"SEL$17")
NO_ACCESS(@"SEL$1" "X"@"SEL$1")
FULL(@"SEL$1" "CP"@"SEL$1")
LEADING(@"SEL$1" "X"@"SEL$1" "CP"@"SEL$1")
USE_NL(@"SEL$1" "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): 06/07/2022 00:00:00
*************************** Display cursor plan from version 11.2.0.4 ************************
Plan hash value: 2917514310
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 43M(100)| | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7FC5_38C7BCF3 | | | | | | | | | | |
| 3 | WINDOW SORT | | 37989 | 1558K| 2104K| 5371 (1)| 00:00:01 | | | | | |
| 4 | NESTED LOOPS | | 37989 | 1558K| | 4984 (1)| 00:00:01 | | | | | |
| 5 | VIEW | | 1 | 13 | | 2 (0)| 00:00:01 | | | | | |
| 6 | COUNT | | | | | | | | | | | |
| 7 | CONNECT BY WITHOUT FILTERING | | | | | | | | | | | |
| 8 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | | | | |
|* 9 | TABLE ACCESS STORAGE FULL | CRD_PFX | 37989 | 1075K| | 4982 (1)| 00:00:01 | | | | | |
| 10 | LOAD TABLE CONVENTIONAL | BINTS_FCT | | | | | | | | | | |
| 11 | PX COORDINATOR | | | | | | | | | | | |
| 12 | PX SEND QC (RANDOM) | :TQ10011 | 353 | 92133 | | 43M (7)| 00:28:26 | | | Q1,11 | P->S | QC (RAND) |
| 13 | HASH GROUP BY | | 353 | 92133 | | 43M (7)| 00:28:26 | | | Q1,11 | PCWP | |
| 14 | PX RECEIVE | | 353 | 92133 | | 43M (7)| 00:28:26 | | | Q1,11 | PCWP | |
| 15 | PX SEND HASH | :TQ10010 | 353 | 92133 | | 43M (7)| 00:28:26 | | | Q1,10 | P->P | HASH |
| 16 | HASH GROUP BY | | 353 | 92133 | | 43M (7)| 00:28:26 | | | Q1,10 | PCWP | |
|* 17 | HASH JOIN | | 4952K| 1232M| | 43M (7)| 00:28:26 | | | Q1,10 | PCWP | |
| 18 | BUFFER SORT | | | | | | | | | Q1,10 | PCWC | |
| 19 | PX RECEIVE | | 4225K| 40M| | 30628 (1)| 00:00:02 | | | Q1,10 | PCWP | |
| 20 | PX SEND BROADCAST | :TQ10003 | 4225K| 40M| | 30628 (1)| 00:00:02 | | | | S->P | BROADCAST |
| 21 | PARTITION HASH ALL | | 4225K| 40M| | 30628 (1)| 00:00:02 | 1 | 1024 | | | |
| 22 | TABLE ACCESS STORAGE FULL | DPM | 4225K| 40M| | 30628 (1)| 00:00:02 | 1 | 1024 | | | |
|* 23 | VIEW | | 4952K| 1185M| | 43M (7)| 00:28:25 | | | Q1,10 | PCWP | |
|* 24 | WINDOW SORT PUSHED RANK | | 4952K| 20G| 37G| 43M (7)| 00:28:25 | | | Q1,10 | PCWP | |
| 25 | PX RECEIVE | | 4952K| 20G| | 43M (7)| 00:28:25 | | | Q1,10 | PCWP | |
| 26 | PX SEND HASH | :TQ10009 | 4952K| 20G| | 43M (7)| 00:28:25 | | | Q1,09 | P->P | HASH |
|* 27 | WINDOW CHILD PUSHED RANK | | 4952K| 20G| | 43M (7)| 00:28:25 | | | Q1,09 | PCWP | |
|* 28 | HASH JOIN RIGHT OUTER | | 4952K| 20G| | 39M (8)| 00:25:40 | | | Q1,09 | PCWP | |
| 29 | BUFFER SORT | | | | | | | | | Q1,09 | PCWC | |
| 30 | PX RECEIVE | | 37989 | 146M| | 62 (2)| 00:00:01 | | | Q1,09 | PCWP | |
| 31 | PX SEND HASH | :TQ10002 | 37989 | 146M| | 62 (2)| 00:00:01 | | | | S->P | HASH |
| 32 | VIEW | | 37989 | 146M| | 62 (2)| 00:00:01 | | | | | |
| 33 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D7FC5_38C7BCF3 | 37989 | 1558K| | 62 (2)| 00:00:01 | | | | | |
| 34 | PX RECEIVE | | 4952K| 1638M| | 39M (8)| 00:25:40 | | | Q1,09 | PCWP | |
| 35 | PX SEND HASH | :TQ10008 | 4952K| 1638M| | 39M (8)| 00:25:40 | | | Q1,08 | P->P | HASH |
| 36 | VIEW | | 4952K| 1638M| | 39M (8)| 00:25:40 | | | Q1,08 | PCWP | |
| 37 | SORT UNIQUE | | 4952K| 1690M| 1406M| 39M (8)| 00:25:40 | | | Q1,08 | PCWP | |
| 38 | PX RECEIVE | | | | | | | | | Q1,08 | PCWP | |
| 39 | PX SEND HASH | :TQ10007 | | | | | | | | Q1,07 | P->P | HASH |
| 40 | BUFFER SORT | | 6600M| 559G| | | | | | Q1,07 | PCWP | |
| 41 | UNION-ALL | | | | | | | | | Q1,07 | PCWP | |
| 42 | PX SELECTOR | | | | | | | | | Q1,07 | PCWP | |
| 43 | TABLE ACCESS STORAGE FULL | PTFRTS_GTT | 2907 | 244K| | 12 (0)| 00:00:01 | | | Q1,07 | PCWP | |
| 44 | NESTED LOOPS OUTER | | 4949K| 844M| | 38M (8)| 00:25:22 | | | Q1,07 | PCWP | |
|* 45 | HASH JOIN OUTER | | 1396K| 175M| | 34M (9)| 00:22:38 | | | Q1,07 | PCWP | |
| 46 | PX RECEIVE | | 1396K| 113M| | 34M (9)| 00:22:27 | | | Q1,07 | PCWP | |
| 47 | PX SEND HASH | :TQ10006 | 1396K| 113M| | 34M (9)| 00:22:27 | | | Q1,06 | P->P | HASH |
|* 48 | HASH JOIN | | 1396K| 113M| | 34M (9)| 00:22:27 | | | Q1,06 | PCWP | |
| 49 | PX RECEIVE | | 1150 | 8050 | | 9 (0)| 00:00:01 | | | Q1,06 | PCWP | |
| 50 | PX SEND BROADCAST | :TQ10004 | 1150 | 8050 | | 9 (0)| 00:00:01 | | | Q1,04 | S->P | BROADCAST |
| 51 | PX SELECTOR | | | | | | | | | Q1,04 | SCWC | |
| 52 | VIEW | index$_join$_012 | 1150 | 8050 | | 9 (0)| 00:00:01 | | | Q1,04 | SCWC | |
|* 53 | HASH JOIN | | | | | | | | | Q1,04 | SCWC | |
| 54 | AN INDEX STORAGE FAST FULL SC | DCT_PK | 1150 | 8050 | | 5 (0)| 00:00:01 | | | Q1,04 | SCWP | |
| 55 | AN INDEX STORAGE FAST FULL SC | DCT_UK | 1150 | 8050 | | 6 (0)| 00:00:01 | | | Q1,04 | SCWP | |
|* 56 | HASH JOIN | | 1396K| 103M| | 34M (9)| 00:22:27 | | | Q1,06 | PCWP | |
| 57 | PX RECEIVE | | 9 | 36 | | 2 (0)| 00:00:01 | | | Q1,06 | PCWP | |
| 58 | PX SEND BROADCAST | :TQ10005 | 9 | 36 | | 2 (0)| 00:00:01 | | | Q1,05 | S->P | BROADCAST |
| 59 | PX SELECTOR | | | | | | | | | Q1,05 | SCWC | |
| 60 | VIEW | index$_join$_014 | 9 | 36 | | 2 (0)| 00:00:01 | | | Q1,05 | SCWC | |
|* 61 | HASH JOIN | | | | | | | | | Q1,05 | SCWC | |
| 62 | CAN INDEX STORAGE FAST FULL S | DRT_PK | 9 | 36 | | 1 (0)| 00:00:01 | | | Q1,05 | SCWP | |
| 63 | CAN INDEX STORAGE FAST FULL S | DRT_UK | 9 | 36 | | 1 (0)| 00:00:01 | | | Q1,05 | SCWP | |
|* 64 | HASH JOIN | | 1396K| 98M| | 34M (9)| 00:22:27 | | | Q1,06 | PCWP | |
| 65 | BUFFER SORT | | | | | | | | | Q1,06 | PCWC | |
| 66 | JOIN FILTER CREATE | :BF0000 | 2 | 10 | | 1 (0)| 00:00:01 | | | Q1,06 | PCWP | |
| 67 | PX RECEIVE | | 2 | 10 | | 1 (0)| 00:00:01 | | | Q1,06 | PCWP | |
| 68 | PX SEND BROADCAST | :TQ10000 | 2 | 10 | | 1 (0)| 00:00:01 | | | | S->P | BROADCAST |
|* 69 | VIEW | index$_join$_010 | 2 | 10 | | 1 (0)| 00:00:01 | | | | | |
|* 70 | HASH JOIN | | | | | | | | | | | |
| 71 | INLIST ITERATOR | | | | | | | | | | | |
|* 72 | INDEX UNIQUE SCAN | DA_UK | 2 | 10 | | 0 (0)| | | | | | |
| 73 | SCAN INDEX STORAGE FAST FULL | DA_PK | 2 | 10 | | 1 (0)| 00:00:01 | | | | | |
| 74 | JOIN FILTER USE | :BF0000 | 6282K| 413M| | 34M (9)| 00:22:27 | | | Q1,06 | PCWP | |
| 75 | PX PARTITION HASH ALL | | 6282K| 413M| | 34M (9)| 00:22:27 | 1 | 1024 | Q1,06 | PCWC | |
|* 76 | TABLE ACCESS STORAGE FULL | T | 6282K| 413M| | 34M (9)| 00:22:27 | 1 | 2048 | Q1,06 | PCWP | |
| 77 | BUFFER SORT | | | | | | | | | Q1,07 | PCWC | |
| 78 | PX RECEIVE | | 55M| 2508M| | 278K (1)| 00:00:11 | | | Q1,07 | PCWP | |
| 79 | PX SEND HASH | :TQ10001 | 55M| 2508M| | 278K (1)| 00:00:11 | | | | S->P | HASH |
| 80 | PARTITION LIST ALL | | 55M| 2508M| | 278K (1)| 00:00:11 | 1 | 3 | | | |
|* 81 | TABLE ACCESS STORAGE FULL | PRT | 55M| 2508M| | 278K (1)| 00:00:11 | 1 | 3 | | | |
| 82 | PARTITION LIST ITERATOR | | 4 | 188 | | 3 (0)| 00:00:01 | KEY | KEY | Q1,07 | PCWP | |
| 83 | PARTITION HASH ITERATOR | | 4 | 188 | | 3 (0)| 00:00:01 | KEY | KEY | Q1,07 | PCWP | |
|* 84 | INDEX RANGE SCAN | TX_PN_PK | 4 | 188 | | 3 (0)| 00:00:01 | | | Q1,07 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*+
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"."PAN_CD"='F')
filter("PRT"."PAN_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"."PAN_CD"='F'))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment