Instance Tuning Oracle Database Tips by Donald Burleson

The concept of instance tuning is one of the most misunderstood areas of Oracle tuning. In practice, an overstressed Oracle SGA can cause serious performance problems, but once tuned, the Oracle SGA really needs little attention from the Oracle professional. This section will show the Oracle DBA how to capture SGA information directly from the STATSPACK tables and provide an automated mechanism for alerting the DBA to shortages within Oracle's library cache, shared pool, and data block buffers, as shown in Figure 1-9.

Figure 9: Tuning the Oracle SGA and background processes

The tuning of the Oracle instance involves checking all of the initialization parameters for the Oracle database. As most Oracle professionals know, the Oracle init.ora parameters are getting more complex as the Oracle database becomes more sophisticated. Chapter 9 will take a very close look at all of the Oracle init.ora parameters and provide techniques and guidelines for understanding how to change the Oracle initialization parameters for optimal performance.

Because the Oracle instance contains the data buffer cache, we'll also discuss how STATSPACK measures the behavior of the data buffer pools. These pools include the DEFAULT pool, the KEEP pool, the RECYCLE pool, and the size-specific data buffers including db_2k_cache_size, db_4k_cache_size, db_8k_cache_size, db_16k_cache_size, and db_32k_cache_size. We will show STATSPACK techniques that can be used to identify when the size of the data storage buffer pools need to be increased, and how to tune to the lowest common denominator setting for the size of the buffer pools.

We'll also take a look at tuning the shared pool and library cache within the Oracle SGA. We will show how STATSPACK information can be collected on the behavior of the SGA, and show techniques whereby we can adjust the relevant initialization parameters in order to maximize the behavior of objects within Oracle's shared pool.

Object Tuning

Very few Oracle professionals recognize that the storage parameters for Oracle tables and indexes can have a great impact on the performance of the database. This section will explore each of the relevant storage parameters for Oracle objects and offer guidelines for setting the storage parameters according to the behavior of the object. This section will also explore object fragmentation, as shown in Figure 1-10, and offer several techniques to ensure that expensive database reorganizations are minimized.

Figure 10: Oracle objects, freelists, and fragmentation

Chapter 10 will also go into the internals of Oracle data blocks and show the internal mechanisms for linking and unlinking from Oracle's freelists. By understanding the detail in the internal operations of Oracle tables, the Oracle professional will gain insight into how to optimally set the storage parameters for maximum Oracle performance. Of course, this chapter will also focus on the STATSPACK utility, and show how you can derive useful information regarding the behavior of individual Oracle objects within your database. We will also look into the STATSPACK tables that measure block wait activities on objects and see scripts that can identify those objects that require adjustment to their storage parameters to improve performance. We will take a close look at the PCTFREE and PCTUSED Oracle object parameters, and understand how the settings for these parameters can dramatically improve the performance of SQL INSERT and UPDATE tasks. We'll also take a look at the use of freelists within Oracle tables to understand how to identify tables that are experiencing freelist contention, and how to set the freelists for Oracle objects in order to maximize the throughput of high-volume transactions against these tables.

SQL Tuning

The tuning of individual SQL statements is the most time-consuming of all of the processes in Oracle tuning. While Oracle SQL tuning is a very time-consuming job, the tuning of SQL also promises the most benefits in the overall performance of the Oracle system. It is not uncommon to increase performance by an order of magnitude by using the proper Oracle SQL tuning techniques. Chapter 11 will focus on identifying the high-use SQL statements within your database and will present a proven technique for tuning the SQL to ensure that it always runs in an optimal fashion.

This chapter will also show how to use the Oracle STATSPACK utility in order to monitor the behavior of SQL within your library cache and periodically alert the Oracle professional to SQL statements that may not be optimized for maximum performance. This is done by examining the SQL source in the stats$sql_summary table.

There is also a section on managing SQL statements within the library cache. As every Oracle professional knows, SQL statements are very transient within the Oracle instance, and may only reside in the library cache for a short period of time. At any given point in time, information within the library cache may change. SQL statements that enter the library cache remain in the library cache until they age-out and are no longer available to the Oracle instance. Chapter 11 will show you that you can capture SQL execution information from the library cache. This valuable technique will allow you to interrogate the library cache at any given point in time and prepare detailed reports showing the execution plans for all of the important SQL statements that are in the Oracle library cache.

The STATSPACK utility monitors the library cache information and can be used to create automated alert reports that will show whenever poorly tuned SQL statements are being executed within the Oracle instance.

We will also go into detail regarding the various optimizer modes for Oracle SQL. We will take a close look at the relative advantages and disadvantages of the cost-based optimizer (CBO), and also examine the rule-based optimizer and where it can be used to improve the throughput of Oracle SQL. We will also look at the differences between the first_rows and all_rows modes within the cost-based optimizer.

When tuning individual SQL statements, we will show how to change the execution plans with the use of SQL hints. We will examine actual examples for SQL tuning and show how the execution time of SQL statements can be reduced from hours down to only a few minutes. We'll also take a look at initialization parameters that affect the performance of Oracle SQL and take a close look at the static SQL binding feature with the optimizer plan stability feature. For Oracle8i and beyond, we will also examine the query rewrite feature within SQL and see how it can be used to improve SQL performance.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.