SOS Oracle Detail Cache

The Oracle Detail Cache Screen

The Oracle Detail Cache screen displays the most important statistics related to Oracle cache management. All of these statistics refer to the Shared Global Area (SGA). Since memory access is much faster than disk access, tuning this area is very important. For best performance results, Oracle should read as much as possible from memory and limit its disk access.

To access the Oracle Detail Cache screen from any SOS display screen:

Type s from the SOS Enter command: prompt to view the Screen Selection Menu.

From the Screen Selection Menu, enter C (upper case). An example of the screen is shown in Figure 39.1.

Figure 39.1 SOS Oracle Detail Cache screen

Oracle Detail Cache Display Items

Row Cache

The Row Cache screen section refers to the portion of SGA where Oracle keeps row buffers.

Table 39.1 SOS Oracle Row Cache data items

Data Item	Description
Hit Ratio	[1 - physical reads / (consistent gets + db block gets)] * 100 The sum of db block gets and consistent gets represents the number of logical reads performed by the database.
DB Block Gets	The number of blocks accessed via single block gets (not through the consistent get mechanism). This statistic is incremented when a block is read for update and when segment header blocks are accessed.
Consistent Gets	The number of accesses made to the block buffer to retrieve data in a consistent way. The SCN (System Change Number) is used to make sure the data being read has not changed since the query was started.
Physical Reads	The number of blocks read from the disk requests. Reading from temporary data segments does not increment this value. Even if the read is a multi-block read, this statistic is incremented only by 1.

Library Cache

The Library Cache portion of the Oracle Detail Cache screen refers to SQL cache and parsing.

Table 39.2 SOS Oracle Library Cache data items

Data Item	Description
Hit Ratio	(1 - reloads/pins) * 100
User Calls	The number of logons, statement parsing, and statement executions.
Recursive Calls	The number of SQL statements generated by the Oracle kernel rather than by user applications.
Exec Count	The number of execute requests and cursors opened.
Parse Cnt (tot)	The number of parse requests. This number is incremented for each parse request, even if the query is already parsed in the cache.
Parse Cnt (hard)	The number of parse requests that result in a load of the cursor into the cursor cache and the building of the plan tree.
Pins	The number of times a PIN was requested for objects from the library cache.
Reloads	The number of PINs of objects which are not the first PIN performed since the object handles were created, and which requires loading the objects from disk.

Redo Buffer

The Redo Buffer portion of the Oracle Detail Cache screen displays information about the "dirty" buffers. The changes have to be written into the redo logs.

Table 39.3 SOS Oracle Redo Buffer data items

Data Item	Description
Redo Syn Wrts	This statistic reflects the number of user commits, the number of checkpoints, and the number of log switches. Its value is incremented every time a write in the log files occurs.
Redo Logspc Req	The "Redo Logspc Req" statistic reflects the number of times a user process waits for space in the redo log buffer area of the SGA. Usually, a space request will be associated with a log switch. This wait is often caused by the archiver being lazy and the log writer not being able to write from the log buffer to the redo log because the redo log has not been copied by the ARCH process. If the value of this statistic is non-zero, setting a bigger value for the LOG_BUFFER parameter in the init.ora file should increase the size of the redo log buffer area of the SGA. Increasing the size of the online redo log files can also help decrease the number of waits associated with redo log entries as fewer log switches happen. This statistic should ideally be zero. It is a key performance indicator.

Waits

The Waits data items are described in the next table. Oracle waits should be minimized as possible.

Table 39.4 SOS Oracle Waits data items

Data Item	Description
Free List	The number of waits for free lists. If this number is too high, you could reduce free lists wait by increasing the FREELIST parameter for tables.
NOTE The next four values could indicate rollback conflicts.
Sys Undo Block	The number of waits for blocks for the SYSTEM rollback segment other than headers.
Sys Undo Header	The number of waits for the buffers that contain the header blocks for the SYSTEM rollback segment.
Undo Block	The number of waits for blocks (except headers) for rollback segments other than SYSTEM.
Undo Header	The number of waits for the buffers that contain the header blocks for rollback segments other than SYSTEM.

Performance Tip

To reduce rollback conflicts, new rollback segments could be added. Undo header wait occurs if there are not enough rollback segments to support the number of concurrent transactions. Undo header wait occurs when multiple users update records in the same block at the same time.

Data Dict Cache

The Data Dict Cache screen section displays information about the portion of memory in which Oracle keeps information about database structure in memory. The data items are described in the next table.

Table 39.5 SOS Oracle Data Dict Cache data items

Data Item	Description
Hit Ratio	(1 - gets/getmisses) * 100
Gets	The number of get requests from the data dictionary.
Get Misses	The number of misses for get requests from the data dictionary—get requests for which the data was not found in the cache and had to be read from disk.