Oracle Performance Tuning

A Methodical approach of Oracle Performance Tuning
Moving Beyond a Symptomatic Approach -This article  is from the book - Oracle Performance Survival Guide: A Systematic Approach to Database Optimization.

The shift from ratio-based to wait-based tuning has resulted in radical improvements in our ability to diagnose and tune Oracle-based applications. However, as noted previously, simplistically focusing on the largest component of response time can have several undesirable consequences:
  1. We might treat the symptoms rather than the causes of poor performance.
  2. We might be tempted to seek hardware-based solutions when configuration or application changes would be more cost-effective.
  3. We might deal with today's pain but fail to achieve a permanent or scalable solution.

To avoid the pitfalls of a narrow wait-based analysis, we need our tuning activities to follow a number of well-defined stages. These stages are dictated by the reality of how applications, databases, and operating systems interact. At a high level, database processing occurs in layers, as follows:

1. Tuning Application layer  ( Application code issues SQL (and PL/SQL), Requests to the database)
  • Applications send requests to the database in the form of SQL statements (including PL/SQL requests). The database responds to these requests with return codes and result sets.
2. Tuning Database Code Layer (Oracle software parses and optimize SQL, Manage locks, security and concurrency etc)
  • To deal with an application request, the database must parse the SQL and perform various overhead operations (security, scheduling, and transaction management) before finally executing the SQL. These operations use operating system resources (CPU and memory) and might be subject to contention between concurrently executing database sessions.
3. Tuning Memory Layer (Buffer cache (data blocks) , other shared memory caches, PGA (sorting and hash memory))
  • Eventually, the database request needs to process (create, read, or change) some of the data in the database. The exact amount of data that needs to be processed can vary depending on the database design (indexing, for example) and the application (wording of the SQL, for example).Some of the required data will be in memory. The chance that a block will be in memory will be determined mainly by the frequency with which the data is requested and the amount of memory available to cache the data. When we access database data in memory, it's called a logical IO. Memory is also used to perform sorting and hashing operations.
4. Tuning Disk Layer ( read/write table/index data, read/write temporary work area , redo log and other IO)
  • If the block is not in memory, it must be accessed from disk, resulting in real physical IO. Physical IO is by far the most expensive of all operations, and consequently the database goes to a lot of effort to avoid performing unnecessary IO operations. However, some disk activity is inevitable. Disk IO also occurs when sorting and hashing operations are too large to complete in memory.

Activity in each of these layers influences the demand placed on the subsequent layer. For instance, if an SQL statement is submitted that somehow fails to exploit an index, it will require an excessive number of logical reads, which in turn will increase contention and eventually involve a lot of physical IO. It's tempting when you see a lot of IO or contention to deal with the symptom directly by tuning the disk layout. However, if you sequence your tuning efforts so as to work through the layers in order, you have a much better chance of fixing root causes and relieving performance at lower layers.

Here's the tuning by layers approach in a nutshell:

Problems in one database layer can be caused or cured by configuration in the higher layer. The logical steps in Oracle tuning are therefore
  1. Reduce application demand to its logical minimum by tuning SQL and PL/SQL and optimizing physical design (partitioning, indexing, and so on).
  2. Maximize concurrency by minimizing contention for locks, latches, buffers, and other resources in the Oracle code layer.
  3. Having normalized logical IO demand by the preceding steps, minimize the resulting physical IO by optimizing Oracle memory.
  4. Now that the physical IO demand is realistic, configure the IO subsystem to meet that demand by providing adequate IO bandwidth and evenly distributing the resulting load.


Comments