Re: Open cursor error

Most simple explanation is the job process was reusing that session that had filled up on cursors.

Thanks,
Andy

On Thu, Aug 17, 2023 at 11:57 AM, Lok P <loknath.73_at_gmail.com> wrote:

> 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