Oracle Metrics

Database metrics are a comprehensive collection of database workloads and resource usages. The WhaTap monitoring service continuously collects metrics to identify bottlenecks in the database and provides data to help identify the root causes of performance issues.

For more information about columns, see the link.

agent_status_summary

This category collects metrics related to agent status every 10 seconds.

Fields

Field name	Unit	Description	Remarks
inActTime	Millisecond(ms)	Amount of time the agent remains inactive	-
isActive	-	Whether the agent is active or not	true / false
isRestart	-	Whether the agent has been recently restarted	true / false
lastActTime	Millisecond(ms)	Time when the agent was last activated	0: If disabled
oid	-	Unique IDs for each agent in the project	-
startTime	Millisecond(ms)	Timestamp when the agent was started.	-

db_oracle_counter

This category is useful for collecting overall performance metrics of the Oracle database to monitor the status and performance of the system.

Tags

The following lists the tags that are collected in the db_oracle_counter category.

Tag name	Description	Remarks
agentIp	IP address per agent.	-
dbIsMulti	Whether to support the multitenant or not.	-
dbName	Database name.	-
dbType	Database type.	-
dbVersion	Version of the database in use.	-
oname	Agent name	Unique value

Field

The following lists the fields that are collected in the db_oracle_counter category.

Metrics	Description	Category
cpu_used_by_this_session	CPU time (in 10 milliseconds) used by the session, from the user call start time to its end time.	Performance
db_time	Elapsed time (in microseconds) to perform user-level calls to the database.	Performance
active_sessions	Number of active sessions.	Connection
connect_error	Number of connection errors.	Connection
consistent_gets	Number of consistent data block read operations.	I/O
cpu	CPU utilization used in the database.	Performance
cpu_cores	Number of available CPU cores	Performance
data_blocks_consistent_reads_-_undo_records_applied	Number of Undo records applied in consistent data block reads.	I/O
db_block_changes	Number of blocks changed in SGA.	I/O
db_block_gets	Number of direct read operations from blocks in the SGA.	I/O
db_file_scattered_read_time_waited	Time waited for scattered read events.	I/O
db_file_scattered_read_total_timeouts	Number of timeouts occurred during the scattered read events.	I/O
db_file_scattered_read_total_waits	Total number of waits occurred during the scattered read events.	I/O
db_file_sequential_read_time_waited	Time waited for sequential read events.	I/O
db_file_sequential_read_total_timeouts	Number of timeouts occurred during the sequential read events.	I/O
db_file_sequential_read_total_waits	Total number of waits occurred during the sequential read events.	I/O
direct path read time waited	Time waited for direct path reads.	I/O
direct path read total timeouts	Number of timeouts occurred during direct path reads.	I/O
direct path read total waits	Total number of waits occurred during direct path reads.	I/O
direct path write time waited	Time waited for direct path writes.	I/O
direct path write total timeouts	Number of timeouts occurred during direct path writes.	I/O
direct path write total waits	Total number of waits occurred during direct path writes.	I/O
enq:_TX_-_row_lock_contention_time_waited	Time waited for row lock contention.	I/O
enq:_TX_-_row_lock_contention_total_timeouts	Number of timeouts occurred for row lock contention.	I/O
enq:_TX_-_row_lock_contention_total_waits	Total number of waits occurred for row lock contention.	I/O
enqueue_waits	Total number of waits during queue conversion or import due to queue import delays.	I/O
execute_count	Total number of commands that executed SQL statements.	Performance
fatal_count	Number of fatal errors.	Info
iowait_cpu	Percentage of CPU time spent in I/O wait state.	Performance
latch free time waited	Time waited in latch-free state.	I/O
latch free total timeouts	Number of timeouts occurred in latch-free state.	I/O
latch free total waits	Total number of waits occurred in latch-free state.	I/O
library cache lock time waited	Time waited in library cache lock state.	I/O
library cache lock total timeouts	Number of timeouts occurred during library cache locks.	I/O
library cache lock total waits	Total number of waits occurred during library cache locks.	I/O
library cache pin time waited	Time waited in library cache pin.	I/O
library cache pin total timeouts	Number of timeouts occurred in library cache pin.	I/O
library cache pin total waits	Total number of waits occurred in library cache pin.	I/O
lock_wait_sessions	Number of waits to acquire the current row lock.	Connection
logons_current	Number of active logons.	Connection
long_running_sessions	Number of long running sessions.	Connection
memory	Percentage of memory in use.	MySQL Resource
opened_cursors_current	Number of open cursors.	Connection
parse_count_(hard)	Total number of syntax analysis calls (actual syntax analysis).	Performance
parse_count_(total)	Total number of syntax analysis calls (hard, soft, describe).	Performance
physical_reads	Number of data blocks read from disk.	I/O
physical_writes	Number of blocks in which data has been written to disk. Sum of physical writes direct and physical writes from cache.	I/O
pq_sessions	Number of parallel query sessions.	Connection
redo_entries	Number of Redo entries created.	I/O
rollback_changes_-_undo_records_applied	Number of Undo records applied due to rollback changes.	I/O
session_logical_reads	Number of data blocks read from memory. Sum of db block gets and consistent gets.	I/O
sorts_(disk)	Number of sort operations occurred on disk.	I/O
sorts_(memory)	Number of sort operations occurred in memory.	I/O
sys_cpu	Sum of CPU time used by the session.	Performance
total_elapse_time	Cumulative total elapsed time of all requests since the last database instance restart.	Performance
total_sessions	Total number of sessions.	Connection
transaction_tables_consistent_reads_-_undo_records_applied	Number of Undo records applied to consistent read operations in the transaction table.	I/O
txn_sessions	Number of transaction sessions.	Connection
user_commits	Number of user commit operations per transaction.	I/O
user_cpu	CPU % used for user tasks.	Performance
user_rollbacks	Number of user rollback operations.	I/O
wait_sessions	Number of sessions waiting for events.	Connection
warning_count	Number of all warnings.	Info

