Abhishek Sharma,
You can write the data to a sequential file and use the bulk load stage or sqlloader to load the data into Oracle.
Are you doing any lookups in your job (server job, not PX)? If so, are they live database connections or hash files? Live database lookups can slow your job down significantly.
Good Luck!
Tony Stark
Performance Issue
Moderators: chulett, rschirm, roy
-
tonystark622
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
Hi,
first try what Tony sugested.
All I can add is this :
Performance issues are complex,
statring with disks speed thru job design to network configuration/traffic.
so besides DS configurations and max I/O with the stage you use check disk speed and network configuration for your connection between the DS server and the DB server.
in case a single connection can't give you better performance try this:
if you'll take your job and split your file loading 2 jobs in paralel you might find that each has about the same speed as your 1 job.
in that case you make your job twice as fast, 4 jobs 4 times faster (almost) etc'.
what I would do, to show that the problem is not with DS is simple:
( or rather show I have a system/network configuration problem )
use a client tool to load a seq file to the DB both from the DS server and on the DB server as well.
this will give you an estimate limit of your load speed at DB server and over the network from your DS server.
if the speed you get, for your load, from the DS server, using a client load utility for your DB server, is the same as your job's then the problem is not with DS!!!
I hope this helps &
Good Luck,
Roy R.
first try what Tony sugested.
All I can add is this :
Performance issues are complex,
statring with disks speed thru job design to network configuration/traffic.
so besides DS configurations and max I/O with the stage you use check disk speed and network configuration for your connection between the DS server and the DB server.
in case a single connection can't give you better performance try this:
if you'll take your job and split your file loading 2 jobs in paralel you might find that each has about the same speed as your 1 job.
in that case you make your job twice as fast, 4 jobs 4 times faster (almost) etc'.
what I would do, to show that the problem is not with DS is simple:
( or rather show I have a system/network configuration problem )
use a client tool to load a seq file to the DB both from the DS server and on the DB server as well.
this will give you an estimate limit of your load speed at DB server and over the network from your DS server.
if the speed you get, for your load, from the DS server, using a client load utility for your DB server, is the same as your job's then the problem is not with DS!!!
I hope this helps &
Good Luck,
Roy R.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can isolate the cause to some extent by making a copy of the job that writes to a sequential file rather than to Oracle. Any difference in throughput is down either to Oracle or the interface to it (stage type and/or client software).
Also be aware of anything that may be happening in the Oracle environment. I remember a site where testing went well above expectations but production was really slow - they'd forgotten to tell us about (a) the database being quiesced for backups, and (b) scheduled bulk loads from another source into the Oracle database!
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Also be aware of anything that may be happening in the Oracle environment. I remember a site where testing went well above expectations but production was really slow - they'd forgotten to tell us about (a) the database being quiesced for backups, and (b) scheduled bulk loads from another source into the Oracle database!
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
-
degraciavg
- Premium Member
- Posts: 39
- Joined: Tue May 20, 2003 3:36 am
- Location: Singapore
In addition to what has been suggested above, you might want to try these...
1. split your job into two streams - one doing the update only while another is doing insert only.
2. for the job that updates, make sure that you are using the right index to avoid full table scans - this is the rule-of-thumb for updates!
3. for the insert job, disable table constraints -> use bulk load -> enable table constraints
important sql fact: UPDATE is always more costly than INSERT.
in DataStage, whenever possible, avoid the "Update existing row or insert new row" (or vice versa) feature.
you'll be surprised how much faster your process will be [;)]
regards,
vladimir
1. split your job into two streams - one doing the update only while another is doing insert only.
2. for the job that updates, make sure that you are using the right index to avoid full table scans - this is the rule-of-thumb for updates!
3. for the insert job, disable table constraints -> use bulk load -> enable table constraints
important sql fact: UPDATE is always more costly than INSERT.
in DataStage, whenever possible, avoid the "Update existing row or insert new row" (or vice versa) feature.
you'll be surprised how much faster your process will be [;)]
regards,
vladimir
quote:Originally posted by degraciavg
[br]3. for the insert job, disable table constraints -> use bulk load -> enable table constraints
I would strongly advise against employing this particular suggestion unless you have a method to restore the table to its previous state in case table constraints failed to be re-enabled.
In fact, for all DataStage jobs, consideration need to be placed on how to restore from failure due to the lack of full transactional control (a major issue still pending with Ascential).
-T.J.
* * *
... now if this can make breakfast, my life is complete.
[br]3. for the insert job, disable table constraints -> use bulk load -> enable table constraints
I would strongly advise against employing this particular suggestion unless you have a method to restore the table to its previous state in case table constraints failed to be re-enabled.
In fact, for all DataStage jobs, consideration need to be placed on how to restore from failure due to the lack of full transactional control (a major issue still pending with Ascential).
-T.J.
* * *
... now if this can make breakfast, my life is complete.