Monitoring Oracle Real Application Clusters

After you install the Instana host agent, Oracle Real Application Clusters (RAC) sensor is automatically installed, but you need to configure Oracle RAC sensor as outlined in Configuring section. Then, you can view metrics that are related to Oracle RAC in the Instana UI.

Supported information
- Supported operating systems
- Supported versions
Discovering Oracle RAC
Configuring
- Granting database permissions
- Configuring Oracle RAC sensor
  - Enabling Oracle RAC monitoring
  - Configuring custom polling
Viewing metrics

Supported information

Supported operating systems

The Oracle RAC sensor supports the operating system on all the hosts where Instana agent runs. You can refer to the Supported operating systems section of each host agent, such as Supported operating systems for Unix.

Supported versions

See the following supported versions for metrics and configuration data:

Oracle Database 19c

Only remote monitoring is supported for Oracle RAC sensor.

Discovering Oracle RAC

Use the remote configuration option to discover and monitor Oracle RAC databases that run on hosts where the Instana agent cannot be installed.

Configuring

Granting database permissions

To connect the sensor to Oracle RAC 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 views

GV_$SESSION, GV_$BGPROCESS, GV_$ACTIVE_SESSION_HISTORY, GV_$SYSSTAT, GV_$SGASTAT, GV_$LIBRARYCACHE, GV_$LATCH, GV_$ROWCACHE, GV_$RESOURCE_LIMIT, GV_$STATNAME, GV_$SESSTAT, GV_$PROCESS, GV_$SQLAREA, GV_$PARAMETER, GV_$SQL, V_$VERSION, GV_$SYS_TIME_MODEL, GV_$SYSTEM_WAIT_CLASS, GV_$INSTANCE,  GV_$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, DBA_TEMP_FILES

eg. GRANT SELECT ON GV_$SESSION to INSTANAROLE;

#Assign this role to a user. Configure the same user in the configuration yaml file. For more information, see the [sensor configuration](#sensor-configuration) section.
GRANT INSTANAROLE to <user>;

Configuring Oracle RAC sensor

To monitor Oracle RAC, you need to inform the host agent about the credentials to access monitoring information by using the agent configuration file:

Enabling Oracle RAC monitoring

To enable Oracle RAC monitoring, you must set the flag enableRacMonitoring to true.

com.instana.plugin.oracledb:
  remote: # we support listing multiple Oracle RACs here!
    - host: '<ORCL_HOST>' # SCAN_HOSTNAME or SCAN_IP'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>' # for Oracle RAC, SID is not required only ServiceName
      availabilityZone: 'Oracle-RAC-AZ' # default is 'Oracle-RAC-AZ
      poll_rate: 1
      enableRacMonitoring: true # default is false. For Oracle RAC set this to true.

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

On the Infrastructure map, the remote Oracle RAC 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.

Configuring custom polling

The following table displays the metrics for which you can configure a maximum of 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 <instana-agent-dir>/etc/instana/configuration.yamlconfiguration file.

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 ten SQL with high I/O in the 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 the 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 ten SQL with high I/O in the last 24 hours.
TOP_CPU_QUERIES_LAST_24_HRS	List of top CPU consuming queries in the 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 custom polling:

com.instana.plugin.oracledb:
  remote: # Instana supports listing of multiple Oracle RACs.
    - host: '<ORCL_HOST>' # SCAN_HOSTNAME or SCAN_IP'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>' # for Oracle RAC, SID is not required, requires only ServiceName
      availabilityZone: 'Oracle-RAC-AZ' # default is 'Oracle-RAC-AZ
      poll_rate: 1
      enableRacMonitoring: true
      customPolling:
        - poll_rate: 30
          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
          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
          metrics:
            - TOP_10_SQL_HIGH_IO_LAST_24_HR
            - TOP_CPU_QUERIES_LAST_24_HRS
            - QUERIES_NOT_USING_BINDING_VARIABLES

Viewing metrics

To view the metrics, complete the following steps:

Select Infrastructure in the sidebar of the Instana UI.
Click a specific monitored host. You can see a host dashboard with all the collected metrics and monitored processes.

The Oracle sensor monitors and collects the following data:

Configuration data (#configuration-data)
Performance metrics (#performance-metrics)
Health signatures (#health-signatures)

Configuration data

Oracle sensor collects the following configuration data:

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

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 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, and 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 that are 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 the last 24 hours with CPU time in milliseconds, number of disk reads, and total duration during which the query is executed.	1 hour
Queries Not Using Bind Variable	List of queries that do not use the bind variable in the code with number of query copies, number of query executions, and total memory that is consumed by SQL queries 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 duration 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

For each sensor, a curated knowledge base of health signatures is evaluated continuously against the incoming metrics and is used to raise issues or incidents that impact the user.