How to check for open database cursors [ID 743605.1]

最新推荐文章于 2024-09-08 10:40:56 发布

cuhongyang4157

最新推荐文章于 2024-09-08 10:40:56 发布

阅读量74

点赞数

文章标签：数据库

In this Document
  Purpose
  Software Requirements/Prerequisites
  Configuring the Script
  Running the Script
  Caution
  Script
  Script. Output

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Siebel Tools - Version: 7.5.3 [100] to 8.0 [20405] - Release: V7 to V8
Information in this document applies to any platform.

Purpose

The purpose of the document is to explain how to check a database such as Oracle for any open database cursors.

This document is specific to an Oracle database. The intention is that the details in this document can be used by Siebel developers and programmers to troubleshoot memory leak issues that occur as a result of custom configuration and/or custom scripting that has been added to the standard SRF.

For example, Siebel programmers often create custom scripts to reference and query business component records and retrieve data. During this process, memory objects are created in the Siebel Object Manager that result in a new database cursor been opened in the database. If this newly created object is not released in the correct way at the end of the script, then this will result in a the database cursor remaining open. This document describes the steps to check for these types of Open Cursors in an Oracle database.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but it is recommended that you set this to at least 500 for most applications.

Software Requirements/Prerequisites

The user will need to have access to the database via SQL Plus or some equivalent client interface

Configuring the Script

There are two main initialization parameters that affect cursors. One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000's or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached.

There's no relationship between the two parameters. If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session.

Therefore to check how many cursors a session has open, don't look in v$open_cursor as it shows the cursors in the session cursor cache for each session, not cursors that are actually open. Instead, to monitor open cursors, query the v$sesstat view.

Running the Script

To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session.

Please use the Select statement below to check the open cursors.

Caution

This script. is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script. before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script. may not be in an executable state when you first receive it. Check over the script. to ensure that errors of this type are corrected.

Script

--total cursors open, by session

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';

Script. Output

Here is sample output from the open cursor check.

SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
2> from v$sesstat a, v$statname b, v$parameter p
3> where a.statistic# = b.statistic#
4> and b.name = 'opened cursors current'
5> and p.name= 'open_cursors'
6> group by p.value;

1953 2500

Note: Please note that the format of the output may be displayed differently in the environment you are using