Oracle Database (Updated)

Version 6

    Oracle Database

    This template assesses the status and performance of an Oracle database by retrieving performance data from the built-in Oracle statistics views.

    Prerequisites: Oracle client installed on Orion APM server. This is available from the SolarWinds customer portal under Additional Downloads.

    Credentials: An Oracle user name and password with read access to the Oracle views: dba_free_space, v$sysstat, v$rowcache, v$librarycache, v$sgastat, and v$session.

    Note: For issues connecting to Oracle databases using this monitor, see the following SolarWinds knowledgebase article: http://knowledgebase.solarwinds.com/kb/questions/2067/.


    Monitored Components

    Components without predetermined threshold values provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find an appropriate threshold for your application. For more information, see http://knowledgebase.solarwinds.com/kb/questions/2415.

    Available free space (MB) 

    This component monitor returns the available free space of the database in MB. This value should be as high as possible.

    Buffer cache hit ratio (%)

    This component monitor returns the percentage of pages found in the buffer cache without having to read from the disk.

    This ratio should exceed 90%, and ideally be over 99%. If your Buffer Cache Hit Ratio is lower than 90%, you should consider adding more RAM, if possible. A higher ratio value returned indicates improved performance by your Oracle Server.

    Note: If your database is very large, you may not be able to get close to 99%, even if you put the maximum amount of RAM in your server.

    Dictionary cache hit ratio (%)

    This component monitor returns the ratio, as a percentage, of dictionary cache hits to total requests. The dictionary cache stores data referenced from the data dictionary. A properly tuned dictionary cache can significantly improve overall database performance. Guidelines for acceptable ratios are as follows:

    • 70% or above. 99% would be ideal.
    • If the ratio is below 70%, increase the value of the initialization parameter, SHARED_POOL_SIZE. It is recommended that the SHARED_POOL_SIZE parameter be at least 4 MB.
      • Large databases may require a shared pool of at least 10 MB.

    Note: Increasing the SHARED_POOL_SIZE parameter will increase the size of the System Global Area (SGA).

    Library cache hit ratio (%)

    This component monitor returns the percentage of Pin requests that result in hits.

    • PINS - Defined as the number of times an item in the library cache was executed.
    • PINHITS - Defined as the number of times an item was executed without reloads.

    The library cache stores the executable form of recently referenced SQL and PL/SQL code. Ideally, the value of this component monitor should be greater than 95%. If the value is less than 95%, you may want to try the following:

    • Increase the SHARED_POOL_SIZE parameter.
    • The CURSOR_SHARING parameter may need to be set to FORCE.
    • Increase the size of the SHARED_POOL_RESERVED_SIZE parameter.
    • Sharing of SQL, PLSQL or JAVA code may be inefficient.
    • Use of bind variables may be insufficient.

    Available free memory (MB)

    This component monitor returns the free memory in MB, of all SGA pools. This value should be as high as possible.

    Number of connected users to the database through SQL Net

    This component monitor returns the number of currently connected users.

    Temp files size (MB)

    This component monitor returns the size of all temporary files in MB. This value should be as low as possible.

    Data files size (MB)

    This component monitor returns the size of all database files in MB.

    Total short table scans

    This component monitor returns the total number of full table scans that were performed on tables having less than five Oracle data blocks since database instance startup.

    Note: It is generally more efficient to perform full table scans on short tables rather than access the data using indexes.

    Total long table scans

    This component monitor returns the total number of full table scans done on tables containing five or more Oracle data blocks since database instance startup.

    Note: It may be advantageous to access long tables using indexes.

    User transactions

    This component monitor returns the total number of users’ transactions.

    Disk sort operations

    This component monitor returns the number of sort operations that require at least one disk write. This value should be as low as possible.

    Note: Sorts that require continual reading and writing to disk can consume a great deal of resources. If this monitor returns a high value, consider increasing the size of the initialization parameter, SORT_AREA_SIZE. For more information, see: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams232.htm#i1134127.

    Memory sort operations

    This component monitor returns the number of sort operations that were performed completely in memory meaning no disk writes were required.

    User rollbacks

    This component monitor returns the number of times that users manually issued the Rollback statement. Use of the Rollback statement may also indicate an error occurred during a user's transactions. This value should be as low as possible.

    Free Space in Tablespace (MB)

    This component monitor returns the free space in MB for a specific oracle tablespace.  The returned value should be as high as possible.

    By default, this monitor returns the statistic for the System tablespace. To change the target tablespace you should change the following line:

     

    AND fs.tablespace_name = 'SYSTEM'

    To identify which tablespace are available run the following command on target oracle server:

     

    select tablespace_name from dba_data_files;


    Free Space in Tablespace (%)

    This component monitor returns the percentage of free space for a specific oracle tablespace.  The returned value should be as high as possible.

    By default, this monitor returns the statistic for the System tablespace. To change the target tablespace you should change the following line:

     

    AND fs.tablespace_name = 'SYSTEM'


    To identify which tablespace are available run the following command on target oracle server:


    select tablespace_name from dba_data_files;

     

    Used Space in Tablespace (%)

    This component monitor returns the free space in MB for a specific oracle tablespace. The returned value should be as high as possible.

    By default, this monitor returns a statistic for the System tablespace. To change the target tablespace you should change the following line:

     

    AND fs.tablespace_name = 'SYSTEM'

     

    To identify which tablespaces are available, run the following command on the target oracle server:

     

    select tablespace_name from dba_data_files;

     

     

    Used Space in Tablespace (%) from Max
    This component monitor returns the percentage of used space from maximum possible tablespace space, for a specific oracle tablespace. The returned value should be as low as possible. By default, this monitor returns a statistic for the System tablespace. To change the target tablespace you should change the following line:

    where a.tablespace_name = b.tablespace_name AND a.tablespace_name = 'SYSTEM'

    To identify which tablespaces are available, run the following command on the target oracle server:

    select tablespace_name from dba_data_files;


    Portions of this document were originally created by and are excerpted from the following sources:

    Oracle Corporation, “Oracle Database Documentation,” Copyright © 2012 Oracle Corporation. 

    All rights reserved. Available at http://download.oracle.com/docs
    http://dirknachbar.blogspot.com/2011/02/percentage-used-of-tablespaces.html

     

    Last updated 1/28/2015