AppDynamics Oracle Database - Monitoring Extension

This extension works only with the standalone machine agent.

##Use Case The Oracle Database is an object-relational database management system.

The Oracle Database monitoring extension captures performance metrics from Oracle databases (version 10g and above) and displays them in AppDynamics. It retrieves metrics from the data dictionary and groups them in three categories:

• Activity: Throughput over the last minute, such as transactions, SQL executes, I/O reads and writes, average active sessions, etc.
• Resource Utilization: What database resources are currently in use (sessions, open cursors, shared pool, etc).
• Efficiency: Ratios and response times as indicators of the instance's efficiency.

##Installation

1. The Oracle DB extension needs an Oracle user account on every Oracle instance that is to be monitored. You might use an existing account with appropriate rights; however, a dedicated account will be a better solution in terms of security and manageability.
   • Example script for account creation (run this with a DBA user):

            CREATE USER appdynamics IDENTIFIED BY oracle;
            GRANT CREATE SESSION TO appdynamics;
            GRANT SELECT ANY DICTIONARY TO appdynamics;

2. Download an extract the OracleDbMonitor.zip into your <machine-agent-home>/monitors directory.
3. If you're monitoring multiple Oracle DB instances, in <machine-agent-home>/monitors, create a subdirectory for every Oracle instance (SID) that you want to monitor. E.g.:

         mkdir OraDbMonitor_ORCL

and copy "OracleDbMonitor.jar" and "monitor.xml" into the new subdirectory. 4. Get a suitable Oracle JDBC driver and copy or link it to the new subdirectory:

JRE VERSION	Driver	Location
Java 1.5	ojdbc5_g.jar	http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html OR $ORACLE_HOME/jdbc/lib/ojdbc5_g.jar
Java 6	ojdbc6_g.jar	http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html OR $ORACLE_HOME/jdbc/lib/ojdbc6_g.jar

Note: You may copy the driver file into your monitor subdirectory or use a symbolic link, like:

        ln \--s $ORACLE_HOME/jdbc/lib/ojdbc5_g.jar ojdbc5_g.jar

5. Restart the Machine Agent.
6. Look for the metrics in the AppDynamics Metric Browser under | Application Infrastructure Performance | <Tier> | Custom Metrics | Oracle Instance (SID).

##Metrics

Here is a summary of the collected metrics. Complete documentation of Oracle's metrics can be found at http://docs.oracle.com/cd/E11882_01/server.112/e17110/waitevents.htm#REFRN101.

AppDynamics displays metric values as integers. Some metrics are therefore scaled up by a factor of 100 for a better display of low values (e.g. between 0 and 2).

Metric Class	Description
Activity	Metric Description Active Sessions Current Number of active sessions at the point in time when the snapshot was taken. Average Active Sessions Average number of active sessions within the last 60 s. This is maybe the single most important DB load metric and a good starting point for a drill-down. Average Active Sessions per logical CPU (\*100) This shows the average load the database imposes on each logical CPU (i.e. cores or hyperthreads). Values above 100 (more than 1 waiting DB session per CPU) indicate a higher demand for resources than the host can satisfy. This often marks the beginning of quickly rising response times. Current OS Load Host CPU load, when available. DB Block Changes Per Sec Database blocks changed in the buffer cache. DB Block Changes Per Txn Database blocks changed in the buffer cache per SQL transaction. DB Block Gets Per Sec Database blocks read from the buffer cache. DB Block Gets Per Txn Database blocks read from the buffer cache per SQL transaction. Executions Per Sec SQL executions/s Executions Per Txn SQL executions per SQL transaction. I/O Megabytes per Second Logical Reads Per Sec Logical reads are comprised of database block reads from the buffer cache + physical reads from disk. Logons Per Sec Physical Reads Per Sec Database blocks read from disk. Physical Read Total Bytes Per Sec Physical Write Total Bytes Per Sec Txns Per Sec Transactions per second.
Wait Class Breakdown	Shows average active sessions per each wait class. Typically, the top wait classes are "CPU" and "User I/O". A shift to other wait classes is a good pointer for further nvestigation (e.g., of network latency issues). Wait classes are documented in the Oracle Database Reference. See here: [http://docs.oracle.com/cd/E11882\_01/server.112/e17110/waitevents001.htm\#BGGHJGII](http://docs.oracle.com/cd/E11882_01/server.112/e17110/waitevents001.htm#BGGHJGII)
Efficiency	Metric Description Database CPU Time Ratio Percentage of CPU time against all database time. Database Wait Time Ratio Complementary to "Database CPU Time Ratio" (percentage of non-CPU waits). Memory Sorts Ratio Percentage of sort operations that were done in RAM (as opposed to disk). Execute Without Parse Ratio Percentage of (soft and hard) parsed SQL against all executed SQL. Soft Parse Ratio Ratio of soft parses to hard parses. Response Time Per Txn (ms) SQL Service Response Time (ms)
Resource Utilization	Metric Description \# of logical CPUs Observation for informational purpose. This count is used, among others, for the metric "Average Active Sessions per logical CPU". Total Sessions Count of all database sessions at the time the snapshot was taken. % of max sessions Open sessions vs. DB parameter "sessions". % of max open cursors Maximum count of open cursors in a session vs. DB parameter "open\_cursors". Shared Pool Free % Temp Space Used (MB) Amount of used temporary tablespace. Total PGA Allocated (MB) Amount of RAM used for sorts, hashes and the like.

##Oracle Licensing

The metrics in the supplied code are retrieved from

-   v$session
-   v$sesstat
-   v$sysmetric
-   v$system_wait_class
-   v$waitclassmetric

all of which are, to the author's knowledge, not subject to additional licensing of the Oracle Diagnostics Pack. See Oracle's "Options and Packs" documentation: http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#CIHIHDDJ

If you plan on extending this code using data dictionary views of the Diagnostics Pack (e.g., DBA_HIST_% views), you might want to make use of the argument "ash_licensed" in monitor.xml to easily en-/disable usage of such code.

##Contributing

Always feel free to fork and contribute any changes directly via GitHub.

##Community

Find out more in the AppSphere community.

##Support

For any questions or feature request, please contact AppDynamics Center of Excellence.