performance issue

pkomalla · Post by **pkomalla** » Thu Jun 22, 2006 5:27 am

Hi all,

I am running a job which extratcs data from one Oracle database passes trough a transform and loads into another oracle DB. Last week it ran at 100 rows/sec now its running at lessthan 1 row/sec.

We tested the sql code on oracle DB its running fine. It seems their is some problem with datastage server.

Any tips of how to find the problem?

thanks,

ArndW · Post by **ArndW** » Thu Jun 22, 2006 5:34 am

Write your data to a sequential file and read it from that file. That will give you the effective speed of all stages leading to the file stage and also the effective speed of all stages after the file stage. This will help you narrow down the cause of the speed slowdown. You can continue doing this by moving the sequential write/read stage to different parts of your job.

Also, does your transform stage have a high %CPU (in that case you are bound by processing capability). Chances are very high that Oracle is the culprit, so monitoring that will give you the best results.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 22, 2006 1:33 pm

pkomalla wrote:We tested the sql code on oracle DB its running fine. It seems their is some problem with datastage server.

Any tips of how to find the problem?

What has changed in the meantime? If the DataStage job has not been changed clearly it can not be the culprit.

What is the total other load on the machine? For example, are you running lots of other work - DataStage or otherwise - that you weren't running when performance (whatever that means) was acceptable?

Chuah · Post by **Chuah** » Sun Jun 25, 2006 9:37 pm

pkomalla wrote:Hi all,

I am running a job which extratcs data from one Oracle database passes trough a transform and loads into another oracle DB. Last week it ran at 100 rows/sec now its running at lessthan 1 row/sec.

We tested the sql code on oracle DB its running fine. It seems their is some problem with datastage server.

Any tips of how to find the problem?

thanks,

I'd suggest running an ANALYZE in your Oracle DB to regenerate the stats for the optimizer.
Try running your SQL within the database and check the performance. I doubt it's a DataStage issue.

tcj · Post by **tcj** » Sun Jun 25, 2006 11:28 pm

I had this same argument this morning with one of the people working on my current project.

Sql was running fast last week. Now it is running slow.

Analyse the tables and it is back to running fast.

Tim

chulett · Post by **chulett** » Mon Jun 26, 2006 5:29 am

I'd say your DBAs aren't really on the ball if you have to ask to have proper stats / histograms maintained on your tables.

tcj · Post by **tcj** » Mon Jun 26, 2006 7:37 pm

That would be true if it was a production environment. :D

chulett · Post by **chulett** » Mon Jun 26, 2006 8:21 pm

Regardless. I'd shoot my DBAs if all they cared about were production environments.

DSXchange

performance issue

performance issue

Re: performance issue

Re: performance issue