Original SQL with detailed plan execution information

gistfile1.sql

SELECT /*+ gather_plan_statistics QB_NAME(OUTER_QUERY) */
       MAPCS.ID,
       MA.ORG_ID_PROVIDER,
       MAPCS.MON_ACCT_PAYER_CALC_SUMMARY_ID,
       MAPCS.CE_SERVICE_ID
FROM
       concuity.MON_ACCOUNT MA,
       concuity.MON_ACCOUNT_PAYER MAPY,
       concuity.MON_ACCOUNT_PAYER_CALC_LATEST MAPC,
       concuity.MON_ACCOUNT_PAYER_CALC_SERVICE MAPCS
WHERE
           MA.ID = MAPY.MON_ACCOUNT_ID
       AND MA.ORG_ID_PROVIDER = :p_org_id
       AND MAPY.ID = MAPC.MON_ACCOUNT_PAYER_ID
       AND MAPC.ID = MAPCS.MON_ACCT_PAYER_CALC_SUMMARY_ID
       AND EXISTS
              (SELECT /*+ QB_NAME(INNER_QUERY) */
                      'x'
               FROM
                      concuity.WQ_ORG_ACCOUNT WQOA
               WHERE
                      WQOA.MON_ACCOUNT_ID = MA.ID AND WQOA.ORG_ID = :p_org_id)
/

197188 rows selected.

Elapsed: 00:09:50.87

