Plugin info

name: ora-sqlstats

Collects SQL stat deltas from V$SQLSTATS and provides feeds of SQL text and SQL plan deltas.

Important

• Only completed queries are reported delta(executions) > 0

Prerequisites

When using BEQ connections:

• Linux
  • User Groups: oinstall, dba
• Windows
  • Group Memeberships: ORA_HOMENAME_DBA

Events

None

Metrics

Metrics are collected at the database instance level by con_id, sql_hash and plan_hash. The value of sql_hash corresponds to V$SQLSTATS.SQL_ID. The con_id is reflected in the value of the source dimension for each metric where:

• con_id == 0, then sources[].name
• con_id > 0, then pdb name.sources[].name

Metric	Description
oracle/db/sqlstats/parse_calls	parse_calls delta
oracle/db/sqlstats/disk_reads	disk_reads delta
oracle/db/sqlstats/buffer_gets	buffer_gets delta
oracle/db/sqlstats/executions	executions delta
oracle/db/sqlstats/cpu_time	cpu_time delta
oracle/db/sqlstats/elapsed_time	elapsed_time delta
oracle/db/sqlstats/sharable_mem	sharable_mem delta
oracle/db/sqlstats/version_count	version_count delta
oracle/db/sqlstats/parse_calls_per_exec	parse_calls delta per execution
oracle/db/sqlstats/disk_reads_per_exec	disk_reads delta per execution
oracle/db/sqlstats/buffer_gets_per_exec	buffer_gets delta per execution
oracle/db/sqlstats/cpu_time_per_exec	cpu_time delta per execution
oracle/db/sqlstats/elapsed_time_per_exec	elapsed_time delta per execution
oracle/db/sqlstats/sharable_mem_per_exec	sharable_mem delta per execution

Configuration

This section describes the configuration settings for this plugin.

Name	Type	Required	Default	Description
timeout	duration string	No	1m	How long to wait for statistics to be calculated
initial_active_age	duration string	No	1h	How far back to look for stat changes on first run
retention_period	duration string	No	192h	How long to keep the last stat values before purging them
retention_window_len	duration string	No	12h	Time bucket width for storing last stat values. Only whole buckets are dropped when retention_period expires
limit	Limit Config	No		Limits the number of stats returned
sources	list[Source Config]	Yes		The database instances to collect stats

Source Config

Name	Type	Required	Default	Description
name	string	Yes		Name for the stats source
oracle_home	string	No		Oracle home path. Only required when using BEQ connection
sid	string	No		SID name. Only required when using BEQ connection
connect_url	string	Yes		Connection string. Supports secrets

Limit Config

Returns only stats whose delta exceeds any of the following thresholds

Name	Type	Required	Default	Description
parse_calls	int	No	ALL
disk_reads	int	No	ALL
buffer_gets	int	No	ALL
executions	int	No	ALL
cpu_time	int	No	ALL
elapsed_time	int	No	ALL
sharable_mem	int	No	ALL

Sample Configuration

sources:
  - name: SomeInstanceName
    oracle_home: /opt/oracle/product/19c/dbhome_1
    sid: catalyst
    connect_url: / as sysdba
initial_active_age: 1h     # first time the plugin runs fetches stats no older than 1h
retention_period: 192h     # keep stats for 8 days
retention_window_len: 24h  # keep stats in 24h buckets 
timeout: 1m
limit:
  - executions: 2 # only queries that execute more than once in an interval