Re: [External] : Re: Open cursor error

Not sure about others but whatever databases we have , I don't see the number of cursor_type "OPEN" to be in such a high range like ~300. Not sure what other downside of the higher open cursor but at least "Memory" appears to be one of the legitimate resources which should be counted as a key performance parameter to be impacted.

On Sat, Aug 19, 2023 at 10:03 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you very much Gerald, Jonathan ,Andy and Lok. This is really great
> information for me to debug the open cursor error in a systematic way.
>
> Actually as Jonathan mentioned, when I categorized based on cursor_type I
> am seeing ~85% are under type "OPEN". I am not sure if we should be really
> concerned over this, but I believe each open cursor must be consuming some
> memory and thus this should be looked into as an optimization opportunity
> and the buggy code should get fixed. And this can be used as an
> alerting/monitoring mechanism across other applications/databases too.
> Correct me if I'm wrong.
>
> Ofcouse, currently i dont have the session info which was showing to be
> consuming ~1000 open_cursor during the issue period and i am not sure if
> that information(or any such trend of the CURSOR_TYPE) can be retrieved by
> anyway from any AWR/DBA_HIST* view. However, as Jonathan mentioned, when I
> currently check the same database and try to group the sessions by the
> cursor_type from v$open_cursor( as there are still ~40 different sessions
> each holding ~300+ open_cursors count from the same application user
> account). The top category is coming as "OPEN" only, which probably means
> the real issue is there in the code.
>
> Below is the breakup of one of the top ones which is showing to be
> consuming ~342 "OPEN_CURSORS" and out of that ~299 are in the category type
> "OPEN" . So I believe we have real issues in the code. Correct me if I'm
> wrong.
>
> As Gerald mentioned, Out of all the sessions , the max value of "SESSION
> CURSOR CACHED" is showing as 36. I believe this is not an issue.
>
> Break up of "342" total Open cursor from one of the session_id :-
>
>
> *SID USER_NAME CURSOR_TYPE OPEN_CURSORS*854 XX_APP
> OPEN-RECURSIVE 6
> 854 XX_APP SESSION CURSOR CACHED 13
> 854 XX_APP PL/SQL CURSOR CACHED 24
> *854 ** XX_APP OPEN 299*
>
> I am yet to reachout to Java/appdev and look into the exact piece of code
> which is causing this.
>
> Thanks and Regards
> Pap
>
>
> On Fri, Aug 18, 2023 at 7:07 AM Gerald Venzl <dmarc-noreply_at_freelists.org>
> wrote:
>
>> 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,
>>
>>
>> ---------------
>>
>> Gerald Venzl | Senior Director | Product Management
>> Email: gerald.venzl_at_oracle.com
>> Oracle ST & Database Development
>> 400 Oracle Parkway | Redwood Shores | 94065 | USA
>>
>> 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> 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
>> <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