Oracle INSERTBUFF and DBCOMMIT settings

JMarkW — Thu, 15 Jan 2009 16:19:39 GMT

I'm using libname and proc append to load data from a SAS data sets into Oracle tables. The row length is extremely large and the number of records is fairly large. I'm trying to figure out the optimal settings for INSERTBUFF and DBCOMMIT. I understand DBCOMMIT has to be equal to or less that INSERTBUFF.

Does anybody know of documentation or have any rules of thumb on how to optimize for loading Oracle in this fashion? Message was edited by: JMarkW

Re: Oracle INSERTBUFF and DBCOMMIT settings

sbb — Thu, 15 Jan 2009 23:51:32 GMT

A SEARCH on the SAS support website http://support.sas.com/ yielded several DOC and technical paper hits to consider. You may also find that the SAS 9.2 DOC has some applicability on this topic, even though you may be running an earlier SAS version (consider the link below).

Also, a Google advanced search using the argument below was quite fruitful with gems to review:

optimize oracle performance site:sas.com

Also, keep aware of SAS HOTFIX maintenance, given the SAS support problem note listed below.

Scott Barry
SBBWorks, Inc.

SAS/ACCESS for Oracle: Maximizing Oracle Performance
http://support.sas.com/documentation/cdl/en/acreldb/59618/HTML/default/a003113611.htm

Problem Note 20712: Decreased performance might occur when you use bulk loading in SAS/ACCESS® Interface to Oracle
http://support.sas.com/kb/20/712.html

topic Re: Oracle INSERTBUFF and DBCOMMIT settings in SAS Procedures

Oracle INSERTBUFF and DBCOMMIT settings

Re: Oracle INSERTBUFF and DBCOMMIT settings