summaryrefslogtreecommitdiffstats
path: root/collectors/python.d.plugin/oracledb
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--collectors/python.d.plugin/oracledb/Makefile.inc13
-rw-r--r--collectors/python.d.plugin/oracledb/README.md97
-rw-r--r--collectors/python.d.plugin/oracledb/oracledb.chart.py831
-rw-r--r--collectors/python.d.plugin/oracledb/oracledb.conf84
4 files changed, 1025 insertions, 0 deletions
diff --git a/collectors/python.d.plugin/oracledb/Makefile.inc b/collectors/python.d.plugin/oracledb/Makefile.inc
new file mode 100644
index 0000000..ea3a824
--- /dev/null
+++ b/collectors/python.d.plugin/oracledb/Makefile.inc
@@ -0,0 +1,13 @@
+# SPDX-License-Identifier: GPL-3.0-or-later
+
+# THIS IS NOT A COMPLETE Makefile
+# IT IS INCLUDED BY ITS PARENT'S Makefile.am
+# IT IS REQUIRED TO REFERENCE ALL FILES RELATIVE TO THE PARENT
+
+# install these files
+dist_python_DATA += oracledb/oracledb.chart.py
+dist_pythonconfig_DATA += oracledb/oracledb.conf
+
+# do not install these files, but include them in the distribution
+dist_noinst_DATA += oracledb/README.md oracledb/Makefile.inc
+
diff --git a/collectors/python.d.plugin/oracledb/README.md b/collectors/python.d.plugin/oracledb/README.md
new file mode 100644
index 0000000..88024f8
--- /dev/null
+++ b/collectors/python.d.plugin/oracledb/README.md
@@ -0,0 +1,97 @@
+<!--
+title: "OracleDB monitoring with Netdata"
+custom_edit_url: https://github.com/netdata/netdata/edit/master/collectors/python.d.plugin/oracledb/README.md
+sidebar_label: "OracleDB"
+-->
+
+# OracleDB monitoring with Netdata
+
+Monitors the performance and health metrics of the Oracle database.
+
+## Requirements
+
+- `cx_Oracle` package.
+- Oracle Client (using `cx_Oracle` requires Oracle Client libraries to be installed).
+
+It produces following charts:
+
+- session activity
+ - Session Count
+ - Session Limit Usage
+ - Logons
+- disk activity
+ - Physical Disk Reads/Writes
+ - Sorts On Disk
+ - Full Table Scans
+- database and buffer activity
+ - Database Wait Time Ratio
+ - Shared Pool Free Memory
+ - In-Memory Sorts Ratio
+ - SQL Service Response Time
+ - User Rollbacks
+ - Enqueue Timeouts
+- cache
+ - Cache Hit Ratio
+ - Global Cache Blocks Events
+- activities
+ - Activities
+- wait time
+ - Wait Time
+- tablespace
+ - Size
+ - Usage
+ - Usage In Percent
+- allocated space
+ - Size
+ - Usage
+ - Usage In Percent
+
+## prerequisite
+
+To use the Oracle module do the following:
+
+1. Install `cx_Oracle` package ([link](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html)).
+
+2. Install Oracle Client libraries
+ ([link](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#install-oracle-client)).
+
+3. Create a read-only `netdata` user with proper access to your Oracle Database Server.
+
+Connect to your Oracle database with an administrative user and execute:
+
+```
+ALTER SESSION SET "_ORACLE_SCRIPT"=true;
+
+CREATE USER netdata IDENTIFIED BY <PASSWORD>;
+
+GRANT CONNECT TO netdata;
+GRANT SELECT_CATALOG_ROLE TO netdata;
+```
+
+## Configuration
+
+Edit the `python.d/oracledb.conf` configuration file using `edit-config` from the Netdata [config
+directory](/docs/configure/nodes.md), which is typically at `/etc/netdata`.
+
+```bash
+cd /etc/netdata # Replace this path with your Netdata config directory, if different
+sudo ./edit-config python.d/oracledb.conf
+```
+
+```yaml
+local:
+ user: 'netdata'
+ password: 'secret'
+ server: 'localhost:1521'
+ service: 'XE'
+
+remote:
+ user: 'netdata'
+ password: 'secret'
+ server: '10.0.0.1:1521'
+ service: 'XE'
+```
+
+All parameters are required. Without them module will fail to start.
+
+
diff --git a/collectors/python.d.plugin/oracledb/oracledb.chart.py b/collectors/python.d.plugin/oracledb/oracledb.chart.py
new file mode 100644
index 0000000..28ef8db
--- /dev/null
+++ b/collectors/python.d.plugin/oracledb/oracledb.chart.py
@@ -0,0 +1,831 @@
+# -*- coding: utf-8 -*-
+# Description: oracledb netdata python.d module
+# Author: ilyam8 (Ilya Mashchenko)
+# SPDX-License-Identifier: GPL-3.0-or-later
+
+from copy import deepcopy
+
+from bases.FrameworkServices.SimpleService import SimpleService
+
+try:
+ import cx_Oracle
+
+ HAS_ORACLE = True
+except ImportError:
+ HAS_ORACLE = False
+
+ORDER = [
+ 'session_count',
+ 'session_limit_usage',
+ 'logons',
+ 'physical_disk_read_write',
+ 'sorts_on_disk',
+ 'full_table_scans',
+ 'database_wait_time_ratio',
+ 'shared_pool_free_memory',
+ 'in_memory_sorts_ratio',
+ 'sql_service_response_time',
+ 'user_rollbacks',
+ 'enqueue_timeouts',
+ 'cache_hit_ratio',
+ 'global_cache_blocks',
+ 'activity',
+ 'wait_time',
+ 'tablespace_size',
+ 'tablespace_usage',
+ 'tablespace_usage_in_percent',
+ 'allocated_size',
+ 'allocated_usage',
+ 'allocated_usage_in_percent',
+]
+
+CHARTS = {
+ 'session_count': {
+ 'options': [None, 'Session Count', 'sessions', 'session activity', 'oracledb.session_count', 'line'],
+ 'lines': [
+ ['session_count', 'total', 'absolute', 1, 1000],
+ ['average_active_sessions', 'active', 'absolute', 1, 1000],
+ ]
+ },
+ 'session_limit_usage': {
+ 'options': [None, 'Session Limit Usage', '%', 'session activity', 'oracledb.session_limit_usage', 'area'],
+ 'lines': [
+ ['session_limit_percent', 'usage', 'absolute', 1, 1000],
+ ]
+ },
+ 'logons': {
+ 'options': [None, 'Logons', 'events/s', 'session activity', 'oracledb.logons', 'area'],
+ 'lines': [
+ ['logons_per_sec', 'logons', 'absolute', 1, 1000],
+ ]
+ },
+ 'physical_disk_read_write': {
+ 'options': [None, 'Physical Disk Reads/Writes', 'events/s', 'disk activity',
+ 'oracledb.physical_disk_read_writes', 'area'],
+ 'lines': [
+ ['physical_reads_per_sec', 'reads', 'absolute', 1, 1000],
+ ['physical_writes_per_sec', 'writes', 'absolute', -1, 1000],
+ ]
+ },
+ 'sorts_on_disk': {
+ 'options': [None, 'Sorts On Disk', 'events/s', 'disk activity', 'oracledb.sorts_on_disks', 'line'],
+ 'lines': [
+ ['disk_sort_per_sec', 'sorts', 'absolute', 1, 1000],
+ ]
+ },
+ 'full_table_scans': {
+ 'options': [None, 'Full Table Scans', 'events/s', 'disk activity', 'oracledb.full_table_scans', 'line'],
+ 'lines': [
+ ['long_table_scans_per_sec', 'full table scans', 'absolute', 1, 1000],
+ ]
+ },
+ 'database_wait_time_ratio': {
+ 'options': [None, 'Database Wait Time Ratio', '%', 'database and buffer activity',
+ 'oracledb.database_wait_time_ratio', 'line'],
+ 'lines': [
+ ['database_wait_time_ratio', 'wait time ratio', 'absolute', 1, 1000],
+ ]
+ },
+ 'shared_pool_free_memory': {
+ 'options': [None, 'Shared Pool Free Memory', '%', 'database and buffer activity',
+ 'oracledb.shared_pool_free_memory', 'line'],
+ 'lines': [
+ ['shared_pool_free_percent', 'free memory', 'absolute', 1, 1000],
+ ]
+ },
+ 'in_memory_sorts_ratio': {
+ 'options': [None, 'In-Memory Sorts Ratio', '%', 'database and buffer activity',
+ 'oracledb.in_memory_sorts_ratio', 'line'],
+ 'lines': [
+ ['memory_sorts_ratio', 'in-memory sorts', 'absolute', 1, 1000],
+ ]
+ },
+ 'sql_service_response_time': {
+ 'options': [None, 'SQL Service Response Time', 'seconds', 'database and buffer activity',
+ 'oracledb.sql_service_response_time', 'line'],
+ 'lines': [
+ ['sql_service_response_time', 'time', 'absolute', 1, 1000],
+ ]
+ },
+ 'user_rollbacks': {
+ 'options': [None, 'User Rollbacks', 'events/s', 'database and buffer activity',
+ 'oracledb.user_rollbacks', 'line'],
+ 'lines': [
+ ['user_rollbacks_per_sec', 'rollbacks', 'absolute', 1, 1000],
+ ]
+ },
+ 'enqueue_timeouts': {
+ 'options': [None, 'Enqueue Timeouts', 'events/s', 'database and buffer activity',
+ 'oracledb.enqueue_timeouts', 'line'],
+ 'lines': [
+ ['enqueue_timeouts_per_sec', 'enqueue timeouts', 'absolute', 1, 1000],
+ ]
+ },
+ 'cache_hit_ratio': {
+ 'options': [None, 'Cache Hit Ratio', '%', 'cache', 'oracledb.cache_hit_ration', 'stacked'],
+ 'lines': [
+ ['buffer_cache_hit_ratio', 'buffer', 'absolute', 1, 1000],
+ ['cursor_cache_hit_ratio', 'cursor', 'absolute', 1, 1000],
+ ['library_cache_hit_ratio', 'library', 'absolute', 1, 1000],
+ ['row_cache_hit_ratio', 'row', 'absolute', 1, 1000],
+ ]
+ },
+ 'global_cache_blocks': {
+ 'options': [None, 'Global Cache Blocks Events', 'events/s', 'cache', 'oracledb.global_cache_blocks', 'area'],
+ 'lines': [
+ ['global_cache_blocks_corrupted', 'corrupted', 'incremental', 1, 1000],
+ ['global_cache_blocks_lost', 'lost', 'incremental', 1, 1000],
+ ]
+ },
+ 'activity': {
+ 'options': [None, 'Activities', 'events/s', 'activities', 'oracledb.activity', 'stacked'],
+ 'lines': [
+ ['activity_parse_count_total', 'parse count', 'incremental', 1, 1000],
+ ['activity_execute_count', 'execute count', 'incremental', 1, 1000],
+ ['activity_user_commits', 'user commits', 'incremental', 1, 1000],
+ ['activity_user_rollbacks', 'user rollbacks', 'incremental', 1, 1000],
+ ]
+ },
+ 'wait_time': {
+ 'options': [None, 'Wait Time', 'ms', 'wait time', 'oracledb.wait_time', 'stacked'],
+ 'lines': [
+ ['wait_time_application', 'application', 'absolute', 1, 1000],
+ ['wait_time_configuration', 'configuration', 'absolute', 1, 1000],
+ ['wait_time_administrative', 'administrative', 'absolute', 1, 1000],
+ ['wait_time_concurrency', 'concurrency', 'absolute', 1, 1000],
+ ['wait_time_commit', 'commit', 'absolute', 1, 1000],
+ ['wait_time_network', 'network', 'absolute', 1, 1000],
+ ['wait_time_user_io', 'user I/O', 'absolute', 1, 1000],
+ ['wait_time_system_io', 'system I/O', 'absolute', 1, 1000],
+ ['wait_time_scheduler', 'scheduler', 'absolute', 1, 1000],
+ ['wait_time_other', 'other', 'absolute', 1, 1000],
+ ]
+ },
+ 'tablespace_size': {
+ 'options': [None, 'Size', 'KiB', 'tablespace', 'oracledb.tablespace_size', 'line'],
+ 'lines': [],
+ },
+ 'tablespace_usage': {
+ 'options': [None, 'Usage', 'KiB', 'tablespace', 'oracledb.tablespace_usage', 'line'],
+ 'lines': [],
+ },
+ 'tablespace_usage_in_percent': {
+ 'options': [None, 'Usage', '%', 'tablespace', 'oracledb.tablespace_usage_in_percent', 'line'],
+ 'lines': [],
+ },
+ 'allocated_size': {
+ 'options': [None, 'Size', 'B', 'tablespace', 'oracledb.allocated_size', 'line'],
+ 'lines': [],
+ },
+ 'allocated_usage': {
+ 'options': [None, 'Usage', 'B', 'tablespace', 'oracledb.allocated_usage', 'line'],
+ 'lines': [],
+ },
+ 'allocated_usage_in_percent': {
+ 'options': [None, 'Usage', '%', 'tablespace', 'oracledb.allocated_usage_in_percent', 'line'],
+ 'lines': [],
+ },
+}
+
+CX_CONNECT_STRING = "{0}/{1}@//{2}/{3}"
+
+QUERY_SYSTEM = '''
+SELECT
+ metric_name,
+ value
+FROM
+ gv$sysmetric
+ORDER BY
+ begin_time
+'''
+QUERY_TABLESPACE = '''
+SELECT
+ m.tablespace_name,
+ m.used_space * t.block_size AS used_bytes,
+ m.tablespace_size * t.block_size AS max_bytes,
+ m.used_percent
+FROM
+ dba_tablespace_usage_metrics m
+ JOIN dba_tablespaces t ON m.tablespace_name = t.tablespace_name
+'''
+QUERY_ALLOCATED = '''
+SELECT
+ nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) tablespace_name,
+ bytes_alloc used_bytes,
+ bytes_alloc-nvl(bytes_free,0) max_bytes,
+ ((bytes_alloc-nvl(bytes_free,0))/ bytes_alloc)*100 used_percent
+FROM
+ (SELECT
+ sum(bytes) bytes_free,
+ tablespace_name
+ FROM sys.dba_free_space
+ GROUP BY tablespace_name
+ ) a,
+ (SELECT
+ sum(bytes) bytes_alloc,
+ tablespace_name
+ FROM sys.dba_data_files
+ GROUP BY tablespace_name
+ ) b
+WHERE a.tablespace_name (+) = b.tablespace_name
+'''
+QUERY_ACTIVITIES_COUNT = '''
+SELECT
+ name,
+ value
+FROM
+ v$sysstat
+WHERE
+ name IN (
+ 'parse count (total)',
+ 'execute count',
+ 'user commits',
+ 'user rollbacks'
+ )
+'''
+QUERY_WAIT_TIME = '''
+SELECT
+ n.wait_class,
+ round(m.time_waited / m.INTSIZE_CSEC, 3)
+FROM
+ v$waitclassmetric m,
+ v$system_wait_class n
+WHERE
+ m.wait_class_id = n.wait_class_id
+ AND n.wait_class != 'Idle'
+'''
+# QUERY_SESSION_COUNT = '''
+# SELECT
+# status,
+# type
+# FROM
+# v$session
+# GROUP BY
+# status,
+# type
+# '''
+# QUERY_PROCESSES_COUNT = '''
+# SELECT
+# COUNT(*)
+# FROM
+# v$process
+# '''
+# QUERY_PROCESS = '''
+# SELECT
+# program,
+# pga_used_mem,
+# pga_alloc_mem,
+# pga_freeable_mem,
+# pga_max_mem
+# FROM
+# gv$process
+# '''
+
+# PROCESS_METRICS = [
+# 'pga_used_memory',
+# 'pga_allocated_memory',
+# 'pga_freeable_memory',
+# 'pga_maximum_memory',
+# ]
+
+
+SYS_METRICS = {
+ 'Average Active Sessions': 'average_active_sessions',
+ 'Session Count': 'session_count',
+ 'Session Limit %': 'session_limit_percent',
+ 'Logons Per Sec': 'logons_per_sec',
+ 'Physical Reads Per Sec': 'physical_reads_per_sec',
+ 'Physical Writes Per Sec': 'physical_writes_per_sec',
+ 'Disk Sort Per Sec': 'disk_sort_per_sec',
+ 'Long Table Scans Per Sec': 'long_table_scans_per_sec',
+ 'Database Wait Time Ratio': 'database_wait_time_ratio',
+ 'Shared Pool Free %': 'shared_pool_free_percent',
+ 'Memory Sorts Ratio': 'memory_sorts_ratio',
+ 'SQL Service Response Time': 'sql_service_response_time',
+ 'User Rollbacks Per Sec': 'user_rollbacks_per_sec',
+ 'Enqueue Timeouts Per Sec': 'enqueue_timeouts_per_sec',
+ 'Buffer Cache Hit Ratio': 'buffer_cache_hit_ratio',
+ 'Cursor Cache Hit Ratio': 'cursor_cache_hit_ratio',
+ 'Library Cache Hit Ratio': 'library_cache_hit_ratio',
+ 'Row Cache Hit Ratio': 'row_cache_hit_ratio',
+ 'Global Cache Blocks Corrupted': 'global_cache_blocks_corrupted',
+ 'Global Cache Blocks Lost': 'global_cache_blocks_lost',
+}
+
+
+class Service(SimpleService):
+ def __init__(self, configuration=None, name=None):
+ SimpleService.__init__(self, configuration=configuration, name=name)
+ self.order = ORDER
+ self.definitions = deepcopy(CHARTS)
+ self.user = configuration.get('user')
+ self.password = configuration.get('password')
+ self.server = configuration.get('server')
+ self.service = configuration.get('service')
+ self.alive = False
+ self.conn = None
+ self.active_tablespaces = set()
+
+ def connect(self):
+ if self.conn:
+ self.conn.close()
+ self.conn = None
+
+ try:
+ self.conn = cx_Oracle.connect(
+ CX_CONNECT_STRING.format(
+ self.user,
+ self.password,
+ self.server,
+ self.service,
+ ))
+ except cx_Oracle.DatabaseError as error:
+ self.error(error)
+ return False
+
+ self.alive = True
+ return True
+
+ def reconnect(self):
+ return self.connect()
+
+ def check(self):
+ if not HAS_ORACLE:
+ self.error("'cx_Oracle' package is needed to use oracledb module")
+ return False
+
+ if not all([
+ self.user,
+ self.password,
+ self.server,
+ self.service,
+ ]):
+ self.error("one of these parameters is not specified: user, password, server, service")
+ return False
+
+ if not self.connect():
+ return False
+
+ return bool(self.get_data())
+
+ def get_data(self):
+ if not self.alive and not self.reconnect():
+ return None
+
+ data = dict()
+
+ # SYSTEM
+ try:
+ rv = self.gather_system_metrics()
+ except cx_Oracle.Error as error:
+ self.error(error)
+ self.alive = False
+ return None
+ else:
+ for name, value in rv:
+ if name not in SYS_METRICS:
+ continue
+ data[SYS_METRICS[name]] = int(float(value) * 1000)
+
+ # ACTIVITIES COUNT
+ try:
+ rv = self.gather_activities_count()
+ except cx_Oracle.Error as error:
+ self.error(error)
+ self.alive = False
+ return None
+ else:
+ for name, amount in rv:
+ cleaned = name.replace(' ', '_').replace('(', '').replace(')', '')
+ new_name = 'activity_{0}'.format(cleaned)
+ data[new_name] = int(float(amount) * 1000)
+
+ # WAIT TIME
+ try:
+ rv = self.gather_wait_time_metrics()
+ except cx_Oracle.Error as error:
+ self.error(error)
+ self.alive = False
+ return None
+ else:
+ for name, amount in rv:
+ cleaned = name.replace(' ', '_').replace('/', '').lower()
+ new_name = 'wait_time_{0}'.format(cleaned)
+ data[new_name] = amount
+
+ # TABLESPACE
+ try:
+ rv = self.gather_tablespace_metrics()
+ except cx_Oracle.Error as error:
+ self.error(error)
+ self.alive = False
+ return None
+ else:
+ for name, offline, size, used, used_in_percent in rv:
+ # TODO: skip offline?
+ if not (not offline and self.charts):
+ continue
+ # TODO: remove inactive?
+ if name not in self.active_tablespaces:
+ self.active_tablespaces.add(name)
+ self.add_tablespace_to_charts(name)
+ data['{0}_tablespace_size'.format(name)] = int(size * 1000)
+ data['{0}_tablespace_used'.format(name)] = int(used * 1000)
+ data['{0}_tablespace_used_in_percent'.format(name)] = int(used_in_percent * 1000)
+
+ # ALLOCATED SPACE
+ try:
+ rv = self.gather_allocated_metrics()
+ except cx_Oracle.Error as error:
+ self.error(error)
+ self.alive = False
+ return None
+ else:
+ for name, offline, size, used, used_in_percent in rv:
+ # TODO: skip offline?
+ if not (not offline and self.charts):
+ continue
+ # TODO: remove inactive?
+ if name not in self.active_tablespaces:
+ self.active_tablespaces.add(name)
+ self.add_tablespace_to_charts(name)
+ data['{0}_allocated_size'.format(name)] = int(size * 1000)
+ data['{0}_allocated_used'.format(name)] = int(used * 1000)
+ data['{0}_allocated_used_in_percent'.format(name)] = int(used_in_percent * 1000)
+
+ return data or None
+
+ def gather_system_metrics(self):
+
+ """
+ :return:
+
+ [['Buffer Cache Hit Ratio', 100],
+ ['Memory Sorts Ratio', 100],
+ ['Redo Allocation Hit Ratio', 100],
+ ['User Transaction Per Sec', 0],
+ ['Physical Reads Per Sec', 0],
+ ['Physical Reads Per Txn', 0],
+ ['Physical Writes Per Sec', 0],
+ ['Physical Writes Per Txn', 0],
+ ['Physical Reads Direct Per Sec', 0],
+ ['Physical Reads Direct Per Txn', 0],
+ ['Physical Writes Direct Per Sec', 0],
+ ['Physical Writes Direct Per Txn', 0],
+ ['Physical Reads Direct Lobs Per Sec', 0],
+ ['Physical Reads Direct Lobs Per Txn', 0],
+ ['Physical Writes Direct Lobs Per Sec', 0],
+ ['Physical Writes Direct Lobs Per Txn', 0],
+ ['Redo Generated Per Sec', Decimal('4.66666666666667')],
+ ['Redo Generated Per Txn', 280],
+ ['Logons Per Sec', Decimal('0.0166666666666667')],
+ ['Logons Per Txn', 1],
+ ['Open Cursors Per Sec', 0.35],
+ ['Open Cursors Per Txn', 21],
+ ['User Commits Per Sec', 0],
+ ['User Commits Percentage', 0],
+ ['User Rollbacks Per Sec', 0],
+ ['User Rollbacks Percentage', 0],
+ ['User Calls Per Sec', Decimal('0.0333333333333333')],
+ ['User Calls Per Txn', 2],
+ ['Recursive Calls Per Sec', 14.15],
+ ['Recursive Calls Per Txn', 849],
+ ['Logical Reads Per Sec', Decimal('0.683333333333333')],
+ ['Logical Reads Per Txn', 41],
+ ['DBWR Checkpoints Per Sec', 0],
+ ['Background Checkpoints Per Sec', 0],
+ ['Redo Writes Per Sec', Decimal('0.0333333333333333')],
+ ['Redo Writes Per Txn', 2],
+ ['Long Table Scans Per Sec', 0],
+ ['Long Table Scans Per Txn', 0],
+ ['Total Table Scans Per Sec', Decimal('0.0166666666666667')],
+ ['Total Table Scans Per Txn', 1],
+ ['Full Index Scans Per Sec', 0],
+ ['Full Index Scans Per Txn', 0],
+ ['Total Index Scans Per Sec', Decimal('0.216666666666667')],
+ ['Total Index Scans Per Txn', 13],
+ ['Total Parse Count Per Sec', 0.35],
+ ['Total Parse Count Per Txn', 21],
+ ['Hard Parse Count Per Sec', 0],
+ ['Hard Parse Count Per Txn', 0],
+ ['Parse Failure Count Per Sec', 0],
+ ['Parse Failure Count Per Txn', 0],
+ ['Cursor Cache Hit Ratio', Decimal('52.3809523809524')],
+ ['Disk Sort Per Sec', 0],
+ ['Disk Sort Per Txn', 0],
+ ['Rows Per Sort', 8.6],
+ ['Execute Without Parse Ratio', Decimal('27.5862068965517')],
+ ['Soft Parse Ratio', 100],
+ ['User Calls Ratio', Decimal('0.235017626321974')],
+ ['Host CPU Utilization (%)', Decimal('0.124311845142959')],
+ ['Network Traffic Volume Per Sec', 0],
+ ['Enqueue Timeouts Per Sec', 0],
+ ['Enqueue Timeouts Per Txn', 0],
+ ['Enqueue Waits Per Sec', 0],
+ ['Enqueue Waits Per Txn', 0],
+ ['Enqueue Deadlocks Per Sec', 0],
+ ['Enqueue Deadlocks Per Txn', 0],
+ ['Enqueue Requests Per Sec', Decimal('216.683333333333')],
+ ['Enqueue Requests Per Txn', 13001],
+ ['DB Block Gets Per Sec', 0],
+ ['DB Block Gets Per Txn', 0],
+ ['Consistent Read Gets Per Sec', Decimal('0.683333333333333')],
+ ['Consistent Read Gets Per Txn', 41],
+ ['DB Block Changes Per Sec', 0],
+ ['DB Block Changes Per Txn', 0],
+ ['Consistent Read Changes Per Sec', 0],
+ ['Consistent Read Changes Per Txn', 0],
+ ['CPU Usage Per Sec', 0],
+ ['CPU Usage Per Txn', 0],
+ ['CR Blocks Created Per Sec', 0],
+ ['CR Blocks Created Per Txn', 0],
+ ['CR Undo Records Applied Per Sec', 0],
+ ['CR Undo Records Applied Per Txn', 0],
+ ['User Rollback UndoRec Applied Per Sec', 0],
+ ['User Rollback Undo Records Applied Per Txn', 0],
+ ['Leaf Node Splits Per Sec', 0],
+ ['Leaf Node Splits Per Txn', 0],
+ ['Branch Node Splits Per Sec', 0],
+ ['Branch Node Splits Per Txn', 0],
+ ['PX downgraded 1 to 25% Per Sec', 0],
+ ['PX downgraded 25 to 50% Per Sec', 0],
+ ['PX downgraded 50 to 75% Per Sec', 0],
+ ['PX downgraded 75 to 99% Per Sec', 0],
+ ['PX downgraded to serial Per Sec', 0],
+ ['Physical Read Total IO Requests Per Sec', Decimal('2.16666666666667')],
+ ['Physical Read Total Bytes Per Sec', Decimal('35498.6666666667')],
+ ['GC CR Block Received Per Second', 0],
+ ['GC CR Block Received Per Txn', 0],
+ ['GC Current Block Received Per Second', 0],
+ ['GC Current Block Received Per Txn', 0],
+ ['Global Cache Average CR Get Time', 0],
+ ['Global Cache Average Current Get Time', 0],
+ ['Physical Write Total IO Requests Per Sec', Decimal('0.966666666666667')],
+ ['Global Cache Blocks Corrupted', 0],
+ ['Global Cache Blocks Lost', 0],
+ ['Current Logons Count', 49],
+ ['Current Open Cursors Count', 64],
+ ['User Limit %', Decimal('0.00000114087015416959')],
+ ['SQL Service Response Time', 0],
+ ['Database Wait Time Ratio', 0],
+ ['Database CPU Time Ratio', 0],
+ ['Response Time Per Txn', 0],
+ ['Row Cache Hit Ratio', 100],
+ ['Row Cache Miss Ratio', 0],
+ ['Library Cache Hit Ratio', 100],
+ ['Library Cache Miss Ratio', 0],
+ ['Shared Pool Free %', Decimal('7.82380268491548')],
+ ['PGA Cache Hit %', Decimal('98.0399767109115')],
+ ['Process Limit %', Decimal('17.6666666666667')],
+ ['Session Limit %', Decimal('15.2542372881356')],
+ ['Executions Per Txn', 29],
+ ['Executions Per Sec', Decimal('0.483333333333333')],
+ ['Txns Per Logon', 0],
+ ['Database Time Per Sec', 0],
+ ['Physical Write Total Bytes Per Sec', 15308.8],
+ ['Physical Read IO Requests Per Sec', 0],
+ ['Physical Read Bytes Per Sec', 0],
+ ['Physical Write IO Requests Per Sec', 0],
+ ['Physical Write Bytes Per Sec', 0],
+ ['DB Block Changes Per User Call', 0],
+ ['DB Block Gets Per User Call', 0],
+ ['Executions Per User Call', 14.5],
+ ['Logical Reads Per User Call', 20.5],
+ ['Total Sorts Per User Call', 2.5],
+ ['Total Table Scans Per User Call', 0.5],
+ ['Current OS Load', 0.0390625],
+ ['Streams Pool Usage Percentage', 0],
+ ['PQ QC Session Count', 0],
+ ['PQ Slave Session Count', 0],
+ ['Queries parallelized Per Sec', 0],
+ ['DML statements parallelized Per Sec', 0],
+ ['DDL statements parallelized Per Sec', 0],
+ ['PX operations not downgraded Per Sec', 0],
+ ['Session Count', 72],
+ ['Average Synchronous Single-Block Read Latency', 0],
+ ['I/O Megabytes per Second', 0.05],
+ ['I/O Requests per Second', Decimal('3.13333333333333')],
+ ['Average Active Sessions', 0],
+ ['Active Serial Sessions', 1],
+ ['Active Parallel Sessions', 0],
+ ['Captured user calls', 0],
+ ['Replayed user calls', 0],
+ ['Workload Capture and Replay status', 0],
+ ['Background CPU Usage Per Sec', Decimal('1.22578833333333')],
+ ['Background Time Per Sec', 0.0147551],
+ ['Host CPU Usage Per Sec', Decimal('0.116666666666667')],
+ ['Cell Physical IO Interconnect Bytes', 3048448],
+ ['Temp Space Used', 0],
+ ['Total PGA Allocated', 200657920],
+ ['Total PGA Used by SQL Workareas', 0],
+ ['Run Queue Per Sec', 0],
+ ['VM in bytes Per Sec', 0],
+ ['VM out bytes Per Sec', 0]]
+ """
+
+ metrics = list()
+ with self.conn.cursor() as cursor:
+ cursor.execute(QUERY_SYSTEM)
+ for metric_name, value in cursor.fetchall():
+ metrics.append([metric_name, value])
+ return metrics
+
+ def gather_tablespace_metrics(self):
+ """
+ :return:
+
+ [['SYSTEM', 874250240.0, 3233169408.0, 27.040038107400033, 0],
+ ['SYSAUX', 498860032.0, 3233169408.0, 15.429443033997678, 0],
+ ['TEMP', 0.0, 3233177600.0, 0.0, 0],
+ ['USERS', 1048576.0, 3233169408.0, 0.03243182981397305, 0]]
+ """
+ metrics = list()
+ with self.conn.cursor() as cursor:
+ cursor.execute(QUERY_TABLESPACE)
+ for tablespace_name, used_bytes, max_bytes, used_percent in cursor.fetchall():
+ if used_bytes is None:
+ offline = True
+ used = 0
+ else:
+ offline = False
+ used = float(used_bytes)
+ if max_bytes is None:
+ size = 0
+ else:
+ size = float(max_bytes)
+ if used_percent is None:
+ used_percent = 0
+ else:
+ used_percent = float(used_percent)
+ metrics.append(
+ [
+ tablespace_name,
+ offline,
+ size,
+ used,
+ used_percent,
+ ]
+ )
+ return metrics
+
+ def gather_allocated_metrics(self):
+ """
+ :return:
+
+ [['SYSTEM', 874250240.0, 3233169408.0, 27.040038107400033, 0],
+ ['SYSAUX', 498860032.0, 3233169408.0, 15.429443033997678, 0],
+ ['TEMP', 0.0, 3233177600.0, 0.0, 0],
+ ['USERS', 1048576.0, 3233169408.0, 0.03243182981397305, 0]]
+ """
+ metrics = list()
+ with self.conn.cursor() as cursor:
+ cursor.execute(QUERY_ALLOCATED)
+ for tablespace_name, used_bytes, max_bytes, used_percent in cursor.fetchall():
+ if used_bytes is None:
+ offline = True
+ used = 0
+ else:
+ offline = False
+ used = float(used_bytes)
+ if max_bytes is None:
+ size = 0
+ else:
+ size = float(max_bytes)
+ if used_percent is None:
+ used_percent = 0
+ else:
+ used_percent = float(used_percent)
+ metrics.append(
+ [
+ tablespace_name,
+ offline,
+ size,
+ used,
+ used_percent,
+ ]
+ )
+ return metrics
+
+ def gather_wait_time_metrics(self):
+ """
+ :return:
+
+ [['Other', 0],
+ ['Application', 0],
+ ['Configuration', 0],
+ ['Administrative', 0],
+ ['Concurrency', 0],
+ ['Commit', 0],
+ ['Network', 0],
+ ['User I/O', 0],
+ ['System I/O', 0.002],
+ ['Scheduler', 0]]
+ """
+ metrics = list()
+ with self.conn.cursor() as cursor:
+ cursor.execute(QUERY_WAIT_TIME)
+ for wait_class_name, value in cursor.fetchall():
+ metrics.append([wait_class_name, value])
+ return metrics
+
+ def gather_activities_count(self):
+ """
+ :return:
+
+ [('user commits', 9104),
+ ('user rollbacks', 17),
+ ('parse count (total)', 483695),
+ ('execute count', 2020356)]
+ """
+ with self.conn.cursor() as cursor:
+ cursor.execute(QUERY_ACTIVITIES_COUNT)
+ return cursor.fetchall()
+
+ # def gather_process_metrics(self):
+ # """
+ # :return:
+ #
+ # [['PSEUDO', 'pga_used_memory', 0],
+ # ['PSEUDO', 'pga_allocated_memory', 0],
+ # ['PSEUDO', 'pga_freeable_memory', 0],
+ # ['PSEUDO', 'pga_maximum_memory', 0],
+ # ['oracle@localhost.localdomain (PMON)', 'pga_used_memory', 1793827],
+ # ['oracle@localhost.localdomain (PMON)', 'pga_allocated_memory', 1888651],
+ # ['oracle@localhost.localdomain (PMON)', 'pga_freeable_memory', 0],
+ # ['oracle@localhost.localdomain (PMON)', 'pga_maximum_memory', 1888651],
+ # ...
+ # ...
+ # """
+ #
+ # metrics = list()
+ # with self.conn.cursor() as cursor:
+ # cursor.execute(QUERY_PROCESS)
+ # for row in cursor.fetchall():
+ # for i, name in enumerate(PROCESS_METRICS, 1):
+ # metrics.append([row[0], name, row[i]])
+ # return metrics
+
+ # def gather_processes_count(self):
+ # with self.conn.cursor() as cursor:
+ # cursor.execute(QUERY_PROCESSES_COUNT)
+ # return cursor.fetchone()[0] # 53
+
+ # def gather_sessions_count(self):
+ # with self.conn.cursor() as cursor:
+ # cursor.execute(QUERY_SESSION_COUNT)
+ # total, active, inactive = 0, 0, 0
+ # for status, _ in cursor.fetchall():
+ # total += 1
+ # active += status == 'ACTIVE'
+ # inactive += status == 'INACTIVE'
+ # return [total, active, inactive]
+
+ def add_tablespace_to_charts(self, name):
+ self.charts['tablespace_size'].add_dimension(
+ [
+ '{0}_tablespace_size'.format(name),
+ name,
+ 'absolute',
+ 1,
+ 1024 * 1000,
+ ])
+ self.charts['tablespace_usage'].add_dimension(
+ [
+ '{0}_tablespace_used'.format(name),
+ name,
+ 'absolute',
+ 1,
+ 1024 * 1000,
+ ])
+ self.charts['tablespace_usage_in_percent'].add_dimension(
+ [
+ '{0}_tablespace_used_in_percent'.format(name),
+ name,
+ 'absolute',
+ 1,
+ 1000,
+ ])
+ self.charts['allocated_size'].add_dimension(
+ [
+ '{0}_allocated_size'.format(name),
+ name,
+ 'absolute',
+ 1,
+ 1000,
+ ])
+ self.charts['allocated_usage'].add_dimension(
+ [
+ '{0}_allocated_used'.format(name),
+ name,
+ 'absolute',
+ 1,
+ 1000,
+ ])
+ self.charts['allocated_usage_in_percent'].add_dimension(
+ [
+ '{0}_allocated_used_in_percent'.format(name),
+ name,
+ 'absolute',
+ 1,
+ 1000,
+ ])
diff --git a/collectors/python.d.plugin/oracledb/oracledb.conf b/collectors/python.d.plugin/oracledb/oracledb.conf
new file mode 100644
index 0000000..6257172
--- /dev/null
+++ b/collectors/python.d.plugin/oracledb/oracledb.conf
@@ -0,0 +1,84 @@
+# netdata python.d.plugin configuration for oracledb
+#
+# This file is in YaML format. Generally the format is:
+#
+# name: value
+#
+# There are 2 sections:
+# - global variables
+# - one or more JOBS
+#
+# JOBS allow you to collect values from multiple sources.
+# Each source will have its own set of charts.
+#
+# JOB parameters have to be indented (using spaces only, example below).
+
+# ----------------------------------------------------------------------
+# Global Variables
+# These variables set the defaults for all JOBs, however each JOB
+# may define its own, overriding the defaults.
+
+# update_every sets the default data collection frequency.
+# If unset, the python.d.plugin default is used.
+# update_every: 1
+
+# priority controls the order of charts at the netdata dashboard.
+# Lower numbers move the charts towards the top of the page.
+# If unset, the default for python.d.plugin is used.
+# priority: 60000
+
+# penalty indicates whether to apply penalty to update_every in case of failures.
+# Penalty will increase every 5 failed updates in a row. Maximum penalty is 10 minutes.
+# penalty: yes
+
+# autodetection_retry sets the job re-check interval in seconds.
+# The job is not deleted if check fails.
+# Attempts to start the job are made once every autodetection_retry.
+# This feature is disabled by default.
+# autodetection_retry: 0
+
+# ----------------------------------------------------------------------
+# JOBS (data collection sources)
+#
+# The default JOBS share the same *name*. JOBS with the same name
+# are mutually exclusive. Only one of them will be allowed running at
+# any time. This allows autodetection to try several alternatives and
+# pick the one that works.
+#
+# Any number of jobs is supported.
+#
+# All python.d.plugin JOBS (for all its modules) support a set of
+# predefined parameters. These are:
+#
+# job_name:
+# name: myname # the JOB's name as it will appear at the
+# # dashboard (by default is the job_name)
+# # JOBs sharing a name are mutually exclusive
+# update_every: 1 # the JOB's data collection frequency
+# priority: 60000 # the JOB's order on the dashboard
+# penalty: yes # the JOB's penalty
+# autodetection_retry: 0 # the JOB's re-check interval in seconds
+#
+# Additionally to the above, oracledb also supports the following:
+#
+# user: username # the username for the user account. Required.
+# password: password # the password for the user account. Required.
+# server: localhost:1521 # the IP address or hostname of the Oracle Database Server. Required.
+# service: XE # the Oracle Database service name. Required. To view the services available on your server,
+# run this query: `SELECT value FROM v$parameter WHERE name='service_names'`.
+#
+# ----------------------------------------------------------------------
+# AUTO-DETECTION JOBS
+# only one of them will run (they have the same name)
+
+#local:
+# user: 'netdata'
+# password: 'secret'
+# server: 'localhost:1521'
+# service: 'XE'
+
+#remote:
+# user: 'netdata'
+# password: 'secret'
+# server: '10.0.0.1:1521'
+# service: 'XE' \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-03 19:20:16 +0000