Performance Tuning in Oracle10g – Best Practices in Application Development

At least fifty percent of the time performance problems are inherent in an application due to the fact that at the time of design the architects are not totally aware of the business usage of the application. As a result there may be some components whose performance is poor during the initial release while other problems may crop up as the business usage increases or changes.

Sometimes the fix may be straight forward; in extreme cases the fix may involve altering the application architecture. Going forward to avoid these performance issues we can keep in mind some Best Practices at the Design stage itself so that the performance requirements are not affected in the long run.

Keep it easy and simple, is the key process at every step. Since the database application should be a tool to business usage the focus should be, not on theoretical design perfection but rather on enabling the end user’s productiveness.

Strive to eliminate excessive logical reads. When we are focused on tuning the physical I/O we may tend to overlook the factor of consumption of CPU resources caused by excessive number of logical reads.

Avoid many trips to the Database. We can combine multiple queries into a single procedure to avoid unnecessary Database access. When there are a large number of queries even if they are tuned, the execution burden of these, multiplied by number of users will consume extensive CPU resources in the server.

This should be done depending on the context. In some cases where large queries include multiple set of grouping operations or complex multi row calculations, when several SQL statements combine many steps into one query, even if it causes reduction in the number of trips, they may fail to complete quickly. The Divide and Conquer approach of breaking the query into atomic components should be done if the operation is on a large scale such as a Batch operation.

For Reporting Systems we need to store the data the way in which it is presented to user, so that there is less work for Oracle in performing end user’s queries.

Avoid repeated opening of connections to the Database as this is one of the slowest operation that is performed. Instead we can keep connections open and reuse.

Use the appropriate indexes. Sometime application developers create many indexes to avoid physical reads. However many of the indexes may be just redundant.

Eliminate unnecessary sorts as sort operation does not return rows until the entire set of rows is sorted. We should also avoid the need to frequently query undo segments; that is queries that frequently access data that others may be changing because database has to look in multiple locations for one piece of data. Since Oracle’s optimizer relies on statistics during evaluation of path in query execution we need to keep statistics updated, give hints when needed for identifying efficient execution path of queries.

We can maximize the throughput in the environment by using Disk Caching, larger Database block size, store data efficiently at the block level and by avoiding the use of temporary segments.

If we cannot avoid performing expensive operations on database we can attempt to split the work by use of Partition, materialized views and by parallelizing major operations.

While doing application testing after development, we must perform adequate system test and performance test. The tests should be able to determine if the application will slow the business process under expected production load.

These can be done by simulation with large volumes of data to avoid faltering of the application in production when the data volume reaches critical levels.

Testing with many concurrent users will help to determine if in production scenario, users will encounter deadlocks, data consistency issues or performance problems. It is important to identify high load times and install monitoring tool that will gather performance data.

We can also have data gathering configured when the application is in its initial trial phase. On the whole testing should be robust enough to simulate production usage of the application thus leading to design issues being detected early itself. These Best Practices if kept in mind will help avoid unnecessary fixes in the application in the long run. They can further lead to scalable and high performance applications.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |