Re: [External] : Re: Open cursor error

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Tue, 29 Aug 2023 18:56:53 +0100
Message-ID: <CALe4HpnJSkdzY5E8znm3v2aX4s5q3GaTLRKKgrd6ZgGXDQCYBQ_at_mail.gmail.com>

+1 to Stefan's suggestion about the dump event. I would add that 19.16 includes the fix for Bug 32216841 - Diagnostic Enhancement for ORA-1000 (Doc ID 32216841.8)

Running the following code on my 19.20 database: SQL> sho parameter open_curs

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
open_cursors                         integer     300

SQL> select xmltype(cursor(select n from dual)) from xmltable('1 to 1000' columns n int path '.');

*The DB alert log has:*
2023-08-29T17:52:17.500697+00:00
PDB(3):Maximum Open Cursors Exceeded in Process 23159 .

*The trace file already contains cursor dump (so that no additional event might be needed from 19.16 onwards):*
DDE rules only execution for: ORA 1000
Maximum Open Cursors(300) has been exceeded. Please review the list of cursors in the following output.

• If the same SQL statement is seen across different cursors the cause may be a cursor leak in the application.
• If this is not the case then:
  • the open_cursors parameter may not be set high enough -OR-
  • cursors are not being closed off in a timely manner.

OPEN CURSOR DUMP - START
Cursor#1 xsc=0x7f80866d6140 cur=0x7f80867bc538 ParentLocked=1 ddl=0
---- sql_id=9zg9qd9bm4spu ---

update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:
1
Cursor#8 xsc=0x7f80865ee660 cur=0x7f80867bc998 ParentLocked=1 ddl=0
---- sql_id=ggxyccdskkkcd ---

SELECT CAST(TO_NUMBER(:CV1$) AS int ) "N" FROM "SYS"."DUAL" "A2" Cursor#9 xsc=0x7f80865e9708 cur=0x7f80867bca38 ParentLocked=1 ddl=0 ...skipped lots of lines
---- sql_id=ggxyccdskkkcd ---

SELECT CAST(TO_NUMBER(:CV1$) AS int ) "N" FROM "SYS"."DUAL" "A2" Cursor#3 xsc=0x7f80866d2a98 cur=0x7f80867bc678 ParentLocked=1 ddl=0
---- sql_id=6m0kcd90vdhf6 ---

select xmltype(cursor(select n from dual)) from xmltable('1 to 1000' columns n int path '.')
...

On Tue, 29 Aug 2023 at 18:43, Stefan Koehler <contact_at_soocs.de> wrote:

> To be honest I did not follow this thread completely and so I might have
> missed something but why not just setting up a trap (e.g. something like
> this "SQL> alter system set events '1000 trace name cursordump level 1,
> lifetime 1';") and wait for next occurrence?
>
> Thanks.
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: www.soocs.de
> Twitter: _at_OracleSK
>
> > Jonathan Lewis <jlewisoracle_at_gmail.com> hat am 29.08.2023 19:28 CEST
> geschrieben:
> >
> >
> > I don't think there's anything that would have recorded the information
> you need. You might get lucky and spot a pointer to the guilty SQL_ID in
> the active session history. If you know when the problem happened and query
> dba_hist_active_sess_history for the 30 minutes (say) before the ORA-01000,
> aggregating by session_id and sql_id, then drill down on the high-frequenty
> sql_ids (for each session in turn) to see if each occurence of the sql_id
> shows a difference sql_exec_id, you may be lucky and find a session which
> executes a specific statement a large number of times. That MIGHT be the
> session (and statement) that got the ORA-01000 by not closing the cursor
> after executing it.
> >
> > The trouble with this approach is that the dba_hist samples are every 10
> seconds, so you'd have to be a bit lucky for this approach to give you any
> solid evidence, but I can't think of any alternatives for searching the
> past.
> >
> > Regards
> > Jonathan Lewis
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 29 2023 - 19:56:53 CEST