DSXchange

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

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.

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

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

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.

Duplicate Post

Please reply to other thread.

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

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

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.

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

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