Oracle Monitor Plugin

Overview

Plugin Details

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:

1. 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.
2. 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
3. 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:
      1. 
      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:
      1. 
4. Adjust log4j properties in the log4j.properties file:
   1. 
5. 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.
6. Use standard deployment procedure to deploy web UI war file on the application server of choice:
1. 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:

1. v$buffer_pool_statistics
2. v$sysstat
3. v$librarycache
4. v$license
5. dba_objects
6. dba_data_files
7. dba_free_space
8. v$locked_object
9. v$session
10. v$sql
11. v$sql_plan
12. v$sql_plan_statistics_all

13. v$rowcache

14. v$sgastat

15. v$latch

16. v$latchname

17. v$waitstat

18. v$rollstat
19. 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!)