Comments have been closed on this page. Please use AppMon & UEM Plugins forum for questions about this plugin.
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.
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.24The 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.sqlProperties files: - database_oracle.properties with password encryption and database_oracle.properties without password encryption. - database_postgresql.properties - database_sqlserver.properties - log4j.propertiesAdded cleanup capabilities to control size of data in the historical tables schema (126.96.36.199+). 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
Chuck Miller (firstname.lastname@example.org) & Joe Hoffman (email@example.com) Thanks to Yakov Sobolev at JPMorgan Chase for helping add Service Name support to this pluginEugene Turetsky (firstname.lastname@example.org) Enhanced Oracle Monitor Plugin v. 1.0.9+.
For author inquiries, contact Eugene, he knows the latest on this plugin.
3.x, 4.x, 5.x, 6.x
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:
2015-02-20 188.8.131.52 Release includes:
2015-03-20 184.108.40.206 Release includes:
(LOAD_BALANCE = off)
(FAILOVER = on)
(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 220.127.116.11 Release includes:
2015-04-30 18.104.22.168 Release includes:
2015-05-06 22.214.171.124 Release fixes encoding of the latest timeslices of slow SQLs dashlet.
2015-06-18 1.0.14 Release includes:
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.
2015-06-19 1.0.15 Release includes:
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 126.96.36.199 Release includes the following new metrics that plugin now gathers:
If you are upgrading plugin from an older version to the version 188.8.131.52, please note that there are 5 new views that user of the monitored Oracle database needs to have read access to. They are:
2015-07-15 1.0.16 Release includes:
2015-07-23 1.0.17 Release includes:
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.
2015-12-03 1.0.18 Release includes:
2016-01-02 1.0.19 Release includes:
2016-04-08 1.0.20 Release includes:
2016-07-02 1.0.21 Release includes:
2016-08-09 1.0.22 Release includes:
2016-09-15 1.0.23 Release includes:
2016-09-22 1.0.24 Release includes:
into the 'Oracle Net Connection Descriptor History' parameter field.
The following image shows the metrics that the monitor provides:
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:
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:
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.
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:
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:
Boolean indicator which shows if Oracle Network Data Encryption and Integrity is on or off.
Valid values for the DB Encryption are any of the following values or comma separated list of any subset of the following values:
Valid types are the following types or any comma separated subset of the following types:
Valid values for the DB Integrity are any of the following values or comma separated list of any subset of the following values:
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:
See section 3 of the plugin documentation for more details about web UI war deployment process.
Cleanup Task controls size of data in the historical tables schema (184.108.40.206+). 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:
Oracle user of the monitored database needs to have SELECT privileges for the following objects:
The monitor requires the following configuration settings:
See Support for Network Data Encryption and Integrity section above on this page for more details.
Default is 'false'.
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.
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.
Please post comments in AppMon & UEM Plugins
Looking for old comments? Find them here (this page is loading very slow!)