Re: Open cursors

spohilko_at_yahoo.com (Sergei) wrote in message news:<b50f7e10.0302101839.676f844e_at_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.

Sergei,

As I understand, a cursor is a handle to a "private SQL area", so it can not be shared even in the same session. It's the "shared SQL area" that can be shared. In theory, if you have a multithread application that shares the connection among threads, you could have multiple cursors opened for the same SQL statement in the same session. But I guess it's not likely that you have 1330 threads running at same time. So it looks like somewhere in your Java application a Statement or maybe a ResultSet is not closed. I'd look for that specific SQL statement in the application source code and make sure the statement/ResultSet is closed after being used.

Regards,
Mike Received on Tue Feb 11 2003 - 16:07:39 CST