Oracle 19c Open_cursor превысил проблему

Я не могу сказать вам, что вызывает вашу проблему с максимальным количеством открытых курсоров, но я расскажу вам, как найти причину, идентифицируя связанные сеансы и оператор SQL, используя GV$OPEN_CURSOR.

Если вам повезет, вы сможете сразу найти проблему с помощью простого запроса, который подсчитывает количество открытых курсоров за сеанс. В приведенном ниже запросе много столбцов, используйте IDE, чтобы вы могли легко просматривать все данные. По моему опыту, достаточно взглянуть на такие столбцы, как USER_NAME и SQL_TEXT, чтобы определить виновника.

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 курсоров. Вы ищете сеансы с сотнями открытых курсоров. (Если только кто-то по глупости не опустил значение параметра ниже значения по умолчанию.)

К сожалению, GV$OPEN_CURSOR не содержит исторических данных, и эти проблемы могут быстро начинаться и прекращаться, если в замкнутом цикле есть исключение, которое быстро открывает множество курсоров. Приведенный ниже блок PL/SQL работает до тех пор, пока не найдет сеанс с большим количеством открытых курсоров, сохранит данные и завершит работу. Этот блок PL/SQL является дорогостоящим и требует целого сеанса обработки в ожидании подходящего момента, поэтому используйте его только один раз, чтобы найти проблему.

--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, чтобы открыть большое количество курсоров.

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