Overview

The article covers the following topics:

  • common use cases where CloudMonix can help with monitoring and automation

  • what happens in the monitoring cycle

  • what is needed to connect to and monitor Oracle databases

  • what metrics can be tracked, visualized and monitored

  • what automated actions can be executed by CloudMonix


Why use CloudMonix for Oracle databases?

Popular usages of CloudMonix include the following examples:

  • Monitoring Uptime and Availability

  • Tracking the number and statuses of connections, sessions and requests

  • Tracking the longest running queries, and overall utilization

  • Execute custom SQL scripts on schedule


Monitoring Cycle

In every monitoring cycle, CloudMonix will attempt to connect to monitored database and retrieve data behind configured metrics. Most of the metrics are retrieved from  Dynamic Performance (V$) Views. CloudMonix also will also attempt to open a new connection in every cycle to measure Response Time (if that metric is enabled).


After each monitored cycle is completed, CloudMonix will evaluate and possibly execute any automated actions and auto-scaling operations.


Configuration 

Oracle database monitoring can be configured using the “Add New” button in the dashboard. In order for CloudMonix to connect to and monitor Oracle databases, it needs username/password credentials for each database, as well as host name, port number and database name.



Username & Password

Most of the metrics are retrieved from  Dynamic Performance (V$) Views. Ensure that the provided credentials have appropriate permissions to access this data.


Firewall

CloudMonix monitoring services are hosted in Azure and utilize static IPs to perform monitoring. Ensure that addressed specified in this article are white-listed.



Metrics


CloudMonix retrieves most of the metrics for Oracle databases from Dynamic Performance (V$) Views. Some metrics are captured by directly querying the databases.

Every diagnostic data point that CloudMonix retrieves from the monitored resource is considered a metric in CloudMonix. Refer to the Metrics article to learn more about metrics in general.


CloudMonix provides a default template for monitoring Oracle databases.



The metrics can be added, removed and customized in the Metrics tab in the resource configuration dialog.


Built-in Metrics

ResourceStatus

Identifies the last state of the monitored resource. This is a critical metric that is captured for most types of resources that CloudMonix tracks. It is used for Uptime reports and should not be removed. 

  • Data Type: string

  • Possible values: Ready, Down, Unknown

  • Included in sample profile: yes, tracked as a metric called Status

  • Included in default alerts: yes, in an alert Resource Outage (Error): Raises an alert when connectivity cannot be established, after specified number of retries on the Advanced tab for sustained period of time

Statuses are determined according to the following rules:

  • Ready - successfully connected to the resource and retrieved metrics

  • Down - there was an error when trying to open a connection

  • Unknown - can’t connect to the resource (e.g. because of invalid credentials) or can’t capture metrics values


ResponseTime

Tracks resource's response time by measuring how long it took to open an active connection to Oracle database, in milliseconds.

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called ResponseTime

  • Included in default alerts: yes, in an alert Slow Response (Warning): Raises an alert if establishing a connection to monitored resource takes over 15 seconds for sustained period of time


DatabaseSize

Tracks the size of the database in megabytes. Calculated as a difference between dba_data_files and dba_free_space

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called Size

  • Included in default alerts: yes, in an alert Database Approaching Max Size (Warning): Fires an alert when the size of the database approaches maximum limit. The alert must be explicitly enabled.


DatabaseActiveSessionCount

Tracks the number of open connections to the database. Retrieved from v$session.

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called ConnectionsActive

  • Included in default alerts: yes, in an alert Open Sessions Approaching Max Limit (Warning): Number of open connections (sesssions) is approaching maximum limit. The alert must be explicitly enabled.


DatabaseOracleUtilization

Tracks system statistics. Retrieved from v$sysstat.

  • Data Type: double

  • Requires specifying a Class, e.g. “User”, “Cache”, “SQL”, etc. and Performance Variable, e.g. “sorts (memory)”

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseOracleUtilizationByClass

Tracks system statistics. Retrieved from v$sysstat.

  • Data Type: array of objects with the following properties:

    • Name (string): name of the Performance Variable

    • Value (decimal): value of the Performance Variable

  • Requires specifying a Class, e.g. “User”, “Cache”, “SQL”, etc.

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseOracleExecutions

Tracks the number of times the parsed representation was requested. Retrieved from v$librarycache.

  • Data Type: double

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseOracleCache

Tracks information about library cache activity. Retrieved from v$librarycache.

  • Data Type: array of objects with the following properties:

    • Namespace (string): Library cache namespace

    • HitRatio (decimal): Ratio of the number of times an object's handle was found in memory to  the number of times a lock was requested for objects of this namespace

    • PinHitRatio (decimal): Number of times all of the metadata pieces of the library object were found in memory

    • Reloads (decimal): Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: yes, tracked as a metric called CacheInformation

  • Included in default alerts: no


DatabaseOracleCacheAdvice

Tracks the predictive models for Oracle RAM areas. Each row contains the prediction of the number of physical reads for the cache size corresponding to each row. Retrieved from v$db_cache_advice.

  • Data Type: array of objects with the following properties:

    • SizeEstimate (decimal): Cache size for prediction (in megabytes)

    • BufferEstimate (decimal): Cache size for prediction (in terms of buffers)

    • PhysicalReadFactor (decimal): Physical read factor for this cache size, which is the ratio of the number of estimated physical reads to the number of reads in the real cache. If there are no physical reads in the real cache, the value of this column is null.

    • PhysicalReads (decimal): Estimated number of physical reads for this cache size

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: yes, tracked as a metric called CacheAdvice

  • Included in default alerts: no


