Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 количество открытых курсоров превысило допустимый максимум  [new]
olginger
Member

Откуда:
Сообщений: 19
Всем добрый день!

Помогите, пожалуйста, разобраться с ошибкой.

Собственно, код:

declare
v_code varchar2(30);
v_sql varchar2(1000);
v_sql1 varchar2(1000);
cursor v_cur is (select object_id from bs where rownum <= 50);
begin
v_sql1 := 'with .. (...) select code from bs join .... where object_id = ';
 for i in v_cur loop
  
    v_sql := v_sql1 || i.object_id;
    execute immediate v_sql
      into v_code;
    v_sql := '';   
  
    dbms_output.put_line(v_code );
  end loop;
end;


не стала расписывать весь код в v_sql1, большой запрос, не играющий особой роли в возникновении ошибки.

Ошибка возникает: ORA-01000: количество открытых курсоров превысило допустимый максимум.

Не совсем понимаю причин возникновений, так как, на мой взгляд, курсор открывается и закрывается неявно.

Спасибо!
10 фев 14, 13:44    [15546039]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
daunito
Member

Откуда:
Сообщений: 645
https://www.sql.ru/forum/287209/ora-01000-prevysheno-maksimalnoe-kolichestvo-otkrytyh-kursorov
10 фев 14, 14:39    [15546464]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
olginger
Member

Откуда:
Сообщений: 19
daunito,

читала эту ветку, причина более менее ясна, но непонятно, как можно избежать ошибки.
10 фев 14, 14:54    [15546570]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
-2-
Member

Откуда:
Сообщений: 15330
olginger
как можно избежать ошибки
Приведенный пример решается одним запросом без plsql. Но будет в несколько раз менее затратен по ресурсам.
10 фев 14, 15:02    [15546614]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
oracle1
Guest
olginger, ты не используешь связанные переменные, поэтому у тебя получаеться столько курсоров сколько выполняеться цикл. В твоём случае Правильно делать execute immediate v_sql into using . Почитай здесь есть пример : http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/11_dynam.htm заголовок : Tips and Traps for Dynamic SQL
10 фев 14, 16:51    [15547364]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
-2-
Member

Откуда:
Сообщений: 15330
oracle1
ты не используешь связанные переменные, поэтомуу тебя получаеться столько курсоров сколько выполняеться цикл.
"поэтому" не поэтому.
10 фев 14, 17:16    [15547505]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
oracle1
Guest
-2-
oracle1
ты не используешь связанные переменные, поэтомуу тебя получаеться столько курсоров сколько выполняеться цикл.
"поэтому" не поэтому.
тогда вариант в студию, почему)
10 фев 14, 17:28    [15547563]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
olginger
Member

Откуда:
Сообщений: 19
oracle1,

а в чём будет разница? execute immediate всё равно остаётся в цикле и ошибка не уходит..

declare
v_code varchar2(30);
v_sql1 varchar2(1000);
cursor v_cur is (select object_id from bs where rownum <= 50);
begin
v_sql1 := 'with .. (...) select code from bs join .... where object_id = :p_object_id';
 for i in v_cur loop
  
    execute immediate v_sql
      into v_code using i.object_id;
  
    dbms_output.put_line(v_code );
  end loop;
end;
10 фев 14, 17:31    [15547574]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
olginger,

дык, где-то они утекают.
сталобыть - мониторить надо, и дыра найдется
10 фев 14, 17:49    [15547666]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
dbLink
Guest
а в запросе, есть dblink?
10 фев 14, 18:10    [15547761]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
olginger
Ошибка возникает: ORA-01000: количество открытых курсоров превысило допустимый максимум.
А полностью стек ошибки?
Не вызываются ли в супер-мега-запросе функции, где тоже может быть утечка курсоров?
11 фев 14, 03:04    [15549099]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Ну и после получения ошибки перелогиниваться не забывай
tst> create function f1 return number as
  2  a number;
  3  begin for i in 1..1000 loop
  4          a := dbms_sql.open_cursor;
  5        end loop;
  6        return 5;
  7  end;
  8  /

Function created.

tst> select f1 from dual;
select f1 from dual
       *
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_SQL", line 1163
ORA-06512: at "U1.F1", line 4


tst> drop function f1;
drop function f1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded


tst> connect u1/u1@tst
Connected.
tst> drop function f1;

Function dropped.
11 фев 14, 03:06    [15549100]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
olginger
Member

