Hello,
I have a job in Datastage version 7.5 that are making the join of three tables in OCI and then performs the loading of 50 million records to an Oracle table.
The source and target is in Oracle 11G and in the source we are using dblink.
Given the poor performance of the Insert and the large volume of data, we replaced the OCI stage for OraBulk, but without success, the performance is identical to the insert.
Does anyone have any suggestions to optimize the performance of the job?
Improve performance with OraBulk
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 176
- Joined: Wed Nov 15, 2006 5:48 am
He meant "factors" rather than literally "parameters".
Sqlldr isn't a magic bullet that makes any load faster, you need to determine if it is applicable depending on the nature of the load being performed. And how to improve any bulk load would be a good conversation to have with your DBA. They should be aware of all of the relevant factors at play in your specific case.
Sqlldr isn't a magic bullet that makes any load faster, you need to determine if it is applicable depending on the nature of the load being performed. And how to improve any bulk load would be a good conversation to have with your DBA. They should be aware of all of the relevant factors at play in your specific case.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 176
- Joined: Wed Nov 15, 2006 5:48 am
For the tests that we had made we had verified that the poor performance of the job is due to two factors:
- The query uses a dblink to source that determines the performance of the job
- The recording is too large, has more than 200 columns, and a large part of columns have the format varchar2(255)
There is no way to reduce the size of the field to load to the table, so the only way to increase the performance of the job will be to optimize the query of source. So the OraBulk can have a larger volume of data to load.
The gurus agree?
- The query uses a dblink to source that determines the performance of the job
- The recording is too large, has more than 200 columns, and a large part of columns have the format varchar2(255)
There is no way to reduce the size of the field to load to the table, so the only way to increase the performance of the job will be to optimize the query of source. So the OraBulk can have a larger volume of data to load.
The gurus agree?
Rai, Sorry if its too late in the day to comment on this ..
Did you consider redoing the join in datastage, making best use of the parallelism etc.. perhaps even ading a few parallel hints on your source query
Also, you could write data into mulitple files in your first job and then have a clever script to load them using sqlldr parallely into the tables.
Did you consider redoing the join in datastage, making best use of the parallelism etc.. perhaps even ading a few parallel hints on your source query
Also, you could write data into mulitple files in your first job and then have a clever script to load them using sqlldr parallely into the tables.