Collect Oracle specific metrics in real time for complete observability of the Oracle database to analyze performance and availability.

Two types of monitoring can be carried out:

  1. Time Series Metric Monitoring (TSMM): Collects metrics that can be used to visualize the performance of Oracle DB via widgets on our dashboard. The metrics captured via time series metric monitoring are shown under the metric tree. Multiple data points from the following category can be collected:
    • Oracle SQL Stats
    • Oracle Efficiency Stats
    • Oracle Wait Events Stats
    • Oracle Active Stats
    • Oracle Resource Usage Stats
    • Oracle DB Session Stats
  2. Detailed DB Monitoring (DDBM): DDBM gives you deeper insight into the performance of your Oracle database by capturing currently running queries, sessions, locks, DB I/O Stats etc. Detailed DB captured data for monitoring can be accessed by clicking on Dashboard Left Pane > DB Monitoring. Following metric groups are captured under detailed database monitoring:
    • Oracle Execution Stats
    • Oracle Service Stats
    • Oracle Temp File Stats
    • Oracle Locks Stats
    • Oracle Session Stats
    • Oracle I/O By File Stats
    • Oracle Job Stats
    • Oracle Job History Stats
    • Oracle Session Wait Stats
    • Oracle Estimate Execution Plan
    • Oracle Blocking Stats

Platform(s) Supported:

Oracle Versions Supported:

Pre-Requisites:

--- Enable Oracle Script (needed for oracle version lower than 11g)
ALTER SESSION SET "_ORACLE_SCRIPT"=true;
Create a user with the name cavisson. Replace <your_password> with a strong password.
CREATE USER cavisson IDENTIFIED BY <your_password>;

--- Grant access to the newly created user:
GRANT CONNECT TO cavisson;
GRANT SELECT ON GV_$PROCESS TO cavisson;
GRANT SELECT ON gv_$sysmetric TO cavisson;
GRANT SELECT ON sys.dba_data_files TO cavisson;
GRANT SELECT ON sys.dba_tablespaces TO cavisson;
GRANT SELECT ON sys.dba_tablespace_usage_metrics TO cavisson;
GRANT SELECT ON SYS.V_$INSTANCE TO cavisson;
GRANT SELECT ON SYS.V_$SQL TO cavisson;
GRANT SELECT ON SYS.V_$SYSMETRIC TO cavisson;
GRANT SELECT ON SYS.V_$SESSION TO cavisson;
GRANT SELECT ON SYS.V_$PARAMETER TO cavisson;
GRANT SELECT ON SYS.V_$SYSTEM_WAIT_CLASS TO cavisson;
GRANT SELECT ON SYS.V_$SQL_PLAN TO cavisson;
GRANT SELECT ON SYS.V_$SQLAREA TO cavisson;
GRANT SELECT ON SYS.V_$WAITCLASSMETRIC TO cavisson;
GRANT SELECT ON SYS.V_$STATNAME TO cavisson;
GRANT SELECT ON SYS.V_$FILESTAT TO cavisson;
GRANT SELECT ON SYS.V_$LOCK_TYPE TO cavisson;
GRANT SELECT ON SYS.V_$SESSTAT TO cavisson;
GRANT SELECT ON SYS.V_$SESSION_WAIT TO cavisson;
GRANT SELECT ON SYS.V_$IOSTAT_FILE TO cavisson;
GRANT SELECT ON SYS.GV_$ACTIVE_SERVICES TO cavisson;
GRANT SELECT ON SYS.V_$LOCK TO cavisson;
GRANT SELECT ON SYS.DBA_TEMP_FILES TO cavisson;
GRANT SELECT ON SYS.V_$LOCKED_OBJECT TO cavisson;

Note: If a user wants to configure in Windows, they have to follow the same configuration process as described above.

Configuration:

To configure monitoring of your Oracle database, fill the following fields:

Settings for Oracle Monitor Groups:

All the metrics groups have the following fields in common which are optional:

  1. Frequency: Each sample will come in selected frequency of the graph.
  2. Other Arguments: It is for enabling the debug mode or passing any extra argument in JSON.

One metric group i.e. Oracle Estimated Execution Plan has an additional field of limit in addition with frequency and other arguments.

Deploy:

You can also have the option to deploy monitors using Cavisson Agent. The following fields need to be filled to deploy:

Validation:

To validate the successful configuration of your Oracle monitors, go to the monitor's homepage and you will be able to see a corresponding section for Oracle monitor. Click on the monitor section to view a list of all the configured monitors and their corresponding metric category and their status.