1240

Глава 16

INSERT INTO T Q4 (A,B,C) VALUES (:Ы,:Ь2,:ЬЗ)

call count cpu elapsed disk query current

Parse

0.07

0.04

Как видите, подпрограмме, использующей средства пакета DBMS SQL, хватило всего одного разбора, а вот при использовании встроенного динамического SQL анализировать операторы пришлось 400 раз. В загруженной системе, где одновременно работает множество пользователей, это может существенно снизить производительность. Поскольку избежать избыточнгх разборов можно и соответствующий код с использованием средств пакета DBMS SQL не намного сложнее, я считаю оптимальным при решении подобного рода задач использовать DBMS SQL. Хотя код и сложнее, но для обеспечения более высокой масштабируемости я бы использовал именно его.

Если сравнить результаты процедур, не обрабатывающих массивы, оказывается, что они существенно хуже:

BEGIN load data.dbmssql noarray(t, 10000); END;

Несомненно, имеет смысл использовать встроенный динамический SQL. Но и без массивов я все равно использовал бы средства пакета DBMS SQL. И вот почему:

insert into t q2 (a, b, c) values (:a, :b, :c)

call count cpu elapsed disk query current rows

Parse 1 0.00 0.00 0

insert into t q3 (a, b, c) values (:a, :b, call count cpu elapsed disk

Parse

10000

1.87

1.84

query

current

Оказывается, что при использовании встроенного динамического SQL пришлось выполнить 10000 мягких разборов, и лишь один - при использовании пакета DBMS SQL В многопользовательской среде реализация на основе пакета DBMS SQL окажется намного более масштабируемой.

rows

rows

Динамический SQL 1241

Аналогичные результаты можно получить и при обработке множества строк, выдаваемых по динамически формируемому запросу. Обычно данные можно выбирать из кур-сорн]х переменн1х массивами, но только из строго типизированных. Это такие курсор-н1е переменные, структура которых известна на этапе компиляции. Встроенный динамический SQL поддерживает использование только слабо типизированных курсор-н]х переменн1х и поэтому не поддерживает множественную выборку, BULK COLLECT. Если попытаться выполнить оператор BULK COLLECT для динамически открытой курсорной переменной, будет получено сообщение об ошибке:

ORA-01001: Invalid Cursor

Вот сравнение двух процедур, выбирающих и подсчитывающих все строки из представления ALL OBJECTS. Процедура, использующая средства пакета DBMS SQL и обрабатывающая массивы, работает почти вдвое быстрее:

scott@TKYTE816> create or replace procedure nativedynamicselect

2 as

3 type rc is ref cursor;

4 l cursor rc;

5 l oname varchar2(255);

6 l cnt number := 0;

7 l start number default dbms utility.get time;

8 begin

9 open l cursor for select object name from all objects; 10

11 loop

12 fetch l cursor into l oname;

13 exit when l cursor%notfound;

14 l cnt := l cnt+l;

15 end loop; 16

17 close l cursor;

18 dbms output.put line(L cnt rows processed);

19 dbmsoutput.putline

20 (round((dbms utility.get time-l start)/100, 2) 11 seconds);

21 exception

22 when others then

23 if (l cursor%isopen)

24 then

25 close l cursor; 2 6 end if;

27 raise;

28 end;

29 / Procedure created.

scott@TKIE816> create or replace procedure dbmssqlselect

2 as

3 l theCursor integer default dbms sql.open cursor;

4 l columnValue dbms sql.varchar2 table;

5 l status integer;

6 l cnt number := 0;

1242

Глава 16

7 l start number default dbms utility.get time;

8 begin

10 dbms sql.parse(l theCursor,

11 select object name from all objects,

12 dbms sql.native);

14 dbms sql.define array(l theCursor, 1, l columnValue, 100, 1);

15 l status := dbms sql.execute(l theCursor);

16 loop

17 l status := dbms sql.fetch rows(l theCursor);

18 dbms sql.column value(l theCursor,1,l columnValue);

20 l cnt := l status+l cnt;

21 exit when l status <> 10 0;

22 end loop;

2 3 dbms sql.close cursor(l theCursor);

24 dbms output.put line(L cnt rows processed);

2 5 dbms output.put line

26 (round((dbms utility.get time-l start)/100, 2 ) 11 seconds);

27 exception

28 when others then

2 9 dbms sql.close cursor(l theCursor);

30 raise;

31 end;

32 /

Procedure created.

scott@TKYTE816> set serveroutput on

scott@TKYTE816> exec native dynamic select 19695 rows processed

1.85 seconds

PL/SQL procedure successfully completed.

scott@TKYTE816> exec native dynamic select 19695 rows processed

1.86 seconds

PL/SQL procedure successfully completed.

scott@TKYTE816> exec dbms sql select 19695 rows processed 1.03 seconds

PL/SQL procedure successfully completed.

scott@TKYTE816> exec dbms sql select 19695 rows processed 1.07 seconds

PL/SQL procedure successfully completed.

Снова приходится выбирать между производительностью и простотой кода. Для обработки массивов средствами пакета DBMS SQL необходимо написать намного