Hi,
Insert happening very slow after sqlldr happening in my program. Please find the below workflow of my program.
1) SQLLDR will be called, it will insert around 4 lakhs records in 'TEMP" table using direct path load.Response time is good here.
2)After, SQLLDR has finished its job, my procedure will be called, there every cursor statement working fine, but when it comes for "INSERT" statement it takes almost 40 mins.
3)Insert statement like this
INSERT INTO HISTORY_TABLE(<COLUMN1>,<COLUMN2>,..etc) SELECT (<COLUMN1>,.<COLUMN2>...etc) from TEMP_TABLE;
4)select records from temp table which records were inserted during sqlldr(direct=true),before procedure call.
5)I check explain plan for the Insert statement it shows conventional path loading
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 409K| 143M| 6752 (2)|
| 1 | LOAD TABLE CONVENTIONAL | HISTORY_TABLE | | | |
| 2 | TABLE ACCESS FULL | TEMP_TABLE | 409K| 143M| 6752 (2)|
----------------------------------------------------------------------------------
6)Since i have no where condition in my insert statement it go for an full table scan.
Kindly advice to impove its performance.
My db is oracle 11g r2(11.2.0.3.0)
OS-Windows server 2008 r2
Thanks
faiz