Откуда:
Сообщений: 19
dbLink,

да,есть
11 фев 14, 09:21    [15549379]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
olginger
Member

Откуда:
Сообщений: 19
Уважаемые знатоки!

После добавления using и перелогина всё заработало. Объясните, пожалуйста, почему, ибо я особой разницы между тем, что было, и тем, что есть сейчас, не вижу.
11 фев 14, 10:31    [15549673]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
dbLink
Guest
olginger,

Проблема вернется, как только выберете все доступные курсоры.
Поскольку висит dbLink, вам можно исправить быстро, явно закрывая транзакцию, либо, что предпочтительнее, открывать и закрывать курсор явно.
Перепишите код с использованием dbms_sql.
11 фев 14, 10:36    [15549705]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
olginger
Уважаемые знатоки!

После добавления using и перелогина всё заработало. Объясните, пожалуйста, почему, ибо я особой разницы между тем, что было, и тем, что есть сейчас, не вижу.

дык, потому курсоры заканчиваются, что есть у вас утечка.
и она вполне может быть совсем не там, где ошибка происходит, (т.е. они таки заканчиваются).
ну было 500, 499 пооткрывали, запустили (пусть и вполне корректную) процедуру, которой надо еще 2 штуки - и привет..
ищите утечку.
а вообще, динамический эскуэль кривыми руками - это неправильно
11 фев 14, 11:56    [15550238]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Lunaire
Member

Откуда:
Сообщений: 44
olginger
Уважаемые знатоки!

После добавления using и перелогина всё заработало. Объясните, пожалуйста, почему, ибо я особой разницы между тем, что было, и тем, что есть сейчас, не вижу.


Запросы без бинд переменных воспринимались ораклом как разные, при каждом селекте требовалось открытие нового курсора (неявного). Отсюда утечка.

При использовании бинд переменных оракл считает, что один запрос выполняется несколько раз, и каждый раз использует один и тот же курсор.

Для лучшего понимания изучите параметр cursor_sharing.
11 фев 14, 12:40    [15550559]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Для лучшего понимания перестань нести чушь
11 фев 14, 12:44    [15550602]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Lunaire
Member

Откуда:
Сообщений: 44
В чем я неправ?

Вячеслав Любомудров
Для лучшего понимания перестань нести чушь


В чем я неправ?
set serveroutput on
declare
i number;
n number;
begin
for i in 1 .. 10
loop
execute immediate 'select 1 from dba_objects where object_id='||i;
execute immediate 'select count(*) from v$open_cursor where sid=sys_context(''USERENV'',''SID'')' into n;
dbms_output.put_line(n);
end loop;
for i in 11 .. 20
loop
execute immediate 'select 1 from dba_objects where object_id=:l' using i;
execute immediate 'select count(*) from v$open_cursor where sid=sys_context(''USERENV'',''SID'')' into n;
dbms_output.put_line(n);
end loop;
end;
/


Вывод:
anonymous block completed
20
21
22
23
24
25
26
27
28
29
30
30
30
30
30
30
30
30
30
30
11 фев 14, 13:32    [15550964]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
В том, что это не вызовет ORA-01000
Попробуй первый цикл провернуть до полусотни-сотни
На каком-то значении PL/SQL будет закрывать курсоры

То, что у каждого будет отдельный hard-parse никто не спорил (более того, до 10.2, если не изменяет склероз, любой execute immediate вызывал hard parse), но это не значит, что курсоры не закрываются. Просто закрытие откладывается, кэшируется PL/SQL движком
11 фев 14, 13:46    [15551073]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
-2-
Member

Откуда:
Сообщений: 15330
Lunaire
В чем я неправ?
Курсоры кешируются на уровне сессии. Если новый запрос попадает в кеш, он переиспользуется. Для литералов попадания нет, но кеш не резиновый и неиспользованный курсор закроется, откроется новый. Вероятно ТС наткнулся на то, что на другой стороне дблинка ограничение open_cursors ниже, чем session_cached_cursors/open_cursors на этой.
11 фев 14, 15:21    [15551763]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
вот - для определения остатка курсоров, на пыльных антресолях откопал.
очевидная, медленная и старая-старая поделка, но кеш сессиионный ей не мешает (в силу брутовой тупости)
declare
  type tp is table of binary_integer index by binary_integer;
  t1 tp;
  c1 binary_integer;
  a_limit binary_integer := 1000;
