Performance Issue

Bhusan · Post by **Bhusan** » Mon Aug 09, 2004 12:48 pm

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.

neena · Post by **neena** » Mon Aug 09, 2004 2:46 pm

First divide your updates and inserts by using transformer and for insertions use BULK loader and for Updates user the OCI stage.
This will imporve your speeds of data transformation
Thanks

mandyli · Post by **mandyli** » Mon Aug 09, 2004 11:33 pm

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]

vmcburney · Post by **vmcburney** » Mon Aug 09, 2004 11:45 pm

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.

peternolan9 · Post by **peternolan9** » Tue Aug 10, 2004 2:03 am

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.

Bhusan,
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...

DSXchange