Re: [External] : Re: Open cursor error

From: Gerald Venzl <"Gerald>
Date: Fri, 18 Aug 2023 01:35:53 +0000
Message-ID: <35DADCC3-AEE5-4ED7-8C28-9E2DE91CC9F9_at_oracle.com>

You may find the Additional Information on this page useful: https://docs.oracle.com/en/error-help/db/ora-01000/ Maria Colgan and I have just recently put that together after looking into the open cursor error conditions with Development.

Regarding

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?

Depending on how long a session has been idle, you may or may not find a session that has 0 open cursors. The parameter SESSION_CACHED_CURSORS<https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/SESSION_CACHED_CURSORS.html#GUID-E4FC0260-3C8A-4379-8143-6A4E6EE27A2F> determines how many (repeatedly executed) cursors should remain cached for faster access, with the default value of 50. This does not mean that you will always have up to 50 cursors/session, it just means that you may have up to 50 cached ones but these can stay around for quite a while. You can read more about sizing SESSION_CACHED_CURSORS in the Tuning Guide here: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-shared-pool-and-large-pool.html#GUID-C13EE398-F162-4A23-8393-393D71133004

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?

This Java code will cause open cursors until the Java garbage collection kicks in and cleans up these PreparedStatement objects. Depending on how much memory (pressure) you have, garbage collection can take a loooooong time to clean these objects up:

// 170 explicit open cursors
for (int i=1;i <=170; i++) {

PreparedStatement stmt_oc1 = conn_gerald_1.prepareStatement("SELECT 'open_" + i + "' FROM dual" ); stmt_oc1.executeQuery();

// Not gonna close my Statement, aka cursor here.
// stmt_oc1.close();

}

This code will explicitly close cursors:

for (int i=1;i <=170; i++) {

PreparedStatement stmt_oc2 = conn_gerald_2.prepareStatement("SELECT 'closed_" + i + "' FROM dual"); stmt_oc2.executeQuery();

// I close my cursor here.

stmt_oc2.close();
}

If you let the attached Java program run, you will see that the first output will always generate 171 open cursors while the latter will never reach that. It may reach about 50 (because of caching, etc.) or even less but it will never get up to 170. In my case, I end up with 52 cursors instead of 171:

/Library/Java/JavaVirtualMachines/jdk-17.0.4.1.jdk/Contents/Home/bin/java -Dfile.encoding=UTF-8 -classpath /Users/gvenzl/.m2/repository/com/oracle/database/jdbc/ojdbc11/23.2.0.0/ojdbc11-23.2.0.0.jar org.example.Main

Open cursors for SID 286: 171
Sleep for 5s.
Open cursors for SID 286: 52

Process finished with exit code 0

Thx,

On Aug 17, 2023, at 13:55, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

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<mailto: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<mailto: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?

On Sun, Aug 13, 2023 at 3:45 AM Andy Sayer <andysayer_at_gmail.com<mailto: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<mailto:oracle.developer35_at_gmail.com>> wrote: "50 to 100 open cursors are fine as long as they are getting reused."

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<mailto: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<mailto: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<mailto: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<mailto: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.

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<mailto: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<mailto: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<mailto: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<mailto: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<https://urldefense.com/v3/__http://b.name__;!!ACWV5N9M2RV99hQ!NP8Rl-O_vx-8jyQLs9aDopFyShEle5qpMpN1-FasoXX51aa8e1jG08Dah-RJy9yh-kV4LPvYM2HCbfDrIjfwMcot$> = '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




application/octet-stream attachment: Main.java

Received on Fri Aug 18 2023 - 03:35:53 CEST

This message: [ Message body ]
Next message: Tim Gorman: "ORA-01017 when DGMGRL observer re-authenticates primary during failover"
Previous message: Mark W. Farnham: "RE: Open cursor error"
Next in thread: Pap: "Re: [External] : Re: Open cursor error"
Reply: Pap: "Re: [External] : Re: Open cursor error"
Maybe reply: Gerald Venzl: "Re: [External] : Re: Open cursor error"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message