SQL_ID 17wx85qmafgrn, child number 2 ------------------------------------- SELECT /*+ gather_plan_statistics */ /* grsdf */ reference_id FROM stpdoc_entry_table WHERE fc_id = :"SYS_B_0" AND doc_parent_id IN ( SELECT doc.doc_parent_id FROM stpdoc_entry_table doc LEFT JOIN dlv_cash_dbf dlv ON dlv.m_reference = doc.doc_parent_id WHERE dlv.m_reference IS NULL AND doc.fc_id = :"SYS_B_1" ) AND ( xmlflow_status IN ( SELECT b.xmlflow_status FROM mwp_tmp_statuses_2b_purged b ) OR ( xmlflow_status = :"SYS_B_2" AND xmlflow_error_type = :"SYS_B_3" ) ) ORDER BY reference_id Plan hash value: 4240113116 --------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 5835K(100)| 0 |00:20:26.14 | 482M| | 1 | SORT ORDER BY | | 1 | 2 | 5835K (1)| 0 |00:20:26.14 | 482M| | 2 | VIEW | VW_ORE_12FFE143 | 1 | 2 | 5835K (1)| 0 |00:20:26.14 | 482M| | 3 | UNION-ALL | | 1 | | | 0 |00:20:26.14 | 482M| | 4 | NESTED LOOPS SEMI | | 1 | 1 | 3 (0)| 0 |00:00:00.01 | 3 | |* 5 | TABLE ACCESS BY INDEX ROWID BATCHED | STPDOC_ENTRY_TABLE | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 3 | |* 6 | INDEX RANGE SCAN | STPDOC_ENTRY_TABLE_I0 | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 3 | | 7 | VIEW PUSHED PREDICATE | VW_NSO_1 | 0 | 1 | 2 (0)| 0 |00:00:00.01 | 0 | | 8 | NESTED LOOPS ANTI | | 0 | 1 | 2 (0)| 0 |00:00:00.01 | 0 | |* 9 | TABLE ACCESS BY INDEX ROWID BATCHED | STPDOC_ENTRY_TABLE | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | |* 10 | INDEX RANGE SCAN | STPDOC_ENTRY_TABLE_I6 | 0 | 5 | 1 (0)| 0 |00:00:00.01 | 0 | |* 11 | INDEX UNIQUE SCAN | DLV_CASH_ND0 | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | |* 12 | FILTER | | 1 | | | 0 |00:20:26.13 | 482M| | 13 | NESTED LOOPS SEMI | | 1 | 1 | 5835K (1)| 456K|00:20:25.90 | 482M| |* 14 | TABLE ACCESS FULL | STPDOC_ENTRY_TABLE | 1 | 2846K| 141K (1)| 2754K|00:00:05.51 | 520K| | 15 | VIEW PUSHED PREDICATE | VW_NSO_1 | 2754K| 1 | 2 (0)| 456K|00:20:20.08 | 481M| | 16 | NESTED LOOPS ANTI | | 2754K| 1 | 2 (0)| 456K|00:20:19.40 | 481M| |* 17 | TABLE ACCESS BY INDEX ROWID BATCHED| STPDOC_ENTRY_TABLE | 2754K| 1 | 1 (0)| 2971K|00:20:07.75 | 476M| |* 18 | INDEX RANGE SCAN | STPDOC_ENTRY_TABLE_I6 | 2754K| 5 | 1 (0)| 2436M|00:04:59.39 | 10M| |* 19 | INDEX UNIQUE SCAN | DLV_CASH_ND0 | 2754K| 1 | 1 (0)| 2298K|00:00:04.30 | 5483K| |* 20 | INDEX RANGE SCAN | MWP_TMP_STATUSES_2B_PURGED_I1 | 1 | 1 | 1 (0)| 0 |00:00:00.01 | 1 | --------------------------------------------------------------------------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :1 (NUMBER): 0 2 - :2 (NUMBER): 0 3 - (VARCHAR2(30), CSID=873): '~SYS_ERROR~' 4 - (VARCHAR2(30), CSID=873): 'D' Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(("XMLFLOW_ERROR_TYPE"=:SYS_B_3 AND "FC_ID"=:SYS_B_0)) 6 - access("XMLFLOW_STATUS"=:SYS_B_2) 9 - filter("DOC"."FC_ID"=:SYS_B_1) 10 - access("DOC"."DOC_PARENT_ID"="DOC_PARENT_ID") 11 - access("DLV"."M_REFERENCE"="DOC_PARENT_ID") filter("DLV"."M_REFERENCE"="DOC"."DOC_PARENT_ID") 12 - filter( IS NOT NULL) 14 - filter(("FC_ID"=:SYS_B_0 AND (LNNVL("XMLFLOW_STATUS"=:SYS_B_2) OR LNNVL("XMLFLOW_ERROR_TYPE"=:SYS_B_3)))) 17 - filter("DOC"."FC_ID"=:SYS_B_1) 18 - access("DOC"."DOC_PARENT_ID"="DOC_PARENT_ID") 19 - access("DLV"."M_REFERENCE"="DOC_PARENT_ID") filter("DLV"."M_REFERENCE"="DOC"."DOC_PARENT_ID") 20 - access("B"."XMLFLOW_STATUS"=:B1)