Open cursors

Job details

Name:	Open cursors
Platform:	Oracle
Category:	Performance
Description:	Checks and collects the amount of open cursors per session.
Long description:	Task checks and collects the amount of open cursors performed per session.
Version:	1.7
Default schedule:	10,30,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’]

Parameters

Name	Default value	Description
warning threshold	99	Returns warning when the percentage (default 99%) of maximum open cursor is reached.
history threshold	14	The number of days the statistics are collected in the history table.
ignore dbwatch sessions	YES	If “YES“ the dbWatch Engine sessions created by dbWatch Server are excluded from being checked.

Job Summary

Purpose: The purpose of this job is to monitor the number of open cursors per session in Oracle databases, ensuring they do not exceed a defined threshold, which could potentially lead to performance issues.
Why: This job is important to maintain optimal database performance and to prevent the excessive consumption of resources associated with open cursors. This monitoring helps in proactive detection of potential problems, avoiding system slowdowns or failures due to cursor exhaustion.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

select s.username "User name", s.sid "Session ID",s.program "Program name" ,s.machine "Host name",a.value "Opened cursors"
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid and s.username is not null and b.name = 'opened cursors current'
order by 5 desc

Implementation Details

The job involves several key operations elaborated below:

Creation of tables to store historical data on open cursors and ranking information:
“dbw_open_cur_histr” for historical data including session ID, username, program name, cursor count, and date of recording.

A stored procedure “open_cur_check” is executed to:
Fetch the configured warning and history thresholds.

Dependency management is also handled to ensure that all components and tables required by the task are appropriately linked and managed.

Reporting and Output

The results of the monitoring are presented through various ways in dbWatch reports:

Open cursors parameter value: Displays the current configuration parameter for maximum open cursors allowed from “v$parameter”.
Top open cursors per session: Provides a tabular view of sessions having the highest counts of open cursors. It excludes dbWatch Engine sessions if specified.
Cursor count history: Shows historical trends in cursor counts for sessions that had the highest values, aiding in pattern analysis and long-term monitoring effectiveness.

Dependencies

Dependency Object	Object Type	Description
open_cur_check	2 (self-referencing task)	This check depends on its instance to function correctly.
dbw_open_cur_histr	0 (table)	Historic table that needs proper setup and management.
dbw_och_rank	0 (table)	Table needed for ranking sessions based on cursor counts.

This monitoring job is vital for maintaining the health and performance of Oracle database environments by ensuring that the use of cursors is within acceptable limits.