The following describes the metrics collected via the ora_ratio option in the agent settings. For more information about the agent option configuration, see the following.

Metrics	Description
buffer cache hit ratio	The number of times the data can be found directly in memory without reading it from the disk . A high number indicates a good database performance, while a low number indicates the increased disk I/O, which may cause poor performance.
soft parse ratio	SQL statements reuse frequency, Soft Parse is that a SQL statement that is in the cache is reused. A high value indicates that SQL statement are being reused frequently.
hard parse ratio	Parsed frequency of the SQL statements from scratch. A hard parse occurs when a SQL statement is run for the first time or when it does not exist in the cache. If this figure is high, it may increase the CPU usage of the database and result in poor performance.
library cache hit ratio	The percentage of successful search of SQL and PL/SQL statements from the Library Cache. A high number indicates that SQL statements and PL/SQL code are being reused effectively in the cache.
library cache pin hit ratio	Percentage of successful Library Cache Pin requests. The Library Cache Pin protects running SQL statements from being changed by other sessions. A high number indicates that this protection is effective.
library cache get hit ratio	If the rate of successful retrieval of objects from the Library Cache is a high number, it indicates that SQL statements and PL/SQL code are being reused effectively in the Library Cache.
latch hit ratio	The rate of successful latch requests , latch is a locking mechanism for controlling access to shared memory in the database. A high number indicates a low latch latency and good concurrency and performance of the system.
shared pool free mem ratio	The percentage of Free Memory in the Shared Pool . The shared pool is a memory area that stores SQL statements, PL/SQL code, and the data dictionary cache. If an appropriate amount of free memory is maintained, it can ensure efficient operation of the shared pool and optimal performance.

db_oracle_tablespace

This category collects tablespace usage data of the Oracle database every one hour. Metrics in this category help you monitor the status and capacity of tablespaces to efficiently manage storage resources and proactively prevent database performance issues.

Tags

The following lists the tags that are collected in the db_oracle_tablespace category.

Tag name	Description	Remarks
con_name	It is the database name of the connected container. It is useful for distinguishing each Pluggable Database (PDB) in the multi-tenant environment.	-
datfile	Path and file name of the data file linked to each tablespace.	-
oname	Agent name	Unique value
tablespace	It is the name of tablespace.	-

Field

The following lists the fields that are collected in the db_oracle_tablespace category.

Metrics	Description
autoextensible	It indicates whether the data file is automatically extensible with YES or NO.
increment_by	Number of blocks that increase at a time when the data file is extended automatically.
maxsize	Maximum size set when the data file is automatically extended.
size	Current allocated capacity of the tablespace.
used	Size of the tablespace in use.

XOS-related metrics

The following describes the metrics collected from the XOS agent.

Metric	Unit	Description
cpu(xos)	pct	CPU utilization, cpu_user + cpu_sys + cpu_iowait.
cpu_user(xos)	pct	Time spent for CPU to execute user code.
cpu_sys(xos)	pct	Time spent for CPU to execute the kernel code.
cpu_iowait(xos)	pct	CPU time waited for IO.
cpu_idle(xos)	pct	CPU processor's idle time.
disk_reads(xos)	block	Amount of data read from disk.
disk_read_bytes(xos)	byte	Amount of data read from disk.
disk_writes(xos)	block	Amount of data written to disk.
disk_write_bytes(xos)	byte	Amount of data written to disk.
mem(xos)	pct	Memory utilization.
mem_total(xos)	MB	Total amount of memory.
mem_free(xos)	MB	Amount of memory not in use by the system.
mem_used(xos)	MB	Amount of memory in use by the system.
mem_buffcache(xos)	MB	Amount of memory used by the buff/cache.
mem_swaptotal(xos)	MB	Set this in preparation for the case where the total Swap size and physical memory are insufficient.
mem_swapfree(xos)	MB	Because the unused Swap area and Swap area are secured in disk space, the system performance may drop drastically while using Swap.
mem_available(xos)	MB	Available memory area.
net_recv_bytes(xos)	byte	Amount of data received over the network.
net_recv_packets(xos)	-	Number of data packets received over the network.
net_send_bytes(xos)	byte	Amount of data transmitted over the network.
net_send_packets(xos)	-	Number of data packets transmitted over the network.