This component monitor template assesses the status and performance of an Oracle database by retrieving performance data from the built-in Oracle statistics views.
This monitor works with the Orion Agent for Linux.
This template is included with SolarWinds SAM during installation. It is also posted in the SolarWinds online IT community, THWACK.
To monitor Oracle database servers, you'll need to download and install drivers to support polling by SAM and other SolarWinds products. See Configure SAM to monitor an Oracle Database Server in the SolarWinds Success Center for more details and instructions.
If you have Additional Polling Engines (APEs), install the Oracle client software on those machines also.
An Oracle user name and password with read access to the following Oracle views:
dba_free_space, v$sysstat, v$rowcache, v$librarycache, v$sgastat, and
If customizing this template for the Oracle User Experience Monitor, the resulting output of the SQL query must return a single numeric value. A message field is optional.
If you have issues connecting to Oracle databases, see Use the SAM Oracle Database template and Oracle User Experience monitor without ODBC.
For reference, see Use SAM templates, application monitors, and component monitors.
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.
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.
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.
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.
It may be advantageous to access long tables using indexes.
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.
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: .
Memory sort operations
This component monitor returns the number of sort operations that were performed completely in memory meaning no disk writes were required.
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.
Used Space in Tablespace (%) from Max
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;