commit frequency in Oracle Enterprise stage
Moderators: chulett, rschirm, roy
commit frequency in Oracle Enterprise stage
Hi,
Is there any way to increase the commit frequency when loading the data into oracle table using Load and Appened method. Right now Commit points occurs at 64 rows by default .Also, If i used OPTION(DIRECT=FALSE,PARRELEL=TRUE,ROWS=1000), then commit point occurs at 587.
Is there any way to increase the commit frequency when loading the data into oracle table using Load and Appened method. Right now Commit points occurs at 64 rows by default .Also, If i used OPTION(DIRECT=FALSE,PARRELEL=TRUE,ROWS=1000), then commit point occurs at 587.
Nirdesh Kumar
-
- Participant
- Posts: 15
- Joined: Wed May 27, 2009 2:42 am
- Location: PARIS
The settings of this variable apply to the supported parallel database engines : DB2, Teradata and Oracle.nirdesh2 wrote:I think this parameter is used $APT_RDBMS_COMMIT_ROWS DB2 database. Still it is not considering this parameter values for commit.
___________________
Benoît CAYLA
benoit.cayla@exl-group.com
my blog (in French) http://www.exl-infosfr.com/
Benoît CAYLA
benoit.cayla@exl-group.com
my blog (in French) http://www.exl-infosfr.com/
What did you specify in your stage as "transaction size"?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi
need to set bindsize & readsize
example
$APT_ORACLE_LOAD_OPTIONS=OPTIONS(SKIP_INDEX_MAINTENANCE = TRUE,bindsize=8250000,readsize=8250000,rows=2000)
Thanks
Sanjay
need to set bindsize & readsize
example
$APT_ORACLE_LOAD_OPTIONS=OPTIONS(SKIP_INDEX_MAINTENANCE = TRUE,bindsize=8250000,readsize=8250000,rows=2000)
Thanks
Sanjay
nagarjuna wrote:If DIRECT = TRUE then is it not auto committed ...i think when using load option there is no commit interval ...for only upsert we have commit interval ..please correct me if i am wrong ..
That's certainly one way. The specific Oracle APT variables are mentioned here. And yes, a "direct path load" does no commits, that whole mechanism is bypassed which is part of where the speed comes from.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Have no idea about the DIRECT option but sounds fair enough comment. However, there is a commit interval for the load option. We have DIRECT set to FALSE (we load in parallel) and we do commits... more frequently than I would like but have not tried to play with the intervals.nagarjuna wrote:If DIRECT = TRUE then is it not auto committed ...i think when using load option there is no commit interval ...for only upsert we have commit interval ..please correct me if i am wrong ..
I believe there are two APT variables that need changing, one is for number of rows and the other a time interval
EDIT: looking at the environment variables, I must be thinking about the upsert options so ignore my last sentence...
Last edited by Kryt0n on Wed May 27, 2009 5:41 pm, edited 1 time in total.