Insert is taking long time

The insert procedure which is select all the row at this moment taking long time.

Select query is giving result in only minutes.

We are getting waits on
log file switch completion
log file switch (checkpoint incomplete)
Latch free

Size of redo log is 50MB.

Please have a look.


Query:
 
Insert Query:
INSERT /*+parallel(TB_DIM_ALL_CUST_ACCT,3)*/
INTO TB_DIM_ALL_CUST_ACCT
  (ALL_CUST_SKEY,
   CUST_SKEY,
   MDM_PARTY_ID,
   MDM_SRCE_ID,
   CUST_SSK,
   DDD_NUM,
   DDD_SUB_CAT_CD,
   HIN,
   DEA_NUM,
   SRCE_CUST_TYPE_CD,
   SRCE_MDM_CUST_TYPE_CD,
   SRCE_ORG_NAME,
   SRCE_FULL_NAME,
   STAT_CD,
   CUST_EXCPTN_FLG,
   EFF_END_DATE,
   MDM_INS_DATE,
   MDM_LAST_MOD_DATE,
   DW_INS_DATE,
   DW_LAST_MOD_DATE,
   DW_LAST_MOD_BY,
   DW_SRCE_ID,
   CURR_IND)
--  explain plan for
  SELECT B.ALL_CUST_SKEY,
         B.CUST_SKEY,
         B.MDM_PARTY_ID,
         B.MDM_SRCE_ID,
         B.CUST_SSK,
         B.DDD_NUM,
         B.DDD_SUB_CAT_CD,
         B.HIN,
         B.DEA_NUM,
         B.SRCE_CUST_TYPE_CD,
         B.SRCE_MDM_CUST_TYPE_CD,
         B.SRCE_ORG_NAME,
         B.SRCE_FULL_NAME,
         B.STAT_CD,
         B.CUST_EXCPTN_FLG,
         B.EFF_END_DATE,
         B.MDM_INS_DATE,
         B.MDM_LAST_MOD_DATE,
         SYSDATE,
         SYSDATE,
         B.DW_LAST_MOD_BY,
         B.DW_SRCE_ID,
         B.CURR_IND
    FROM TB_DIM_ALL_CUST_ACCT_HIST B
   WHERE (B.CDW_EFF_START_DATE = TO_DATE('2009-03-25', 'YYYY-MM-DD') + 1 OR
         B.CUST_EXCPTN_FLG = 'Y')
     AND B.CURR_IND = 'C'
            AND NOT EXISTS (SELECT 1
            FROM TB_DIM_ALL_CUST_ACCT A
           WHERE A.ALL_CUST_SKEY = B.ALL_CUST_SKEY)
 
Explain Plan:
 
 
SQL Statement which produced this data:
  select * from table(dbms_xplan.display())
 
PLAN_TABLE_OUTPUT
Plan hash value: 713779952
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                           |  2513K|   431M|  8155   (2)| 00:02:27 |       |       |
|   1 |  NESTED LOOPS ANTI    |                           |  2513K|   431M|  8155   (2)| 00:02:27 |       |       |
|   2 |   PARTITION HASH ALL  |                           |  2513K|   400M|  8155   (2)| 00:02:27 |     1 |     3 |
|*  3 |    TABLE ACCESS FULL  | TB_DIM_ALL_CUST_ACCT_HIST |  2513K|   400M|  8155   (2)| 00:02:27 |     1 |     3 |
|*  4 |   INDEX FAST FULL SCAN| PK_TB_DIM_ALL_CUST_ACCT   |     1 |    13 |     0   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("B"."CDW_EFF_START_DATE"=TO_DATE('2009-03-26 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR 
              "B"."CUST_EXCPTN_FLG"='Y') AND "B"."CURR_IND"='C')
   4 - filter("A"."ALL_CUST_SKEY"="B"."ALL_CUST_SKEY")

Open in new window

sachin_dbaAsked:
Who is Participating?
 
mganeshConnect With a Mentor Commented:
At first look, it looks you need to increase the redolog file size. 50MB is the default and is seldom enough for DMLs of decent weightage.

There are two things you can do initially:

1. Check the number of indexes on TB_DIM_ALL_CUST_ACCT and see if they are same in both your environments. Unwanted indexes slow down inserts.

2. I would suggest you to increase your redolog size to 256 MB at the minimum and observe the waits
0
 
sachin_dbaAuthor Commented:
On the dev DB where there is only one partition, and insert is running in 10 mins.

Explain plan and waits on session attached.
Dev:
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                           |     1 |   173 | 13889   (6)| 00:02:47 |       |       |
|   1 |  NESTED LOOPS ANTI     |                           |     1 |   173 | 13889   (6)| 00:02:47 |       |       |
|   2 |   PARTITION HASH SINGLE|                           |     1 |   167 | 13888   (6)| 00:02:47 |     1 |     1 |
|*  3 |    TABLE ACCESS FULL   | TB_DIM_ALL_CUST_ACCT_HIST |     1 |   167 | 13888   (6)| 00:02:47 |     1 |     1 |
|*  4 |   INDEX UNIQUE SCAN    | PK_TB_DIM_ALL_CUST_ACCT   |     1 |     6 |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("B"."CDW_EFF_START_DATE"=TO_DATE('2009-03-26 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR 
              "B"."CUST_EXCPTN_FLG"='Y') AND "B"."CURR_IND"='C')
   4 - access("A"."ALL_CUST_SKEY"="B"."ALL_CUST_SKEY")
 
Test:
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                           |  2513K|   431M|  8155   (2)| 00:02:27 |       |       |
|   1 |  NESTED LOOPS ANTI    |                           |  2513K|   431M|  8155   (2)| 00:02:27 |       |       |
|   2 |   PARTITION HASH ALL  |                           |  2513K|   400M|  8155   (2)| 00:02:27 |     1 |     3 |
|*  3 |    TABLE ACCESS FULL  | TB_DIM_ALL_CUST_ACCT_HIST |  2513K|   400M|  8155   (2)| 00:02:27 |     1 |     3 |
|*  4 |   INDEX FAST FULL SCAN| PK_TB_DIM_ALL_CUST_ACCT   |     1 |    13 |     0   (0)| 00:00:01 |       |       |

Open in new window

sess-waits.JPG
0
 
sachin_dbaAuthor Commented:
No one looked at the explain plan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.