Performance Tuning
Moderators: chulett, rschirm, roy
Performance Tuning
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
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
Re: Performance Tuning
You will probably have to provide a little more information than this before people are going to be able to offer any real suggestions.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
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
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
-
- Participant
- Posts: 83
- Joined: Sat Oct 28, 2006 6:25 am
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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
"You can never have too many knives" -- Logan Nine Fingers
Oracle OCI
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
Cheers,
Nige