Hi all,
I have a small question for research purposes.
I have a job that loads data from an sql server stage to Oracle stage. My question involves what is the commit interval set for the oracle stage by default?
Let's say my sql server source involves 10 million records, I don't think it will only commit after inserting 10 million records? It would be disasterous if the connection cuts of in between.
I can't seem to find a setting in Oracle Stage pertaining to this option (Specifying commit intervals).
Would any of you guys know? Thanks in advanced.
Oracle Stage Commit Point
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 222
- Joined: Tue Aug 30, 2005 2:07 am
- Location: pune
- Contact:
The Environment variables available to control the commit intervals are -
APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL
These two environment variables work together to specify how often target rows are committed when using the Upsert method to write to Oracle.
Commits are made whenever the time interval period has passed or the row interval is reached, whichever comes first. By default, commits are made every 2 seconds or 5000 rows.
The Bird
APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL
These two environment variables work together to specify how often target rows are committed when using the Upsert method to write to Oracle.
Commits are made whenever the time interval period has passed or the row interval is reached, whichever comes first. By default, commits are made every 2 seconds or 5000 rows.
The Bird
Deciding the Commit intervals.
Hi,
I appreciate if someone can throw light on how to decide the values for Row commit and Commit Time intervals for Oracle.
Thanks,
Sajarman.
I appreciate if someone can throw light on how to decide the values for Row commit and Commit Time intervals for Oracle.
Thanks,
Sajarman.
You will need to talk to your DBA in order to make this decision, if you have no business rules for this. The less commits you make, the faster a load can be, but the more the rollback segments fill up. If they reach their limit you will have errors. If you commit too often when you don't have to you end up having a lot of system overhead.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>