==========
General
==========
Error ORA-01000 Too many open cursors is coming, and it is not clear why.
Code below is logging number of cursors for current session, and can be called from various places in code.
CREATE SEQUENCE DEBUG_OPN_CURSORS_ID_SEQ NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE;
CREATE TABLE DEBUG_OPEN_CURSORS (
entry_id NUMBER,
sid NUMBER,
machine VARCHAR2(64 BYTE),
user_name VARCHAR2(30 BYTE),
sql_id VARCHAR2(13 BYTE),
sql_text VARCHAR2(1000 BYTE),
open_cursors NUMBER,
comments VARCHAR2(1000 BYTE)
)
TABLESPACE IGT_TABLE ;
AS SYS !!
GRANT SELECT ON SYS.V_$OPEN_CURSOR TO SPARX_700;
GRANT SELECT ON SYS.V_$SESSION TO SPARX_700;
GRANT SELECT ON SYS.V_$SQLAREA TO SPARX_700;
-----------------------
--procedure write_cursors
-----------------------
create or replace procedure write_cursors(p_comments IN VARCHAR2, p_username IN VARCHAR2) is
pragma autonomous_transaction;
begin
INSERT INTO DEBUG_OPEN_CURSORS (entry_id, sid, machine, user_name, sql_id, sql_text, open_cursors, comments)
SELECT DEBUG_OPN_CURSORS_ID_SEQ.nextval, sid, machine, user_name, TOP_OPEN_CURSORS_SESSIONS.sql_id, V$SQLAREA.sql_text, open_cursors, p_comments
FROM (
--Get sessions with open cursors
SELECT SESS.sid sid,
SESS.machine machine,
OPEN_CURS.user_name user_name,
OPEN_CURS.sql_id sql_id,
count(*) open_cursors
FROM v$open_cursor OPEN_CURS,
v$session SESS
WHERE OPEN_CURS.saddr = SESS.saddr
AND SESS.username IS NOT NULL AND SESS.username NOT IN ('SYS')
AND (p_username IS NULL OR (p_username IS NOT NULL AND SESS.username = p_username ))
AND OPEN_CURS.user_name IS NOT NULL AND OPEN_CURS.user_name NOT IN ('SYS')
AND (p_username IS NULL OR (p_username IS NOT NULL AND OPEN_CURS.user_name = p_username ))
GROUP BY SESS.sid, SESS.machine, OPEN_CURS.user_name, OPEN_CURS.sql_id
HAVING COUNT(1) > 1
) TOP_OPEN_CURSORS_SESSIONS,
V$SQLAREA
WHERE TOP_OPEN_CURSORS_SESSIONS.sql_id = V$SQLAREA.sql_id(+)
;
commit;
end write_cursors;
-----------------------
BEGIN
write_cursors('Step A', 'MY_SCHEMA');
write_cursors('Step B', 'MY_SCHEMA');
write_cursors('Step C', 'MY_SCHEMA');
END;
/
====================
Get SQL Text of current running SQLs
====================
SELECT sql_text, sql_id, executions, fetches, end_of_fetch_count
FROM V$SQLAREA
WHERE sql_id IN (
SELECT NVL(sql_id,prev_sql_id) sql_id
FROM (
SELECT V_SESSTAT.value OPEN_CURSORS,
V_SESSION.username,
V_SESSION.sid||'-'||V_SESSION.serial# sid_serial,
V_PROCESS.spid,
V_SESSION.machine,
V_SESSION.program,
V_SESSION.sql_id,
V_SESSION.prev_sql_id
FROM V$SESSTAT V_SESSTAT,
V$STATNAME V_STATNAME,
V$SESSION V_SESSION,
V$PROCESS V_PROCESS
WHERE V_SESSTAT.statistic# = V_STATNAME.statistic#
AND V_PROCESS.addr=V_SESSION.paddr
AND V_SESSTAT.sid = V_SESSION.sid
AND V_STATNAME.name = 'opened cursors current'
AND V_SESSTAT.value > 1
)
)
Executions - Total number of executions, totalled over all the child cursors
Fetches - Number of fetches associated with the SQL statement
End of Fetch Count - Number of times this cursor was fully executed
The value of this statistic is not incremented when the cursor is partially executed,
either because it failed during the execution or
because only the first few rows produced by this cursor are
fetched before the cursor is closed or re-executed.
================================
A script to sample open cursors per session
================================
gen_cursors_list.sql
COL sid FOR A10
COL machine FOR A30
COL program FOR A30
col USER_NAME FOR A30
COL sql_id FOR A20
COL sql_text FOR A200
COL run_date FOR A20
COL open_cursors FOR 99999999
SET PAGESIZE 1000
SET LINESIZE 1000
SET heading on
SET feedback off
spool open_cursors_list.txt append
SELECT sid,
spid,
open_cursors,
machine,
program,
user_name,
TOP_OPEN_CURSORS_SESSIONS.sql_id,
V$SQLAREA.sql_text,
TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') as run_date
FROM (
--Get sessions with open cursors
SELECT SESS.sid sid,
PROC.spid spid,
SESS.machine machine,
SESS.program program,
OPEN_CURS.user_name user_name,
OPEN_CURS.sql_id sql_id,
count(*) open_cursors
FROM v$open_cursor OPEN_CURS,
v$session SESS,
v$process PROC
WHERE OPEN_CURS.saddr = SESS.saddr
AND SESS.username IS NOT NULL AND SESS.username NOT IN ('SYS')
AND OPEN_CURS.user_name IS NOT NULL AND OPEN_CURS.user_name NOT IN ('SYS')
AND PROC.addr=SESS.paddr
GROUP BY SESS.sid, PROC.spid, SESS.machine, SESS.program, OPEN_CURS.user_name, OPEN_CURS.sql_id
HAVING COUNT(1) > 10
) TOP_OPEN_CURSORS_SESSIONS,
V$SQLAREA
WHERE TOP_OPEN_CURSORS_SESSIONS.sql_id = V$SQLAREA.sql_id(+);
spool off
EXIT;
gen_cursors_list.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus DEU_TDGQQ_SBQQQ/"Deu_#123_SBQQQ$"@rraf @gen_cursors_list.sql
