Q

Methods to quicken update of 30-column table

I am updating 80% of the rows in a 30-column (6 VC [4000] and a blob) table. I have the STD 10% pctfree set up. I am getting fair throughput, but have been tasked with making it faster. So far, I haven't been able to improve on my original design. I've looked at what indexes are affected, used preparedStatements, batching (4000 updates at a time, each update affecting 0-N rows), set auto-commit off and switched to using the thin driver. Can you list other areas that I should explore?

I am updating 80% of the rows in a 30-column (6 VC [4000] and a blob) table. I have the STD 10% pctfree set up. I am getting fair throughput, but have been tasked with making it faster. So far, I haven't been able to improve on my original design. I've looked at what indexes are affected, used preparedStatements, batching (4000 updates at a time, each update affecting 0-N rows), set auto-commit off and switched to using the thin driver. Can you list other areas that I should explore?
Are you updating one row at a time? If so, your biggest payoff would probably be to modify the SQL to modify multiple rows at once. Otherwise, the things you've done are all worthwhile. Make sure that you're not parsing each statement more than once (I assume that using preparedStatements takes care of that) and that your access path to the rows to be updated is efficient (in this case, you probably don't want to be using an index). Have you traced the updates to see the breakdown of elapsed time? If not, that should be your next step. Get statistics on elapsed time, CPU time and wait time and proceed from there.
This was first published in January 2006

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close