Better Oracle Performance Tuning Comes from Better Application Design
Proper application design principles share many similarities with Oracle performance tuning efforts on deployed databases and applications. Which is to say that Oracle performance tuning activities attempt to find performance deficiencies that are often a result of poor or inadequate application design. Without enough time or effort to design these performance issues out of the application, DBAs must spend unnecessary time finding them after the database is deployed.
Get additional Oracle performance tuning info.
The amount of time spent on Oracle performance tuning can be greatly reduced with proper planning and an understanding of a few application design principles.
Application Design for Better Oracle Tuning
There are three critical tasks of application design:
- Removing unnecessary SQL requests
- Cutting down on network round trips
- Reducing lock contentions
Let’s keep these tasks in mind as we take a look how to apply them to application design and reduce long-term Oracle tuning activities.
Use Caching
Caching is an effective strategy for improving network traffic. Cache frequently accessed static data to reduce the number of requests and overall network traffic necessary for database overhead.
Eliminate Hard Parses
Hard parses are the enemy optimal performance. Proper application design practices demand fewer hard parses than soft. A large number of hard parses will slow down performance and require Oracle tuning efforts to improve.
Discover the advantages of proper Oracle tuning.
Optimize 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
Optimal Oracle performance tuning may often require that some SQL statements not be processed at the same time. This is known as a lock.
Locks should be held for the least amount of time possible to avoid performance issues. You can utilize the pessimistic or optimistic locking strategies. Generally, the optimistic strategy holds lock for a shorter time, but is not as effective as the pessimistic strategy for maintaining transaction integrity. Effective Oracle tuning requires the proper balance between the number of locks and the amount of time each is held.