Disk drive I/O can be monitored with the sar -d or iostat -d UNIX command. Either of these commands will report on read/write activity to all system disk drives. System administrators and/or database administrators should monitor system level disk drive I/O and make sure that it is spread as equally as possible across all available drives. Oracle database file I/O can be monitored with the Database I/O SQL statement (Listing 24.1). Review the statistics returned here with the statistics from the physical disk drives to see if object distribution within the Oracle environment is balanced and if the UNIX disk activity is balanced. If not, these statistics should help the DBA and the System Administrator determine the nature of the unbalanced disk activity.

Listing 24.1. Database I/O.

select b.name "File Name", a.phyrds "Physical Reads", a.phywrts "Physical

Writes"

from v$filestat a, v$datafile b

where a.file# = b.file#

Oracle RDBMS-Level Tuning

Oracle RDBMS-level tuning concentrates on (1) monitoring and tuning the SGA, and (2) monitoring, tuning, and eliminating contention for various Oracle RDBMS common services such as rollback segments, online redo logs, and the TEMPORARY tablespace.

The System Global Area or SGA is comprised of several memory structures (see Figure 24.2). The SGA is comprised of the buffer cache, the log buffer, the sort area, and the shared pool, which consists of the library cache, the dictionary cache, and the SQL area.

Figure 24.2.  Oracle8 System Global Area.

The buffer cache holds the database blocks read from disk. If other processes can use a data block already in the buffer cache to satisfy an I/O request, Oracle does not have to do a physical I/O, a time-consuming task. Oracle uses a least-recently-used (LRU) algorithm to keep the most active data blocks in the buffer cache. The size of the buffer cache is determined by the init.ora parameter DB_BLOCK_BUFFERS. The SQL statement in Listing 24.2 will calculate a buffer-cache-hit ratio. This ratio varies greatly depending on the type of application or applications utilizing the Oracle RDBMS.

---

Tip:  
Increase the DB_BLOCK_BUFFERS until the buffer-cache-hit ratio is unaffected by additional amounts of DB_BLOCK_BUFFERS. Then decrease the DB_BLOCK_BUFFERS until the buffer-cache-hit ratio begins to be adversely affected. This is the best way to determine the optimal size of the buffer cache.

---

Listing 24.2. Buffer-cache-hit ratio calculation.

select round(((sum(decode(name,'db block gets',value))

            +  sum(decode(name,'consistent gets',value)))

            -  sum(decode(name,'physical reads',value))) /

              (sum(decode(name,'db block gets'value)) +

            +  sum(decode(name,'consistent gets',value))) * 100)

"Buffer Cache Hit Ratio"

from v$sysstat

The log buffer, or the redo log buffer, contains all the changed data blocks. These changed blocks are then written to the redo log files in a contiguous manner by the Oracle log-writer process. If the redo buffer waits, from the calculation in the following code, is greater than zero, processes are waiting for space to become available in the redo log buffer. Increase the init.ora parameter LOG_BUFFER and periodically check this calculation again.

select sum(value) "Redo Buffer Waits" from v$sysstat where name = 'redo



log space wait time'

The sort area is any SQL statement “order by” clause’s work area. For peak performance, all sorts, or the majority of sorts, should be done in memory. The calculation in the following code will determine the number of disk sorts performed. Tune this area much like the buffer cache: increase the init.ora parameter SORT_AREA_SIZE until the result of the disk sorts calculation decrease’s. Then reduce the SORT_AREA_SIZE init.ora parameter until the disk sorts increase. The point at which the disk sorts increase will be the optimal setting for this parameter.

select value "Disk Sorts" from v$sysstat where name = 'sorts (disk)'

The shared pool consists of the buffer areas library cache, dictionary cache, and the SQL area. The entire pool size is determined by the init.ora parameter SHARED_POOL_SIZE. The library cache keeps track of shared SQL and shared PL/SQL. Any SQL statement that matches the text of a SQL statement already in the SQL area has already been parsed. It is important that all SQL code follow coding standards so identical SQL statements in different applications even have the same upper/lowercase attributes. The library cache ratio (see the following code) should be below 1 percent. There are two solutions for an unacceptable ratio: increase the init.ora parameter SHARED_POOL_SIZE or make similar SQL statements identical.

Select (sum(reloads) / sum(pins))* 100 "Library Cache Ratio" from



v$librarycache

The other cache in the shared pool is the dictionary cache. The dictionary cache is similar in function to the library cache but is used for the Oracle dictionary SQL statements. If the dictionary-cache-hit ratio (see the following code) is greater than 10 percent, increase the SHARED_POOL_SIZE init.ora parameter.

select (sum(getmisses) / sum(gets) ) * 100 "Dictionary Cache Ratio" from



v$rowcache

If there is always free space in the shared pool, increasing the SHARED_POOL_SIZE will have no affect; the problem is dissimilar SQL statements. The following code (the shared-pool free memory SQL statement) monitors the size of the shared pool:

select bytes "Shared Pool Free Memory" from v$sgastat where name = 'free



memory'