Performance Tuning

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Shabnam
Participant
Posts: 17
Joined: Thu Mar 10, 2005 2:06 am

Performance Tuning

Post by Shabnam »

Hi

I have some Insert and update statements in Oracle DB, which is taking too much of time which id not affordable. The table is having lacs of records. Do you have any option to optimise it.

Thanks and Regards
Shabnam
ShaneMuir
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

Re: Performance Tuning

Post by ShaneMuir »

Shabnam wrote:Hi

I have some Insert and update statements in Oracle DB, which is taking too much of time which id not affordable. The table is having lacs of records. Do you have any option to optimise it.

Thanks and Regards
Shabnam
You will probably have to provide a little more information than this before people are going to be able to offer any real suggestions.

Anything we offer at present would merely be guesses.
Shabnam
Participant
Posts: 17
Joined: Thu Mar 10, 2005 2:06 am

Post by Shabnam »

Hi I am having problem in inserts to ODS and DW. In ODS i have some indexes also. The data is growing everyday both in ODS and DW. The total performance of the system is going down. Some of the jobs we identified which is taking more time. From that identified some statements. Would like to know in such situations how the inserts and updates can be optimised.
I have Oracle 9i as source, staging, ODS and DW Database.
This situation can come in any system as the DW Data keep on growing.
So would like to know how that can be handled.

...Shabnam
tagnihotri
Participant
Posts: 83
Joined: Sat Oct 28, 2006 6:25 am

Post by tagnihotri »

Can you please detail the job design, how you have identified the "slow running statements" from jobs!
I do agree the scenario can occur in any DW but recommendations depends on Design and Functional requirements :wink:
Cheers,
BA
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

Split the inserts and updates
Play around with the transaction size and array size to get optimal performance.
If you are already doing that then there is something else going on in your job. Need more info on the design.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Duplicate Post

Please reply to other thread.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Dang, and I was just about to push the Submit button here. [sigh] Ok. :cry:
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

The 'other' thread seems to be about select performance, while this one is insert/update related. I think I'll stay here. :wink:

Dollars to doughnuts, I'll bet it's all about Oracle and how you have it setup -and- how you are leveraging it. While inserts can suffer the same fate, what typically 'goes down' in performance as volume grows are poorly implemented updates. And it only takes a small percentage mixed in with the inserts to really bring your average throughput down.

Are the Key fields you are using in your update statements indexed? If not, or you don't have a way to use indexed fields - then you pay the piper by doing a full table scan for each record being updated. Should be easy enough to check the explain plan of your statements and see if that is your issue.
-craig

"You can never have too many knives" -- Logan Nine Fingers
NigeGriff
Premium Member
Premium Member
Posts: 46
Joined: Mon Nov 24, 2003 5:46 am

Oracle OCI

Post by NigeGriff »

You should not try to process large volumes through the Oracle OCI (if this is the stage you are using) in datastage. You need to think about Bulk Loader or the Oracle Merge statement in a Stored Procedure.

Cheers,
Nige
Shabnam
Participant
Posts: 17
Joined: Thu Mar 10, 2005 2:06 am

Post by Shabnam »

DB stage used is Oracle 8, not Oracle OCI?
Will that also give problem?

Actually the project when developed it was using Oracle 8,
Then the database upgraded to 9i and the DB stage still remaining as oracle 8.

...Shabnam
Post Reply