5 Replies Latest reply: Jan 30, 2013 12:52 PM by new learner RSS

    Help me in reading/understanding Explain Plan

    new learner
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      SELECT SRC2.PGM_MSTR_NBR,
                                                    SRC2.PGM_TRK_NBR,
                                                    SRC2.CNTL_LOCN,
                                                    SRC2.PGM_NAME,
                                                    SRC2.PGM_STS,
                                                    SRC2.SIS_PGM_START_DATE,
                                                    SRC2.SIS_PGM_END_DATE,
                                                    SRC2.AWARD_TRGT,
                                                    SRC2.AWARD_MAX,
                                                    SRC2.COMPLNC_TYPE,
                                                    SRC2.CMPNY_VNDR_LOCN,
                                                    SRC2.CMPNY_VNDR_NBR,
                                                    LKP3.ADDR_NAME,
                                                    SRC2.INV_IND,
                                                    SRC2.LAST_INV_THRU_DATE,
                                                    SRC2.INV_RPT_DAY,
                                                    SRC2.INV_RPT_CYCLE,
                                                    SRC2.BEG_COLL_DATE,
                                                    SRC2.END_COLL_DATE,
                                                    SRC2.SLS_CONT_DIST,
                                                    SRC2.SLS_CONT_NBR,
                                                    SRC2.ORD_INV_START_DATE,
                                                    SRC2.ORD_INV_END_DATE,
                                                    SRC2.CMPLNC_RPT_IND,
                                                    SRC2.PROD_ENTRY_LVL,
                                                    SRC2.DIST_ENTRY_LVL,
                                                    SRC2.CUST_ENTRY_LVL,
                                                    SRC2.VNDR_ENTRY_LVL,
                                                    SRC2.VNDR_CONT,
                                                    SRC2.SIS_CMPNY_VNDR_NBR
                                                    FROM CASADM.SBA_REB_PGM SRC2 
                                                    INNER JOIN(SELECT PGM_MSTR_NBR,PGM_TRK_NBR,CNTL_LOCN  FROM CASADM.ACCR_SIS_PURCH_DTL
                                                               UNION SELECT PGM_MSTR_NBR,PGM_TRK_NBR,CNTL_LOCN  FROM CASADM.ACCR_SIS_EXCL_DTL)ACCR2
                                                          ON  (SRC2.PGM_MSTR_NBR=ACCR2.PGM_MSTR_NBR AND SRC2.PGM_TRK_NBR=ACCR2.PGM_TRK_NBR AND SRC2.CNTL_LOCN=ACCR2.CNTL_LOCN)
                                                    LEFT OUTER JOIN
                                                              CASADM.MT_CMPNY_VNDR LKP3
                                                          ON (LKP3.CMPNY_VNDR_NBR=SRC2.CMPNY_VNDR_NBR);
      
      Record Count in each table:
      -----------------------------
      select count(*) from casadm.accr_sis_purch_dtl --375,968
      select count(*) from casadm.accr_sis_excl_dtl --1,988,867
      select count(*) from casadm.sba_reb_pgm --526,133
      select count(*) from casadm.mt_cmpny_vndr --20743
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1465316812
      
      --------------------------------------------------------------------------------
      
      ------------------------------
      
      | Id  | Operation                    | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time|
      
      --------------------------------------------------------------------------------
      
      
      
      |   0 | SELECT STATEMENT             |                       |     9 |  1908 | | 26988   (2)| 00:06:18 |
      
      |   1 |  NESTED LOOPS OUTER          |                       |     9 |  1908 | | 26988   (2)| 00:06:18 |
      
      |*  2 |   HASH JOIN                  |                       |     9 |  1656 |83M| 26979   (2)| 00:06:18|
      
      |   3 |    TABLE ACCESS FULL         | SBA_REB_PGM           |   536K|    77M| |  2624   (2)| 00:00:37 |
      
      |   4 |    VIEW                      |                       |  2364K|    74M| | 16424   (2)| 00:03:50 |
      
      |   5 |     SORT UNIQUE              |                       |  2364K|    49M|72M| 16424  (86)| 00:03:50|
      
      |   6 |      UNION-ALL               |                       |       |       ||            |          |
      
      |   7 |       INDEX FAST FULL SCAN   | ACCR_SIS_PURCH_DTL_PK |   375K|  8077K||   871   (1)| 00:00:13 |
      |   8 |       TABLE ACCESS FULL      | ACCR_SIS_EXCL_DTL     |  1988K|    41M||  5634   (1)| 00:01:19 |
      
      |   9 |   TABLE ACCESS BY INDEX ROWID| MT_CMPNY_VNDR         |     1 |    28 | |     1   (0)| 00:00:01 |
      
      |* 10 |    INDEX UNIQUE SCAN         | MT_CMPNY_VNDR_PK      |     1 |       | |     0   (0)| 00:00:01 |
      
      --------------------------------------------------------------------------------------------------------
      
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("SRC2"."PGM_MSTR_NBR"="ACCR2"."PGM_MSTR_NBR" AND
                    "SRC2"."PGM_TRK_NBR"="ACCR2"."PGM_TRK_NBR" AND "SRC2"."CNTL_LOCN"=
      
      "ACCR2"."CNTL_LOCN")
      
        10 - access("LKP3"."CMPNY_VNDR_NBR"(+)="SRC2"."CMPNY_VNDR_NBR")
      
      
      Statistics
      ----------------------------------------------------------
              102  recursive calls
                0  db block gets
            34558  consistent gets
            23241  physical reads
               88  redo size
           531258  bytes sent via SQL*Net to client
             2760  bytes received via SQL*Net from client
              361  SQL*Net roundtrips to/from client
                1  sorts (memory)
                0  sorts (disk)
             5392  rows processed