Commit Size on Load Append.

sambit · Post by **sambit** » Tue Dec 30, 2008 4:04 pm

Hi,
I am using load append method to insert 30 million rows into oracle table. But it is commiting after every 64 records. For this there is huge numbers of Commit Point reached message that is consuming all the space and also the performance is degrading.

Please advise how I can set the commit interval to 10,000 or more.

Parameter currently using,

$APT_ORACLE_LOAD_OPTIONS=OPTIONS(DIRECT=FALSE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=FALSE,SKIP_UNUSABLE_INDEXES
=TRUE,ERRORS=1000000)

chulett · Post by **chulett** » Tue Dec 30, 2008 4:11 pm

You should investigate the use of the APT_ORAUPSERT_COMMIT_ROW_INTERVAL and APT_ORAUPSERT_COMMIT_TIME_INTERVAL environment variables. Or, if you'd rather use the Oracle OPTION, use ROWS=x there with whatever number you feel is appropriate.

sambit · Post by **sambit** » Tue Dec 30, 2008 4:21 pm

Thanks Chullet. I have tried the APT_ORAUPSERT_COMMIT_ROW_INTERVAL and APT_ORAUPSERT_COMMIT_TIME_INTERVAL but it is not working probably due to Load Append method. I also tried with Rows=10000 but I am not sure why it is working. Probably I need other valiables.

chulett · Post by **chulett** » Tue Dec 30, 2008 4:28 pm

Well... they certainly won't work with a direct path load as there are no commits done when DIRECT=TRUE. So, is the ROWS option working for you or not? Hard to tell for sure from the way you phrased it. Note that you may also need to increase the BINDSIZE depending on the length of your records so it can hold that number of rows:

http://www.orafaq.com/forum/t/84903/0/

sambit · Post by **sambit** » Tue Dec 30, 2008 4:30 pm

No Chullet. ROWS= option is not working. same thing for DIRECT = TRUE.

chulett · Post by **chulett** » Tue Dec 30, 2008 4:33 pm

Edited my post to help. Again, there are no commits done in a DIRECT=TRUE load, so don't expect to be able to control that there. And 64 is the default commit interval.

Have a look at the discussion I linked to.

kandyshandy · Post by **kandyshandy** » Tue Dec 30, 2008 5:41 pm

Craig, will there be any use of sub property ARRAY SIZE in oracle stage?

chulett · Post by **chulett** » Tue Dec 30, 2008 6:14 pm

I don't have access to the Oracle Enterprise stage to know if / how it (array size) would be leveraged by the stage for the Load option.

kandyshandy · Post by **kandyshandy** » Tue Dec 30, 2008 7:18 pm

Craig / Sambit, I just checked and wanted to share the information.

"Insert Array Size" sub property is available for UPSERT mdoe and not LOAD mode.

sambit · Post by **sambit** » Tue Dec 30, 2008 8:05 pm

Thanks for the updates. I just checked the link which Chulett provided and it helped me in getting the commit size to 10000. I am still exploring other possibilities of tuning my job further as it is taking huge amount of time.