Oracle Database Discussions

Hi we are using version 11.2.0.4 of Oracle exadata. We have an INSERT query which is doing batch insert(~230 rows at one shot) from informatica. From dba_hist_sqlstat i see the avg elapsed time/execution is ~7milli seconds. Now I didn't able to get the sql monitor but have the awr plan as below. I see from the gv$active_session_history , 90% of the time the INSERT waits on index "TAB1_PK" with wait event "cell single block physical read". The primary key index TAB1_PK is a composite key index on column(c1,c2,c4,c3_date). Below is the table and index stats. Table having size 144GB and its the only primary key index which is there in the table and no other indexes are there.

Now from the details wait events in gv$active_session_history , i can see its mainly the primary key index maintenance which is taking most of the DB time during data load. We do have ~50-60% of rows exists in the table which are not needed anymore and we can get those purged and are planning for that too. But some members in team suggesting purging those rows will make the data load faster. I am not able to relate this though. So want to understand from experts , if its true that reducing/purging the data from table will reduce the table/index size and thus help in improving the data load performance?

INSERT INTO TAB1(c1,c2,c3_date,c4,c5,c6,c7,c8,c9)  VALUES ( :1, :2, :3, :4, :5,

:6, :7, :8, :9)

-------------------------------------------------

| Id  | Operation                | Name | Cost  |

-------------------------------------------------

|   0 | INSERT STATEMENT         |      |     1 |

|   1 |  LOAD TABLE CONVENTIONAL |      |       |

-------------------------------------------------

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

-------------------------------------------------------------

   1 - INS$1

Note

-----

   - cpu costing is off (consider enabling it)