Re: Open cursor error

Did the OP ever aggregate the open cursors by type to see if that supplied a pointer to how the "excess" was appearing.

It's an unfortunate feature of more modern versions of Oracle that the contents of v$open_cursor (for a single session) can easily exceed the settingof open_cursors by a factor of 2 (or more) because of the way many different cursor types appear in the view. (Including pl/sql cursors, session cached cursors, and dictionary cache cursors - as well as the basic "OPEN" cursors).

Regards
Jonathan Lewis

On Thu, 17 Aug 2023 at 20:55, Andy Sayer <andysayer_at_gmail.com> wrote:

> 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