This extension works only with the standalone machine agent.
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:
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.
CREATE USER appdynamics IDENTIFIED BY oracle; GRANT CREATE SESSION TO appdynamics; GRANT SELECT ANY DICTIONARY TO appdynamics;
2. Download and extract the OracleDBMonitor.zip into your
3. Configure the extension by editing the config.yml (See Configuration section below).
4. Restart the Machine Agent.
5. Look for the metrics in the
AppDynamics Metric Browser under | Application Infrastructure Performance | <Tier> | Custom Metrics | Oracle Instance (SID).
Note : Please make sure to not use tab (\t) while editing yaml files. You may want to validate the yaml file using a yaml validator
Configure the Oracle DB parameters by editing the config.yml file in
<MACHINE_AGENT_HOME>/monitors/OracleDBMonitor/. Specify the host, port, username, password and sid of the Oracle DB instance.
# Oracle DB connection params host: "localhost" port: 49161 sid: "xe" username: "system" password: "oracle" # prefix used to show up metrics in AppDynamics metricPathPrefix: "Custom Metrics|ORACLE Server|"
Configure the path to the config.yml file by editing the in the monitor.xml file in the
<MACHINE_AGENT_HOME>/monitors/OracleDBMonitor/ directory. Below is the sample
<task-arguments> <!-- config file--> <argument name="config-file" is-required="true" default-value="monitors/OracleDBMonitor/config.yml" /> .... </task-arguments>
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).
|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|
|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)|
|# 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.|
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 best of our 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
Always feel free to fork and contribute any changes directly via GitHub.
Find out more in the AppSphere community.
For any questions or feature request, please contact AppDynamics Center of Excellence.