Hi, We have moved to version 19.0.0.0 Of Oracle from 11G. Below query is running for ~10minutes on 19c where as when i tried running the query with hint optimizer_features_enable('11.2.0.1'), i see it got finished in minutes. I have captured sql monitor for both the versions and below are the ones.
I do see a note section in 19c version stating "this is an adaptive plan" but not quite sure if that's the one affecting. Also sql monitor is not showing all the activities properly in the 19C path but i checked the ASH and the sql monitor also giving some hints around the plan its clear the plan line id- 52 is the one i.e scanning table PAY_ITM ~34k times where its spending all the time and its mainly ON CPU only. It looks like some strange transformation on the 19C version causing the sub optimal plan. Wanted to understand what exact thing on 19C is affecting the query badly and if any other query will be affected by this?
Sample Query Text:-
SELECT......
FROM ( SELECT EID, MAX (OP_CNT) AS OP_CNT, MAX (CLS_CNT) AS CLS_CNT, MAX (DLP) AS DLP,......
FROM ( SELECT EID, MAX (OP_CNT) AS OP_CNT, MAX (CLS_CNT) AS CLS_CNT,.....
FROM ( SELECT EID,COUNT (DT_CR) AS OP_CNT, NULL AS CLS_CNT, MAX (DT_CR) AS DLP,........
FROM PAY WHERE STAT = 'AAAAAA' AND CCD = 'XXX' AND ECD = 'CC'
GROUP BY EID
)
GROUP BY EID
UNION ALL
SELECT EID, NULL AS OP_CNT, NULL AS CLS_CNT, NULL AS DLP, NULL AS POAMT,NULL AS CPAMT,........
FROM ( SELECT CI.CID AS EID, SUM (A.A_AMT) AS TPF_TOT, 0 AS DWO_TOT
FROM ( SELECT MAX (AD_ID) AS MA_ID FROM PAY_DTL WHERE OT_CD = 'CCCLLL' AND A_CCD = 'XXX'
GROUP BY OID) MA, PAY_DTL A, PAY_ITM CI
WHERE MA_ID = A.AD_ID AND A.AD_FLG = 'F' AND ( A.A_CODE = 'TTTTTTTTT' OR A.A_CODE = 'FFFFFFFFFFFF')
AND A.OID = CI.ITM_ID
GROUP BY CI.CID
UNION
SELECT CI.CID AS EID, 0 AS TPF_TOT, SUM (A.A_AMT) AS DWO_TOT
FROM ( SELECT MAX (AD_ID) AS MA_ID
FROM PAY_DTL
WHERE OT_CD = 'CCCLLL'
AND A_CCD = 'XXX'
GROUP BY OID) MA,
PAY_DTL A,
PAY_ITM CI
WHERE MA_ID = A.AD_ID AND A.AD_FLG = 'F' AND A.A_CODE = 'DDDDDD' AND A.OID = CI.ITM_ID
GROUP BY CI.CID)
GROUP BY EID)
GROUP BY EID) PAYINFO,
E_PORT PC,
COMP C,
USERS_TAB U
WHERE PAYINFO.EID = PC.EID AND PC.ECD = 'CC' AND PC.EID = C.EID AND C.ECD = 'CC' AND C.UID = U.UID(+)
AND ( C.UID = 9999
OR C.UID IN (SELECT B.UID FROM USERS_TAB A, USERS_TAB B WHERE A.UID = B.BKP_UID AND B.UA_FLG = 'N' AND A.UID = 9999)
)
AND PAYINFO.EID IN ( SELECT EID
FROM (SELECT CID AS EID FROM PAY_ITM WHERE C_STAT = 'OOO'
UNION ALL
SELECT EID FROM PAY WHERE STAT = 'AAAAAA'
UNION ALL
SELECT CID AS EID FROM PAY_OFF WHERE sO_STAT = 'OOO'
)
WHERE EID IS NOT NULL
GROUP BY EID);
| ******************** Sql Monitor with Optimizer version 11.2.0.1 | | | | | | |
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 8j7n7jkwvshjm
SQL Execution ID : 33554432
Execution Started : 07/11/2020 15:20:28
First Refresh Time : 07/11/2020 15:20:28
Last Refresh Time : 07/11/2020 15:20:29
Duration : 1s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 22
Global Stats
================================================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
================================================================================================================================================
| 0.94 | 0.84 | 0.01 | 0.00 | 0.09 | 22 | 79466 | 45 | 36MB | 15MB | 36MB | 249KB | 99.32% |
================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1985169742)
===============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 2 | +0 | 1 | 310 | | | . | | |
| 1 | FILTER | | | | 2 | +0 | 1 | 310 | | | . | | |
| 2 | NESTED LOOPS OUTER | | 1 | 23436 | 2 | +0 | 1 | 3503 | | | . | | |
| 3 | NESTED LOOPS | | 1 | 23435 | 2 | +0 | 1 | 3503 | | | . | | |
| 4 | NESTED LOOPS | | 2 | 23434 | 2 | +0 | 1 | 3503 | | | . | | |
| 5 | HASH JOIN RIGHT SEMI | | 4 | 23426 | 2 | +0 | 1 | 3503 | | | 6MB | | |
| 6 | VIEW | VW_NSO_1 | 11698 | 1400 | 1 | +0 | 1 | 6582 | | | . | | |
| 7 | HASH GROUP BY | | 11698 | 1400 | 1 | +0 | 1 | 6582 | | | 5MB | | |
| 8 | VIEW | | 13139 | 1399 | 1 | +0 | 1 | 13146 | | | . | | |
| 9 | UNION-ALL | | | | 1 | +0 | 1 | 13146 | | | . | | |
| 10 | TABLE ACCESS STORAGE FULL | PAY_ITM | 13015 | 1276 | 1 | +0 | 1 | 13017 | 45 | 36MB | 7MB | | |
| 11 | TABLE ACCESS STORAGE FULL | PAY | 31 | 14 | 1 | +0 | 1 | 36 | | | . | | |
| 12 | TABLE ACCESS STORAGE FULL | PAY_OFF | 93 | 108 | 1 | +0 | 1 | 93 | | | . | | |
| 13 | VIEW | | 51898 | 22025 | 2 | +0 | 1 | 34655 | | | . | | |
| 14 | HASH GROUP BY | | 51898 | 22025 | 2 | +0 | 1 | 34655 | | | 10MB | | |
| 15 | VIEW | | 51898 | 22023 | 1 | +0 | 1 | 34664 | | | . | | |
| 16 | UNION-ALL | | | | 1 | +0 | 1 | 34664 | | | . | | |
| 17 | HASH GROUP BY | | 29 | 15 | 1 | +0 | 1 | 22 | | | 4MB | | |
| 18 | VIEW | | 29 | 15 | 1 | +0 | 1 | 22 | | | . | | |
| 19 | HASH GROUP BY | | 29 | 15 | 1 | +0 | 1 | 22 | | | 4MB | | |
| 20 | TABLE ACCESS STORAGE FULL | PAY | 29 | 14 | 1 | +0 | 1 | 34 | | | . | | |
| 21 | HASH GROUP BY | | 51869 | 22008 | 1 | +0 | 1 | 34642 | | | 6MB | | |
| 22 | VIEW | | 51869 | 22006 | 1 | +0 | 1 | 34998 | | | . | | |
| 23 | SORT UNIQUE | | 51869 | 22006 | 1 | +0 | 1 | 34998 | | | 2MB | | |
| 24 | UNION-ALL | | | | 1 | +0 | 1 | 34998 | | | . | | |
| 25 | HASH GROUP BY | | 2043 | 8429 | 1 | +0 | 1 | 425 | | | 5MB | | |
| 26 | HASH JOIN | | 2043 | 8427 | 1 | +0 | 1 | 5756 | | | 5MB | | |
| 27 | HASH JOIN | | 2043 | 7149 | 1 | +0 | 1 | 5812 | | | 5MB | | |
| 28 | INLIST ITERATOR | | | | 1 | +0 | 1 | 12027 | | | . | | |
| 29 | TABLE ACCESS BY INDEX ROWID | PAY_DTL | 5957 | 491 | 1 | +0 | 2 | 12027 | | | . | | |
| 30 | INDEX RANGE SCAN | PAY_DTL_IX5 | 6028 | 28 | 1 | +0 | 2 | 12027 | | | . | | |
| 31 | VIEW | | 294K | 6657 | 1 | +0 | 1 | 289K | | | . | | |
| 32 | HASH GROUP BY | | 294K | 6657 | 1 | +0 | 1 | 289K | | | 21MB | | |
| 33 | TABLE ACCESS STORAGE FULL | PAY_DTL | 787K | 3897 | 1 | +0 | 1 | 787K | | | . | | |
| 34 | TABLE ACCESS STORAGE FULL | PAY_ITM | 288K | 1276 | 1 | +0 | 1 | 288K | | | . | | |
| 35 | HASH GROUP BY | | 49826 | 13578 | 1 | +0 | 1 | 34573 | | | 7MB | | |
| 36 | HASH JOIN | | 49826 | 12359 | 1 | +0 | 1 | 142K | | | 9MB | | |
| 37 | HASH JOIN | | 49826 | 11081 | 1 | +0 | 1 | 142K | | | 17MB | | |
| 38 | VIEW | | 294K | 6657 | 1 | +0 | 1 | 289K | | | . | | |
| 39 | HASH GROUP BY | | 294K | 6657 | 1 | +0 | 1 | 289K | | | 21MB | | |
| 40 | TABLE ACCESS STORAGE FULL | PAY_DTL | 787K | 3897 | 1 | +0 | 1 | 787K | | | . | | |
| 41 | TABLE ACCESS STORAGE FULL | PAY_DTL | 145K | 3896 | 1 | +0 | 1 | 147K | | | . | | |
| 42 | TABLE ACCESS STORAGE FULL | PAY_ITM | 288K | 1276 | 1 | +0 | 1 | 288K | | | . | | |
| 43 | TABLE ACCESS BY INDEX ROWID | E_PORT | 1 | 2 | 2 | +0 | 3503 | 3503 | | | . | | |
| 44 | INDEX UNIQUE SCAN | E_PORT_PK | 1 | 1 | 2 | +0 | 3503 | 3503 | | | . | | |
| 45 | TABLE ACCESS BY INDEX ROWID | COMP | 1 | 1 | 2 | +0 | 3503 | 3503 | | | . | | |
| 46 | INDEX UNIQUE SCAN | COMP_PK | 1 | | 2 | +0 | 3503 | 3503 | | | . | | |
| 47 | TABLE ACCESS BY INDEX ROWID | USERS_TAB | 1 | 1 | 2 | +0 | 3503 | 3503 | | | . | | |
| 48 | INDEX UNIQUE SCAN | USER_PK | 1 | | 2 | +0 | 3503 | 3503 | | | . | | |
| 49 | NESTED LOOPS | | 1 | 2 | | | 30 | | | | . | | |
| 50 | TABLE ACCESS BY INDEX ROWID | USERS_TAB | 1 | 2 | 2 | +0 | 30 | 0 | | | . | | |
| 51 | INDEX UNIQUE SCAN | USER_PK | 1 | 1 | 2 | +0 | 30 | 30 | | | . | | |
| 52 | INDEX UNIQUE SCAN | USER_PK | 1 | | | | | | | | . | | |
===============================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"."UID"=9999 OR EXISTS (SELECT 0 FROM "SCHEMA1"."USERS_TAB" "B","SCHEMA1"."USERS_TAB" "A" WHERE
"A"."UID"=9999 AND "B"."UID"=:B1 AND "B"."BKP_UID"=9999 AND "B"."UA_FLG"='N'))
5 - access("PAYINFO"."EID"="EID")
10 - storage("C_STAT"='OOO' AND "CID" IS NOT NULL)
filter("C_STAT"='OOO' AND "CID" IS NOT NULL)
11 - storage("STAT"='AAAAAA' AND "EID" IS NOT NULL)
filter("STAT"='AAAAAA' AND "EID" IS NOT NULL)
12 - storage("O_STAT"='OOO' AND "CID" IS NOT NULL)
filter("O_STAT"='OOO' AND "CID" IS NOT NULL)
20 - storage("STAT"='AAAAAA' AND "CCD"='XXX' AND "ECD"='CC')
filter("STAT"='AAAAAA' AND "CCD"='XXX' AND "ECD"='CC')
26 - access("A"."OID"="CI"."ITM_ID")
27 - access("MA_ID"="A"."AD_ID")
29 - filter("A"."AD_FLG"='F')
30 - access("A"."A_CODE"='FFFFFFFFFFFF' OR "A"."A_CODE"='TTTTTTTTT')
33 - storage("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
filter("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
36 - access("A"."OID"="CI"."ITM_ID")
37 - access("MA_ID"="A"."AD_ID")
40 - storage("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
filter("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
41 - storage("A"."A_CODE"='DDDDDD' AND "A"."AD_FLG"='F')
filter("A"."A_CODE"='DDDDDD' AND "A"."AD_FLG"='F')
44 - access("PAYINFO"."EID"="PC"."EID" AND "PC"."ECD"='CC')
46 - access("C"."ECD"='CC' AND "PC"."EID"="C"."EID")
48 - access("C"."UID"="U"."UID"(+))
50 - filter("B"."BKP_UID"=9999 AND "B"."UA_FLG"='N')
51 - access("B"."UID"=:B1)
52 - access("A"."UID"=9999)
Statistics
----------------------------------------------------------
31 recursive calls
0 db block gets
79498 consistent gets
4599 physical reads
220 redo size
26673 bytes sent via SQL*Net to client
4846 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
****************** With 19C version
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 2n5utzu6bs5xx
SQL Execution ID : 33554432
Execution Started : 07/11/2020 15:22:24
First Refresh Time : 07/11/2020 15:22:28
Last Refresh Time : 07/11/2020 15:30:56
Duration : 512s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 22
Global Stats
===========================================================================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
===========================================================================================================================================================================
| 511 | 511 | 0.07 | 0.00 | 0.00 | 0.17 | 22 | 175M | 1608 | 2GB | 66 | 19MB | 1GB | 2GB | 47MB | 96.94% |
===========================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3176679337)
==============================================================================================================================================================================================================================
| 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 | SELECT STATEMENT | | | | 509 | +4 | 1 | 310 | | | | | . | . | | |
| 1 | FILTER | | | | 509 | +4 | 1 | 310 | | | | | . | . | | |
| 2 | HASH JOIN RIGHT OUTER | | 5227 | 78437 | 509 | +4 | 1 | 34655 | | | | | 5MB | . | | |
| 3 | TABLE ACCESS STORAGE FULL | USERS_TAB | 789 | 9 | 1 | +4 | 1 | 789 | | | | | . | . | | |
| 4 | HASH JOIN | | 5227 | 78428 | 509 | +4 | 1 | 34655 | | | | | 11MB | . | | |
| 5 | HASH JOIN | | 59132 | 53652 | 1 | +4 | 1 | 51222 | | | | | 10MB | . | | |
| 6 | JOIN FILTER CREATE | :BF0000 | 59132 | 53652 | 1 | +4 | 1 | 51222 | | | | | . | . | | |
| 7 | NESTED LOOPS | | 59132 | 53652 | 1 | +4 | 1 | 1 | | | | | . | . | | |
| 8 | NESTED LOOPS | | | | 1 | +4 | 1 | 1 | | | | | . | . | | |
| 9 | STATISTICS COLLECTOR | | | | 1 | +4 | 1 | 51222 | | | | | . | . | | |
| 10 | TABLE ACCESS STORAGE FULL | COMP | 51217 | 141 | 1 | +4 | 1 | 51222 | | | | | . | . | | |
| 11 | INDEX UNIQUE SCAN | E_PORT_PK | | | | | | | | | | | . | . | | |
| 12 | TABLE ACCESS BY INDEX ROWID | E_PORT | 1 | 53510 | | | | | | | | | . | . | | |
| 13 | JOIN FILTER USE | :BF0000 | 351K | 53510 | 1 | +4 | 1 | 65435 | | | | | . | . | | |
| 14 | TABLE ACCESS STORAGE FULL | E_PORT | 351K | 53510 | 1 | +4 | 1 | 65435 | 1542 | 2GB | | | 7MB | . | | |
| 15 | VIEW | | 62118 | 24224 | 509 | +4 | 1 | 34655 | | | | | . | . | | |
| 16 | HASH GROUP BY | | 62118 | 24224 | 509 | +4 | 1 | 34655 | | | | | 11MB | . | | |
| 17 | VIEW | | 62118 | 24222 | 1 | +4 | 1 | 34664 | | | | | . | . | | |
| 18 | UNION-ALL | | | | 1 | +4 | 1 | 34664 | | | | | . | . | | |
| 19 | HASH GROUP BY | | 29 | 15 | 1 | +4 | 1 | 22 | | | | | 4MB | . | | |
| 20 | VIEW | | 29 | 15 | 1 | +4 | 1 | 22 | | | | | . | . | | |
| 21 | HASH GROUP BY | | 29 | 15 | 1 | +4 | 1 | 22 | | | | | 4MB | . | | |
| 22 | TABLE ACCESS STORAGE FULL | PAY | 29 | 14 | 1 | +4 | 1 | 34 | | | | | . | . | | |
| 23 | HASH GROUP BY | | 62089 | 24207 | 1 | +4 | 1 | 34642 | | | | | 7MB | . | | |
| 24 | VIEW | | 62089 | 24205 | 1 | +4 | 1 | 34998 | | | | | . | . | | |
| 25 | SORT UNIQUE | | 62089 | 24205 | 1 | +4 | 1 | 34998 | | | | | 2MB | . | | |
| 26 | UNION-ALL | | | | 1 | +4 | 1 | 34998 | | | | | . | . | | |
| 27 | HASH GROUP BY | | 11885 | 8912 | 1 | +4 | 1 | 425 | | | | | 5MB | . | | |
| 28 | HASH JOIN | | 11885 | 8910 | 1 | +4 | 1 | 5756 | | | | | 5MB | . | | |
| 29 | HASH JOIN | | 11885 | 2252 | 1 | +4 | 1 | 11905 | | | | | 5MB | . | | |
| 30 | JOIN FILTER CREATE | :BF0001 | 11885 | 975 | 1 | +4 | 1 | 12027 | | | | | . | . | | |
| 31 | INLIST ITERATOR | | | | 1 | +4 | 1 | 12027 | | | | | . | . | | |
| 32 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_DTL | 11885 | 975 | 1 | +4 | 2 | 12027 | | | | | . | . | | |
| 33 | INDEX RANGE SCAN | PAY_DTL_IX5 | 12027 | 52 | 1 | +4 | 2 | 12027 | | | | | . | . | | |
| 34 | JOIN FILTER USE | :BF0001 | 288K | 1276 | 1 | +4 | 1 | 18613 | | | | | . | . | | |
| 35 | TABLE ACCESS STORAGE FULL | PAY_ITM | 288K | 1276 | 1 | +4 | 1 | 18613 | | | | | . | . | | |
| 36 | VIEW | | 294K | 6657 | 1 | +4 | 1 | 289K | | | | | . | . | | |
| 37 | HASH GROUP BY | | 294K | 6657 | 1 | +4 | 1 | 289K | 14 | 7MB | 14 | 7MB | 20MB | 7MB | | |
| 38 | TABLE ACCESS STORAGE FULL | PAY_DTL | 787K | 3897 | 1 | +4 | 1 | 787K | | | | | . | . | | |
| 39 | HASH GROUP BY | | 50204 | 14062 | 1 | +4 | 1 | 34573 | | | | | 7MB | . | | |
| 40 | HASH JOIN | | 145K | 12850 | 1 | +4 | 1 | 142K | | | | | 13MB | . | | |
| 41 | VIEW | VW_GBC_20 | 145K | 5745 | 1 | +4 | 1 | 147K | | | | | . | . | | |
| 42 | HASH JOIN | | 145K | 5745 | 1 | +4 | 1 | 147K | 37 | 4MB | 37 | 4MB | 17MB | 6MB | | |
| 43 | TABLE ACCESS STORAGE FULL | PAY_ITM | 288K | 1276 | 1 | +4 | 1 | 288K | | | | | . | . | | |
| 44 | TABLE ACCESS STORAGE FULL | PAY_DTL | 145K | 3896 | 1 | +4 | 1 | 147K | | | | | . | . | | |
| 45 | VIEW | | 294K | 6657 | 1 | +4 | 1 | 289K | | | | | . | . | | |
| 46 | HASH GROUP BY | | 294K | 6657 | 3 | +2 | 1 | 289K | 15 | 7MB | 15 | 7MB | 18MB | 8MB | | |
| 47 | TABLE ACCESS STORAGE FULL | PAY_DTL | 787K | 3897 | 1 | +4 | 1 | 787K | | | | | . | . | | |
| 48 | FILTER | | | | 509 | +4 | 34655 | 3503 | | | | | . | . | | |
| 49 | HASH GROUP BY | | 1 | 1400 | 509 | +4 | 34655 | 216M | | | | | 5MB | . | | |
| 50 | VIEW | | 13139 | 1399 | 509 | +4 | 34655 | 456M | | | | | . | . | | |
| 51 | UNION-ALL | | | | 509 | +4 | 34655 | 456M | | | | | . | . | | |
| 52 | TABLE ACCESS STORAGE FULL | PAY_ITM | 13015 | 1276 | 510 | +3 | 34655 | 451M | | | | | . | . | | |
| 53 | TABLE ACCESS STORAGE FULL | PAY | 31 | 14 | 509 | +4 | 34655 | 1M | | | | | . | . | | |
| 54 | TABLE ACCESS STORAGE FULL | PAY_OFF | 93 | 108 | 509 | +4 | 34655 | 3M | | | | | . | . | | |
| 55 | NESTED LOOPS | | 1 | 2 | | | 30 | | | | | | . | . | | |
| 56 | TABLE ACCESS BY INDEX ROWID | USERS_TAB | 1 | 2 | 348 | +4 | 30 | 0 | | | | | . | . | | |
| 57 | INDEX UNIQUE SCAN | USER_PK | 1 | 1 | 348 | +4 | 30 | 30 | | | | | . | . | | |
| 58 | INDEX UNIQUE SCAN | USER_PK | 1 | | | | | | | | | | . | . | | |
==============================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM ( (SELECT "CID" "EID" FROM "SCHEMA1"."PAY_ITM" "PAY_ITM"
WHERE "C_STAT"='OOO' AND "CID" IS NOT NULL) UNION ALL (SELECT "EID" "EID" FROM
"SCHEMA1"."PAY" "PAY" WHERE "STAT"='AAAAAA' AND "EID" IS NOT NULL) UNION ALL (SELECT "CID"
"EID" FROM "SCHEMA1"."PAY_OFF" "PAY_OFF" WHERE "O_STAT"='OOO' AND "CID" IS NOT NULL))
"from$_subquery$_019" GROUP BY "EID" HAVING "EID"=:B1) AND ("C"."UID"=9999 OR EXISTS (SELECT 0 FROM
"SCHEMA1"."USERS_TAB" "B","SCHEMA1"."USERS_TAB" "A" WHERE "A"."UID"=9999 AND "B"."UID"=:B2 AND "B"."BKP_UID"=9999
AND "B"."UA_FLG"='N')))
2 - access("C"."UID"="U"."UID"(+))
4 - access("PAYINFO"."EID"="PC"."EID")
5 - access("PC"."EID"="C"."EID")
7 - storage("C"."ECD"='CC')
filter("C"."ECD"='CC')
9 - storage("PC"."ECD"='CC' AND SYS_OP_BLOOM_FILTER(:BF0000,"PC"."EID"))
filter("PC"."ECD"='CC' AND SYS_OP_BLOOM_FILTER(:BF0000,"PC"."EID"))
17 - storage("STAT"='AAAAAA' AND "CCD"='XXX' AND "ECD"='CC')
filter("STAT"='AAAAAA' AND "CCD"='XXX' AND "ECD"='CC')
23 - access("MA_ID"="A"."AD_ID")
24 - access("A"."OID"="CI"."ITM_ID")
27 - filter("A"."AD_FLG"='F')
28 - access("A"."A_CODE"='FFFFFFFFFFFF' OR "A"."A_CODE"='TTTTTTTTT')
30 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"CI"."ITM_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"CI"."ITM_ID"))
33 - storage("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
filter("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
35 - access("MA_ID"="ITEM_1")
37 - access("A"."OID"="CI"."ITM_ID")
39 - storage("A"."A_CODE"='DDDDDD' AND "A"."AD_FLG"='F')
filter("A"."A_CODE"='DDDDDD' AND "A"."AD_FLG"='F')
42 - storage("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
filter("OT_CD"='CCCLLL' AND "A_CCD"='XXX')
43 - filter("EID"=:B1)
47 - storage("C_STAT"='OOO' AND "CID" IS NOT NULL)
filter("C_STAT"='OOO' AND "CID" IS NOT NULL)
48 - storage("STAT"='AAAAAA' AND "EID" IS NOT NULL)
filter("STAT"='AAAAAA' AND "EID" IS NOT NULL)
49 - storage("O_STAT"='OOO' AND "CID" IS NOT NULL)
filter("O_STAT"='OOO' AND "CID" IS NOT NULL)
51 - filter("B"."BKP_UID"=9999 AND "B"."UA_FLG"='N')
52 - access("B"."UID"=:B1)
53 - access("A"."UID"=9999)
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
45 recursive calls
0 db block gets
174666415 consistent gets
199000 physical reads
132 redo size
26673 bytes sent via SQL*Net to client
4845 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
310 rows processed