SQL statement statistics

Job details

Name:	SQL statement statistics
Platform:	Oracle
Category:	Performance
Description:	Collects SQL statement statistics from v$sqlarea performance view (Oracle 10g only).
Long description:	Task collects SQL statement statistics from v$sqlarea performance view (Oracle 10g only).
Version:	3.7
Default schedule:	30 6,11,18 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘10%’)]

Name	Default value	Description
max loop time	60	The maximum time (in seconds) the task will
max sql records	100	The maximum numbers of records
buffer gets threshold	10000000	The threshold for when the statistics will be collected.
disk reads threshold	500000	The threshold for when the statistics will be collected.
excl. cursor text	DECLARE,BEGIN	Cursors which includes strings defined by this parameter, are excluded from the tasks check.
logging threshold	100	…

Purpose: The purpose of this job is to monitor and collect performance statistics for SQL statements in Oracle 10g instances, using the v$sqlarea view, in order to identify and track SQL cursors with high resource use.
Why: This job is essential for maintaining optimal performance and resource utilization within the Oracle database environment. High resource-using cursors can indicate inefficiencies or problematic queries that might impair database response times and overall system performance.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT * FROM v$sqlarea WHERE executions > 0;

“SQL statement statistics” job specifically targets Oracle 10g instances as indicated by the component query and metadata.
It includes functionalities to specify the maximum runtime of the task, the history size for SQL records, thresholds for buffer gets and disk reads, and exclusion criteria for certain cursors.
Dependencies include views like v$sqlarea and additional support tables and sequences created for managing historical data and task execution.
SQL procedures implemented as part of this job handle the insertion of execution statistics into a designated historical table, considering the defined criteria and thresholds.

The SQL procedures of this job create and manage several database objects:

Object Name	Type	Description
dbw_sqlhistr_#instance~id#	Table	Stores historical data of SQL cursor statistics.
dbw_sqlhistr_seq_#instance~id#	Sequence	Provides a sequence for entries in the historical table.
dbv_sqlarea_#instance~id#	View	Summarizes current SQL execution statistics from v$sqlarea.
dbv_sqlhistr_#instance~id#	View	Represents data from the historical table, filtered and formatted for reporting.

The dbWatch Report template includes statistical analyses prepared from the collected data.
The report breaks down SQL performance metrics into multiple presentations, including rows processed, buffer gets, disk reads, and execution frequencies, to facilitate comprehensive analysis.
These statistics help in pinpointing resource-intensive operations and guide database tuning and optimization efforts.