Cursors Faq

Вообще курсоры  бывают 2 типа:

• implicit - подразумеваемый, не выраженный явно, скрытый (translated by yandex lingvo)
• explicit - искренний, откровенный, открытый, прямой

Пример первого типа : 

declare

x varchar2(100);

begin 

select fullname into 'x' from emp where id = 10023;

end;

Пример второго:

declare

c cursor is select fullname from emp;

begin 

open c;

for 1....10000 loop

fetch c into c.rec ;

end loop;

close c;

end;

Что использовать в обычной жизни ? Благодаря Tom Kyte мы знаем теперь что неявные курсоры быстрее и лучше. Вот код который он использовал для тестирования:

Неявный курсор:

declare
l_dummy dual.dummy%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 10000
loop
select dummy into l_dummy from dual;
end loop;
dbms_output.put_line
( 'Elapsed ' || round( (dbms_utility.get_time-l_start)/100, 2 ) );
end;
/

Elapsed 1.98

И напротив, с явным курсором:

declare
cursor c1 is select * from dual;
l_dummy dual.dummy%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 10000
loop
open c1;
fetch c1 into l_dummy;
close c1;
end loop;
dbms_output.put_line
( 'Elapsed ' || round( (dbms_utility.get_time-l_start)/100, 2 ) );
end;
/

Elapsed 2.55

Видно что явные курсоры проигрывают. Обидно ! :))) (Я привожу цифры для Oracle 9i, Windows 2000, Celeron 1,7 Mh)

Кол-во открытых и отпарсенных  курсоров, а также текст запросов  можно увидеть с помощью v$open_cursor:

select sql_text from v$open_cursor where user_name = USER;

Также кол-во текущих открытых  курсоров в v$mystat:

select count(*) from v$mystat where statistic# = 3;

Но если Вы посмотрите на v$open_cursor, то Вы не увидите там наших запросов ?!! Это потому, что курсоры были нами закрыты, а кешировать мы их не разрешили (см. session_cached_cursors ниже) 

Посмотрим наконец на параметры влияющие на наши курсоры:

SQL> show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- -----------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 0

open_cursors

Сколько открытых курсоров может иметь пользовательский процесс одновременно. Под этот параметр резервируется память при старте экземпляра. Сколько памяти ? Я думаю что оценить это можно с помощью запроса до и после изменения этого параметра:

select a.name, b.value  from v$statname a, v$mystat b  where a.statistic# = b.statistic# and lower(a.name) like 'session%memory%'

 

Когда я увеличил значение open_cursors с 300 до 1000, значение session_pga_memory также увеличилось.

cursor_sharing (metalink reference note)

Этот параметр показывает как Oracle будет производить поиск вашего курсора в SGA. 

• EXACT

  Текст запроса должен совпадать точно. 

•  FORCE

  Текст запроса может быть с точностью до bind переменных. Это очень полезно если программисты не знали про то что нужно использовать bind переменные и писали прямо where name = 'пупкин'. И таких запросов у Вас в SGA очень много. 

• SIMILAR (начиная с 9i)

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

cursor_space_for_time (metalink reference note)

Этот параметр  позволяет держать в SGA связанные с закрытым курсором SQL Area, что позволяет инициировать курсор быстрее. Но использовать его рекомендуется только когда  в SGA достаточно свободного места.

session_cached_cursors (metalink reference note)

Показывает сколько вы хотите курсоров держать открытыми в сессии, даже несмотря на их явное закрытие в коде. Это позволяет избежать повторного hard парсинга и уменьшить количесво latch в вашем приложении. Об отличии hard от soft и влиянии этого параметра написал Tom Kyte

Проведем интересный эксперимент. Выставим cursor_sharing = EXACT, и session_cached_cursor = 20. Выполним время выполнения обоих скриптов начинает выравниваться (!), но не значительно.   Также в v$open_cursor появятся наши select from dual.

И наконец что же делать с упоминаемой в начала ошибкой ORA-1000.  Ну во первых посмотреть на значение параметра open_cursors. Значение в 200-300 должно хватать для большинства задач. Дальше посмотреть, а не увеличивается ли значение числа открытых курсоров во времени, возможно их просто забывают закрывать ? Используются ли bind переменные в вашем приложение, и каково значение  параметра cursor_sharing.

Конечно нужно прочитать что рекомендует в этом случае metalink.