Documentation forServer & Application Monitor
Monitoring your applications and environment is a key capability of SolarWinds Observability Self-Hosted (formerly Hybrid Cloud Observability) and is available in the Essentials edition. Server & Application Monitor (SAM) is also available in a standalone module.

Oracle Database

Use this SAM application monitor template to monitor the status and performance of an Oracle database by retrieving performance data from built-in Oracle statistics views.

This template includes a predefined SAM component monitor, the Oracle User Experience Monitor, that does not support polling via SolarWinds Platform Remote Collectors (ORCs).

Prerequisites

Review Configure SAM to monitor Oracle database servers and then:

  • Install an Oracle driver on the SolarWinds Platform server and any Additional Polling Engines (APEs) in your environment.
  • Adjust Oracle User Experience Monitor settings to match the settings for Oracle databases on target servers.

Run the following commands on target Oracle servers to provide SAM with necessary permissions:

grant select on dba_data_files

grant select on dba_temp_files

To use this template with the SolarWinds Platform agent for Linux, you may need to install and configure ODBC. See Configure Linux/Unix systems for monitoring by the SolarWinds Platform agent.

If you cannot connect to Oracle databases, see Use the SAM Oracle Database template and Oracle User Experience monitor without ODBC article in the SolarWinds Success Center.

Credentials

The account running SolarWinds Platform services has Full Control privileges for files in the following default folder:
C:\Program Files (x86)\Solarwinds\Orion\APM\OracleClient.

Oracle server credentials have Read access to the following Oracle views:

  • dba_data_files
  • dba_free_space
  • dba_temp_files
  • v$librarycache
  • v$rowcache
  • v$session.
  • v$sgastat
  • v$sysstat

Do not use Oracle credentials with limited permissions to run SQL statements defined in the Oracle User Experience Monitor. This will return errors such as: Table or view doesn't exist.

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.

Component monitors

Component monitors 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 environment. See Manage thresholds in SAM.

Available free space (MB)

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

Buffer cache hit ratio (%)

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 the Buffer Cache Hit Ratio is lower than 90%, consider adding more RAM. 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 (%)

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 (%)

Returns the percentage of Pin requests that result in hits.

  • PINS: The number of times an item in the library cache was executed.
  • PINHITS: 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)

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

Returns the number of currently connected users.

Temp files size (MB)

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

Data files size (MB)

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.

Perform full table scans on short tables rather than access the data using indexes.

Total long table scans

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

To access long tables, use indexes.

User transactions

Returns the total number of users’ transactions.

Disk sort operations

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

Sort operations 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. See SORT_Area_Size for details (© 2020 Oracle, available at docs.oracle.com, obtained on June 19, 2020).

Memory sort operations

Returns the number of sort operations that were performed completely in memory meaning no disk writes were required.

User rollbacks

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;