Monitoring OracleDB

The OracleDB sensor is automatically deployed and installed after you install the Instana agent.

To monitor OracleDB in a Kubernetes or Red Hat OpenShift cluster, do not install Instana host agents on each node of the cluster. Install host agents on dedicated host machines.

Supported information

Supported versions

See the following supported versions for metrics and configuration data:

  • Oracle Database 11g Release 2 (11.2.0.1)
  • Oracle Database 12c Release 1 (12.1.0.1)
  • Oracle Database 12c Release 2 (12.2.0.1)
  • Oracle Database 18c (18.1.0 // 12.2.0.2)
  • Oracle Database 19c (19.1.0 // 12.2.0.3)
  • Oracle Database 21c (21.1.0)

Supported character sets

The OracleDB sensor supports all character sets supported by Oracle for fetching metrics from the Oracle database. For more information about the character sets that the Oracle database supports, see Character Sets.

Instana now supports remote monitoring for Oracle Real Application Clusters.

Supported client-side tracing

For this technology, Instana supports client-side tracing for the following languages and runtimes:

Discovery

Instana agent automatically discovers and monitors all OracleDB processes named: oracle, oracle.exe, ora_mman_*, xe_mman_*. It also extracts database SID from the process name or arguments.

Use the remote configuration option to instruct Instana agent to discover and monitor OracleDB instances that are running on other hosts where the agent cannot be installed for any reason.

Configuration

Required DB Permissions

To make the sensor connect to Oracle and monitor the metrics and configuration, you need to have the read permission for the following tables:

#Create a role
create ROLE INSTANAROLE;

#Grant privileges to this role for following tables and viewes

V_$SESSION, V_$BGPROCESS, V_$ACTIVE_SESSION_HISTORY, V_$SYSSTAT, V_$SGASTAT, V_$LIBRARYCACHE,  
V_$LATCH, V_$ROWCACHE, V_$RESOURCE_LIMIT, V_$STATNAME, V_$SESSTAT, V_$PROCESS, V_$SQLAREA,  
V_$PARAMETER, V_$SQL, V_$VERSION, V_$SYS_TIME_MODEL, V_$SYSTEM_WAIT_CLASS, V_$INSTANCE,  
V_$LOCK, DBA_HIST_SQLTEXT, DBA_HIST_SQLSTAT, DBA_HIST_SNAPSHOT, DBA_HIST_ACTIVE_SESS_HISTORY,  
DBA_USERS, DBA_OBJECTS, DBA_TABLESPACE_USAGE_METRICS, DBA_DATA_FILES  

eg. GRANT SELECT ON V_$SESSION to INSTANAROLE;

#Assign this role to a user. Same user needs to be configured in the configuration yaml (see sensor configuration section). 
GRANT INSTANAROLE to <user>;

Sensor configuration

To monitor Oracle DB, you need to inform the Agent about the credentials to access monitoring information in its Configuration file:

Local

com.instana.plugin.oracledb:
  user: '<ORCL_USER>'   # default is 'sys as sysdba'
  password: '<ORCL_PASSWORD>'
  host: '<ORCL_HOST>'   # default is 127.0.0.1
  port: '<ORCL_PORT>'   # default is 1521
  databaseSID: '<ORCL_DB_SID>' # taken from process args or 'orcl' as default
  poll_rate: 1

The poll rate defines how often the sensor polls OracleDB metrics, in seconds. The default is 1 second.

Monitoring multiple local instances

To monitor multiple local instances, you must use the following configurations in the agent configuration file:

  • user and password: Create one user with password in all DBs (the default user is sys as sysdba).
  • databaseSID: Do not specify it. The agent auto-discovers it for each process.
  • host: Do not specify it until an instance is not reachable on IP address.
  • port: Use the same specified port or the default 1521 for all databases. If you use different ports, see host agent configuration instructions.
  • poll_rate: Specify the poll rate. The default value is 1 second.

When a database SID is added to the agent configuration file, that value is used for all instances.

See the following example:

com.instana.plugin.oracledb:
  user: 'instana'
  password: 'passw0rd'
  port: '1521'

See the following example with different ports. Each instance defines its port in the MY_ORACLE_PORT environment variable.

com.instana.plugin.oracledb:
  user: 'instana'
  password: 'passw0rd'
  port:
    configuration_from:
      type: env
      env_name: MY_ORACLE_PORT
      default_value: 1521

For each Oracle instance, set 'MY_ORACLE_PORT' environment variable and assign the port number corresponding to the instance. Then, restart the OracleDB.

Remote

com.instana.plugin.oracledb:
  remote: # we support listing multiple DBs here!
    - host: '<ORCL_HOST_1>'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseSID: '<ORCL_DB_SID>' # either databaseSID or databaseServiceName is allowed
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>'
      availabilityZone: 'Remote Monitoring'
      poll_rate: 20
    - host: '<ORCL_HOST_2>'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseSID: '<INSERT_DB_SID>' # either databaseSID or databaseServiceName is allowed
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>'
      availabilityZone: 'Remote Monitoring'
      poll_rate: 5

The poll rate defines how often the sensor polls OracleDB metrics, in seconds. Default: 1 second.

On the Infrastructure map, the remote OracleDB instance appears as a separate box with the name that is specified in the availabilityZone key in the configuration.yaml file. To locate the instance, enter entity.type:oracleDB in the Instana Dynamic Focus search bar.

Custom Polling

The following table displays the metrics for which you can configure maximum three nonzero custom poll rates. Metrics with a poll rate as zero or less than zero is disabled by default. To disable any metric, remove that metric entry from the configuration file <instana-agent-dir>/etc/instana/configuration.yaml.

Name Description
TOP_ELAPSED_TIME_QUERIES List of top elapsed time queries.
SQL_ID_CONSUMING_MORE_CPU List of SQLs that consume more CPU.
USER_FOREGROUND_SESSIONS Displays active foreground sessions.
TOP_10_SQL_HIGH_IO_LAST_1_HR List of top SQL with high I/O in last 1 hour.
TOP_TEN_CPU_CONSUMING_SESSION List of top CPU consuming sessions.
TOP_CPU_CONSUMING_SESSION_LAST_10_MIN List of top CPU that consumes sessions in last 10 minutes.
CURRENT_BLOCKING_SESSIONS Used to view information about the blocking sessions.
LIBRARY_CACHE_HIT_RATIO The ratio indicates the number of pin requests that result in pin hits.
ACTIVE_SESSIONS_RUNNING_MORE_THAN_600_SECS All active sessions that are running for more than 10 minutes in the Oracle database.
ACTIVE_SESSION_HISTORY Sampled session activity in the database.
TOP_10_SQL_HIGH_IO_LAST_24_HR List of top SQL With high I/O last 24 hours.
TOP_CPU_QUERIES_LAST_24_HRS List of top CPU consuming queries in last 24 hours.
QUERIES_NOT_USING_BINDING_VARIABLES List of queries that don't use the bind variable in the code.

See the following configuration for local and remote monitoring for custom polling:

Local
com.instana.plugin.oracledb:
  user: '<ORCL_USER>'   # default is 'sys as sysdba'
  password: '<ORCL_PASSWORD>'
  host: '<ORCL_HOST>'   # default is 127.0.0.1
  port: '<ORCL_PORT>'   # default is 1521
  databaseSID: '<ORCL_DB_SID>' # taken from process args or 'orcl' as default
  poll_rate: 1
  customPolling: # Restricted to maximum 3 nonzero poll rates
    - poll_rate: 30 # in seconds
      metrics:
        - TOP_CPU_CONSUMING_SESSION_LAST_10_MIN
        - ACTIVE_SESSIONS_RUNNING_MORE_THAN_600_SECS
        - LIBRARY_CACHE_HIT_RATIO
        - CURRENT_BLOCKING_SESSIONS
    - poll_rate: 60 # in seconds
      metrics:
        - TOP_ELAPSED_TIME_QUERIES
        - TOP_TEN_CPU_CONSUMING_SESSION
        - SQL_ID_CONSUMING_MORE_CPU
        - USER_FOREGROUND_SESSIONS
        - TOP_10_SQL_HIGH_IO_LAST_1_HR
        - ACTIVE_SESSION_HISTORY
    - poll_rate: 3600 # in seconds
      metrics:
        - TOP_10_SQL_HIGH_IO_LAST_24_HR
        - TOP_CPU_QUERIES_LAST_24_HRS
        - QUERIES_NOT_USING_BINDING_VARIABLES
Remote
com.instana.plugin.oracledb:
  remote: # we support listing multiple DBs here!
    - host: '<ORCL_HOST_1>'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseSID: '<ORCL_DB_SID>' # either databaseSID or databaseServiceName is allowed
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>'
      availabilityZone: 'Remote Monitoring'
      poll_rate: 20
      customPolling: # Restricted to maximum 3 nonzero poll rates
        - poll_rate: 30 # in seconds
          metrics:
            - TOP_CPU_CONSUMING_SESSION_LAST_10_MIN
            - ACTIVE_SESSIONS_RUNNING_MORE_THAN_600_SECS
            - LIBRARY_CACHE_HIT_RATIO
            - CURRENT_BLOCKING_SESSIONS
        - poll_rate: 60 # in seconds
          metrics:
            - TOP_ELAPSED_TIME_QUERIES
            - TOP_TEN_CPU_CONSUMING_SESSION
            - SQL_ID_CONSUMING_MORE_CPU
            - USER_FOREGROUND_SESSIONS
            - TOP_10_SQL_HIGH_IO_LAST_1_HR
            - ACTIVE_SESSION_HISTORY
        - poll_rate: 3600 # in seconds
          metrics:
            - TOP_10_SQL_HIGH_IO_LAST_24_HR
            - TOP_CPU_QUERIES_LAST_24_HRS
            - QUERIES_NOT_USING_BINDING_VARIABLES

Metrics collection

To view the metrics, select Infrastructure in the sidebar of the Instana User interface, click a specific monitored host, and then you can see a host dashboard with all the collected metrics and monitored processes.

Configuration data

  • Version
  • SID
  • Service names
  • Start time
  • CPU count
  • Max sessions
  • DB block size

Performance metrics

Metric Description Granularity
Running Process Count Number of processes that are running. 1 second
Process Limit Usage Usage of the process resource. Process is displayed in percentage. 1 second
Active Sessions (More Than 10 Mins) All active sessions in the Oracle database for more than 10 minutes. 30 seconds
Process Max Utilization Maximum consumption of process after the last instance start-up. 1 second
Process Current Utilization Number of processes that are currently used. 1 second
Process Initial Allocation Initial allocation of the process. This allocation is equal to the value that is specified for the process in the initialization parameter file. 1 second
Process Limit Value Maximum limit value of a process. The value of the process must remain within this limit. 1 second
Process Limit Usage The percentage of all the current processes to the limit set. 1 second
DB Time per Second Amount of elapsed time that is spent on performing the database user-level calls. 1 second
DB CPU Time Amount of CPU time that is spent on performing the database user-level calls. 1 second
SQL Execute Time Amount of elapsed time during which the SQL query statements are executing. 1 second
Parse Time Amount of elapsed time that is spent parsing the SQL query statements. It includes both the soft and hard parse time. 1 second
Database CPU Time Ratio Amount of CPU used in the database by the amount of total database time. 1 second
Wait Time per Second Wait time per second for User I/O, Other, System I/O, Concurrency, Scheduler, Application, Commit, Configuration, Administrative, Network, Queueing. 1 second
Library Cache Hit Ratio (Pin Hits) The ratio indicates the number of pin requests that result in pin hits. 30 seconds
SGA Total Memory Total memory in System Global Area (SGA) in MB. 1 second
SGA Used Memory Memory that is used by SGA in MB. 1 second
SGA Free Memory Free memory in SGA in MB. 1 second
SGA Pool Name Pool name of the System Global Area (SGA). 1 second
SGA Pool Total Memory Total memory in SGA Pool in MB. 1 second
SGA Pool Used Memory Used memory in SGA Pool in MB. 1 second
SQL Execution Count Total number of SQL queries that are executed. 1 second
SQL Execution Average Time Average SQL execution time. 1 second
SQL Parse Count Hard and total parse count. 1 second
SQL Parse Ratios Ratio of SQL executions performed with soft parse and without any parses. 1 second
User Calls Number of calls, commits, and rollbacks. 1 second
Buffer Cache Hit Ratio Percentage of pages that are found in the buffer cache without having to read from disk. 1 second
Physical and Logical Session Reads Number of physical and logical reads. 1 second
Sessions Number of user sessions (active and inactive), and the number of background sessions. 1 second
Tablespace Usage Tablespace (permanent, temporary, and undo) usage in GB, tablespace usage in percentage, maximum size of the tablespace and auto-extensible flag. 1 second
Top CPU Queries (Last 24 Hr) List of top CPU queries in last 24 hours with CPU time in milliseconds, number of disk reads, and total amount of time during which the query is executed. 1 hour
Queries Not Using Bind Variable List of queries that do not use bind variables in the code with number of query copies, number of query executions, and total memory consumed by SQL query in MB. 1 hour
Top Elapsed Time Queries List of top elapsed time queries with SQL ID, SQL query, and execution time in minutes. 60 seconds
Active Session History (Last 1 Hr) Sampled session activity in the database for the last one hour with SQL ID, load percentage, and session count. 60 seconds
Top Ten CPU Consuming Sessions List of top ten CPU-consuming sessions with session ID, serial number, CPU time in minutes, and operating system program name. 60 seconds
Top CPU Consuming Sessions (Last 10 Min) List of top CPU-consuming sessions in the last 10 minutes with session ID, serial number, and total amount of time during which the query is executed. 30 seconds
Top Ten SQL With High IO (Last 1 Hr) List of top ten SQL queries with high I/O in the last 1 hour with SQL ID, username, and total wait time in milliseconds. 60 seconds
Current Blocking Sessions Information about the blocking sessions. 30 seconds
Foreground Sessions Active foreground sessions. 60 seconds
SQL Consuming More CPU List of SQL queries that use more CPU. 60 seconds
Top Ten SQL With High IO Last 24 Hr List of top ten SQL queries with high I/O in the last 24 hours. 1 hour

Health Signatures

Each sensor has a curated knowledge base of health signatures that are evaluated continuously against the incoming metrics and are used to raise issues or incidents that impact the user.

Built-in events trigger issues or incidents based on failing health signatures on entities, and custom events trigger issues or incidents based on the thresholds of an individual metric of any entity.

For information about built-events for the OracleDB sensor, see the Built-in events reference.