Oracle 19c Open_cursor exceeded issue

Question

We have same stored procedure exist in Oracle 10g and 19c with same set of data and setup. The procedure does so many data fetching and manipulation. When we execute with same set of data(let say 10000 records) it works fine in 10g with less time but in 19c it takes much time and after some time it throws "Open cursor limit exceeded" error. We did the basic comparison from both the data bases for OPEN_CURSOR & CACHED_CURSOR size which is same.

What else parameters or settings we can compare from server side so as to resolve this issue?

Answer 1

I can't tell you what is causing your maximum open cursors problem, but I tell you how to find the cause by identifying the related sessions and SQL statement using GV$OPEN_CURSOR.

If you're lucky you can find the problem immediately with a simple query that counts the number of open cursors per session. There are a lot of columns in the below query, use an IDE so you can easily browse all the data. In my experience, just glancing at columns like USER_NAME and SQL_TEXT is enough to identify the culprit.

select count(*) over (partition by inst_id, sid) cursors_per_session, gv$open_cursor.*
from gv$open_cursor
order by cursors_per_session desc, inst_id, sid;

Keep in mind that there will be many strange queries in that view that may make the counts larger than you anticipated. With all the recursive and cached queries, it's not unusual to have a "boring" session use 50 cursors. You're looking for sessions with hundreds of open cursors. (Unless someone foolishly lowered the parameter value below the default.)

Unfortunately, GV$OPEN_CURSOR does not contain historical data, and these problems can start and stop quickly if there's an exception inside a tight loop that quickly opens lots of cursors. The below PL/SQL block runs until it finds a session with a large number of open cursors, stores the data, and exits. This PL/SQL block is expensive, and will use up an entire session of processing waiting for the right moment, so only use it once to find the problem.

--Create table to hold the results.
create table too_many_cursors as
select 1 cursors_per_session, gv$open_cursor.*
from gv$open_cursor
where 1 = 0;


--Write the open cursor data when a session gets more than N open cursors.
declare
    v_open_cursor_threshold number := 50;
    v_count number;
begin
    --Loop forever until the problem is found.
    loop
        --Count the largest numbe of open cursors.
        select max(the_count)
        into v_count
        from
        (
            select count(*) the_count
            from gv$open_cursor
            group by inst_id, sid
        );

        --If the threshold is reached, write the data, commit it, and quit the program.
        if v_count >= v_open_cursor_threshold then

            insert into too_many_cursors
            select *
            from
            (
                select count(*) over (partition by inst_id, sid) cursors_per_session, gv$open_cursor.*
                from gv$open_cursor
            )
            where cursors_per_session >= v_open_cursor_threshold;
            
            commit;
            
            exit;
        end if;
        
    end loop;
end;
/


--Your problem should now be in this table:
select * from too_many_cursors;

If you want to test the monitoring, you can use the below PL/SQL block to open a large number of cursors.

--Open a large number of cursors in and wait for 20 seconds.
--(Done by creating a dynamic PL/SQL block with many "open" commands with a "sleep" at the end.
declare
    v_number_of_open_cursors number := 200;
    v_declarations clob;
    v_opens clob;
    v_sql clob;
begin
    for i in 1 .. v_number_of_open_cursors loop
        v_declarations := v_declarations || 'v_cursor'|| i ||' sys_refcursor;' || chr(10);
        v_opens := v_opens || 'open v_cursor' || i || ' for select * from dual;';
    end loop;

    v_sql :=
        'declare '||chr(10)||v_declarations||chr(10)||
        'begin'||chr(10)||v_opens||chr(10)||
        'dbms_lock.sleep(20);'||chr(10)||'end;';

    --Print for debugging.
    --dbms_output.put_line(v_sql);

    execute immediate v_sql;
end;
/