begin
  for i in 1..a_limit loop
    c1 := dbms_sql.open_cursor;
    t1(t1.count+1) := c1;
  end loop;
  dbms_output.put_line('>='||a_limit);
  for i in 1..a_limit loop
    dbms_sql.close_cursor(t1(i));
  end loop;
exception when others then
  dbms_output.put_line('='||t1.count||' sqlerrm:'||sqlerrm);
  for i in 1..t1.count loop
    if dbms_sql.is_open(t1(i)) then
      dbms_sql.close_cursor(t1(i));
    end if;
  end loop;
end;
11 фев 14, 15:40    [15551919]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Вячеслав Любомудров
В том, что это не вызовет ORA-01000
Попробуй первый цикл провернуть до полусотни-сотни
На каком-то значении PL/SQL будет закрывать курсоры

То, что у каждого будет отдельный hard-parse(это не в тему)это разные курсоры никто не спорил (более того, до 10.2, если не изменяет склероз, любой execute immediate вызывал hard parse, не кешировался), но это не значит, что курсоры не закрываются. Просто закрытие откладывается, кэшируется на уровне сессии и PL/SQL движком
немного уточню
Есть shared pool -- там хранится вся информация по разобранным курсорам, планы и т.д.
Есть кеш сессии (параметр SESSION_CACHED_CURSORS рулит его размером), туда попадают курсоры, когда либо открывающиеся/разбирающиеся в сессии. Курсоры эти вполне могут быть закрыты (как правило, большинство из них). V$OPEN_CURSOR как раз и показывает как открытые, так и эти закешированных на уровне сессии курсоры. В следущий раз когда встретится новый курсор, проверится сначала этот кеш, и если он там найден, не надо будет выполнять даже "мягкий" разбор. Попадания в этот кеш можно увидеть через статистику "session cursor cache hits". Насколько я понимаю, это просто ссылочка (адрес) на shared pool.
Есть кеш PL/SQL движка -- он старается держать все статические(?) курсоры вообще открытыми, чтобы из бежать не только разбора, но и (пере)открытия курсора. Просто ставится меточка, что курсор свободен, насколько я понимаю. Там тоже свой лимит на основе open_cursors и/или session_cached_cursors.

Вызов EXECUTE IMMEDIATE -- это полный цикл обработки курсора -- открытие, поиск в кеше/разбор, связывание переменных (по необходимости), веделение места для возращаемых значений (по необходимости), выполнение (по необходимости), запрос значений (опять же по необходимости) и закрытие. Его незачем держать открытым.
Статические неявные курсоры "закрываются" (на самом деле, необязательно, см. выше) опять же автоматом, а явно открытые можно безболезненно закрыть по окончании выполнения PL/SQL блока (что и делается, насколько помню).
С другой стороны, курсоры, открытые явно через dbms_sql или REF CURSOR нельзя закрывать даже при завершении PL/SQL блока. Поэтому именно они могут вызывать утечку курсоров и вызывать ORA-01000.

+ Блин, на самом деле там вообще все позапуталось уже
tst> connect system/manager@tst
Connected.
tst> alter system flush shared_pool;

System altered.

tst> set serveroutput on
tst> declare
  2  procedure print_cursors is
  3      type n_table is table of number index by binary_integer;
  4      type c_table is table of varchar2(100) index by binary_integer;
  5      ntab n_table; ctab c_table;
  6  begin
  7      select count(*), cursor_type bulk collect into ntab, ctab from v$open_cursor group by cursor_type;
  8      for r in 1..ntab.count loop
  9          dbms_output.put_line(rpad(ctab(r), 65, '.')||to_char(ntab(r), '99999'));
 10      end loop;
 11      dbms_output.put_line(rpad('-', 71, '-'));
 12  end;
 13  begin
 14      print_cursors;
 15      for i in 1..100 loop
 16          execute immediate 'select 1 from dba_objects where object_id='||i;
 17      end loop;
 18      print_cursors;
 19      for i in 101..200 loop
 20          execute immediate 'select 1 from dba_objects where object_id=:l' using i;
 21      end loop;
 22      print_cursors;
 23  end;
 24  /
