Commit frequency when using Oracle Load/Append
Moderators: chulett, rschirm, roy
Commit frequency when using Oracle Load/Append
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
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
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
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
Re: Commit frequency when using Oracle Load/Append
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.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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
I really need to get my hands on a good sqlldr book.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Not that that helps the commit frequency but may save a few seconds.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)
FEEDBACK=SILENT makes more sense though, did you mis-type or they really configured it that way?