Overview
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: dynaTrace 5.x+: |
---|---|
Author | Chuck Miller (charles.miller@dynatrace.com) & Joe Hoffman (joe.hoffman@dynatrace.com) For author inquiries, contact Eugene, he knows the latest on this plugin. |
dynaTrace Versions | 3.x, 4.x, 5.x, 6.x |
License | |
Support | |
Known Problems |
|
Release History | 2010-06-30 1.0.5 Initial Release
2015-02-20 1.0.11.1 Release includes:
2015-03-20 1.0.12.0 Release includes:
2015-04-21 1.0.12.2 Release includes:
2015-04-30 1.0.12.3 Release includes:
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:
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 1.0.15.0 Release includes the following new metrics that plugin now gathers:
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:
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:
|
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:
- 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.
- 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
- Note:
- Password encryption
To encrypt user password in the database.properties file please follow next steps:- Use the folloowing syntax for the password field in the database.properties file:
- db.password=ENC(XXXXXXXXX), where XXXXXXXXX is encrypted user password.
- Use encrypt utility to encrypt user password:
- The 'input' parameter of the encrypt utility contains actual password, e.g. in our example below the 'Top_Secret' is a user password.
- The 'password' field should be always the same, i.e. 'password=SEED_VALUE' as shown in the above screenshot.
- 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:
- Use the folloowing syntax for the password field in the database.properties file:
- 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:
- “set ext.prop.dir=C:\Users\dmaext0\”
- 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!)