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:
-
Make a copy of the oracledb example configuration file
task-oracledb.yaml.example, renaming it totask-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 -
Edit the
task-oracledb.yamltask manifest file to match your custom settings. There must be at least one connection string defined inoracle_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-appopticsoracle_connection_stringsspecifies a list of connection strings to your Oracle databases.custom_sql_queriesallows 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.secfrom example will be converted tooracledb.custom.disk.sort.per.secmetric. Tags for custom metrics can be added intask-oracledb.yamlfile by creatingtagssection. Refer to default task file for example.
-
Restart the agent.
On Windows, run the following in the command line:
net stop swisnapd net start swisnapdOn Linux, run the following in the command line:
sudo service swisnapd restart -
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.