Tags: applications, database, databases, dml, facing, improve, link, links, mysql, oltp, operations, oracle, performance, remote, sql, transactions
How to improve database link performance?
We use db links to do DML operations on remote databases. For OLTP applications we are facing performance problems for transactions dependent on data on remote database.
For legal and business reasons we cannot state all the data locally.
Could anybody suggest how to improve database links performance or suggest methods/procedures/techniques to enhance speed of OLTP applications going against remote databases ?
Leave a comment...
- 3 Comments
- There are a number of potential techniques one can use to tune this or any other network problem-- adjusting packet sizes, installing dedicated, high-speed connections between the machines, etc. That's not where my expertise lies, though, so I will defer to competent network engineers...
Does all of your interaction with the remote database need to be synchronous? Are you pulling back data from the remote database to display to your OLTP users? If not, you may consider making the interaction asynchronous using Oracle Advanced Queues so you OLTP users didn't need to wait on the remote database. That would also allow you to batch requests to the remote database.
If you can give a bit more background on your system, we may be able to provide more assistance.
Distributed Database Consulting, Inc.#1; Fri, 22 Feb 2008 03:47:00 GMT
- Hi Justin,
Interaction in the application that is having problem is synchronous. We do display data from remote database. In places where we can we are doing batch transcations.
I am looking for tuning tips that can be done from oracle side to improve db link performance.
As you mentioned AQ, I would like to know how reliable are AQs? Also in terms of administration and maintenance what is involved?
Sky#2; Fri, 22 Feb 2008 03:48:00 GMT
- AQ is as reliable as Oracle-- the guarantees about delivery of queued messages are the same as the guarantees about committed transactions (i.e. ACID). AQ is designed for asynchronous operation, though. If you are batching transactions, it sounds like you are already doing some sort of asynchronous operations-- I've generally found AQ a lot easier to administer & maintain than rolling your own batching system.
If you want to tune the Oracle side of things, you'll need to explain more about the system(s) involved here. Architecture, data flow, operations that involve the dblink, etc. If you're not comfortable posting that sort of information to a public forum, feel free to send me mail directly jcave.oracle.mscer.com.ddbcinc.com
As an aside, I'm interested in how you can legally pull data from the remote system to display to your users but that you can't legally cache that data in your system via replication. Sounds like an odd constraint.
Distributed Database Consulting, Inc.#3; Fri, 22 Feb 2008 03:49:00 GMT