Scenerio:
I am writing from a source file(no transform) to a OCI stage with 20 fields. The first 7 fields are key fields and they are setup as unique constraint key fields in Oracle. Per Informix/Ascential techs, I have the array size set to 1 and rows per transaction set to 1.
I am not able to acheive more than a 45-65 rec/sec ratio.
Short of using SQLLDR from the command line, is there anything more I can do to speed up this performance?
--
Michael Feckler mfeckler@onel.com
Minneapolis, MN
952-996-9145
Writing to OCI Stage
1) Check the target table for triggers, constraints, indexes, etc. These impact performance of loading a table no matter what the ETL tool.
2) Is your SQL action insert, insert else update, update else insert, update, etc. If youre doing inserts only, you SHOULD be using SQL*Loader. If youre doing inserts & updates, try separating your inserts and updates, and using SQL*Loader for the inserts. Your SQL action has HUGE ramifications as to the performance, no matter what the ETL tool.
3) Why are you commiting after every row? Try stretching out the commit count a bit.
4) Whats the utilization on the database server? Are there enough free resources? Is the server CPU utilization at 100%, how about disks?
5) Whats the network like between the DataStage server and the database server? Are you on the backbone, 10mbit, 100mbit?
There are no easy answers. Your situation is a perfect example of how your ETL tool has nothing to do with the performance of a load. You are completely at the mercy of your target database, hardware, and network.
mfeckler@onel.com on 22-Aug-2001 15:14
Please respond to datastage-users@oliver.com
To: datastage-users
cc:
Subject: Writing to OCI Stage
Scenerio:
I am writing from a source file(no transform) to a OCI stage with 20 fields. The first 7 fields are key fields and they are setup as unique constraint key fields in Oracle. Per Informix/Ascential techs, I have the array size set to 1 and rows per transaction set to 1.
I am not able to acheive more than a 45-65 rec/sec ratio.
Short of using SQLLDR from the command line, is there anything more I can do to speed up this performance?
--
Michael Feckler mfeckler@onel.com
Minneapolis, MN
952-996-9145
2) Is your SQL action insert, insert else update, update else insert, update, etc. If youre doing inserts only, you SHOULD be using SQL*Loader. If youre doing inserts & updates, try separating your inserts and updates, and using SQL*Loader for the inserts. Your SQL action has HUGE ramifications as to the performance, no matter what the ETL tool.
3) Why are you commiting after every row? Try stretching out the commit count a bit.
4) Whats the utilization on the database server? Are there enough free resources? Is the server CPU utilization at 100%, how about disks?
5) Whats the network like between the DataStage server and the database server? Are you on the backbone, 10mbit, 100mbit?
There are no easy answers. Your situation is a perfect example of how your ETL tool has nothing to do with the performance of a load. You are completely at the mercy of your target database, hardware, and network.
mfeckler@onel.com on 22-Aug-2001 15:14
Please respond to datastage-users@oliver.com
To: datastage-users
cc:
Subject: Writing to OCI Stage
Scenerio:
I am writing from a source file(no transform) to a OCI stage with 20 fields. The first 7 fields are key fields and they are setup as unique constraint key fields in Oracle. Per Informix/Ascential techs, I have the array size set to 1 and rows per transaction set to 1.
I am not able to acheive more than a 45-65 rec/sec ratio.
Short of using SQLLDR from the command line, is there anything more I can do to speed up this performance?
--
Michael Feckler mfeckler@onel.com
Minneapolis, MN
952-996-9145
1) Try using parallel inserts.
2) Increase the rows per transaction count.
3) Try using direct insert (8i supports). With this ORAOCI is as good as ORABULK (SQL*LOADER)
Regds
Vivek
> -----Original Message-----
> From: Michael Feckler [SMTP:mfeckler@onel.com]
> Sent: Thursday, August 23, 2001 12:45 AM
> To: datastage-users@oliver.com
> Subject: Writing to OCI Stage
>
> Scenerio:
>
> I am writing from a source file(no transform) to a OCI stage with 20
> fields. The first 7 fields are key fields and they are setup as unique
> constraint key fields in Oracle.
> Per Informix/Ascential techs, I have the array size set to 1 and rows
> per transaction set to 1.
>
> I am not able to acheive more than a 45-65 rec/sec ratio.
>
> Short of using SQLLDR from the command line, is there anything more I
> can do to speed up this performance?
>
> --
> Michael Feckler mfeckler@onel.com
> Minneapolis, MN
> 952-996-9145
>
2) Increase the rows per transaction count.
3) Try using direct insert (8i supports). With this ORAOCI is as good as ORABULK (SQL*LOADER)
Regds
Vivek
> -----Original Message-----
> From: Michael Feckler [SMTP:mfeckler@onel.com]
> Sent: Thursday, August 23, 2001 12:45 AM
> To: datastage-users@oliver.com
> Subject: Writing to OCI Stage
>
> Scenerio:
>
> I am writing from a source file(no transform) to a OCI stage with 20
> fields. The first 7 fields are key fields and they are setup as unique
> constraint key fields in Oracle.
> Per Informix/Ascential techs, I have the array size set to 1 and rows
> per transaction set to 1.
>
> I am not able to acheive more than a 45-65 rec/sec ratio.
>
> Short of using SQLLDR from the command line, is there anything more I
> can do to speed up this performance?
>
> --
> Michael Feckler mfeckler@onel.com
> Minneapolis, MN
> 952-996-9145
>
Michael,
Follow these guidelines:
If all rows will be inserts then make sure the sql action is insert rows without clearing. If this is set to update or insert or vice versa will cause a major performance hit.
I would also set the array and transaction size to about 1000. This is usually a good starting point and can be further tweaked with testing.
In most cases if all the rows are inserts then doing the above will have about the same speed of loading compared to a non direct load with SqlLoader. To really blast the rows in if inserts than use the sqlloader with the DIRECT=TRUE. There are some other considerations that you must be aware of for this function. It is advisable to visit with your Oracle DBA to discuss these.
If some of the rows are updates with all 20 columns it may be faster to include them in the loader file and execute a delete from within the job for each row. This way the rows that would be updated will be removed first and then inserted.
______________________________
Rick Schirm
VP Operations
2201 Harwood Rd.
Bedford, TX 76021
mailto:rick.schirm@performart.com
http://www.performart.com
Phone 817-318-6278
______________________________
-----Original Message-----
From: Michael Feckler [mailto:mfeckler@onel.com]
Sent: Wednesday, August 22, 2001 2:15 PM
To: datastage-users@oliver.com
Subject: Writing to OCI Stage
Scenerio:
I am writing from a source file(no transform) to a OCI stage with 20 fields. The first 7 fields are key fields and they are setup as unique constraint key fields in Oracle. Per Informix/Ascential techs, I have the array size set to 1 and rows per transaction set to 1.
I am not able to acheive more than a 45-65 rec/sec ratio.
Short of using SQLLDR from the command line, is there anything more I can do to speed up this performance?
--
Michael Feckler mfeckler@onel.com
Minneapolis, MN
952-996-9145
Follow these guidelines:
If all rows will be inserts then make sure the sql action is insert rows without clearing. If this is set to update or insert or vice versa will cause a major performance hit.
I would also set the array and transaction size to about 1000. This is usually a good starting point and can be further tweaked with testing.
In most cases if all the rows are inserts then doing the above will have about the same speed of loading compared to a non direct load with SqlLoader. To really blast the rows in if inserts than use the sqlloader with the DIRECT=TRUE. There are some other considerations that you must be aware of for this function. It is advisable to visit with your Oracle DBA to discuss these.
If some of the rows are updates with all 20 columns it may be faster to include them in the loader file and execute a delete from within the job for each row. This way the rows that would be updated will be removed first and then inserted.
______________________________
Rick Schirm
VP Operations
2201 Harwood Rd.
Bedford, TX 76021
mailto:rick.schirm@performart.com
http://www.performart.com
Phone 817-318-6278
______________________________
-----Original Message-----
From: Michael Feckler [mailto:mfeckler@onel.com]
Sent: Wednesday, August 22, 2001 2:15 PM
To: datastage-users@oliver.com
Subject: Writing to OCI Stage
Scenerio:
I am writing from a source file(no transform) to a OCI stage with 20 fields. The first 7 fields are key fields and they are setup as unique constraint key fields in Oracle. Per Informix/Ascential techs, I have the array size set to 1 and rows per transaction set to 1.
I am not able to acheive more than a 45-65 rec/sec ratio.
Short of using SQLLDR from the command line, is there anything more I can do to speed up this performance?
--
Michael Feckler mfeckler@onel.com
Minneapolis, MN
952-996-9145