Documentation forAppOptics

Oracle

Overview

This plugin collects runtime metrics from an Oracle database. It gathers information about database usage and allows defining custom metrics.

Setup

This integration is included as part of a SWI collector. The SWI collector bundles several collectors into one integrated binary.

The oracledb plugin is included with the SolarWinds Snap Agent by default, follow the directions below to enable it for an agent instance.

With Snap Agent version 4.5.0 and later, this plugin was upgraded to use the v2 plugins framework. If you are running v1 of the collector, see AppOptics Snap Agent 4.4.0 documentation.

To migrate to the v2 plugins framework, update Snap Agent to the latest version and see Migrate from v1 to v2 appoptics publisher plugins.

Prerequisites

The plugin also requires the user to be able to read several statistic tables in Oracle. Grant the following permissions to a solarwinds read-only user:

grant select on v_$instance to solarwinds;
grant select on v_$database to solarwinds;
grant select on DBA_FEATURE_USAGE_STATISTICS to solarwinds;
grant select on V_$ASM_DISK to solarwinds;
grant select on V_$SYSSTAT to solarwinds;
grant select on V_$SESSION to solarwinds;
grant select on V_$SESSION_WAIT to solarwinds;
grant select on GV_$LOCK to solarwinds;
grant select on v_$parameter to solarwinds;
grant select on v_$timer to solarwinds;
grant select on v_$osstat to solarwinds;
grant select on v_$system_event to solarwinds;
grant select on v_$sga_dynamic_components to solarwinds;
grant select on v_$process to solarwinds;
grant select on v_$librarycache to solarwinds;
grant select on v_$sgastat to solarwinds;
grant select on v_$metric to solarwinds;
grant select on v_$process_memory to solarwinds;
grant select on v_$temp_space_header to solarwinds;
grant select on dba_tablespace_usage_metrics to solarwinds;
grant select on dba_data_files to solarwinds;
grant select on dba_segments to solarwinds;

Configuration

