Using Application Design for Oracle Performance Tuning

Proper application design principles share many similarities with Oracle performance tuning efforts on deployed databases and applications. That is, Oracle performance tuning is often finding faults and deficiencies that should have been caught during the application design phase. Since they weren’t found, however, DBAs spend many hours searching through queries and stored procedures trying to increase their efficiency.

Get some extra details on Oracle performance tuning.

With proper planning and an understanding of optimal application design, the amount of time spent on Oracle performance tuning can be greatly reduced.

Better Oracle Tuning through Better Application Design
There are three main considerations that affect application design:

- Reducing unnecessary SQL requests
- Cutting down on network round trips
- To reduce the number of lock contentions for processes

With those considerations in mind, let’s take a look at how applying them to application design can reduce long-term Oracle tuning requirements.


Use Caching
Early and often is a term best used for caching. It can be greatly reduced by caching frequently accessed data, which reduces the number of requests necessary to retrieve the cached data.

Eliminate Hard Parses
That is to say you should reduce the occurrences of hard parses. Proper application design practices demand fewer hard parses than soft. A greater number of hard parses use more system resources and slow down both speed and overall performance.

Discover the advantages of proper Oracle tuning.

Improve Performance with Stored Procedures
Many transactions are made up of numerous individual SQL statements. Bundling these statement together in stored procedures reduces the amount of network traffic because fewer server transactions are necessary to complete the transaction.

Locking Strategies That Improve Performance
Oracle performance tuning sometimes requires SQL statements to not be executed simultaneously. This is known as a lock.


Application design should aim to hold locks for the least amount of time possible. You can utilize the pessimistic or optimistic locking strategies. Optimistic locking strategies usually hold locks for a short time, but aren’t as effective for lock processing as pessimistic strategies. Striking a balance between the number of locks and the time each takes is a key component to effective Oracle tuning.