Plan hash value: 619129746

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                           | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                 |                                |      1 |    298K|    24M|    10M|   267K  (1)| 00:53:29 |       |       |    197K|00:06:03.95 |     814K|    682K|
|*  2 |   HASH JOIN RIGHT SEMI     |                                |      1 |    162K|  9344K|  6080K| 68179   (1)| 00:13:39 |       |       |    160K|00:00:07.85 |   51010 |      1 |
|   3 |    PARTITION LIST SINGLE   |                                |      1 |    239K|  3271K|       |   445   (1)| 00:00:06 |   KEY |   KEY |    222K|00:00:00.22 |    1877 |      0 |
|*  4 |     TABLE ACCESS FULL      | WQ_ORG_ACCOUNT                 |      1 |    239K|  3271K|       |   445   (1)| 00:00:06 |   KEY |   KEY |    222K|00:00:00.01 |    1877 |      0 |
|*  5 |    HASH JOIN               |                                |      1 |    162K|  7126K|  9472K| 66999   (1)| 00:13:24 |       |       |    160K|00:00:07.81 |   49133 |      1 |
|*  6 |     HASH JOIN              |                                |      1 |    242K|  6627K|       | 20616   (1)| 00:04:08 |       |       |    244K|00:00:02.94 |   16003 |      0 |
|   7 |      PARTITION LIST SINGLE |                                |      1 |  79837 |  1091K|       |   912   (1)| 00:00:11 |   KEY |   KEY |  80157 |00:00:00.16 |    4167 |      0 |
|*  8 |       TABLE ACCESS FULL    | MON_ACCOUNT                    |      1 |  79837 |  1091K|       |   912   (1)| 00:00:11 |   KEY |   KEY |  80157 |00:00:00.08 |    4167 |      0 |
|   9 |      VIEW                  | index$_join$_002               |      1 |   1917K|    25M|       | 19695   (1)| 00:03:57 |       |       |   1935K|00:00:07.75 |   11836 |      0 |
|* 10 |       HASH JOIN            |                                |      1 |        |       |       |            |          |       |       |   1935K|00:00:05.82 |   11836 |      0 |
|  11 |        INDEX FAST FULL SCAN| MON_ACCT_PAYER_FK_MON_ACCT     |      1 |   1917K|    25M|       |  9055   (1)| 00:01:49 |       |       |   1935K|00:00:00.01 |    7428 |      0 |
|  12 |        INDEX FAST FULL SCAN| MON_ACCT_PAYER_PK              |      1 |   1917K|    25M|       |  5350   (1)| 00:01:05 |       |       |   1935K|00:00:00.01 |    4408 |      0 |
|  13 |     VIEW                   | index$_join$_003               |      1 |   1263K|    20M|       | 44178   (1)| 00:08:51 |       |       |   1275K|00:00:07.50 |   33130 |      1 |
|* 14 |      HASH JOIN             |                                |      1 |        |       |       |            |          |       |       |   1275K|00:00:06.22 |   33130 |      1 |
|  15 |       INDEX FAST FULL SCAN | MAPCS_LATEST_MAPY_CLAIM_ID     |      1 |   1263K|    20M|       |  7483   (1)| 00:01:30 |       |       |   1275K|00:00:00.01 |    6151 |      1 |
|  16 |       INDEX FAST FULL SCAN | MAPCS_LATEST_PK                |      1 |   1263K|    20M|       | 33032   (1)| 00:06:37 |       |       |   1275K|00:00:01.28 |   26979 |      0 |
|  17 |   TABLE ACCESS FULL        | MON_ACCOUNT_PAYER_CALC_SERVICE |      1 |     18M|   465M|       |   165K  (1)| 00:33:02 |       |       |     18M|00:09:44.73 |     763K|    682K|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$D6887185
   4 - SEL$D6887185 / WQOA@INNER_QUERY
   8 - SEL$D6887185 / MA@OUTER_QUERY
   9 - SEL$173B53D5 / MAPY@OUTER_QUERY
  10 - SEL$173B53D5
  11 - SEL$173B53D5 / indexjoin$_alias$_001@SEL$173B53D5
  12 - SEL$173B53D5 / indexjoin$_alias$_002@SEL$173B53D5
  13 - SEL$802A7B11 / MAPC@OUTER_QUERY
  14 - SEL$802A7B11
  15 - SEL$802A7B11 / indexjoin$_alias$_001@SEL$802A7B11
  16 - SEL$802A7B11 / indexjoin$_alias$_002@SEL$802A7B11
  17 - SEL$D6887185 / MAPCS@OUTER_QUERY

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$173B53D5")
      OUTLINE_LEAF(@"SEL$802A7B11")
      OUTLINE_LEAF(@"SEL$D6887185")
      UNNEST(@"INNER_QUERY")
      OUTLINE(@"SEL$D6887185")
      UNNEST(@"INNER_QUERY")
      OUTLINE(@"OUTER_QUERY")
      OUTLINE(@"INNER_QUERY")
      FULL(@"SEL$D6887185" "MA"@"OUTER_QUERY")
      INDEX_JOIN(@"SEL$D6887185" "MAPY"@"OUTER_QUERY" ("MON_ACCOUNT_PAYER"."MON_ACCOUNT_ID") ("MON_ACCOUNT_PAYER"."ID"))
      INDEX_JOIN(@"SEL$D6887185" "MAPC"@"OUTER_QUERY" ("MON_ACCOUNT_PAYER_CALC_LATEST"."MON_ACCOUNT_PAYER_ID" "MON_ACCOUNT_PAYER_CALC_LATEST"."CLAIM_ID")
              ("MON_ACCOUNT_PAYER_CALC_LATEST"."ID"))
      FULL(@"SEL$D6887185" "WQOA"@"INNER_QUERY")
      FULL(@"SEL$D6887185" "MAPCS"@"OUTER_QUERY")
      LEADING(@"SEL$D6887185" "MA"@"OUTER_QUERY" "MAPY"@"OUTER_QUERY" "MAPC"@"OUTER_QUERY" "WQOA"@"INNER_QUERY" "MAPCS"@"OUTER_QUERY")
      USE_HASH(@"SEL$D6887185" "MAPY"@"OUTER_QUERY")
      USE_HASH(@"SEL$D6887185" "MAPC"@"OUTER_QUERY")
      USE_HASH(@"SEL$D6887185" "WQOA"@"INNER_QUERY")
      USE_HASH(@"SEL$D6887185" "MAPCS"@"OUTER_QUERY")
      SWAP_JOIN_INPUTS(@"SEL$D6887185" "WQOA"@"INNER_QUERY")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 966965592
   2 - (NUMBER, Primary=1)

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAPC"."ID"="MAPCS"."MON_ACCT_PAYER_CALC_SUMMARY_ID")
   2 - access("WQOA"."MON_ACCOUNT_ID"="MA"."ID")
   4 - filter("WQOA"."ORG_ID"=:P_ORG_ID)
   5 - access("MAPY"."ID"="MAPC"."MON_ACCOUNT_PAYER_ID")
   6 - access("MA"."ID"="MAPY"."MON_ACCOUNT_ID")
   8 - filter("MA"."ORG_ID_PROVIDER"=:P_ORG_ID)
  10 - access(ROWID=ROWID)
  14 - access(ROWID=ROWID)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "MAPCS"."MON_ACCT_PAYER_CALC_SUMMARY_ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22], "MAPCS"."ID"[NUMBER,22], "MAPCS"."CE_SERVICE_ID"[NUMBER,22]
   2 - (#keys=1) "MAPC"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22]
   3 - "WQOA"."MON_ACCOUNT_ID"[NUMBER,22]
   4 - "WQOA"."MON_ACCOUNT_ID"[NUMBER,22]
   5 - (#keys=1) "MA"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22], "MAPC"."ID"[NUMBER,22]
   6 - (#keys=1) "MA"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22], "MAPY"."ID"[NUMBER,22]
   7 - "MA"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22]
   8 - "MA"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22]
   9 - "MAPY"."ID"[NUMBER,22], "MAPY"."MON_ACCOUNT_ID"[NUMBER,22]
  10 - (#keys=1) "MAPY"."MON_ACCOUNT_ID"[NUMBER,22], "MAPY"."ID"[NUMBER,22]
  11 - ROWID[ROWID,10], "MAPY"."MON_ACCOUNT_ID"[NUMBER,22]
  12 - ROWID[ROWID,10], "MAPY"."ID"[NUMBER,22]
  13 - "MAPC"."ID"[NUMBER,22], "MAPC"."MON_ACCOUNT_PAYER_ID"[NUMBER,22]
  14 - (#keys=1) "MAPC"."MON_ACCOUNT_PAYER_ID"[NUMBER,22], "MAPC"."ID"[NUMBER,22]
  15 - ROWID[ROWID,10], "MAPC"."MON_ACCOUNT_PAYER_ID"[NUMBER,22]
  16 - ROWID[ROWID,10], "MAPC"."ID"[NUMBER,22]
  17 - "MAPCS"."ID"[NUMBER,22], "MAPCS"."MON_ACCT_PAYER_CALC_SUMMARY_ID"[NUMBER,22], "MAPCS"."CE_SERVICE_ID"[NUMBER,22]