ORA-01000: maximum open cursors exceeded due to “Cursor leak”
The maximum open cursors (handles to private SQL areas) that a session can have at once are specified by OPEN CURSORS. This setting allows you to limit the amount of cursors that a session opens.
It’s crucial to increase the value of OPEN CURSORS so that your application doesn’t run out of open cursors. Depending on the use, the quantity will change. There is no additional overhead to setting this value higher than necessary, assuming that a session does not open the number of cursors indicated by OPEN CURSORS.
- Application executes a number of SQL statements, that uses the Oracle Database as a backend repository.
- In Oracle Database, a specific amount of memory is allocated for each SQL statement that is executed.
- Context area or cursor are the names for this private SQL space.
- These cursors consume memory in the library cache of Oracle Database’s shared pool, a crucial memory component.
- The OPEN CURSORS database parameter must be set to limit the number of cursors in order to prevent a renegade session from overloading the CPU with millions of parse requests or filling up the library cache.
- The maximum number of cursors that can be open during each session is determined by the OPEN CURSORS option.
- Example: A session may have up to 300 open cursors at once, for instance, if the value of OPEN CURSORS is set to 300.
ORA-01000 error code is thrown, when an individual session reaches the hard limit for open cursors as per open_cursors parameter. Default value is 300.
declare
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 18Check the existing value of the open cursor parameter
kIsH@Xhydra<>show parameter open_cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 200
A particular procedure executes for a long time without closing the cursor and hence open cursor reached 190. Limit is only 200. Hence some times it may be even application code fault of not efficiently closing the cursor.
kIsH@Xhydra<>set lines 200 pages 1000
col name for a30
col current_value for 9999999999999
col total_value for a20
select st.name,
max(ses.value) "current_value",
p.value "total_value",
round(100 * (max(ses.value) / p.value)) "PCT"
from v$statname st
inner join v$sesstat ses on st.statistic# = ses.statistic#
cross join v$parameter p
where p.name='open_cursors'
and
st.name='opened cursors current'
group by st.name,p.value;kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<> 2 3 4 5 6 7 8 9 10 11
NAME current_value total_value PCT
------------------------------ -------------- -------------------- ----------
opened cursors current 190 200 95Filter the SQL which consume many cursor
kIsH@Xhydra<>select sid,
sql_id,
count(*) ocursors
from v$open_cursor
group by sid,sql_id
having count(*) > 3
order by count(*) desc; 2 3 4 5 6 7
SID SQL_ID OCURSORS
---------- ------------- ----------
92 f3q3fhgz2qmhk 160
34 16jd1y3vpvfbt 8
34 avvfapa4gfgc4 4
34 66tyyrdwrbmx7 4
34 0qv27wt724y7s 4
--After the error is triggered, the job is failed and cursors are released
kIsH@Xhydra<>select sid,
sql_id,
address,
count(*) ocursors
from v$open_cursor
group by sid,sql_id,address
having count(*) > 3
order by count(*) desc;
2 3 4 5 6 7 8
SID SQL_ID ADDRESS OCURSORS
--------- ------------- -------------------- ----------
41 f3q3fhgz2qmhk 0000000071C97F00 48
42 16jd1y3vpvfbt 000000006ADCC9B0 4
374 frccccnh76gtx 0000000068D94A58 4
347 aj14ngnvpkk6t 000000006828E168 4
347 bxpcry2tpc217 0000000065B09E68 4
Identify the sql text which is responsible. This statement should tuned in terms of PLSQL procedure calls for consuming less cursors.
kIsH@Xhydra<>select sql_text from v$sql where sql_id='f3q3fhgz2qmhk';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A2"."PK" "PK","A2"."FK" "FK" FROM "OC" "A2" WHERE :CV1$="A2"."FK"
SELECT "A2"."PK" "PK","A2"."FK" "FK" FROM "OC" "A2" WHERE :CV1$="A2"."FK"
Trace file shows the stacks of the failing procedure which can be utilized to identify a potential bugs which are related to the error.
ORA-01000: maximum open cursors exceeded
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x6e789520 18 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+95 call kgdsdst() 7FFCC5E8B910 000000002
7FFCC5E85C40 ? 7FFCC5E85D58 ?
000000000 000000082 ?
ksedst()+58 call ksedst1() 000000000 000000001
7FFCC5E85C40 ? 7FFCC5E85D58 ?
000000000 ? 000000082 ?
dbkedDefDump()+2308 call ksedst() 000000000 000000001 ?
0 7FFCC5E85C40 ? 7FFCC5E85D58 ?
000000000 ? 000000082 ?
ksedmp()+577 call dbkedDefDump() 00000000C 000000000
7FFCC5E85C40 ? 7FFCC5E85D58 ?
000000000 ? 000000082 ?
dbkdaKsdActDriver() call ksedmp() 00000000C 000000000 ?
+2484 7FFCC5E85C40 ? 7FFCC5E85D58 ?
000000000 ? 000000082 ?
dbgdaExecuteAction( call dbkdaKsdActDriver() 7F324A4846D0 7FFCC5E8DDA0
)+354 7FFCC5E85C40 ? 7FFCC5E85D58 ?
000000000 ? 000000082 ?
dbgdaRunAction()+76 call dbgdaExecuteAction( 7F324A4846D0 0140B5E00
2 ) 044174BC0 7FFCC5E8DDA0
000000001 000000082 ?
dbgdRunActions()+83 call dbgdaRunAction() 7F324A4846D0 7FFCC5E8E020
7F324A4846D0 000000000
7FFCC5E8E028 000000082 ?
dbgdProcessEventAct call dbgdRunActions() 7F324A4846D0 ? 7FFCC5E8E020 ?
ions()+525 7F324A4846D0 ? 000000000 ?
7FFCC5E8E028 ? 000000082 ?
dbgdChkEventKgErr() call dbgdProcessEventAct 7F324A4846D0 7F324A4C49A0
+352 ions() 7F3244174E58 000000000 ?
7FFCC5E8E028 ? 000000082 ?
dbkdChkEventRdbmsEr call dbgdChkEventKgErr() 7F324A4846D0 ? 7F324A4C49A0 ?
r()+65 7F3244174E58 ? 000000000 ?
7FFCC5E8E028 ? 000000082 ?
dbgePostErrorKGE()+ call dbkdChkEventRdbmsEr 7F324A4846D0 ? 0000003E8
1066 r() 7F3244174E58 ? 000000000 ?
7FFCC5E8E028 ? 000000082 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 7F324A4C49A0 7F3244E0DA20
71 0000003E8 000000000 ?
7FFCC5E8E028 ? 000000082 ?
kgeade()+392 call dbkePostKGE_kgsf() 7F324A4C49A0 7F3244E0DA20
0000003E8 000000000 ?
7FFCC5E8E028 ? 000000082 ?
kgeselv()+89 call kgeade() 7F324A4C49A0 ? 7F324A4C4BE8 ?
7F3244E0DA20 ? 0000003E8 ?
000000000 7F3200000000
ksesecl0()+189 call kgeselv() 7F324A4C49A0 ? 7F3244E0DA20 ?
0000003E8 ? 013D1226C
013D1227C 000000000
kxsOpenACursor()+88 call ksesecl0() 7F324A4C49A0 ? 7F3244E0DA20 ?
4 0000003E8 ? 000000000
7F324A5153C0 7F324A3E2570
opiopr()+228 call kxsOpenACursor() 7F324A4C49A0 ? 000000000 ?
0000003E8 ? 000000000 ?
7F324A5153C0 ? 7F324A3E2570 ?
opiodr()+1202 call opiopr() 7F324A4C49A0 ? 000000000 ?
0000003E8 ? 000000000 ?
7F324A5153C0 ? 7F324A3E2570 ?
rpidrus()+198 call opiodr() 000000064 000000003
0000003E8 ? 000000000
7F324A5153C0 ? 7F324A3E2570 ?
skgmstack()+65 call rpidrus() 7FFCC5E8FA38 000000003 ?
7F324A4C4BE8 000000000 ?
7F324A5153C0 ? 7F324A3E2570 ?
rpidru()+132 call skgmstack() 7FFCC5E8FA38 ? 000000003 ?
7F324A4C4BE8 ? 000000000 ?
7F324A5153C0 ? 7F324A3E2570 ?
rpiswu2()+541 call rpidru() 7FFCC5E902C8 ? 000000003 ?
7F324A4C4BE8 ? 000000000 ?
7F324A5153C0 ? 7F324A3E2570 ?
rpidrv()+1248 call rpiswu2() 7FFCC5E902C8 ? 000000003 ?
7F324A4C4BE8 ? 000000000 ?
7F324A5153C0 ? 7F324A3E2570 ?
rpiope()+65 call rpidrv() 000000000 000000064
7FFCC5E903C0 000000019
7F324A5153C0 ? 7F324A3E2570 ?
kokbenc()+115 call rpiope() 004000401 000000064 ?
7FFCC5E903C0 ? 000000019 ?
7F324A5153C0 ? 7F324A3E2570 ?
evaopn2()+737 call kokbenc() 072636BF0 ? 000000064 ?
7FFCC5E903C0 ? 000000019 ?
7F324A5153C0 ? 7F324A3E2570 ?
opifcr()+552 call evaopn2() 072636BF0 ? 000000064 ?
7FFCC5E903C0 ? 000000019 ?
7F324A5153C0 ? 7F324A3E2570 ?
kdstf00001010000000 call opifcr() 072636BF0 ? 000000064 ?
0km()+661 7F324A4C49A0 000000019 ?
7F324A5153C0 ? 7F324A3E2570 ?
kdsttgr()+2214 call kdstf00001010000000 7F3244177040 000000000
0km() 0123FF4C0 7FFCC5E92620
000000001 7FFCC5E915F8
qertbFetch()+1089 call kdsttgr() 7F3244177040 ? 000000000 ?
000000000 ? 7FFCC5E92620 ?
000000001 ? 7FFCC5E915F8 ?
opifch2()+3206 call qertbFetch() 7F3244177040 ? 000000000 ?
0123FF4C0 7FFCC5E92620 ?
000000001 ? 7FFCC5E915F8 ?
opifch()+61 call opifch2() 7F3244177040 ? 000000000 ?
7FFCC5E927B0 7FFCC5E92620 ?
000000001 ? 7FFCC5E915F8 ?
opipls()+5554 call opifch() 7F3244177040 ? 000000000 ?
7FFCC5E927B0 ? 000000001
000000000 012400002
opiodr()+1202 call opipls() 7F3244177040 ? 000000000 ?
7FFCC5E94F60 000000001 ?
7F324A3E2570 012400002 ?
rpidrus()+198 call opiodr() 000000066 000000004
7FFCC5E94F60 ? 000000002
7F324A3E2570 ? 012400002 ?
skgmstack()+65 call rpidrus() 7FFCC5E93C98 000000004 ?
7F324A4C4BE8 000000002 ?
7F324A3E2570 ? 012400002 ?
rpidru()+132 call skgmstack() 7FFCC5E93C98 ? 000000004 ?
7F324A4C4BE8 ? 000000002 ?
7F324A3E2570 ? 012400002 ?
rpiswu2()+541 call rpidru() 7FFCC5E94528 ? 000000004 ?
7F324A4C4BE8 ? 000000002 ?
7F324A3E2570 ? 012400002 ?
rpidrv()+1248 call rpiswu2() 7FFCC5E94528 ? 000000004 ?
7F324A4C4BE8 ? 000000002 ?
7F324A5153C0 ? 012400002 ?
psddr0()+467 call rpidrv() 000000002 000000066
7FFCC5E94F60 000000039
7F324A5153C0 ? 012400002 ?
psdnal()+624 call psddr0() 000000002 000000066
7FFCC5E94F60 000000030
7F324A4C4BE8 000000001
pevm_BFTCHC()+314 call psdnal() 7FFCC5E95EC0 000000066 ?
000000002 7F324417DBE8
000000000 ? 000000001 ?
pfrinstr_FTCHC()+13 call pevm_BFTCHC() 7F3244174630 ? 7F324A3A6CA0
5 000000000 ? 7F324417DBE8 ?
000000000 ? 000000001 ?
pfrrun_no_tool()+52 call pfrinstr_FTCHC() 7F3244174630 ? 0724271E0
7F32441746A0 ? 7F324417DBE8 ?
000000000 ? 000000001 ?
pfrrun()+902 call pfrrun_no_tool() 7F3244174630 ? 0724271E0 ?
7F32441746A0 ? 7F324417DBE8 ?
000000000 ? 000000001 ?
plsql_run()+752 call pfrrun() 7F3244174630 0724271E0 ?
7F32441746A0 ? 7FFCC5E95EC0
06F16732A 000000001 ?
peicnt()+282 call plsql_run() 7F3244174630 000000001
000000000 7FFCC5E95EC0 ?
06F16732A ? 000000001 ?
kkxexe()+720 call peicnt() 7FFCC5E95EC0 7F3244174630
000000000 ? 7FFCC5E95EC0 ?
06F16732A ? 000000001 ?
opiexe()+24817 call kkxexe() 7F324417E478 7F3244174630 ?
000000000 ? 7FFCC5E95EC0 ?
000000001 000000001 ?
kpoal8()+2387 call opiexe() 000000049 7F3244174630 ?
7FFCC5E97840 7FFCC5E95EC0 ?
000000001 ? 000000001 ?
opiodr()+1202 call kpoal8() 00000005E 000000026
7FFCC5E9B2B0 7FFCC5E95EC0 ?
000000001 ? 000000001 ?
ttcpip()+1222 call opiodr() 00000005E 000000026
7FFCC5E9B2B0 ? 000000000
000000001 ? 000000001 ?
opitsk()+1895 call ttcpip() 7F324A4EEB10 ? 0000005E0 ?
7FFCC5E9B2B0 000000000 ?
7FFCC5E9AD10 7FFCC5E9B4FC
opiino()+936 call opitsk() 000000000 000000000
7FFCC5E9B2B0 ? 000000000 ?
7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
opiodr()+1202 call opiino() 00000003C 000000004
7FFCC5E9CE98 000000000 ?
7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
opidrv()+1094 call opiodr() 00000003C 000000004
7FFCC5E9CE98 ? 000000000
7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
sou2o()+165 call opidrv() 00000003C 000000004
7FFCC5E9CE98 000000000 ?
7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
opimai_real()+422 call sou2o() 7FFCC5E9CE70 00000003C
000000004 7FFCC5E9CE98
7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
ssthrdmain()+417 call opimai_real() 000000000 7FFCC5E9D688
000000004 ? 7FFCC5E9CE98 ?
7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
main()+256 call ssthrdmain() 000000000 000000002
7FFCC5E9D688 000000001
000000000 7FFCC5E9B4FC ?
__libc_start_main() call main() 000000002 7FFCC5E9D898
+243 7FFCC5E9D688 ? 000000001 ?
000000000 ? 7FFCC5E9B4FC ?
_start()+46 call __libc_start_main() 000DA7880 000000002
7FFCC5E9D898 006A35C30 ?
000000000 ? 7FFCC5E9B4FC ?Failing stack show the function kxsOpenACursor() which calls ksesecl0()
========== FRAME [17] (kxsOpenACursor()+884 -> ksesecl0()) ==========
defined by frame pointers 0x7ffcc5e8ed40 and 0x7ffcc5e8ecc0
CALL TYPE: call ERROR SIGNALED: yes COMPONENT: cursor
RDI 00007F324A4C49A0 RSI 00007F3244E0DA20 RDX 00000000000003E8
RCX 0000000000000000 R8 00007F324A5153C0 R9 00007F324A3E2570
RAX 0000000000000000 RBX 0000000000000000 RBP 00007FFCC5E8ED40
R10 00007FFCC5E8EC90 R11 0000000000000000 R12 00007F324A5153C0
R13 0000000000000000 R14 00007F324A515301 R15 00007F324A3E2570
RSP 00007FFCC5E8ECD0 RIP 0000000012572AA4
Dump of memory from 0x7ffcc5e8ecc0 to 0x7ffcc5e8ed40
7FFCC5E8ECC0 C5E8ED40 00007FFC 12572AA4 00000000 [@........*W.....]
7FFCC5E8ECD0 4A3E2570 00007F32 00000000 00000000 [p%>J2...........]
7FFCC5E8ECE0 C5E8ED20 00007FFC 4A5153C0 00007F32 [ ........SQJ2...]
7FFCC5E8ECF0 44019E00 00007F32 6D0FE0C8 00000000 [...D2......m....]
7FFCC5E8ED00 4A5153C0 00007F32 7F7E6738 00000000 [.SQJ2...8g~.....]
7FFCC5E8ED10 7F7E6738 00000000 4A515301 00007F32 [8g~......SQJ2...]
7FFCC5E8ED20 4A4C49A0 00007F32 4A5153C0 00007F32 [.ILJ2....SQJ2...]
7FFCC5E8ED30 00000000 00000000 00000001 00000000 [................]Solution:
From DBA point of view, there are ways to increase the below parameter or try to tune the sql as per database version.
alter system set open_cursors=1000 scope=spfile;
or
alter system set optimizer_features_enable=12.1.0.2;Will keep on increase the parameter if the error occur recursively? That is not always feasible as a long term proactive fix! Hence schedule a call and discuss this with application team. Identify the session details and report the module of application with SQL statement to the developer.
kIsH@Xhydra<>col MODULE for a20
kIsH@Xhydra<>col ACTION for a20
kIsH@Xhydra<>col PROGRAM for a20
kIsH@Xhydra<>select sid,terminal,module,action,program from v$session where SID=35;
SID TERMINAL MODULE ACTION PROGRAM
---------- ------------------------------ -------------------- -------------------- -------------------- APP task44
92 UNKNOWN oracle@xhydra (JDBC Thin client)
But optimal solution is to close the application cursors properly in the code
Example: close cur;