Re: Open cursor error

Thank you so much.

I will try to check with the appdev team regarding the same. However, one question comes to my mind is, is it true that , in an ideal scenario if the cursor has been closed properly without a miss in the application code, then the number of open cursors associated with a session after its back to connection pool (or say when it's lying inactive after completing the transaction) should always be zero? Because I checked multiple databases and even the sessions are lying inactive but the open cursor count for those sessions are always staying higher(10 to 50).

On Sat, Aug 12, 2023 at 2:34 PM Lok P <loknath.73_at_gmail.com> wrote:

> As Andy mentioned you probably have bugs in application code in Java. As I
> see in our databases the max open_cursor currently shows as <50 and we do
> have applications which are batch having dedicated type connections and
> also other ones that use connection pool too. As you mentioned in
> your case, the majority of sessions showing open cursor count as 300+ and
> the top one which you killed was already having 900+ so it looks higher to
> me. But yes every application is different so maybe others can comment on
> this figure though. However, as per my understanding , a JDBC resultSet is
> supported by a single cursor on the database. When close() is called on the
> ResultSet, the cursor is released and this close() should always be there
> in the final block always without a miss.
>
> However, I am not sure in the case of connection pool type of applications
> when connections released to pool and already have some of the open cursors
> from the previously served functionality, then the new invoke of the same
> connection/session will keep on adding to the open cursor count and can
> cause such failures. Again others can comment.
>
>
> On Sat, Aug 12, 2023 at 2:48 AM Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Thank you so much for the response.
>>
>> I can understand in plsql code when we define explicit cursors it has to
>> be closed or else it can cause increase in open cursor count. And as you
>> mentioned in exception blocks it has to be closed without miss. And I agree
>> that could be easily be missed in many cases by the developer.
>>
>> However as I mentioned in our cases this session was submitting
>> individual sqls directly from Java code but not from/through any plsql
>> blocks or procedures. So I was wondering what kind of code in Java can
>> cause such open cursor issue? Can you please guide me to some examples?
>>
>> And also during the issue period I saw almost all the session from the
>> application(from jobs thin client program) were having open cursor count
>> 300+ and top one was showing 999(which we killed). Are these number of open
>> cursor count for individual sessions, normal or as you said there is
>> definitely a cursor leak bug in the Java code?
>>
>> On Fri, 11 Aug, 2023, 7:17 pm Andy Sayer, <andysayer_at_gmail.com> wrote:
>>
>>> You have a cursor leak bug in the code that used the session. Your job
>>> was reusing that session.
>>>
>>> I find that most of the time, cursor leaks come from explicitly opening
>>> and closing cursors in the main code body but not handling open cursors
>>> when exceptions are raised.
>>>
>>> Thanks,
>>> Andy
>>>
>>> On Fri, Aug 11, 2023 at 3:09 AM, yudhi s <learnerdatabase99_at_gmail.com>
>>> wrote:
>>>
>>>> Thank you.
>>>>
>>>> I was tring to see how the "opened cursors" count is increasing for
>>>> the session, so i tried running the simple statement as below by connecting
>>>> to a sql plus session.
>>>>
>>>> Create table test (c1 varchar2(4000));
>>>>
>>>> Insert into test values('AB');
>>>> Insert into test values('CD');
>>>> Insert into test values('EF');
>>>> Insert into test values('XX');
>>>> Insert into test values('YY');
>>>>
>>>> Then running the stats from gv$sesstat doesnt show a increase in the
>>>> 'opened cursors current' after each Insert/DML statement irrespective of
>>>> COMMIT. Which means its not true that each DML in a session caused a "open
>>>> cursor" for that session.
>>>>
>>>> So it seems this stats "opened cursors current" depends on the normal
>>>> explicit CURSOR declaration in which we have to open and then close the
>>>> cursor explicitly in plsql block/procedure. But then I am wondering how its
>>>> caused the query submitted directly from Java to have those 1000+ "open
>>>> cursor"?
>>>>
>>>> Regards
>>>>
>>>> Yudhi
>>>>
>>>> On Fri, 11 Aug, 2023, 1:18 am Lok P, <loknath.73_at_gmail.com> wrote:
>>>>
>>>>> Can't answer why killing the top session helped fixing the issue.
>>>>> However I believe each SQL statements creates a cursor in memory and would
>>>>> be closed implicitly once it's done. I am not sure if it's kept open till
>>>>> commit happen.
>>>>>
>>>>> But as you mentioned the SQL statement or the session which was
>>>>> consuming 999 open cursor was coming on top was from Java but not any plsql
>>>>> procedure. So it may be something like opening the connection but not
>>>>> closing etc.
>>>>>
>>>>> On Thu, 10 Aug, 2023, 12:50 am Pap, <oracle.developer35_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hello Experts,
>>>>>> We have this database with Oracle version 19C.We encounter below
>>>>>> error Ora-01000 for one of the application jobs even though we have not
>>>>>> changed anything in the code recently. Then as this parameter
>>>>>> "open_cursors" description says "max # cursors per session" and we have set
>>>>>> it as ~1000. We thought , by just rerunning the job may make the job
>>>>>> succeed , as because this limit was for the specific SESSION and killing
>>>>>> and rerunning the job might grab a new session and thus will make the job
>>>>>> succeed. But rerunning again made the job fail with the same error.
>>>>>>
>>>>>> ORA-01000: maximum open cursors exceeded
>>>>>>
>>>>>> Then we ran the below query on "GV$*" views and found one of the top
>>>>>> sessions was showing the 'opened cursors current' VALUE as ~999 and other
>>>>>> followed sessions were having values in the range of ~300. And that
>>>>>> specific top session was showing as "inactive" though in v$session but the
>>>>>> sql it was showing from a different job altogether. But still we killed
>>>>>> that session. And then reran the job which was failing with that Ora-01000
>>>>>> and surprisingly that succeeded.
>>>>>>
>>>>>> So I have a few questions here,
>>>>>> 1)As this parameter "open_cursors" is a limit for a specific session
>>>>>> but not at the database level, So how come killing an existing session
>>>>>> makes the new session/job successful?
>>>>>> 2)Though we have the query which the top session was pointing to,
>>>>>> that was inactive, and was directly submitted from Java code and also that
>>>>>> code was not changed recently. So how can that query cause Ora-01000? Or Is
>>>>>> there any other way to debug and find more info for the root cause and fix
>>>>>> for the Ora-1000 error from the AWR/dba_hist* views? Want to ensure this
>>>>>> error should not appear again in future.
>>>>>>
>>>>>>
>>>>>> SELECT a.VALUE,
>>>>>> s.username,
>>>>>> s.sid,
>>>>>> s.serial#
>>>>>> FROM gv$sesstat a, gv$statname b, gv$session s
>>>>>> WHERE a.statistic# = b.statistic#
>>>>>> AND s.sid = a.sid
>>>>>> AND b.name = 'opened cursors current'
>>>>>> AND b.inst_id = s.inst_id
>>>>>> AND s.inst_id = 1
>>>>>> AND s.inst_id = b.inst_id
>>>>>> order by a.value desc
>>>>>>
>>>>>

--
http://www.freelists.org/webpage/oracle-l