This SAM template assesses the status and performance of an Oracle database by retrieving performance data from the built-in Oracle statistics views.
To monitor Oracle database servers, install an additional driver on systems where SAM is running, including the Orion server and Additional Polling Engines (APEs) in your environment. See Configure SAM to monitor Oracle database servers.
To use this monitor with the Orion agent for Linux, you may need to install and configure ODBC. See Configure Linux/Unix systems for monitoring by the Orion agent.
Adjust Oracle User Experience Monitor settings to match the settings for Oracle databases on target servers. See Configure Oracle User Experience monitor settings.
Run the following commands on target Oracle servers to provide SAM component monitors with necessary permissions:
grant select on dba_data_files
grant select on dba_temp_files
If you have issues connecting to Oracle databases, see Use the SAM Oracle Database template and Oracle User Experience monitor without ODBC article in the SolarWinds Success Center.
The account running Orion 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:
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 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)
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.
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
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;