Re: Open cursor error

Hello Andy,

Out of curiosity, OP has mentioned below i.e. , there were many sessions holding 300+ open cursors but the top one was showing up with ~1000 open cursors and as you stated it might be that the ~300 open cursor session's might be part of normal processes but the top one/session with ~1000 open cursor might be generated out of a buggy code. However the OP also mentioned, when the top session was killed the rerun of the job rerun succeeded. So I was wondering , this max open_cursor limit parameter is at session level(but not at database level), so howcome by killing one of the existing top sessions helped the other session/or rerun of the job to succeed?

*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.*

On Sun, Aug 13, 2023 at 3:45 AM Andy Sayer <andysayer_at_gmail.com> wrote:

> “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?”
> No, the cursor can remain cached by the session ready for when the client
> (or another via the pool) picks up the session.
>
> From your first email, only one session had managed to open 1000 cursors
> without closing them. I would only be suspicious of the application code
> behind that one session. The fixed view for open cursors would have told
> you which queries have multiple open cursors which should allow you to
> narrow down the code that you look at.
>
> Thanks,
> Andy
>
> On Sat, Aug 12, 2023 at 1:54 PM, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> "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