nikhilanshuman

 Profile Answers by nikhilanshuman 

• Nov 30th, 2010

 

Database performance tunings:

Whenever you are firing queries on source databases containing
joins, make sure that proper indexes are created for optimizing query performance.
Try to partition the table and read the table's data in parallel using "partition table" property of OEE stage. This will allow reading data in parallel in source.
Also, while
writing, make sure that in target OEE stage, the execution mode is parallel.

If a single query is taking more time to
execute, you can break it into datastage job's stages. This will reduce overhead on Oracle and distribute the load on Datastage and Oracle.
This results in increased performance. For example, if many joins are there in source SQL, you can remove the joins with higher cost and use join stage in datastage to achieve similar results.

Use of hints :
Use Oracle hints in the SQLs to increase the performance of
SQLs.

Datastage job performance:

a) Multiple nodes : Run the datastage jobs on multiple nodes. Running jobs on multiple nodes
introduces partition parallelism and increases job's performance.
b) use the env variable APT_DUMP SCORE to verify if the job score is getting generated properly and unnecessary operators are not introduced in the datastage job score.
c) Do not sort -> if the data is already
sorted, select the option "do not sort already sorted" in the sort stage. This will avoid overheads for resorting the data.
d) instead of using sort
stage, try using Order by in the source SQL itself.
e) Instead of using remove
duplicates, try using distinct in source SQLs
f) Lesser are the number of
stages, lesser will be the job compilation efforts and better will be the job's performance.
g) While reading from sequential
file, use "number of readers per node". this introduces parallel reading of data from source sequential file stage itself.
h) Avoid
repartitioning -> Avoid repartitioning of data. It is an overhead.
i) If number of records are more in reference
link, go for join stage instead of lookup.
j) If possible, try to use modify stage instead of transformers.

Nikhil Anshuman