PL/SQL CURSOR CACHED.............................................    10
OPEN-RECURSIVE...................................................    48
DICTIONARY LOOKUP CURSOR CACHED..................................    44
OPEN.............................................................    21
SESSION CURSOR CACHED............................................    35
OPEN-PL/SQL......................................................     1
BUNDLE DICTIONARY LOOKUP CACHED..................................   118
-----------------------------------------------------------------------
PL/SQL CURSOR CACHED.............................................    59
OPEN-RECURSIVE...................................................    44
DICTIONARY LOOKUP CURSOR CACHED..................................    31
OPEN.............................................................    21
SESSION CURSOR CACHED............................................    24
OPEN-PL/SQL......................................................     1
BUNDLE DICTIONARY LOOKUP CACHED..................................   100
-----------------------------------------------------------------------
PL/SQL CURSOR CACHED.............................................    59
OPEN-RECURSIVE...................................................    44
DICTIONARY LOOKUP CURSOR CACHED..................................    31
OPEN.............................................................    21
SESSION CURSOR CACHED............................................    24
BUNDLE DICTIONARY LOOKUP CACHED..................................   100
-----------------------------------------------------------------------

PL/SQL procedure successfully completed.

tst> /
OPEN-RECURSIVE...................................................    46
PL/SQL CURSOR CACHED.............................................    50
DICTIONARY LOOKUP CURSOR CACHED..................................    32
OPEN.............................................................    23
SESSION CURSOR CACHED............................................    23
BUNDLE DICTIONARY LOOKUP CACHED..................................   104
-----------------------------------------------------------------------
OPEN-RECURSIVE...................................................    43
PL/SQL CURSOR CACHED.............................................    55
DICTIONARY LOOKUP CURSOR CACHED..................................    31
OPEN.............................................................    23
SESSION CURSOR CACHED............................................    22
OPEN-PL/SQL......................................................     1
BUNDLE DICTIONARY LOOKUP CACHED..................................   104
-----------------------------------------------------------------------
OPEN-RECURSIVE...................................................    43
PL/SQL CURSOR CACHED.............................................    55
DICTIONARY LOOKUP CURSOR CACHED..................................    31
OPEN.............................................................    23
SESSION CURSOR CACHED............................................    22
BUNDLE DICTIONARY LOOKUP CACHED..................................   104
-----------------------------------------------------------------------

PL/SQL procedure successfully completed.
Даже уже трудно понять, какой здесь можно сделать вывод
По крайней мере видно, что количество открытых курсоров не растет
Хотя, это можно было увидеть и по статистикам "open cursors"
PS. Насчет dblink не очень понятно. Распределенная транзакция автоматом не завершается, но зачем держать курсоры открытыми?
12 фев 14, 03:31    [15554642]     Ответить | Цитировать Сообщить модератору
 Re: количество открытых курсоров превысило допустимый максимум  [new]
-2-
Member

Откуда:
Сообщений: 15330
Вячеслав Любомудров
Распределенная транзакция автоматом не завершается, но зачем держать курсоры открытыми?
Если локальный курсор остался открытым, то и в составе распределенного на другой стороне. Для другой стороны это типа ref cursor, отданный на управление клиенту. Хотя это не мешает удаленной стороне неявно закрывать курсоры при коммите.

Для теста количество открытых курсоров в удаленной сессии можно локально подсмотреть в Gv$dblink.
12 фев 14, 06:27    [15554673]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: количество открытых курсоров превысило допустимый максимум  [new]
Владимир Симаков
Member

Откуда:
Сообщений: 5
Вячеслав Любомудров,

Добрый день!
Столкнулись с проблемой ограничения кол-ва курсоров на сессию.
Откуда запускается пакет стоит параметр в 1500, на остальных схемах в которые стучимся по дблинк стоит параметр в 300.
Прокомментируйте пожалуйста: "С другой стороны, курсоры, открытые явно через dbms_sql или REF CURSOR нельзя закрывать даже при завершении PL/SQL блока. Поэтому именно они могут вызывать утечку курсоров и вызывать ORA-01000."
Как можно обнаружить утечку курсоров?(Единичный прогон проходит быстро, при массовом запуска все валится)
Понятно, что использование пакета dbms_sql упростит работу по сравнению с EXECUTE IMMEDIЕATE, что написанно у Фейерштейна, но решит ли?
Можно ли понимать какие курсоры не закрываются(если они сформированы динамически(тип - sys_refcursor), то какой элемент мы ему передали) и как?
Версии: Oracle 11gR2 и тоже такое творится на 12с.
Заранее спасибо.
27 окт 15, 15:43    [18333472]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить