Oracle Monitor Plugin

Overview

Icon

Please note that most of this plugin's functionality was transferred to the Database Agent available in Dynatrace AppMon 6.3.

The Oracle Monitor plugin enables monitoring the values provided in Oracle's v$ tables.The plugin uses JDBC to connect to the Oracle Database and queries the most important metrics from these tables. Having these measures in dynaTrace enables quick correlation of database related performance issues such as high I/O or too many database connections to application transaction performance problems such as long running transactions or slow database queries.

Plugin Details

Plug-In Files	dynaTrace 3.2: Oracle Monitor Plugin 1.0.5 Oracle Monitor Dashboard dynaTrace 3.5+: Oracle Monitor Plugin 1.0.8 Oracle Monitor Dashboard dynaTrace 5.x+: The Enhanced Oracle Monitor Plugin 1.0.24 The Web UI war file for historical analysis of SQL statements, database locks, explain plans, tablespaces, etc. It supports password encryption in the database.properties file. See section 3 of plugin documentation for instructions about deploying web UI application on the application server of choice. The encrypt utility that encrypts user password for the database.properties file. Example of Oracle Monitor Dashboard DDL scripts with supporting historical tables for Web UI application: - Oracle database: create_top_sqls_oracle_ddl.sql, top_wait_events_oracle_ddl.sql; - MS SQL Server database:create_top_sqls_sqlserver_ddl.sql, top_wait_events_sqlserver_ddl.sql; - MS SQL Server 2008 and lower: create_top_sqls_sqlserver_2008_ddl.sql, top_wait_events_sqlserver_ddl.sql - PostgreSql database: create_top_sqls_postgresql_ddl.sql, top_wait_events_postgresql_ddl.sql Properties files: - database_oracle.properties with password encryption and database_oracle.properties without password encryption. - database_postgresql.properties - database_sqlserver.properties - log4j.properties Added cleanup capabilities to control size of data in the historical tables schema (1.0.11.1+). Cleanup process is a task. The Purge After parameter sets the purge interval for the historical tables schema. New plugin parameter isCleanupTask controls cleanup task. See Cleanup Task and Cleanup Task configuration screenshots for details. Documentation
Author	Chuck Miller (charles.miller@dynatrace.com) & Joe Hoffman (joe.hoffman@dynatrace.com) Thanks to Yakov Sobolev at JPMorgan Chase for helping add Service Name support to this plugin Eugene Turetsky (eugene.turetsky@dynatrace.com) Enhanced Oracle Monitor Plugin v. 1.0.9+. For author inquiries, contact Eugene, he knows the latest on this plugin.
dynaTrace Versions	3.x, 4.x, 5.x, 6.x
License	dynaTrace BSD
Support	Not Supported
Known Problems
Release History	2010-06-30 1.0.5 Initial Release 2010-11-30 1.0.7 Updated for 3.5, handle reconnect after DB restart, improved logging 2011-06-06 1.0.8 Updated to handle the Oracle Thin Driver syntax for clustered databases which are using a Service name 2014-03-28 1.0.9 Added support for historical analysis of top N slow SQL statements, historical analysis of database locks, historical analysis of explain plans etc. (see documentation for details) 2014-11-08 1.0.10 Release includes: Support for Oracle tablespaces. New dynamic measures allow to gather and monitor available free/used/total space of Oracle tablespaces (see example here). Added dashlets with historical and current analysis of Oracle tablespaces. Added support for persisting historical data into the PostgreSQL 9.x and MS SQL Server (2005+) databases. Added configuration parameters that are allowing to manage dynamic measures, historical analysis data, and explain plans. 2015-02-20 1.0.11.1 Release includes: Support for Network Data Encryption and Integrity for Oracle Database servers. See Support for Network Data Encryption and Integrity section below. The htmlFileSqls, htmlFileLocks, and htmlFileTablespaces parameters are not mandatory anymore. Please note that the htmlFileSqls parameter overrides the isDynamicMeasures and the isHistoryOn parameters. So, if the htmlFileSqls parameter is not set then plugin will not gather any of the dynamic measures or history data. Plugin produces just basic statistics of the pre 1.0.9 version if all of these parameters are omitted. Added cleanup capabilities to control size of data in the historical tables schema. Cleanup process is a task. The Purge After parameter sets the purge interval for the historical tables schema. New plugin parameter isCleanupTask controls cleanup task. See Cleanup Task and Cleanup Task configuration screenshots for details. 2015-03-20 1.0.12.0 Release includes: Added support for Oracle Net Connection Descriptor feature. isOracleNetConnectionDescriptor is a Boolean indicator, when it is "true" then the Oracle Net Connection Descriptor is used, otherwise it is "false"; Oracle Net Connection Descriptor is a string taken from the tnsnames.ora file, e.g, in order to use this feature please copy and paste the following example of the Oracle Net Connection Descriptor string (DESCRIPTION_LIST = (LOAD_BALANCE = off) (FAILOVER = on) (DESCRIPTION = (ADDRESS_LIST = ( ADDRESS = (PROTOCOL = TCP)(HOST =dbhost-01)(PORT = 1521)) ( ADDRESS = (PROTOCOL = TCP)(HOST =dbhost-02)(PORT = 1521))) ( CONNECT_DATA =(SERVER=DEDICATED)(SERVICE_NAME=db1.test.local)))) into the Oracle Net Connection Descriptor parameter field. 2015-04-21 1.0.12.2 Release includes: Support when history schema is hosted by the MS SQL Server 2008 and lower. New isSqlServer2008 parameter is managing it. Miscellaneous bug fixes (require re-deploying of the SqlMVC.war application). 2015-04-30 1.0.12.3 Release includes: Slightly changed way to handle gathered by the plugin statistics, i.e. htmlFileSqls, htmlFileLocks, and htmlFileTablespaces are handling the latest timeslices with slow SQLs, Locks, and Tablespaces. If they are not setup or equals white-spaces, no html files will be generated by the plugin. isHistoryOn manages historical data gathered by the plugin. If it is 'true', plugin generates and saves statistics in the history tables. Gathered historical measures will be displayed in the Analysis of Slow SQLs, Slow SQLs Explain Plans, Analysis of Locks, and Analysis of Tablespaces dashlets by the SqlMVC application. isExplainPlan manages creation of explain plans for slow SQLs. It is used only when isHistoryOn set to 'true'. isDynamicMeasures manages populated by the plugin set of dynamic measures (for more details see section 4.7 on page 16 of the plugin documentation) 2015-05-06 1.0.12.4 Release fixes encoding of the latest timeslices of slow SQLs dashlet. 2015-06-18 1.0.14 Release includes: Added the following three measures of the "Oracle System" metric group: "Instance Up" is 1 when monitoring Oracle database instance is up, otherwise it is set to 0; "History Instance Up" measure: if isHistoryOn indicator is true then the "History Instance Up" measure is 1 when database which hosts history tables is up, otherwise it is set to 0. If isHistoryOn indicator is false then the "History Instance Up" measure is not populated. "Success" is 1 when execution of the monitor was successful, otherwise it is set to 0. Please see plugin's log file for reason of failure. Added ability to setup thresholds for certain tablespaces of monitoring databases. In order to use this feature user needs to create a Metric Group Monitor plugin as it is described here and deploy it together with the Oracle Monitor plugin. Metric groups and metrics in the Metric Group Monitor plugin should follow next rules: The metric group name should be <db-name>_TS where <db-name> is name of a monitored database which contains tablespace in question; Metrics names for the tablespace in question should be: <tablespace-name>_TOTAL for total number of bytes; <tablespace-name>_USED for used number of bytes; <tablespace-name>_FREE for free number of bytes; <tablespace-name>_USED_PCT for used percentage (%) of bytes; <tablespace-name>_FREE_PCT for free percentage (%) of bytes. Example of a metric group monitor plugin jar file is here. Examples of a metric group monitor plugin are in Part 1 and Part 2. Example of gathered non-dynamic tablespaces measures is here. Use of datasources for accessing monitored databases During initialization, plugin now writes into the log file the INFO level message with version of the monitored Oracle database. 2015-06-19 1.0.15 Release includes: Added library cache hit ratio metrics to the Oracle Cache Hit Ratio metric group: SQL Area Pin Hit Ratio; Table/Procedure Get and Pin Hit Ratios; Trigger Get and Pin Hit Ratios; Body Get and Pin Hit Ratios. Note: for compatibility with previous releases of the plugin the SQL Area Get Ratio metric was left in the Oracle SGA metric group. 2015-06-22 1.0.15.0 Release includes the following new metrics that plugin now gathers: Oracle Cache Hit Ratio metric group: Dictionary Cache Hit Ratio; Library Cache Get Hit Ratio; Library Cache Pin Hit Ratio. Oracle Latches metric group: Immediate Latch Gets Wait Latch Gets. Oracle Redo metric group: Redo Allocation Latch; Redo Copy Latches; Redo Space Wait Ratio. Oracle Shared Pool metric group: Shared Pool Free Memory; Shared Pool Reloads. Oracle Table Contention metric group: Chained Fetch Ratio; Free List Contention. Oracle Miscellaneous metric group: Recursive Calls Ratio; Rollback Segment Contention; Short Table Scans Ratio. If you are upgrading plugin from an older version to the version 1.0.15.0, please note that there are 5 new views that user of the monitored Oracle database needs to have read access to. They are: v$rowcache v$sgastat v$latch v$latchname v$waitstat v$rollstat v$system_event 2015-07-15 1.0.16 Release includes: New Oracle Top 5 Wait Events dashlet (see examples below); The URL property of the Content tab in the Analysis of Top 5 Wait Events dashlet is depicted here. Please note that URI part of this URL is case sensitive. Here is an example of the URL property: http://localhost:8080/SqlMVC/topWaitEvents Use of POST requests instead of GET requests by the SqlMVC application; Improved troubleshooting of the Oracle plugin and SqlMVC application. 2015-07-23 1.0.17 Release includes: The T_LOCK table was slightly changed. It needs to be altered to accommodate the following changes: Added two new columns in the T_LOCK table. They are: OS_USER_NAME associated with the lock; OWNER of the locked object. Removed the SERIALl# part from the SID_SERIAL column to simplify extraction of the locks Please see the create_top_sqls_<XXXXXX>_ddl.sql files for DDL definition of the updated T_LOCK table. The XXXXXX is either oracle, or postgresql, or sqlserver. Oracle user needs to have SELECT privilege to the v$locked_object view. SELECT privilege to the gv$locked_object view can be removed. Miscellaneous changes to improve logging The SqlMVC war file needs to be re-deployed 2015-12-03 1.0.18 Release includes: Fixed issue described in this post. 2016-01-02 1.0.19 Release includes: Added new indicator 'Is Monitored Oracle Instance Up or Down'. When it is ON plugin checks if monitored instance of Oracle database is up or down. It sets the 'Instance Up' and 'Success' measures only. No other measures are set. When its is OFF plugin gathers full scale of measures depending on settings of other configuration parameters. Default value of the 'Is Monitored Oracle Instance Up or Down' indicator is OFF. 2016-04-08 1.0.20 Release includes: Fixed visibility of the Cleanup Task configuration parameters. 2016-07-02 1.0.21 Release includes: Oracle NLS information becomes visible in the FINER log files. 2016-08-09 1.0.22 Release includes: Fixed issue with special characters for the simple Top 5 Wait Events Dashlet (non-Analysis type of dashlets). 2016-09-15 1.0.23 Release includes: Fixed issue related to gathering database locks. 2016-09-22 1.0.24 Release includes: Added support for Oracle Net Connection Descriptor feature for the History Tables database. the 'isOracleNetConnectionDescriptorHistory' parameter is a Boolean indicator, when it is "true" then the Oracle Net Connection Descriptor is used, otherwise it is "false"; the 'Oracle Net Connection Descriptor History' parameter is a string taken from the tnsnames.ora file, e.g, in order to use this feature please copy and paste the following example of the Oracle Net Connection Descriptor string (DESCRIPTION_LIST = (LOAD_BALANCE = off) (FAILOVER = on) (DESCRIPTION = (ADDRESS_LIST = ( ADDRESS = (PROTOCOL = TCP)(HOST =dbhost-01)(PORT = 1521)) ( ADDRESS = (PROTOCOL = TCP)(HOST =dbhost-02)(PORT = 1521))) ( CONNECT_DATA =(SERVER=DEDICATED)(SERVICE_NAME=db1.test.local)))) into the 'Oracle Net Connection Descriptor History' parameter field.

Provided Measures

The following image shows the metrics that the monitor provides:

New Capabilities of the Enhanced Oracle Monitor Plugin

Enhanced Oracle Monitor Plugin adds to the existing Oracle Monitor Plugin ability to gather statistics on the SQL level. It provides customers with the following statistics for every SQL statement:

SQL SID
SQL Full Text
Child Number
Number of Executions
Elapsed Time
Average Elapsed Time
CPU Time
Average CPU Time
Disk Reads
Direct Writes
Buffer Gets
Rows Processed
Parse Calls
First Load Time
Last Load Time

For in-depth SQL analysis there is SQL explain plan which was captured at the time when this SQL statement was executed.

Besides detailed SQL level statistics there are stats about database locks, tablespaces (coming) etc. which give user additional information about state of the database. For database locks the following information is captured:

Concatenation of the session_id from the gv$locked_object view and serial# from the v$session view
Oracle User
Object Name
Object Type
Lock Mode
Status
Last DDL Time

Plugin keeps information in the performance warehouse (or in any external relational database) and hence allows going back in history to compare performance of the SQL in question over time. The Web UI piece of the plugin handles getting historical data and allows performing analysis of the slow SQL statements, explain plans, locks, etc. historically.

Following screenshot contains top N SQL statements Dashlet:

Following screenshot contains Database Locks Dashlet:

Following screenshot contains Tablespaces Dashlet:

Following screenshot contains Top 5 Wait Events Dashlet:

Following screenshot contains Analysis Dashlet for top N SQL statements:

Following screenshot contains Explain Plans Dashlet taken at the time of SQL statement execution:

Following screenshot contains Analysis of Database Locks Dashlet:

Following screenshot contains Analysis of Tablespaces Dashlet:

Following screenshot contains Analysis of Top 5 Wait Events Dashlet:

Following screenshot shows list of dynamic measures which are gathered by the plugin for every top N SQL statement:

Following screenshot shows list of dynamic measures which are gathered by the plugin for every database lock:

Please see documentation of the Enhanced Oracle Monitor Plugin for more details.

Sorting Columns of HTML Tables

All columns of HTML tables presented in the Oracle Monitor dashlets are sortable. Just click on the HTML table's column header to sort content of the table in ascending or descending order by this column. Following two screenshots show example of the tablespaces dashlet sorted by "USED (%)" column in descending and ascending order respectively:

Support for Network Data Encryption and Integrity

Oracle Monitor Plugin starting with version 1.0.11 supports Network Data Encryption and Integrity. There are new configuration parameters added to the plugin which describe Encryption and Integrity algorithms and their types. These parameters are added to the monitored and history databases in case the history database is an Oracle database. Below please find description of these parameters:

isEncryption, isEncryptionHistory

Boolean indicator which shows if Oracle Network Data Encryption and Integrity is on or off.

DB Encryption, DB Encryption History

Valid values for the DB Encryption are any of the following values or comma separated list of any subset of the following values:

- REJECTED
- ACCEPTED
- REQUESTED
- REQUIRED
DB Encryption Types, DB Encryption Types History

Valid types are the following types or any comma separated subset of the following types:

RC4_256
RC4_128
RC4_56
RC4_40
AES256
AES192
AES128
3DES168
3DES112
DES
DES40
DB Checksum, DB Checksum History

Valid values for the DB Integrity are any of the following values or comma separated list of any subset of the following values:

REJECTED
ACCEPTED
REQUESTED
REQUIRED

DB Checksum Types, DB Checksum Types History

Valid types are the following types or any comma separated subset of the following types:

SHA256
SHA384
SHA512
SHA1
MD5

Deploying Enhanced Oracle Monitor Plugin and web UI application

Besides regular steps which are described for the previous versions of the Oracle Monitor Plugin in the "Configuration Oracle Monitor" section below, there are following deployment steps which need to be done before Enhanced Oracle Monitor Plugin and web UI application can be used:

Create supporting tables for the historical analysis:
1. Changing owner's placeholder name in the create_top_sqls_ddl.sql script from "c##dt55" to a real name which will be used by the web UI application.
2. Execute the create_top_sqls_ddl.sql script using Oracle sqlplus utility or SQL Developer.
  Scripts which create database schemas for MS SQL Server and PostgreSQL databases are: create_top_sqls_sqlserver_ddl.sql and create_top_sqls_postgresql_ddl.sql.
Change values of the ${db.url}, ${db.driver}, ${db.user}, and ${db.password}, ${db.encryption}, ${db.encryption.types}, ${db.checksum}, and ${db.checksum.types} variables in the database_oracle.properties file to the appropriate values. See screenshot of the database properties file for the Oracle RDMS below:
- Note:
  - Here are other examples of the database properties files. The following variables ${db.encryption}, ${db.encryption.types}, ${db.checksum}, and ${db.checksum.types} should be set to empty strings as shown in the next examples below:
  - For the PostgreSql database: database_postgresql.properties;
  - For the SQLServer database: database_sqlserver.properties
Password encryption
To encrypt user password in the database.properties file please follow next steps:
1. Use the folloowing syntax for the password field in the database.properties file:
  1. db.password=ENC(XXXXXXXXX), where XXXXXXXXX is encrypted user password.
2. Use encrypt utility to encrypt user password:
  2. The 'input' parameter of the encrypt utility contains actual password, e.g. in our example below the 'Top_Secret' is a user password.
  3. The 'password' field should be always the same, i.e. 'password=SEED_VALUE' as shown in the above screenshot.
3. Copy encrypted password from the encrypt utility output (under the "-----OUTPUT---------" string) and paste encrypted password into the database.properties file as shown in the next screenshot:
Adjust log4j properties in the log4j.properties file:
Set environmental variable “ext.prop.dir” to a directory where the database.properties and the log4j.properties files are located. For example, the following commands are setting the ext.prop.dir environmental variable in Windows and Linux OS respectively:
1. “set ext.prop.dir=C:\Users\dmaext0\”
2. env "ext.prop.dir=/home/myhome/" bash

Note:
- Do not forget to add “\” (backward slash) at the end of the directory name. On Unix systems use “/”.
- Make sure that user which owns application server process (e.g. Tomcat, Jetty, JBoss, etc.) where web UI application will be executed has read access to the database.properties and log4j.properties files.

Use standard deployment procedure to deploy web UI war file on the application server of choice:

For Tomcat 6.0+ the standard deployment procedure is described here. One of the options to deploy war file (probably the easiest one) is to follow up steps from the following extract from the above article:
Copy the web application archive file into directory $CATALINA_BASE/webapps/. When Tomcat is started, it will automatically expand the web application archive file into its unpacked form, and execute the application that way. This approach would typically be used to install an additional application, provided by a third party vendor or by your internal development staff, into an existing Tomcat installation. NOTE - If you use this approach, and wish to update your application later, you must both replace the web application archive file AND delete the expanded directory that Tomcat created, and then restart Tomcat, in order to reflect your changes.

See section 3 of the plugin documentation for more details about web UI war deployment process.

Cleanup Task

Cleanup Task controls size of data in the historical tables schema (1.0.11.1+). Cleanup process is a task. New plugin parameter isCleanupTask controls cleanup task. The Purge After parameter sets the purge interval for the historical tables schema. The following two screenshots show Cleanup Task and Cleanup Task configuration examples:

Access privileges to the Oracle system tables and views for the Enhanced Oracle Monitor Plugin

Oracle user of the monitored database needs to have SELECT privileges for the following objects:

v$buffer_pool_statistics
v$sysstat
v$librarycache
v$license
dba_objects
dba_data_files
dba_free_space
v$locked_object
v$session
v$sql
v$sql_plan
v$sql_plan_statistics_all
v$rowcache
v$sgastat
v$latch
v$latchname
v$waitstat
v$rollstat
v$system_event

Configuration Oracle Monitor

The monitor requires the following configuration settings:

isCleanupTask indicator. If 'true' then plugin needs to be configured as a task to periodically cleanup history tables based on its schedule; otherwise plugin needs to be configured as a monitor to gather performance statistics of the Oracle database Instance. Default is 'false'.
isOracleNetConnectionDescriptor indicator. If 'true' then the Net Connection Descriptor syntax is used to connect to the Oracle Database Instance. See the 1.0.12.0 Release Notes above. Default is 'false'
hostName: Host name of the Oracle Database Instance
isServiceName indicator. If 'true' then the service name syntax of the URL connection string is used, Otherwise DBName (SID) syntax of the URL connection string is used. Default is 'false'.
dbName: Database Instance Name (SID) or Service name
dbUsername: Username that is used to access the database. User needs to have query rights to v$ tables
dbPassword: Password that is used to access the database
dbPort: Oracle Database Port for JDBC Connections (default: 1521)
isEncryption indicator. If 'true' then support for Network Data Encryption and Integrity is used. Then the following parameters needs to be configured:
- DB Encryption
- DB Encryption Types
- DB Checksum
- DB Checksum Types

See Support for Network Data Encryption and Integrity section above on this page for more details.

Default is 'false'.

Top Slow SQLs. Sets the number of top slow SQLs that plugin will gather. Default is 10. Please choose this number carefully to avoid measures explosion.
isExplainPlan indicator. If 'true' then explain plans of top slow SQL statements will be captured. See Release Notes 1.0.12.3 above on this page for details. Default is 'false'.
isDynamicMeasures indicator. If 'true' then dynamic measures will be captured. See Release Notes 1.0.12.3 above on this page for details. Default is 'false'.
htmlFileSqls, htmlFileLocks, htmlFileTablespaces, htmlFileTopWaitEvents paths to the files which contains list of slow SQLs, database locks, tablespaces, and top wait events respectively.
typeOfSlowness. There are 8 different types of slowness. They are:
- Elapsed Time;
- Buffer Gets;
- CPU Time;
- Executions;
- Parse Calls;
- Disk Reads;
- Direct Writes;
- Rows Processed.
Metric Group Suffix see Release Notes 1.0.14 above on this page for details.
isHistoryOn indicator. If 'true' then performance metrics about the monitored Oracle Instance will be gathered and saved in the historical tables. Default is 'false'.
DB type for History Tables. Sets type of the DBMS system for the history tables. Takes one of three values: Oracle, PostgreSQL, and MS SQL Server. Default is Oracle.
Is SQL Server 2008 and lower? indicator set to true if version of the MS SQL Server is 2008 or lower.
isEncryptionHistory indicator: the same as the isEncryption above applied to the database for history tables. Ignored when DB type for History Tables is different from the Oracle DBMS.
DB Encryption History Tables, DB Encryption Types History Tables, DB Checksum History Tables, DB Checksum Types for History Tables are similar to the above parameters for DB Encryption for the monitored Oracle Instance. Ignored when DB type for History Tables is different from the Oracle DBMS.
isServiceNameHistory indicator is the same as the isServiceName indicator applied for the history tables. Ignored when DB type for History Tables is different from the Oracle DBMS.
dbNameOracleHistory, hostNameHistory, dbPortHistory, dbUsernameHistory, and dbPasswordHistory are similar to the respective parameters above applicable to the history tables database.

Installation of the plugin jar file

Import the Plugin into the dynaTrace Server via the dynaTrace Server Settings menu -> Plugins -> Install Plugin. For details how to do this please refer to the dynaTrace documentation.

To use the provided dashboard please leave the default name of the Monitor as "RepositoryDB", then open the Dashboard and set the Data Source accordingly.

Usage Notes

As of v1.0.8 the Thin Driver syntax is now supported which provides support for clustered databases which use a service name. The DBName (SID) syntax is also still supported.

Comments

Please post comments in AppMon & UEM Plugins

Looking for old comments? Find them here (this page is loading very slow!)