| Previous | Table of Contents | Next |
The basic premise of this approach is to demonstrate how the DBA can automate the tedious analysis tasks within Oracle databases and be automatically alerted in extraordinary conditions. While many products exist that perform this function, the DBA can benefit from an understanding of the internal workings of the Oracle SGA as well as from insights into the complex interactions that contribute to response time.
In general terms, Oracle tuning can be divided into two areas: disk tuning and memory tuning. Since many shops now use RAID or LVM to stripe data files across disks, disk tuning has become a moot issue for most Oracle DBAs.
Tuning of memory is another story. Since an Oracle instance resides in the real memory of the CPU, the allocation of memory pages and the configuration of the SGA is a primary concern for understanding performance. SGA tuning for Oracle involves two areas: the parameters in the init.ora file, and parameters that are used when an individual table is created. The init.ora file is used at startup time by Oracle to allocate the memory region for the instance, and the parameters in the init.ora control this allocation. Other parameters used at table creation time (such as freelist and pctused) can influence performance, but we will focus on the system-wide tuning parameters.
When tuning Oracle, two init.ora parameters become more important than all of the others combined:
These two parameters define the size of the in-memory region that Oracle consumes on startup and determine the amount of storage available to cache data blocks, SQL, and stored procedures.
Note: It is a good idea to set your db_block_size as large as possible for your system. For example, HP-9000 computers read 8 K blocks; hence, db_block_size should be set to 8 K.
Keep in mind that many of these tuning issues will change when Oracle 7.3 allows for dynamic modification of the SGA. It will not be necessary to bounce the instance to change the amount of db_block_size, and utilities will soon appear to detect extraordinary conditions and dynamically reconfigure the SGA.
Preparing The SGA For Packages And Stored Procedures
Oracle’s shared pool is very important, especially if the system relies heavily on stored procedures and packages. This is because the code in the stored procedures is loaded into memory. The shared pool consists of the following subpools:
Both the data buffer and the shared pool utilize a least-recently-used algorithm to determine which objects are paged out of the shared pool. As this paging occurs, fragments or discontiguous chunks of memory are created within the memory areas.
Imagine the shared pool as being similar to a tablespace. Just as ORA-1547 error appears when insufficient contiguous free space occurs in the tablespace; similarly, you will encounter an ORA-4031 error when contiguous free space is not available in the shared pool of the SGA.
This means that a large procedure that initially fit into memory may not fit into contiguous memory when it is reloaded after being paged out. For example, consider a problem that occurs when the body of a package has been paged out of the instance’s SGA because of other more recent/frequent activity. Fragmentation occurs, and the server cannot find enough contiguous memory to reload the package body, resulting in an ORA-4031 error.
To effectively measure memory, a standard method is recommended. It is a good idea to regularly run the estat-bstat utility (usually located in ~/rdbms/admin/utlbstat.sql and utlestat.sql) for measuring SGA consumption over a range of time.
Many new Oracle DBAs rely on the V$ tables to gather tuning statistics—often with misleading results. As we know, the V$ tables are in-memory tables that gather statistics from the startup of the instance and do not provide any “point in time” measures of system resources. As such, basic measures such as the buffer hit ratio may be misleading. For example, assume that an instance has been running for one week. Computing the buffer hit ratio for an instance using the V$ tables may yield a value of 95 percent. However, this is a running average and will not show the current buffer hit ratio of 60 percent.
The standard utilities for creating a performance report are known as estat-bstat, found in $ORACLE_HOME/rdbms/admin. The names of the utilities are utlbstat.sql (begin statistics) and utlestat.sql (end statistics). You must enter SQL*DBA to run the reports:
$ > cd $ORACLE_HOME/rdbms/admin $ sqldba mode=line SQLDBA > connect internal connected. SQLDBA > @utlbstat ... 5 rows processed SQLDBA > @utlestat
| Previous | Table of Contents | Next |