Open cursors

From: Sergei <spohilko_at_yahoo.com>
Date: 10 Feb 2003 18:39:58 -0800
Message-ID: <b50f7e10.0302101839.676f844e@posting.google.com>

Hi!

I have a problem with monitoring of the opened cursors. We have Java application, with custom object persistent and data layers, so all SQL statements are generated on the fly. And as expected (based on Thomas Kyte Expert One-On-One), we have problems to close opened cursors.

But I'm curious about opened cursor details. What I saw in the database, looks like there are multiple cursors for the same SELECT statement.
Using:

select o.*,a.sql_text as sql_text_full
from v$session s, v$open_cursor o, v$sqlarea a where

s.saddr=o.saddr and 
s.sid=o.sid and 
o.address=a.address and 
o.hash_value=a.hash_value

and user_name = 'AP_ROB1'

I got the same SADDRD, SID, USER_NAME, ADDRESS, HASH_VALUE, and the same SQL_TEXT_FULL for 1330 cursors.
For example:

SADDRD SID USER ADDRESS HASH_VALUE

69BB82D0 114 AP_ROB1 651D1C38 SQL_TEXT_FULL

SELECT HW_CATEGORIES.CATEGORY_NAME, AGENTS.ID, AGENTS.CREATED_BY,

AGENTS.CREATED_DATE, AGENTS.MODIFIED_BY, AGENTS.MODIFIED_DATE,
AGENTS.APP_SEQ_NUMBER FROM AGENTS INNER JOIN AGENT_GLOBAL_SETTINGS ON
AGENTS.AGENT_SETTING_ID=AGENT_GLOBAL_SETTINGS.ID INNER JOIN

HW_CATEGORIES ON AGENTS.AGENT_TYPE_ID=HW_CATEGORIES.ID WHERE AGENTS.ID ='123-1-260194' Why Oracle can not reuse the same cursor in the same session of the same user?

Thanks.
Sergei. Received on Mon Feb 10 2003 - 20:39:58 CST