Oracle 19c Open_cursor 超出问题 [英] Oracle 19c Open_cursor exceeded issue

问题描述

我们在 Oracle 10g 和 19c 中存在相同的存储过程，具有相同的数据集和设置.该过程执行了如此多的数据获取和操作.当我们使用相同的数据集(比如 10000 条记录)执行时，它在 10g 中运行良好，时间更少，但在 19c 中它需要很多时间，一段时间后它会抛出超出打开游标限制"错误.我们对 OPEN_CURSOR 和 OPEN_CURSOR 的两个数据库进行了基本比较.CACHED_CURSOR 大小相同.

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?

推荐答案

我不能告诉你是什么导致了你的最大打开游标问题，但我告诉你如何通过使用识别相关会话和 SQL 语句来找到原因GV$OPEN_CURSOR.

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.

如果幸运的话，您可以通过一个简单的查询立即找到问题，该查询计算每个会话的打开游标数.下面的查询中有很多列，使用 IDE 可以轻松浏览所有数据.根据我的经验，只需看一眼 USER_NAME 和 SQL_TEXT 等列就足以确定罪魁祸首.

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;

请记住，该视图中会有许多奇怪的查询，这些查询可能会使计数超出您的预期.对于所有递归和缓存查询，无聊"的查询并不罕见.会话使用 50 个游标.您正在寻找具有数百个打开游标的会话.(除非有人愚蠢地将参数值降低到默认值以下.)

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.)

不幸的是，GV$OPEN_CURSOR 不包含历史数据，如果在一个快速打开大量游标的紧密循环中出现异常，这些问题可以快速启动和停止.下面的 PL/SQL 块会一直运行，直到找到具有大量打开游标的会话、存储数据并退出.这个 PL/SQL 块很昂贵，并且会用完整个会话等待合适的时间，所以只用一次就可以找到问题.

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;

如果要测试监控，可以使用下面的PL/SQL块打开大量游标.

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;
/

这篇关于Oracle 19c Open_cursor 超出问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！