Data Management

Dynamic adjustment of the Oracle9i SGA

Database performance is a constant issue in today's always-available world. Oracle has addressed this issue with on-the-fly performance tuning in Oracle9i, but admins must develop a strategy for monitoring and adjusting system performance.

The ability to dynamically reconfigure Oracle9i was the most exciting new feature of the new version's release, propelling Oracle into the world of true 24/7 systems for shops that need constant availability. But there's another important benefit to dynamic parameters. Because all 250 Oracle parameters can now be changed in real time, Oracle DBAs can reconfigure the database at any time, and many savvy Oracle administrators are adjusting their databases to match current processing load.

Prior to Oracle9i, Oracle administrators could use real-time performance monitors and detect performance problems. However, Oracle8i DBAs were sometimes powerless to make a change unless they were allowed to stop and restart the database. In Oracle9i, administrators have more options. When a DBA detects a performance problem, he or she can immediately correct the glitch using Oracle9i alter system commands. For example, if the library cache/hit ratio falls too low, a DBA can dynamically add RAM memory pages to the shared pool to correct the problem, all without affecting the database's availability.

As you can imagine, dynamic parameters can solve a host of performance problems. Let’s take a close look at how you can use Oracle9i dynamic parameters to resolve performance issues.

The foundation for a self-tuning database
Because a change to Oracle parameters can affect all aspects of an Oracle instance, Oracle has built the foundation of a self-tuning database architecture. For example, if you're using the Oracle STATSPACK utility to track Oracle statistics, you can develop predictive models based on your historical data and predict when your database will experience a shortage of a specific Oracle resource. Your STATSPACK reports may indicate that your buffer cache/hit ratio falls below acceptable levels every Wednesday afternoon between 1 P.M. and 3 P.M., as illustrated in Figure A.

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
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. If the data buffer hit ratio is less than 90 percent, you can dynamically add RAM to the 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 );

Redo log space requests
If redo log space requests are greater than 10 per hour, you may want to increase the log_buffer init.ora parameter. A high number of redo log space requests indicates a high level of update activity, and the Oracle log buffer (and the LGWR background process) may have trouble keeping up with the volume of redo log images. Use the following command to increase the RAM for the redo log buffer:
— steal RAM from the shared_pool;
Alter system set shared_pool_size = xxxx;
Alter system set log_buffer = 5m;

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;

As Oracle9i evolves, there will be many attempts to exploit this powerful new dynamic reconfiguration feature, with the ultimate goal of creating a self-tuning database engine. However, because Oracle is very flexible, it's also one of the most complex databases ever created. Eventually, artificial intelligence will be developed to automatically tune Oracle, but today, the Oracle professional must develop a strategy for detecting and correcting real-time performance problems.

Editor's Picks