Re: Open cursors

Oracle version please?

Look up value and info on parameter CURSOR_SHARING (can be set as "FORCE" or "EXACT"). Are your SQL statements EXACTLY the same (same values, same case for each character, same number of spaces, ...)?

Daniel

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.
Received on Tue Feb 11 2003 - 08:21:01 CST