Commit frequency when using Oracle Load/Append

jreddy · Post by **jreddy** » Wed Aug 13, 2008 12:43 pm

Hi,

I have a job that reads about 92 million records from a source table and loads it to a oracle table. I have the target stage in this case have a LOAD/Append option. I did not set the DIRECT=TRUE. But this job takes forever and i am trying to figure out if there is a way to speed up this load.
The ORA_UPSERT_COMMIT parameters are set to 200 minutes and 500000 rows. But i think (maybe i am wrong) but these parameters are not used by SQLLDR that is actually performing the bulk loads.

When i see the director log for this job, it shows messages such as
'Commit point reached - logical record count 7298709'
'Commit point reached - logical record count 7301043'
and each such statement shows that it is actually commiting after a couple of thousand records only. My question is - is this the actual commit frequency? Is there a parameter to control the frequency of these commits.

I tried DIRECT=TRUE for this job and it apparently is not committing anything until it reads the 92 mil records from source, so i decided its not going to work for this job. And many other jobs with similar volumes will be running simultaneously in real time.

I would appreciate your suggestions.
Thanks

gabrielac · Post by **gabrielac** » Tue Aug 19, 2008 7:47 am

The ORA_UPSERT_COMMIT parameters are specific to using the Upsert option, not the Load option.
When using the Load option, one temporary file is created for each partition that is running for the Oracle stage. When the job finishes running for all the source rows, then the sqlloader is called for each partition. Optionally, depending on the index option selected, the indexes are rebuilt. That could be the reason why your job seems to be taking forever.

HTH,
Gaby

chulett · Post by **chulett** » Tue Aug 19, 2008 8:05 am

jreddy wrote:I tried DIRECT=TRUE for this job and it apparently is not committing anything until it reads the 92 mil records from source

There is no concept of intermediate commits in a direct path load, that is only applicable to a 'conventional' load because it does normal inserts.

munch9 · Post by **munch9** » Tue Aug 19, 2008 8:48 am

For conventional path load add the following to APT_ORACLE_LOAD_OPTIONS to set the commit interval where nnnnn is the number of rows

OPTIONS(ROWS=nnnnn)

Kryt0n · Post by **Kryt0n** » Sun Jun 28, 2009 6:12 pm

Did you find this resolved your commit frequency? Did you still get the messages in the log?

We have updated our commit row size to 50000 and bind size to 10M but still have the logs filling up with "Commit point reached". Not much difference in time to load between the default settings and 50K choices either! Since this Windows version is giving us problems all over the place, always possible this is just another feature but interested to know if it did resolve your issue.

Sreenivasulu · Post by **Sreenivasulu** » Mon Jun 29, 2009 10:57 am

What munch9 says will work surely. That is the best option.

Regards
Sreeni

Kryt0n · Post by **Kryt0n** » Mon Jun 29, 2009 3:17 pm

Surely you would think so... but it didn't...

The Oracle log identifies a bulk size of 50000 but I still get the stream of commit point messages every 30 rows... I was expecting them to disappear with the change, hence my question.

I will worry more about how it performs when we move on to our Unix environment but was looking for some insight from others with the same problem first.

chulett · Post by **chulett** » Mon Jun 29, 2009 4:30 pm

We'd have to know precisely what kind of "load" you are doing to be able to answer that, Kryt0n.

Kryt0n · Post by **Kryt0n** » Mon Jun 29, 2009 6:22 pm

Conventional load

Load options set to DIRECT=FALSE, PARALLEL=TRUE... along with ROWS=50000, BINDSIZE=10485760 and READSIZE=10485760

chulett · Post by **chulett** » Mon Jun 29, 2009 11:53 pm

ROWS does control commit points in a Conventional load but you may be overriding your setting by also including BINDSIZE and READSIZE. Try just using ROWS without the other two, it will then calculate the others appropriately for the bind array from what I recall.

Kryt0n · Post by **Kryt0n** » Tue Jun 30, 2009 12:14 am

I did, without the bindsize it takes a default size (256000 if I remember correctly - which is unlikely!) and reduces the rows value to fit (1600 rows for my data set). The readsize is set because it needs to be at least as large as the bindsize.

chulett · Post by **chulett** » Tue Jun 30, 2009 5:49 am

Right, BINDSIZE and READSIZE must be the same. How 'fat' are your rows, perhaps they need to be larger? Regardless, this is strictly a sqlldr issue so your DBA should be able to help you figure out what's going on and get it working 'properly' for you I would think.

I really need to get my hands on a good sqlldr book.

Kryt0n · Post by **Kryt0n** » Tue Jul 14, 2009 11:23 pm

Seems it was either Windows version or Oracle related. Our actual dev environment is committing with the frequency we requested, can't say I'm going to try find out which it was...

psbans · Post by **psbans** » Fri Apr 30, 2010 10:02 am

For newcomers: to Suppress "Commit point reached" log add SILENT=FEEDBACK option

like this:

APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,SILENT=FEEDBACK)

Kryt0n · Post by **Kryt0n** » Sun May 02, 2010 4:53 pm

psbans wrote:For newcomers: to Suppress "Commit point reached" log add SILENT=FEEDBACK option

like this:

APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,SILENT=FEEDBACK)

Not that that helps the commit frequency but may save a few seconds.

FEEDBACK=SILENT makes more sense though, did you mis-type or they really configured it that way?

DSXchange

Commit frequency when using Oracle Load/Append

Commit frequency when using Oracle Load/Append

Re: Commit frequency when using Oracle Load/Append