I would appreciate any help for a performance issue in loading data into a Oracle DW table. The target has over 10 million rows. The new rows are mostly being updated while there are some insertions as well. For the updates, the where clause matches an existing index. Can anyone please give some ideas for the possible options to improve performance?
Thanks.
Performance Issue
Moderators: chulett, rschirm, roy
Performance Issue
Added neena said spilt the insert and update using transformer stage then use Oracle Bulk loader for insert and OCI stage for update.
please check in your database any composite index for target table. If more index in your target table update will take time.
Sql or Dataset --------------- Transformer ----------------- Insert
------------------- Update
Thanks
Mand
[/img]
please check in your database any composite index for target table. If more index in your target table update will take time.
Sql or Dataset --------------- Transformer ----------------- Insert
------------------- Update
Thanks
Mand
[/img]
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
See if you can get some involvement from your Oracle DBA support team, get them to run tracing against the database while your job is running. They may be able to identify better indexing or storage options Long term it helps if they take some responsibility and interest in the the performance of loads into the database.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 214
- Joined: Mon Feb 23, 2004 2:10 am
- Location: Dublin, Ireland
- Contact:
Re: Performance Issue
Bhusan,Bhusan wrote:I would appreciate any help for a performance issue in loading data into a Oracle DW table. The target has over 10 million rows. The new rows are mostly being updated while there are some insertions as well. For the updates, the where clause matches an existing index. Can anyone please give some ideas for the possible options to improve performance?
Thanks.
it would help if you would say what hardware/OS, version of oracle, number of columns on the table, on the index, and rate of update....it may well be you are going as fast as you are going to go.....it might be that your updates are slow.....
But if you say 'my updates are slow, how do I speed them up' all we can respond is check indexes, make sure you have analysed the database, check the plan oracle is using to perform the update, then get your DBA involved......these are 'all the normal steps' for tuning an Oracle update statement....DS does nothing that will speed up/slow down Oracle update statements...
Also, we have experienced some strange behaviour in Oracle updates on version 7.1 on AIX where just some of the update processes are going slowly...we are investigating with ASCL support....you might have the same problem we are having...some updates are running at 5,000/second, some at 50/second and we have not been able to isolate the differences....yet...