Dynamic Adjustment of the Oracle SGA Pools

 

Figure A

A predictive model for proactive dynamic Oracle tuning

Using the Oracle9i dynamic System Global Area (SGA) feature, you can dynamically "steal" RAM pages from another RAM memory region of the Oracle instance (referred to as the SGA) and dynamically reallocate the RAM page frames to the data buffer cache every Wednesday during the crunch period. Ultimately, the Oracle9i dynamic SGA features will be incorporated with real-time performance monitors that suggest a dynamic correction for a performance problem. For example, software could be written that detects impending changes in data access patterns and then dynamically reconfigure the Oracle database to accommodate those changes.

Dynamic bimodal databases

Almost all Oracle databases have a distinct processing signature, which is modeled by hour of the day or day of the week; these processing signatures exist for disk I/O, CPU consumption, data buffer behavior, and shared pool activity. Essentially, these signatures can be combined to define specific "modes" of processing that are clearly identifiable and attributable to specific application requirements.

For example, a system could be operating in online transaction processing (OLTP) mode during the day and then switch to data-warehouse and decision-support modes each evening. If an Oracle DBA can detect when the system's processing characteristics change from OLTP to DSS, he or she can dynamically submit batch jobs that issue the alter system commands to reconfigure the SGA and background processes.

Future Oracle releases will most likely incorporate artificial intelligence to create a true self-tuning database. However, don't underestimate the challenge and complexity of creating a self-tuning database engine. Oracle has 250 parameters that interact with each other in a factorial fashion; there are literally many billions of unique processing scenarios. Any effort to develop a program to dynamically monitor and reconfigure Oracle will be a formidable software engineering challenge.

What can I do today?

Without getting into the minutiae of Oracle tuning parameters, you can monitor and change several critical metrics whenever they fall below predefined thresholds. The following recommendations require the STATSPACK utility to identify the values. The book Oracle9i High-Performance Tuning with STATSPACK, by Oracle Press, has great prewritten scripts to detect exceptional Oracle conditions.

Let's examine the major thresholds that can be used to trigger signal dynamic reconfiguration:

Hot file reads
You can use STATSPACK to identify files whose reads are greater than 25 percent of total database reads. Internally, the code compares the individual I/O for a data file from the stats$filestatxs table with the overall I/O for the period in the stats$sysstat table. When you find a hot file, you can locate the relevant table or index inside the file and dynamically place the table of index in the KEEP pool with this syntax:
 
Alter table customer storage (buffer_pool keep);

Hot file writes
A STATSPACK script alerts you to files whose write I/Os are greater than (25 percent or 50 percent or 75 percent) of total writes. This information can help you locate files that are consuming more than a normal portion of I/O writes. You may want to place these files in the KEEP pool or stripe them across multiple disks.

Data buffer hit ratio
 

2007 Update:  Since the days of Oracle7 when Oracle Corporation recommended keeping the buffer cache hit ratio above a fixed threshold, research has show some important facts. 

You can use a STATSPACK script to alert you when the data buffer hit ratio falls below the preset threshold. This alert is useful for identifying times when decision-support type queries are being run, since a lot of large-table/full-table scans and random data access may make the data buffer hit ratio drop.

This script can also reports on all three data buffers, including the KEEP and RECYCLE pools. It can also be customized to report on individual pools, because the KEEP pool should always have enough data blocks to cache all table rows, while the RECYCLE pool should get a very low buffer hit ratio. See here for scripts to automate the KEEP pool size to insure a 100% cache.  The syntax is:
 
-- steal RAM from the shared_pool;
Alter system set shared_pool_size = xxxx;
Alter system set db_cache_size = xxxx; - new, higher value
 

Here is the corresponding alert log message:
 
Wed Jul 31 16:30:47 2002
ALTER SYSTEM SET db_cache_size='2048000' SCOPE=BOTH;
Wed Jul 31 16:31:21 2002
CKPT: Begin resize of buffer pool 7 (DEFAULT for block size 16384)
CKPT: Current size = 16 MB, Target size = 8 MB
CKPT: Resize completed for buffer pool DEFAULT for blocksize 16384

Disk sorts

If the number of disk sorts is greater than 1,000 an hour, you may want to increase the value of the sort_area_size parameter or tune SQL to perform index scans instead of a disk sort. This report is very useful for monitoring the amount of activity against the TEMP tablespace, and it also helps ensure that sort_area_size is set to an optimal level. As a general rule, increasing sort_area_size will reduce the number of disk sorts, but huge sorts will always need to be performed on disk in the TEMP tablespace. The syntax for the command is:
 
Alter system set sort_area_size = 10m;

Buffer busy wait alert

Whenever you see buffer busy waits, a data block is in the data buffer but is unavailable. This type of contention is usually for a segment header block of a high-level index node block. Adding freelists for the object often corrects such buffer busy wait conditions. The syntax is:
 
Alter table customer storage ( freelists 10 );

Library cache misses alert

This alert interrogates the stats$sysstat table to look for excessive library cache miss ratios. When the library cache/miss ratio is greater than .02, you may want to increase shared_pool_size. The syntax is:
 
Alter system set shared_pool_size = 100m;

Database writer contention alert

This alert looks at Oracle for values in summed dirty queue length, write requests, and DBWR checkpoints. When the write request length is greater than 3 or your DBWR checkpoint waits, you need to look at tuning the database writer processes by changing the number of factotum (slave) processes. The syntax is:
 
Alter system set dbwr_io_slaves = 10;

Data dictionary miss ratio

This alert looks at the Oracle data dictionary to compute data dictionary gets, data dictionary cache misses, and the data dictionary hit ratio, and then alerts the DBA to times when requests for data dictionary metadata are high. This problem can sometimes be relieved by increasing the shared_pool_size init.ora parameter, using the following syntax.
 
Alter system set shared_pool_size = 100m;