PLAN_TABLE_OUTPUT Plan hash value: 124769705 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25 | 2800 | 11436 (1)| 00:02:18 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ20009 | 25 | 2800 | 11436 (1)| 00:02:18 | | | Q2,09 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 25 | 2800 | 11436 (1)| 00:02:18 | | | Q2,09 | PCWP | | | 4 | PX RECEIVE | | | | | | | | Q2,09 | PCWP | | | 5 | PX SEND RANGE | :TQ20008 | | | | | | | Q2,08 | P->P | RANGE | | 6 | BUFFER SORT | | 25 | 2800 | | | | | Q2,08 | PCWP | | | 7 | NESTED LOOPS | | | | | | | | Q2,08 | PCWP | | | 8 | NESTED LOOPS | | 25 | 2800 | 11402 (1)| 00:02:17 | | | Q2,08 | PCWP | | |* 9 | FILTER | | | | | | | | Q2,08 | PCWC | | | 10 | NESTED LOOPS OUTER | | 25 | 2600 | 11402 (1)| 00:02:17 | | | Q2,08 | PCWP | | |* 11 | HASH JOIN | | 30 | 2850 | 11402 (1)| 00:02:17 | | | Q2,08 | PCWP | | | 12 | PX RECEIVE | | 3274 | 239K| 11398 (1)| 00:02:17 | | | Q2,08 | PCWP | | | 13 | PX SEND HASH | :TQ20006 | 3274 | 239K| 11398 (1)| 00:02:17 | | | Q2,06 | P->P | HASH | |* 14 | HASH JOIN BUFFERED | | 3274 | 239K| 11398 (1)| 00:02:17 | | | Q2,06 | PCWP | | | 15 | JOIN FILTER CREATE | :BF0000 | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,06 | PCWP | | | 16 | PX RECEIVE | | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,06 | PCWP | | | 17 | PX SEND HASH | :TQ20003 | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,03 | P->P | HASH | | 18 | PX BLOCK ITERATOR | | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,03 | PCWC | | | 19 | TABLE ACCESS STORAGE FULL | MSRMNT_PRD | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,03 | PCWP | | | 20 | PX RECEIVE | | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,06 | PCWP | | | 21 | PX SEND HASH | :TQ20004 | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,04 | P->P | HASH | | 22 | JOIN FILTER USE | :BF0000 | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,04 | PCWP | | |* 23 | HASH JOIN | | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,04 | PCWP | | | 24 | PART JOIN FILTER CREATE | :BF0001 | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,04 | PCWP | | | 25 | PX RECEIVE | | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,04 | PCWP | | | 26 | PX SEND BROADCAST | :TQ20001 | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,01 | P->P | BROADCAST | | 27 | PX BLOCK ITERATOR | | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,01 | PCWC | | |* 28 | TABLE ACCESS STORAGE FULL | SRC_SYST_CLNDR_DAY | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,01 | PCWP | | |* 29 | HASH JOIN | | 1071K| 56M| 11393 (1)| 00:02:17 | | | Q2,04 | PCWP | | | 30 | PX RECEIVE | | 153K| 5528K| 29 (4)| 00:00:01 | | | Q2,04 | PCWP | | | 31 | PX SEND BROADCAST | :TQ20002 | 153K| 5528K| 29 (4)| 00:00:01 | | | Q2,02 | P->P | BROADCAST | |* 32 | HASH JOIN | | 153K| 5528K| 29 (4)| 00:00:01 | | | Q2,02 | PCWP | | | 33 | PX RECEIVE | | 1 | 24 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | | | 34 | PX SEND BROADCAST | :TQ20000 | 1 | 24 | 2 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST | | 35 | NESTED LOOPS | | 1 | 24 | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | | | 36 | PX BLOCK ITERATOR | | | | | | | | Q2,00 | PCWC | | |* 37 | TABLE ACCESS STORAGE FULL | MSRMNT_PRD | 1 | 18 | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | | | 38 | SORT AGGREGATE | | 1 | 60 | | | | | Q2,00 | PCWP | | | 39 | PX COORDINATOR | | | | | | | | | | | | 40 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 60 | | | | | Q1,03 | P->S | QC (RAND) | | 41 | SORT AGGREGATE | | 1 | 60 | | | | | Q1,03 | PCWP | | | 42 | NESTED LOOPS | | 26850 | 1573K| 32 (7)| 00:00:01 | | | Q1,03 | PCWP | | |* 43 | HASH JOIN | | 26850 | 1494K| 32 (7)| 00:00:01 | | | Q1,03 | PCWP | | |* 44 | HASH JOIN | | 26850 | 1206K| 30 (7)| 00:00:01 | | | Q1,03 | PCWP | | | 45 | PX RECEIVE | | 743 | 28977 | 3 (34)| 00:00:01 | | | Q1,03 | PCWP | | | 46 | PX SEND BROADCAST | :TQ10001 | 743 | 28977 | 3 (34)| 00:00:01 | | | Q1,01 | P->P | BROADCAST | |* 47 | VIEW | | 743 | 28977 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | | |* 48 | ANK WINDOW BUFFER PUSHED R | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | | | 49 | SORT GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | | | 50 | PX RECEIVE | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | | | 51 | PX SEND HASH | :TQ10000 | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,00 | P->P | HASH | | 52 | HASH GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,00 | PCWP | | | 53 | NESTED LOOPS | | 2648 | 58256 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | | | 54 | R PX BLOCK ITERATO | | | | | | | | Q1,00 | PCWC | | |* 55 | ORAGE FULL TABLE ACCESS ST | SRC_SYST_CLNDR_DAY | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | | |* 56 | N INDEX UNIQUE SCA | UQ_SRC_SYST_SCHEMA_LOAD_EV | 1 | 13 | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | | | 57 | PX BLOCK ITERATOR | | 2906K| 19M| 26 (0)| 00:00:01 | | | Q1,03 | PCWC | | | 58 | L TABLE ACCESS STORAGE FUL | AR_RSRV_SUMRY | 2906K| 19M| 26 (0)| 00:00:01 | | | Q1,03 | PCWP | | | 59 | PX RECEIVE | | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,03 | PCWP | | | 60 | PX SEND BROADCAST | :TQ10002 | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCAST | | 61 | PX BLOCK ITERATOR | | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,02 | PCWC | | |* 62 | LL TABLE ACCESS STORAGE FU | MSRMNT_PRD | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | | |* 63 | INDEX UNIQUE SCAN | UQ_SRC_SYST | 1 | 3 | 0 (0)| 00:00:01 | | | Q1,03 | PCWP | | |* 64 | INDEX UNIQUE SCAN | UQ_CL | 1 | 6 | 0 (0)| 00:00:01 | | | Q2,00 | PCWP | | | 65 | PX BLOCK ITERATOR | | 2906K| 36M| 26 (0)| 00:00:01 | | | Q2,02 | PCWC | | | 66 | TABLE ACCESS STORAGE FULL | AR_RSRV_SUMRY | 2906K| 36M| 26 (0)| 00:00:01 | | | Q2,02 | PCWP | | | 67 | PX BLOCK ITERATOR | | 72M| 1241M| 11363 (1)| 00:02:17 | 1 | LAST | Q2,04 | PCWC | | | 68 | TABLE ACCESS STORAGE FULL | CRDT_ACCT_PRD | 72M| 1241M| 11363 (1)| 00:02:17 | 1 | 150 | Q2,04 | PCWP | | | 69 | PX RECEIVE | | 743 | 14860 | 3 (34)| 00:00:01 | | | Q2,08 | PCWP | | | 70 | PX SEND HASH | :TQ20007 | 743 | 14860 | 3 (34)| 00:00:01 | | | Q2,07 | P->P | HASH | |* 71 | VIEW | | 743 | 14860 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | | |* 72 | WINDOW NOSORT | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | | | 73 | SORT GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | | | 74 | PX RECEIVE | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | | | 75 | PX SEND HASH | :TQ20005 | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,05 | P->P | HASH | | 76 | HASH GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,05 | PCWP | | | 77 | NESTED LOOPS | | 2648 | 58256 | 2 (0)| 00:00:01 | | | Q2,05 | PCWP | | | 78 | PX BLOCK ITERATOR | | | | | | | | Q2,05 | PCWC | | |* 79 | TABLE ACCESS STORAGE FULL | SRC_SYST_CLNDR_DAY | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,05 | PCWP | | |* 80 | INDEX UNIQUE SCAN | UQ_SRC_SYST_SCHEMA_LOAD_EV | 1 | 13 | 0 (0)| 00:00:01 | | | Q2,05 | PCWP | | | 81 | TABLE ACCESS BY INDEX ROWID | CRDT_ACCT | 1 | 9 | 0 (0)| 00:00:01 | | | Q2,08 | PCWP | | |* 82 | INDEX UNIQUE SCAN | UQ_CRDT_ACCT | 1 | | 0 (0)| 00:00:01 | | | Q2,08 | PCWP | | |* 83 | INDEX UNIQUE SCAN | UQ_SRC_SYST | 1 | | 0 (0)| 00:00:01 | | | Q2,08 | PCWP | | |* 84 | TABLE ACCESS BY INDEX ROWID | SRC_SYST | 1 | 8 | 1 (0)| 00:00:01 | | | Q2,08 | PCWP | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 9 - filter("END_DT" IS NULL OR "END_DT">"M"."MSRMNT_PRD_DT") 11 - access("A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID") 14 - access("C"."MSRMNT_PRD_MNTH"=TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0')) AND "A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID") 23 - access("C"."SRC_SYST_ID"="A"."SRC_SYST_ID" AND "C"."MSRMNT_PRD_ID"="A"."MSRMNT_PRD_ID") 28 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y') filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y') 29 - access("A"."AR_ID"="C"."ACCT_AR_ID") 32 - access("A"."MSRMNT_PRD_ID"="M"."MSRMNT_PRD_ID") 37 - storage("M"."MSRMNT_PRD_ID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("A"."MSRMNT_PRD_ID")),0)) FROM (SELECT "A"."MSRMNT_PRD_ID" "MSRMNT_PRD_ID","A"."SRC_SYST_ID" "SRC_SYST_ID",RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC ) "RANK" FROM "BDW"."SRC_SYST_SCHEMA_LOAD_EV" "B","BDW"."SRC_SYST_CLNDR_DAY" "A" WHERE "A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y' AND "B"."SCHEMA_NAME"='EDXF' AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" GROUP BY "A"."SRC_SYST_ID",INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID")) "from$_subquery$_013","BDW"."MSRMNT_PRD" "C","BDW"."SRC_SYST" "B","BDW"."AR_RSRV_SUMRY" "A" WHERE "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID" AND TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206 AND "RANK"<=7)) filter("M"."MSRMNT_PRD_ID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("A"."MSRMNT_PRD_ID")),0)) FROM (SELECT "A"."MSRMNT_PRD_ID" "MSRMNT_PRD_ID","A"."SRC_SYST_ID" "SRC_SYST_ID",RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC ) "RANK" FROM "BDW"."SRC_SYST_SCHEMA_LOAD_EV" "B","BDW"."SRC_SYST_CLNDR_DAY" "A" WHERE "A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y' AND "B"."SCHEMA_NAME"='EDXF' AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" GROUP BY "A"."SRC_SYST_ID",INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID")) "from$_subquery$_013","BDW"."MSRMNT_PRD" "C","BDW"."SRC_SYST" "B","BDW"."AR_RSRV_SUMRY" "A" WHERE "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID" AND TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206 AND "RANK"<=7)) 43 - access("A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID") 44 - access("A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID") 47 - filter("RANK"<=7) 48 - filter(RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC )<=7) 55 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y') filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y') 56 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "B"."SCHEMA_NAME"='EDXF') 62 - storage(TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206) filter(TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206) 63 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID") 64 - access("CL"."CL_ID"="M"."MSRMNT_PRD_TYPE_ID") 71 - filter("RANK"<=7) 72 - filter(RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC )<=7) 79 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y') filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y') 80 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "B"."SCHEMA_NAME"='EDXF') 82 - access("A"."AR_ID"="ACCT_AR_ID"(+)) 83 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID") 84 - filter("B"."SRC_SYST_CODE"='ABC') Note ----- - dynamic sampling used for this statement (level=7)