DatabaseOracleCacheMisses

Tracks the number of times the parsed representation did not exist in the cache. Retrieved from v$librarycache.

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called CacheMiss

  • Included in default alerts: no


DatabaseOracleCacheMissRatio

Tracks the percent of times the parsed representation did not exist in the cache. Calculated as a ratio of cache misses to executions, retrieved from v$librarycache.

  • Data Type: double

  • Included in sample profile: yes, tracked as a metric called CacheMissRatio

  • Included in default alerts: no


DatabaseOracleTopQueries

Tracks top 10 longest running queries. Retrieved from v$sql. The query text display is limited to 100 characters. 

  • Data Type: array of objects with the following properties:

    • SqlId (string): SQL identifier of the parent cursor in the library cache

    • Text (string): First 100 characters of the SQL text for the current cursor

    • ElapsedTime (decimal): Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching

    • DiskReads (decimal): Number of disk reads for this child cursor

    • Executions (decimal): Number of executions that took place on this object since it was brought into the library cache

    • FirstLoadTime (DateTime): Timestamp of the parent creation time

    • LastLoadTime (DateTime): Time at which the query plan (heap 6) was loaded into the library cache

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: yes, tracked as a metric called TopQueries

  • Included in default alerts: no


DatabaseSqlBlockingQueryCount

Tracks blocking (possibly dead-locking) queries. Retrieved from v$lock.

  • Data Type: int

  • Included in sample profile: yes, tracked as a metric called BlockingQueryCount

  • Included in default alerts: no


DatabaseOracleBlockingQueries

Tracks queries that are currently blocking each other. Retrieved from v$lock, v$session, dba_scheduler_running_jobs.

  • Data Type: array of objects with the following properties:

    • Username (string): Oracle username

    • SystemUser (string): Operating system client user name

    • LockingSid (decimal): Identifier for session holding or acquiring the lock

    • Status (string): Status of the session, possible values: 

      • ACTIVE (Session currently executing SQL)

      • INACTIVE KILLED (Session marked to be killed)

      • CACHED (Session temporarily cached for use by Oracle*XA)

      • SNIPED ( Session inactive, waiting on the client)

    • Module (string): Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure

    • HoldingProgram (string): Operating system program name

    • JobName (string): Name of the running Scheduler job

    • WaitingUsername (string): Oracle username of the waiting query

    • WaitingSid (decimal):  Operating system client user name of the waiting query

    • WaitingProgram (string):  Operating system program name of the waiting query

    • WaitingJobName (string): Name of the waiting Scheduler job

    • KillQuery  (string): Text of the query that will kill the blocking query

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: no

  • Included in default alerts: yes, in an alert Blocking Queries (Warning): Raises an alert when blocking queries are detected for 5 minutes sustained


DatabaseSqlNumeric

Captures numeric value from custom user-defined SQL statement. This metric is very useful to define alerts based on user-owned data. It is important the user-defined SQL statement can execute quickly. 


Example:

SELECT COUNT(*) FROM Customers

 

The statement should return a single scalar numeric value. Queries returning NULLs, non-numeric data, multiple rows, and etc. will not work properly.


Note: sometimes data from this metric needs to be evaluated side by side with data in other resources. For example, current count of customers may be used as a factor in scaling actions for Azure Web Roles. In such a case, use LinkedMetric to import data from Oracle database metrics into other resources.

  • Data Type: double

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseSqlQuery

Captures a table-based collection of data retrieved via user-defined SQL statement. This metric is very useful to define alerts based on user-owned data.


Note: It is important the user-defined SQL statement can execute quickly. It is also important that the size of the result set is reasonable (no more than a few hundred rows and within 20-30kb). Only one result set can be returned. Furthermore, ensure that column names of the result set do not contain spaces, start with numeric characters, and contain non-alphanumeric characters. Column names should also not duplicate. Column names of the resulting dataset will be converted to C# object properties before being visualized in the dashboard.

  • Data Type: array of objects (rows with properties as defined by the SQL statement)

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: no

  • Included in default alerts: no


Alerts

Users can create alerts based on changes in any value tracked by CloudMonix (including custom metrics). Each resource template includes alerts which are suitable for a given resource. The predefined alerts for Oracle databases are listed in the Metrics section. Refer to the Alerts article to learn more about alerts in general.


Alerts are available during the Trial period or in Professional and Ultimate plans only.


Actions and Automation 


Automation features (Actions) allow users to set up powerful reactive, proactive and scheduled actions and auto-scaling rules. CloudMonix can execute actions and scale adjustments when a specific monitoring condition occurs or according to a schedule. Refer to the Actions article to learn more.


Automation features are available during the Trial period or in the Ultimate plan only.


Built-in Actions 

CustomSqlDatabaseScript

CloudMonix will execute on the target Oracle database the custom SQL script specified in the action definition. That action is especially useful when used in combination with other CloudMonix features, such as metrics or schedules.