Performance Tuning
Moderators: chulett, rschirm, roy
Performance Tuning
Hi
I am tuning a select query used in datastage job.It is having 35 NVL functions. If i handle this in transformer, will it increase the performance of the job as a whole.This single query is taking 1 hr in the production environment.
Thanks and Regards
Shabnam
I am tuning a select query used in datastage job.It is having 35 NVL functions. If i handle this in transformer, will it increase the performance of the job as a whole.This single query is taking 1 hr in the production environment.
Thanks and Regards
Shabnam
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 83
- Joined: Sat Oct 28, 2006 6:25 am
Re: Performance Tuning
Tunning querries depends on n number of things i.e. whether db is paritioned ? whether you are using indexing efficiently ? Whether you are extracting only required collumns? ....
As far as handling derivation in transformer stage is concenred it again depends but db2 normally turns to be efficient in comparison to DS server. But worth trying it on both and do a comparison of stats!
As far as handling derivation in transformer stage is concenred it again depends but db2 normally turns to be efficient in comparison to DS server. But worth trying it on both and do a comparison of stats!
You said the query is taking about an hour to finish in production env. Was that the same case in dev. and uat etc.?
How many cpus do you have in your prod. box. You can probably create a multi-instance job which does selects and handles nvl in the transformer.
Extract your data in chunks depending upon the invocation id you send in.
Or you can do a simple select on the complete data set, partition it with a link partitioner and send it to different transformers (the number will depend upon how many cpus you have) and then do collect it at the end.
How many cpus do you have in your prod. box. You can probably create a multi-instance job which does selects and handles nvl in the transformer.
Extract your data in chunks depending upon the invocation id you send in.
Or you can do a simple select on the complete data set, partition it with a link partitioner and send it to different transformers (the number will depend upon how many cpus you have) and then do collect it at the end.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 19
- Joined: Wed Feb 15, 2006 11:08 am
Re: Performance Tuning
HI,
as said by the others indexing also affects.
better to use DB than DS.
My suggestin is while populating data into Prd Db use Std Prc stage and write a proc apply ur NVL functions which wld be better i think
chk it in Toad sql editor
as said by the others indexing also affects.
better to use DB than DS.
My suggestin is while populating data into Prd Db use Std Prc stage and write a proc apply ur NVL functions which wld be better i think
chk it in Toad sql editor
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
Another checks could to see if your query is picking up the right indices.
Sometimes the Oracle optomizer does not pick up the indices you would like it to pick up.
In such cases adding the correct HINTS can increase the performance dramatically.
Also ask your DBA's to run stats on the involved tables, if it not done for a while.
Sometimes the Oracle optomizer does not pick up the indices you would like it to pick up.
In such cases adding the correct HINTS can increase the performance dramatically.
Also ask your DBA's to run stats on the involved tables, if it not done for a while.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
Hi all,
In other utilities (Toad) also it is taking that much time. In other environment, the data is very less, so simulation is a long process( Need to do a data refresh from production server). I am not sure about the number of CPU's in the server as i am new to this project, but raised a ticket to OS team to know it.
In other utilities (Toad) also it is taking that much time. In other environment, the data is very less, so simulation is a long process( Need to do a data refresh from production server). I am not sure about the number of CPU's in the server as i am new to this project, but raised a ticket to OS team to know it.
If it is taking almost the same time in TOAD, then you don't have option other than to tune it, before putting using it in datastage. The performance of the query cannot be enhanced inside datastage.
Tip:
Instead of doing a data refresh from production server, can you point your query to run against the production(If performance to production environment is not an issue)?
Tip:
Instead of doing a data refresh from production server, can you point your query to run against the production(If performance to production environment is not an issue)?
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.