An example task manifest file is included with the Snap Agent to help you start collecting Oracle metrics. It includes a pre-defined task configuration, which you can modify to match your custom settings. To enable the plugin:

  1. Make a copy of the oracledb example configuration file task-oracledb.yaml.example, renaming it to task-oracledb.yaml:

    On Windows, using Explorer or PowerShell:

    copy "C:\ProgramData\SolarWinds\Snap\tasks-autoload.d\task-oracledb.yaml.example" "C:\ProgramData\SolarWinds\Snap\tasks-autoload.d\task-oracledb.yaml"

    On Linux using command line:

    sudo cp -p /opt/SolarWinds/Snap/etc/tasks-autoload.d/task-oracledb.yaml.example /opt/SolarWinds/Snap/etc/tasks-autoload.d/task-oracledb.yaml
  2. Edit the task-oracledb.yaml task manifest file to match your custom settings. There must be at least one connection string defined in oracle_connection_strings.

    ---
    version: 2
    schedule:
      # Run every minute
      type: cron
      interval: "0 * * * * *"
    
    
    plugins:
      - plugin_name: oracledb
    
        config:
          ## Set timeout duration for collection of metrics - defaults to 40s
          #collect_timeout: 40s
    
          ## Set list of connection strings to your Oracle database. The template is oracle://<username>:<password>@<ip>:<port>/<SID or SERVICE_NAME>
          #connection_strings:
          #  - oracle://solarwinds:password123@localhsot:32118/XE
    
          ## Set list of custom sql queries and metrics. The result of sql query must be a single row of numeric values,
          ## that will be converted to respective metric. Every custom metric is automatically prefixed,
          ## i.e. disk.sort.per.sec from example will be converted to oracledb.custom.disk.sort.per.sec metric.
          #custom_sql_queries:
          #  - sql: select distinct value from v$metric where metric_name='Disk Sort Per Sec' and  ROWNUM= 1
          #    metrics:
          #      - disk.sort.per.sec
          #  - sql: select distinct v1.value, v2.value from v$metric v1, v$metric v2 where v1.metric_name='Logons Per Sec' and v2.metric_name='Current Logons Count' and ROWNUM= 1
          #    metrics:
          #      - logons.per.sec
          #      - current.logons.count
    
        metrics:
          ## Default metrics. Each metric is prefixed dynamically with db_host, /oracledb/[db_host]/...
          - /oracledb/[db_host]/buffer/cache/hit_ratio
          - /oracledb/[db_host]/buffer/cache/used
          - /oracledb/[db_host]/cpu/utilization_by_oracle
          - /oracledb/[db_host]/cpu/utilization
          - /oracledb/[db_host]/cursor/cache/hit_ratio
          - /oracledb/[db_host]/disk/read/total
          - /oracledb/[db_host]/disk/read/time/single_block
          - /oracledb/[db_host]/disk/read/time/multi_block
          - /oracledb/[db_host]/disk/read/time/commits
          - /oracledb/[db_host]/disk/write/total
          - /oracledb/[db_host]/disk/used
          - /oracledb/[db_host]/disk/user/[user]/used
          - /oracledb/[db_host]/distinct/machines
          - /oracledb/[db_host]/distinct/users
          - /oracledb/[db_host]/library/cache/hit_ratio
          - /oracledb/[db_host]/logical/io/total
          - /oracledb/[db_host]/net/response_time
          - /oracledb/[db_host]/net/sql/sent
          - /oracledb/[db_host]/net/sql/received
          - /oracledb/[db_host]/pga/cache/used
          - /oracledb/[db_host]/sessions/active
          - /oracledb/[db_host]/sessions/blocked
          - /oracledb/[db_host]/sessions/total
          - /oracledb/[db_host]/shared_pool/used
          - /oracledb/[db_host]/shared_pool/free
          - /oracledb/[db_host]/tablespace/*/size
          - /oracledb/[db_host]/user/commits
          - /oracledb/[db_host]/user/rollbacks
          #- /oracledb/[db_host]/database_status
          #- /oracledb/[db_host]/instance_status
          #- /oracledb/[db_host]/net/response_time
          #- /oracledb/[db_host]/net/sql/sent
          #- /oracledb/[db_host]/net/sql/received
          #- /oracledb/[db_host]/enqueue/timeouts_per_sec
          #- /oracledb/[db_host]/sessions/active
          #- /oracledb/[db_host]/sessions/blocked
          #- /oracledb/[db_host]/user/transactions
          #- /oracledb/[db_host]/user/commits
          #- /oracledb/[db_host]/user/rollbacks
          #- /oracledb/[db_host]/wait_time_ratio
          #- /oracledb/[db_host]/disk/used
          #- /oracledb/[db_host]/disk/io/total
          #- /oracledb/[db_host]/disk/user/[user]/used
          #- /oracledb/[db_host]/disk/read/total
          #- /oracledb/[db_host]/disk/read/time/single_block
          #- /oracledb/[db_host]/disk/read/time/multi_block
          #- /oracledb/[db_host]/disk/read/time/commits
          #- /oracledb/[db_host]/disk/write/total
          #- /oracledb/[db_host]/cpu/utilization
          #- /oracledb/[db_host]/cpu/utilization_by_oracle
          #- /oracledb/[db_host]/cursor/cache/hit_ratio
          #- /oracledb/[db_host]/buffer/cache/hit_ratio
          #- /oracledb/[db_host]/buffer/cache/used
          #- /oracledb/[db_host]/library/cache/hit_ratio
          #- /oracledb/[db_host]/lms/service_time
          #- /oracledb/[db_host]/logical/io/total
          #- /oracledb/[db_host]/pga/cache/used
          #- /oracledb/[db_host]/shared_pool/used
          #- /oracledb/[db_host]/shared_pool/free 
          #- /oracledb/[db_host]/shared_pool/total
          #- /oracledb/[db_host]/shared_pool/free_percent
          #- /oracledb/[db_host]/cpu/count
          #- /oracledb/[db_host]/distinct/machines
          #- /oracledb/[db_host]/distinct/users
          #- /oracledb/[db_host]/logons/current
          #- /oracledb/[db_host]/logons/total
          #- /oracledb/[db_host]/sessions/total
          #- /oracledb/[db_host]/sessions/limit_percent
          #- /oracledb/[db_host]/tablespace/[tablespace]/size
          #- /oracledb/[db_host]/tablespace/[tablespace]/temp_space_used
          #- /oracledb/[db_host]/tablespace/[tablespace]/used_space
          #- /oracledb/[db_host]/tablespace/[tablespace]/used_percent
          #- /oracledb/[db_host]/sorts/per_user_call
          #- /oracledb/[db_host]/sorts/disk
          #- /oracledb/[db_host]/sorts/memory_ratio
          #- /oracledb/[db_host]/sorts/rows_per_sort
          #- /oracledb/[db_host]/pga/process/[process]/memory_allocated
          #- /oracledb/[db_host]/pga/process/[process]/memory_used
          #- /oracledb/[db_host]/pga/process/[process]/memory_total
          #- /oracledb/[db_host]/asm/summary/reads
          #- /oracledb/[db_host]/asm/summary/read/bytes
          #- /oracledb/[db_host]/asm/summary/read/time
          #- /oracledb/[db_host]/asm/summary/writes
          #- /oracledb/[db_host]/asm/summary/write/bytes
          #- /oracledb/[db_host]/asm/summary/write/time
          #- /oracledb/[db_host]/cell/single_block_physical_read_latency
          #- /oracledb/[db_host]/cell/multiblock_physical_read_latency
          #- /oracledb/[db_host]/cell/smart_table_scan_latency
          #- /oracledb/[db_host]/io_saved_by_cell_offload
          #- /oracledb/[db_host]/flash_cache/hit_ratio
          #- /oracledb/[db_host]/smart_scan_efficiency
          #- /oracledb/[db_host]/gc/blocks_corrupted
          #- /oracledb/[db_host]/gc/blocks_lost
          #- /oracledb/[db_host]/gc/cr/avg_block_build_time
          #- /oracledb/[db_host]/gc/cr/avg_block_flush_time
          #- /oracledb/[db_host]/gc/cr/avg_block_receive_time
          #- /oracledb/[db_host]/gc/cr/avg_block_send_time
          #- /oracledb/[db_host]/gc/cr/block_received
          #- /oracledb/[db_host]/gc/current/avg_block_flush_time
          #- /oracledb/[db_host]/gc/current/avg_block_pin_time
          #- /oracledb/[db_host]/gc/current/avg_block_receive_time
          #- /oracledb/[db_host]/gc/current/avg_block_send_time
          #- /oracledb/[db_host]/gc/current/block_service_time
          
        publish:
          - plugin_name: publisher-appoptics
    • oracle_connection_strings specifies a list of connection strings to your Oracle databases.
    • custom_sql_queries allows to define a list of custom sql queries and metrics. The result of sql query must be a single row of numeric values, that will be converted to respective metric. Every custom metric is automatically prefixed, i.e. disk.sort.per.sec from example will be converted to oracledb.custom.disk.sort.per.sec metric. Tags for custom metrics can be added in task-oracledb.yaml file by creating tags section. Refer to default task file for example.
  3. Restart the agent.

    On Windows, run the following in the command line:

    net stop swisnapd
    net start swisnapd

    On Linux, run the following in the command line:

    sudo service swisnapd restart
  4. Enable the OracleDb plugin in AppOptics.

If you completed these steps successfully, after a few minutes the OracleDB plugin will be listed on the Integrations page. If the plugin is not included on the page, see Troubleshooting Linux.

Select the OracleDB plugin to open the configuration menu in the user interface, and then enable the plugin. When completed, the oracledb metrics are available on the dashboard.

Metrics and Tags

All of the metrics can be enabled or disabled in task-oracledb.yaml. The tables below outline the default set of metrics collected by the Oracle plugin along with the optional metrics available.

All metrics including custom ones are float64 values.

Default Metrics

Namespace Description
oracledb.buffer.cache.hit_ratio the rate at which this database finds the data blocks it needs in memory rather than having to read from disk
oracledb.buffer.cache.used memory allocated to all Oracle buffer caches
oracledb.cpu.utilization_by_oracle percent of CPU being utilized by the database instance, which is a subset of the CPU utilized by the entire system
oracledb.cpu.utilization percent of CPU being utilized by the entire system
oracledb.cursor.cache.hit_ratio cursor cache hit ratio
oracledb.disk.read.time.commits average number of milliseconds waiting for the log file sync event indicating commit times for this database
oracledb.disk.read.time.multi_block average number of milliseconds waiting for the db file scattered read event in this database
oracledb.disk.read.time.single_block average number of milliseconds waiting for the db file sequential read event in this database
oracledb.disk.read.total number of bytes read from disk by database
oracledb.disk.write.total number of bytes written to disk by database
oracledb.disk.used space consumed by database on disk
oracledb.disk.user.used space consumed by database user on disk
oracledb.distinct.machines number of distinct client machines connected to this instance (even if the connection is idle)
oracledb.distinct.users number of distinct users connected to this instance (even if the connection is idle)
oracledb.library.cache.hit_ratio library cache hit ratio
oracledb.logical.io.total number of memory reads (session logical reads statistic from v$sysstat) per second for this database
oracledb.net.response_time round-trip time when running select 1 from dual (includes network time but not connect time) on this database
oracledb.net.sql.sent SQL*Net bytes sent to the clients
oracledb.net.sql.received SQL*Net bytes received from the clients
oracledb.pga.cache.used memory allocated to the PGA
oracledb.sessions.active number of sessions actively performing work or waiting for a resource (excludes idle sessions) for this database
oracledb.sessions.blocked number of sessions that are blocked because another session is using a needed resource on this database
oracledb.sessions.total number of sessions connected to this instance (even if the connection is idle)
oracledb.shared_pool.free free memory in Oracle shared pool
oracledb.shared_pool.total total memory available in Oracle shared pool
oracledb.tablespace.size total size of the tablespace. Tablespace metric is additionally tagged with tablespace name
oracledb.user.commits number of user commits
oracledb.user.rollbacks number of user rollbacks

Default Metric Tags

All OracleDb metrics are tagged with db_host, db_id, db_version and db_instance_name.

Optional Metrics

Optional metrics can be activated by editing task-oracledb.yaml. For more information see SolarWinds Snap Agent Task File.

Namespace Description
oracledb.asm.summary.read.bytes total number of kilobytes written to disk
oracledb.asm.summary.read.time average I/O time per read request over all disks
oracledb.asm.summary.reads total number of all I/O read requests
oracledb.asm.summary.write.bytes total number of kilobytes read from disk
oracledb.asm.summary.write.time average I/O time per write request over all disks
oracledb.asm.summary.writes total number of all I/O write requests
oracledb.cell.multiblock_physical_read_latency average number of milliseconds waiting for the cell multiblock physical read Exadata event in this database
oracledb.cell.single_block_physical_read_latency average number of milliseconds waiting for the cell single block physical read Exadata event in this database
oracledb.cell.smart_table_scan_latency average number of milliseconds waiting for the cell smart table scan Exadata event in this database
oracledb.cpu.count number of cores used by the instance
oracledb.database_status status of database (1 - active, 2 - supsended, 3 - instance recovery)
oracledb.disk.io.total number of bytes read and written to disk
oracledb.enqueue.timeouts_per_sec total number of table and row locks (acquired and converted) per second that time out before they could complete
oracledb.flash_cache.hit_ratio the amount of I/O operations satisfied by the Exadata Smart Flash Cache within the Storage Servers
oracledb.gc.blocks_corrupted number of blocks that encountered a corruption or checksum failure during interconnect
oracledb.gc.blocks_lost total numbe of global cache lost blocks
oracledb.gc.cr.avg_block_receive_time average round-trip time or latency for all requests for a Consistent Read (CR) from this instance across the RAC Interconnect. If the transfer time is too high, or if one of the nodes in the cluster shows excessive transfer times, the RAC interconnect should be checked (using system level commands) to verify that it is functioning correctly. Calculation in (ms) is as follows: (gc_current_block_receive_time)/(gc_cr_blocks_received) * 10
oracledb.gc.cr.avg_block_build_time average global cache cr block build times being experienced from this instance across the RAC Interconnect. The average time to build a consistent read block is calculated as follows: (gc cr block build time * 10)/(gc cr blocks served)
oracledb.gc.cr.avg_block_send_time average global cache cr block send times being experienced from this instance across the RAC Interconnect. The average time to send a complete consistent read block is calculated as follows: (gc cr block send time * 10)/(gc cr blocks served)
oracledb.gc.cr.avg_block_flush_time average global cache cr block flush times being experienced from this instance across the RAC Interconnect. The average time spent waiting for a redo log flush is calculated as follows: (gc cr block flush time * 10)/(gc cr blocks served)
oracledb.gc.cr.block_received total number of blocks received
oracledb.gc.current.avg_block_receive_time average round-trip time or latency for all processing requests for Current Mode Block from this instance across the RAC Interconnect. Calculation in (ms) is as follows: (gc_current_block_receive_time)/(gc_current_block_receive_time)*10
oracledb.gc.current.block_service_time average Current Block Service Time (ms) is calculated as follows: (gc current block pin time)+(gc current block flush time)+(gc current block send time)/(gc current blocks served)* 10
oracledb.gc.current.avg_block_pin_time average current block pin times being experienced from this instance across the RAC Interconnect. Calculated as follows: (gc_current_block_pin_time * 10) / gc_current_blocks_served as average_pin_time
oracledb.gc.current.avg_block_send_time average current block send times being experienced from this instance across the RAC Interconnect. Calculated as follows: (gc_current_block_send_time * 10) / gc_current_blocks_served as average_send_time
oracledb.gc.current.avg_block_flush_time average current block flush times being experienced from this instance across the RAC Interconnect. Calculated as follows: (gc_current_block_flush_time * 10) / (gc_current_blocks_served)
oracledb.instance_status status of database instance (1 - started, 2 - mounted, 3 - open, 4 - open migrate)
oracledb.io_saved_by_cell_offload the amount of physical I/O that has been saved by offloading it to the Exadata storage servers
oracledb.lms.service_time average LMS Service Time measures overall latency for a Consistent Read. This includes queue, build, flush and send time. The Lock Manager Server (LMS) process, also called the GCS (Global Cache Services) process, is used to transport blocks across the nodes for cache-fusion requests. If there is a Consistent Read request, the LMS process rolls back the block, makes a Consistent Read image of the block and then ships this block across the HSI (High Speed Interconnect) to the process requesting from a remote node. LMS must also check constantly with the LMD background process (or GES process) to get the lock requests placed by the LMD proces
oracledb.logons.current current number of sessions in v$session
oracledb.logons.total number of logons since database started
oracledb.pga.process.memory_allocated PGA memory allocated by category
oracledb.pga.process.memory_used PGA memory used by category
oracledb.pga.process.memory_total PGA maximum memory ever allocated by category
oracledb.sessions.limit_percent utilization of number of sessions allowed by database
oracledb.shared_pool.used memory allocated to the Oracle shared pool
oracledb.shared_pool.free_percent ratio of free memory in Oracle shared pool
oracledb.smart_scan_efficiency when the storage cells process full table scans they can apply columns filters and perform column projection so that not all blocks are returned to the database server, only the ones that are needed. This metric shows an efficiency of how well that is occurring. The data comes from v$sysstat and looking at the cell IO uncompressed bytes (a), cell physical IO bytes saved by storage index (b) and cell physical IO interconnect bytes returned by smart scan (c) metrics. It then applies the formula of 100 * (a + b) / c to get a percentage of data saved by the smart scans
oracledb.sorts.disk total disk sorts (v$metric)
oracledb.sorts.memory_ratio memory sorts ratio (v$metric)
oracledb.sorts.per_user_call total sorts per user call
oracledb.sorts.rows_per_sort rows per sort (v$metric)
oracledb.tablespace.temp_space_used current space used from v$temp_space_header
oracledb.tablespace.used_space total space consumed by the tablespace
oracledb.tablespace.used_percent percentage of used space, as a function of the maximum possible tablespace size
oracledb.user.transactions number of transactions (user commits + user rollbacks statistics from v$sysstat)
oracledb.wait_time_ratio database wait time ratio from v$metric

Navigation Notice: When the APM Integrated Experience is enabled, AppOptics shares a common navigation and enhanced feature set with other integrated experience products. How you navigate AppOptics and access its features may vary from these instructions.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.