TOCPREVNEXTINDEX

Lund Performance Solutions


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.

    Lund Performance Solutions
    www.lund.com
    Voice: (541) 812-7600
    Fax: (541) 81207611
    info@lund.com
    TOCPREVNEXTINDEX