Re: Open cursor error

"50 to 100 open cursors are fine as long as they are getting reused."

Is this correct to assume, that the above statement is true for a running/live transactions or an active session. However if a transaction is completed sending the connection back into the connection pool and leaving the database session in inactive state, then the open cursor count should be zero for that session. Is my understanding correct?

In our case I see lot of inactive sessions having non zero (50-100) open cursor count , so it must mean, we really have bugs in our app code in Java with regards to cursor open/close. Correct me if I'm wrong.

On Sun, 13 Aug, 2023, 12:46 am Andy Sayer, <andysayer_at_gmail.com> wrote:

> 50 to 100 open cursors are fine as long as they are getting reused. The
> expected number depends on the amount of different SQL your application is
> expected to run. The problem comes when the application keeps opening the
> same cursor without closing and without reusing it.
>
> Thanks,
> Andy
>
> On Sat, Aug 12, 2023 at 11:29 AM, Pap <oracle.developer35_at_gmail.com>
> wrote:
>
>> 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