OracleDb
Overview
This plugin collects runtime metrics from OracleDb database. It gathers information about database usage and allows defining custom metrics.
This plugin is currently available for x86_64 Linux and Windows platforms.
Setup
The oracledb
plugin is included with the SolarWinds Snap Agent by default, please follow the directions below to enable it for a agent instance.
Prerequisites
This plugin requires Oracle Instant Client to be installed and added to the PATH
environment variable. Follow the Oracle Instant Client installation instructions for your platform to have it properly set up.
The plugin also requires the user to be able to read various statistic tables in OracleDb. i.e. for a solarwinds read-only user, you need to grant the following privileges:
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
The agent provides an example configuration file to help you get started quickly. It defines the plugin and task file to be loaded by the agent, but requires you to provide the correct settings for your database. To enable the plugin:
- Make a copy of the oracledb example configuration file
oracledb.yaml.example
, renaming it tooracledb.yaml
:
On Windows, using Explorer or PowerShell:
Copy> copy "C:\ProgramData\SolarWinds\Snap\plugins.d\oracledb.yaml.example" "C:\ProgramData\SolarWinds\Snap\plugins.d\oracledb.yaml"
On Linux using command line:
Copy$ sudo cp /opt/SolarWinds/Snap/etc/plugins.d/oracledb.yaml.example /opt/SolarWinds/Snap/etc/plugins.d/oracledb.yaml
- Update the
oracledb.yaml
configuration file with settings specific to your database instance, for example:
Copycollector:
oracledb:
all:
oracle_connection_strings: |
solarwinds/password123@localhost:32118/XE
#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
load:
plugin: snap-plugin-collector-oracledb
task: task-oracledb.yaml
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 intask-oracledb.yaml
file by creatingtags
section. Refer to default task file for example.
- Restart the agent:
On Windows command line:
Copy> net stop swisnapd
> net start swisnapdOn Linux command line:
Copy$ sudo service swisnapd restart
- Enable the OracleDb plugin in the AppOptics UI
On the Integrations Page you will see the OracleDb plugin available if the previous steps were successful. If you do not see the plugin, see Troubleshooting Linux.
Select the OracleDb plugin to open the configuration menu in the UI, and enable the plugin.
You should soon see the
oracledb
metrics reported to your 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 oracledb
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
Optinal metrics can be activated by editing the task yaml. For more information please read the SolarWinds Snap Agent configuration article.
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 |
When the APM Integrated Experience is enabled, AppOptics shares a common navigation and enhanced feature set with the other integrated experiences' products. How you navigate AppOptics and access its features may vary from these instructions. For more information, go to the APM Integrated Experience documentation.
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.