Re: Open cursors

Daniel,

Oracle version is 9.2.0.1, tested on W2k platform. CURSOR_SHARING is set to EXACT.

The statement is exactly the same (it's sort of daemon waiting for incoming requests and scanning the table with the same SELECT statement).
I tried also the CURSOR_SHARING=SIMILAR, but it did not help - the statment text become to look like:

SELECT ...
WHERE AGENTS.ID =:SYS...something...

and it was still huge number of the same statments. I did not tried FORCE. So it looks like Java application does not close result sets (or statement, or both), which prevents Oracle to reuse cursor in both cases.

Thanks.
Sergei.

danielroy10_at_hotmail.com (Daniel Roy) wrote in message news:<1b061893.0302110621.171c0ce6_at_posting.google.com>...
> 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 - 12:36:21 CST