Oracle tuning has always been the most difficult area of Oracle database management. Because of the flexibility of Oracle, the internal software is phenomenally complex and there are many hundreds of causes of Oracle performance problems. The inherent complexity of the Oracle database leaves many shops unable to certify that their database is properly tuned.

In addition to the complex nature of the Oracle software, we also have the issue of the dynamic nature of Oracle applications. An Oracle database is constantly changing, and it is never exactly the same at any two times. Because of the dynamic nature of tuning an Oracle environment, it is very difficult for the Oracle database administrator to get a handle on what's going on inside their database. Many people who've attempted to tune an Oracle database say that it's analogous to attempting to work on a car while it is flying down the highway at 60 miles per hour!

While it may be true that the Oracle databases are in a constant state of flux, there is a general approach that most Oracle experts use when tuning the Oracle database. It's very important to take a top-down approach to tuning Oracle databases, such that you start at a very high level, taking a look at the overall Oracle environment and then successively drill down into more detail as you begin to tune the individual components within the database engine. For Oracle, the top-down approach means starting with the server, drilling down to the instance, drilling down to the objects, and finally examining the Oracle SQL.

The goal of this text is to give you the STATSPACK tools and diagnostic techniques that are required to ensure that your Oracle database is performing at an optimal level. While you may not become an Oracle tuning expert from reading this book, you will have a high-level understanding of the important Oracle tuning issues and know how to run STATSPACK queries to get performance metrics.

While there is no silver bullet for tuning Oracle databases, a comprehensive approach to Oracle tuning can help ensure that all of the bases are covered, and that no important tuning facts have been overlooked. In tuning an Oracle database, you have to start by taking a broad look at the external environment and successively drill down for more details (see Figure 1-1).

Figure 1: The Oracle database tuning hierarchy

The concept of using a drill-down technique is very important to Oracle tuning. We must start at a very broad level, examining the overall environment and looking carefully at the database server for any problems that might exist within CPU, RAM, or disk configurations, as shown in Figure 1-2. No amount of tuning is going to help in an Oracle database when the Oracle database server is short on resources.

Figure 2: Tuning the Oracle environment

Once we've completed the tuning of the Oracle server environment, we can then begin to take a look at the global parameters that affect the Oracle database (the Oracle instance). When looking at the Oracle database, we take a look at the database as a whole, and we pay careful attention to the Oracle initialization parameters that govern the configuration of the SGA and the overall behavior of the database, as shown in Figure 1-3.

Figure 3: Tuning the Oracle instance

Once the database server in the Oracle instance has been tuned, we can then begin the work of taking a look at individual Oracle tables and indexes within the database. At this phase, we take a look at the storage settings that can govern the behavior of a table and take a look at how well the settings accommodate the processing needs of the individual objects (see Figure 1-4).

Figure 4: Tuning Oracle objects

Once the Oracle objects are tuned, we then move into tuning the individual SQL queries that are issued against the Oracle database. This is one of the most challenging of all of the areas of Oracle tuning because there can be many thousands of SQL statements issued against a highly active Oracle database. The task for the person tuning the Oracle SQL is to identify those SQL statements that are used most frequently and apply the tools necessary in order to tune each statement for the optimal execution plan (see Figure 1-5). We will also explore static binding and show how to improve execution by keeping bind plans for the SQL.

Figure 5: Tuning Oracle SQL

In summary, Oracle tuning involves the following steps, with each step being more general and broad than the step beneath it:

1.      Server, network, and disk tuning If there is a problem with the Oracle server, such as an overloaded CPU, excessive memory swapping, or disk I/O bottleneck, then no amount of tuning within the Oracle databases is going to improve your performance. Hence, the first thing the Oracle professional examines is the server, disk, and network environment.

2.      Instance tuning The Oracle SGA is tuned, and all of the Oracle initialization parameters are reviewed to ensure that the database has been properly configured. This phase of Oracle tuning is directed at looking for resource shortages in the db_cache_size, shared_pool_size, and pga_aggregate_target. We also investigate important default parameters for Oracle such as optimizer_mode.

3.      Object tuning This phase of tuning looks at the setting for Oracle tables and indexes. Settings such as pctfree, pctused, and freelists can have a dramatic impact on Oracle performance, and each object can benefit from proper storage settings.

4.      SQL tuning This is the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. At a high level, we identify the most common SQL statements and tune each statement by carefully reviewing the execution plan for the SQL and adjusting the execution plan using Oracle hints. We will also be investigating the new optimizer plan stability feature. Optimizer plan stability allows improved performance by storing a ready-to-go execution plan for SQL statements. We will also see how to implement optimizer plan stability with the OUTLINE package, so that we can modify execution plans for specific SQL statements. This is especially useful in cases where you are using vendor-supplied SQL and you cannot change the SQL source code. For example, in Oracle Applications and SAP, you are not allowed to change the SQL, but with optimizer plan stability you can tune the SQL by changing the stored execution plan for the SQL.

5.      Design tuning The design of the application is the single most important factor in Oracle performance. Unfortunately, most Oracle administrators are unable to change a poor design, either because they are using proprietary software or because the design is already implemented in production.

It is critical to the success of your Oracle tuning effort to follow the tuning steps in their proper order. Many neophyte Oracle Remote DBAs will immediately begin to tune SQL statements without considering the environment in which the SQL is running. Mistakes like these can often cause problems with the overall tuning effort because the broader tuning issues have not yet been identified and corrected.

While later chapters will explore each of these areas in great detail, let's begin by covering the major areas so that we can understand their impact on Oracle performance.