title: Oracle Select vs Insert/Select (11GR2)
body: <p>So, I'm in the process of writing a process that will populate a table using about 10 different selects.  As part of the process I've picked up a few things, however I'm facing some inconsistent behaviour at times when performing an INSERT/SELECT.   When I run the select query alone, I get results back within 10 seconds (after I flush buffer cache and shared_pool), but at soon as I tag on the insert portion, takes over 10 min... </p>
  
  <p>Here's what I'm doing after I clear the table... Added the "Append" hint on insert which appeared to have helped quite a bit earlier this week, but now it's back to taking longer than usual, but only when performing the insert!!</p>
  
  <pre><code>--// Disable GL_JLOG_DETAILS INDEXES
  Execute immediate 'alter index IDX_GLDTL1 unusable';
  Execute immediate 'alter index IDX_GLDTL2 unusable';
  
  
  --// SECTION 1
  INSERT /*+ APPEND PARALLEL(6) */ INTO GL_JLOG_DETAILS ( MAT_SECTION    
                                                    ,JLOG_KEY        
                                                    ,SRC_CD          
                                                    ,SRC_KEY         
                                                    ,CASE_KEY        
                                                    ,CASE_MBR_KEY    
                                                    ,UNALLOC_ACCT_CD 
                                                    ,FDRT_KEY        
                                                    ,FDRT_TR_CD      
                                                    ,TR_CD           
                                                    ,TR_REF_NO       
                                                    ,STAT_CD         
                                                    ,FD_DESC_ID      
                                                    ,FD_NO           
                                                    ,FD_TYP_CD       
                                                    ,BKT_NO          
                                                    ,RVSL_CD         
                                                    ,CO_CD           
                                                    ,BEN_RPT_TYP_CD  
                                                    ,DB_CR_CD        
                                                    ,ACCT_NO         
                                                    ,SUB_ACCT_NO     
                                                    ,JRNL_AMT        
                                                    ,TDTL_AMT        
                                                    ,RVSL_ERROR      
                                                    ,RVSL_SAME_DAY   
                                                    ,TDTL_REF_KEY)    
            ---// SECTION 1 //--------
            Select  /*+ USE_NL(TMAP,TMOV,ACCT,SBNT,BNTP,FDRT) */ '1' MAT_SECTION,
             JLOG.JLOG_KEY,
             JLOG.SRC_CD,
             TDTL.TDTL_KEY AS SRC_KEY,
             TDTL.CASE_KEY,
             TDTL.CASE_MBR_KEY,
             TDTL.UNALLOC_ACCT_CD,
             TDTL.FDRT_KEY,
             FDRT.TR_CD FDRT_TR_CD,
             TDTL.TR_CD,
             TDTL.TR_REF_NO,
             TDTL.STAT_CD,
             TDTL.FD_DESC_ID,
             TDTL.FD_NO,
             FDDC.FD_TYP_CD,
             TDTL.BKT_NO,
             JLOG.RVSL_CD,
             BNTP.CO_CD,
             SBNT.BEN_RPT_TYP_CD,
             JLOG.DB_CR_CD,
             ACCT.ACCT_NO,
             ACCT.SUB_ACCT_NO,
             JLOG.JRNL_AMT,
             ABS (TDTL.AMT) AS TDTL_AMT,
             CASE
                WHEN TDTL.PROC_DT &lt; TDTL.RVSL_CYC_DT AND TDTL.ORIG_INBS_KEY IS NULL
                THEN
                   1
                ELSE
                   0
               END  RVSL_ERROR,
             CASE
                WHEN  TDTL.PROC_DT = TDTL.RVSL_CYC_DT  AND NOT TDTL.TR_CD IN ('3002','3004','1501','1502','1503','1504','1505')
                THEN 1
                ELSE 0
             END  RVSL_SAME_DAY,
             TDTL.REF_KEY TDTL_REF_KEY
            from GL_JOURNAL_LOGS JLOG, 
                 Transact_Details TDTL, 
                 FUND_DESC FDDC, 
                 FD_RATES FDRT, 
                 BEN_TYPES BNTP, 
                 SYS_BEN_TYPES SBNT, 
                 LEDGER_ACCOUNTS ACCT, 
                 TRANS_MAP_OVRD TMOV, 
                 TRANSACTION_MAP TMAP 
             WHERE JLOG.JRNL_CD = '0' 
             AND JLOG.SRC_CD = '2' 
             AND JLOG.MKEY_FD_NUM &lt;&gt; 0 
             AND NVL(JLOG.TMOV_KEY, -1) &gt; 0 
             AND NVL(JLOG.ORIG_SCAT_KEY, 1) = 1 
             AND JLOG.Scat_key = TDTL.SCAT_KEY 
             AND JLOG.TR_CD = TDTL.TR_CD 
             AND JLOG.CASE_KEY = TDTL.CASE_KEY 
             AND JLOG.TR_REF_NO = TDTL.TR_REF_NO 
             AND JLOG.ACCT_KEY = ACCT.ACCT_KEY 
             AND JLOG.TMOV_KEY = TMOV.TMOV_KEY 
             AND NVL(TDTL.ORIG_SCAT_KEY, 1) = 1 
             AND TDTL.STAT_CD &lt;&gt; '4' 
             AND TDTL.FD_DESC_ID = FDDC.FD_DESC_ID 
             AND TDTL.FDRT_KEY = FDRT.FDRT_KEY 
             AND BNTP.BNTP_KEY = FDRT.BNTP_KEY 
             AND BNTP.SBNT_KEY  (+) = SBNT.SBNT_KEY 
             AND TMOV.TMAP_KEY = TMAP.TMAP_KEY 
             AND TMOV.CO_CD = BNTP.CO_CD 
             AND DECODE(FDDC.MKEY_FD_NUM, NULL, TMAP.MKEY_FD_NUM, FDDC.MKEY_FD_NUM) = TMAP.MKEY_FD_NUM;
  </code></pre>
  
  <p>Any tips / advice would be greatly appreciated!</p>
  
  <p>Explain Plan</p>
  
  <pre><code>Plan hash value: 4157721082
  
  -----------------------------------------------------------------------------------------------------------
  | Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------------------
  |   0 | INSERT STATEMENT                       |                  |     1 |   212 |  1596   (1)| 00:00:20 |
  |   1 |  LOAD AS SELECT                        | GL_JLOG_DETAILS  |       |       |            |          |
  |   2 |   NESTED LOOPS                         |                  |       |       |            |          |
  |   3 |    NESTED LOOPS                        |                  |     1 |   212 |  1596   (1)| 00:00:20 |
  |   4 |     NESTED LOOPS                       |                  |     1 |   195 |  1595   (1)| 00:00:20 |
  |   5 |      NESTED LOOPS                      |                  |     1 |   190 |  1594   (1)| 00:00:20 |
  |   6 |       NESTED LOOPS                     |                  |    12 |  2172 |  1582   (1)| 00:00:19 |
  |*  7 |        HASH JOIN                       |                  |    12 |  2004 |  1570   (1)| 00:00:19 |
  |   8 |         TABLE ACCESS FULL              | FUND_DESC        |   168 |  1176 |     4   (0)| 00:00:01 |
  |   9 |         NESTED LOOPS                   |                  |       |       |            |          |
  |  10 |          NESTED LOOPS                  |                  |   257 | 41120 |  1566   (1)| 00:00:19 |
  |  11 |           NESTED LOOPS                 |                  |   257 | 22359 |   537   (0)| 00:00:07 |
  |  12 |            NESTED LOOPS                |                  |   257 | 20817 |   280   (0)| 00:00:04 |
  |* 13 |             TABLE ACCESS BY INDEX ROWID| GL_JOURNAL_LOGS  |   257 | 18504 |    23   (0)| 00:00:01 |
  |* 14 |              INDEX RANGE SCAN          | IDX_ORIGSCATKEY  |   690 |       |     4   (0)| 00:00:01 |
  |  15 |             TABLE ACCESS BY INDEX ROWID| TRANS_MAP_OVRD   |     1 |     9 |     1   (0)| 00:00:01 |
  |* 16 |              INDEX UNIQUE SCAN         | TMOV_PK          |     1 |       |     0   (0)| 00:00:01 |
  |  17 |            TABLE ACCESS BY INDEX ROWID | TRANSACTION_MAP  |     1 |     6 |     1   (0)| 00:00:01 |
  |* 18 |             INDEX UNIQUE SCAN          | TMAP_PK          |     1 |       |     0   (0)| 00:00:01 |
  |* 19 |           INDEX RANGE SCAN             | IX_AML8890       |     3 |       |     3   (0)| 00:00:01 |
  |* 20 |          TABLE ACCESS BY INDEX ROWID   | TRANSACT_DETAILS |     1 |    73 |     4   (0)| 00:00:01 |
  |  21 |        TABLE ACCESS BY INDEX ROWID     | FD_RATES         |     1 |    14 |     1   (0)| 00:00:01 |
  |* 22 |         INDEX UNIQUE SCAN              | FDRT_PK          |     1 |       |     0   (0)| 00:00:01 |
  |* 23 |       TABLE ACCESS BY INDEX ROWID      | BEN_TYPES        |     1 |     9 |     1   (0)| 00:00:01 |
  |* 24 |        INDEX UNIQUE SCAN               | BNTP_PK          |     1 |       |     0   (0)| 00:00:01 |
  |  25 |      TABLE ACCESS BY INDEX ROWID       | SYS_BEN_TYPES    |     1 |     5 |     1   (0)| 00:00:01 |
  |* 26 |       INDEX UNIQUE SCAN                | SBNT_PK          |     1 |       |     0   (0)| 00:00:01 |
  |* 27 |     INDEX UNIQUE SCAN                  | ACCT_PK          |     1 |       |     0   (0)| 00:00:01 |
  |  28 |    TABLE ACCESS BY INDEX ROWID         | LEDGER_ACCOUNTS  |     1 |    17 |     1   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     7 - access("TDTL"."FD_DESC_ID"="FDDC"."FD_DESC_ID")
         filter("TMAP"."MKEY_FD_NUM"=DECODE(TO_CHAR("FDDC"."MKEY_FD_NUM"),NULL,"TMAP"."MKEY_FD_NUM","
                FDDC"."MKEY_FD_NUM"))
    13 - filter("JLOG"."TMOV_KEY" IS NOT NULL AND "JLOG"."SRC_CD"='2' AND "JLOG"."MKEY_FD_NUM"&lt;&gt;0 
                AND NVL("TMOV_KEY",(-1))&gt;0 AND "JLOG"."JRNL_CD"='0')
    14 - access(NVL("ORIG_SCAT_KEY",1)=1)
    16 - access("JLOG"."TMOV_KEY"="TMOV"."TMOV_KEY")
    18 - access("TMOV"."TMAP_KEY"="TMAP"."TMAP_KEY")
    19 - access("JLOG"."SCAT_KEY"="TDTL"."SCAT_KEY" AND "JLOG"."CASE_KEY"="TDTL"."CASE_KEY")
         filter("TDTL"."FD_DESC_ID" IS NOT NULL AND "TDTL"."STAT_CD"&lt;&gt;'4' AND 
                "JLOG"."CASE_KEY"="TDTL"."CASE_KEY")
    20 - filter("TDTL"."FDRT_KEY" IS NOT NULL AND NVL("TDTL"."ORIG_SCAT_KEY",1)=1 AND 
                "JLOG"."TR_CD"="TDTL"."TR_CD" AND "JLOG"."TR_REF_NO"="TDTL"."TR_REF_NO")
    22 - access("TDTL"."FDRT_KEY"="FDRT"."FDRT_KEY")
    23 - filter("TMOV"."CO_CD"="BNTP"."CO_CD")
    24 - access("BNTP"."BNTP_KEY"="FDRT"."BNTP_KEY")
    26 - access("BNTP"."SBNT_KEY"="SBNT"."SBNT_KEY")
    27 - access("JLOG"."ACCT_KEY"="ACCT"."ACCT_KEY")
  </code></pre>