Если привилегии владельца процедур или пакетов изменяются, перед выполнением они должны будут пройти повторную аутентификацию. Если у владельца процедур или пакетов отбираются необходимые привилегии доступа к ссылочным объектам, процедуры или пакеты не будут выполняться.

Если вы используете динамический SQL, особенно операторы языка определения данных (DDL) типа показанных выше, я рекомендую предварительно тестировать операторы следующим образом:

SQL> set role none;
SQL> "тестируемый оператор"

Если вы сможете сделать это в SQL*Plus с выключенными ролями, вы сможете сделать это и в процедуре. Если вы получите ошибку, вы не сможете использовать для этого процедуры (если только не использовать в Oracle8i права вызывающего).

Более подробно об этом см. в документации по PL/SQL (убедитесь, что вы понимаете все нюансы). Для выполнения подобных операций в обычной процедуре нужно иметь необходимые привилегии, выданные непосредственно вам.

Динамический SQL и переменные связывания

Как я понимаю, в динамическом SQL переменные связывания имеют важное значение. Объясните, пожалуйста, почему важно использовать переменные связывания в динамическом SQL и почему этот вопрос не возникает в статическом SQL.

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

Причина отсутствия необходимости всегда использовать в статическом SQL переменные связывания заключается в том, что статический SQL по определению фиксируется во время компиляции операторов. Каждый раз, когда вы выполняете такую программу, статические операторы SQL будут одними и теми же и они будут на 100% разделяемыми. Так, запрос, в котором используется статический SQL, например:

   select * from t where x = 'некоторое значение'
всегда будет выглядеть как

        select * from t where x = 'некоторое значение'
Не имеет никакого значения, сколько раз вы запускаете такую программу, каждый раз будет выполняться один и тот же старый запрос (использоваться повторно).

С помощью динамического SQL можно конструировать операторы типа:

   select * from t where x = 'a'
   select * from t where x = 'b'
   select * from t where x = 'c'

и т.д. Каждый оператор будет совершенно новым, уникальным.

В приведенном ниже примере я динамически выполняю оператор:

select count(*) from t where x = 1
(x изменяется от 1 до 1 000) и переменные связывания не используются. А затем я сделаю то же самое, используя статический PL/SQL с переменными связывания.

Во время выполнения я собираю некоторые важные статистики, а также использую процедуру dbms_utility.get_time для измерения времени. Я начинаю пример с создания таблицы и представления для нашего набора статистик и создаю таблицу T, как это показано в листинге 5.

ЛИСТИНГ 5: создание статистических таблиц

SQL> create table run_stats ( runid varchar2(15), name varchar2(80), value int ); Table created.   SQL> create or replace view stats   2  as select 'STAT...' || a.name name, b.value   3        from v$statname a, v$mystat b   4       where a.statistic# = b.statistic#   5      union all   6      select 'LATCH.' || name,  gets   7        from v$latch; View created.   SQL> create table t ( x int ); Table created.

Затем я выполняю два тестовых прогона: один с переменными связывания, другой без них. См. листинг 6.

ЛИСТИНГ 6: тестирование без переменных связывания и с ними

SQL> declare   2      l_start number;   3          l_cnt   number;   4  begin   5      insert into run_stats select 'before', stats.* from stats;   6    7      l_start := dbms_utility.get_time;   8      for i in 1 .. 1000   9      loop  10         execute immediate 'select count(*) from t where x = ' || i into l_cnt;  11      end loop;  12      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );  13   14      insert into run_stats select 'after 1', stats.* from stats;  15   16      l_start := dbms_utility.get_time;  17      for i in 1 .. 1000  18      loop  19          select count(*) into l_cnt from t where x = i;  20      end loop;  21      dbms_output.put_line((dbms_utility.get_time-l_start) || ' hsecs' );  22   23      insert into run_stats select 'after 2', stats.* from stats;  24  end;  25  / 108 hsecs 18 hsecs   PL/SQL procedure successfully completed.

Прогон с переменными связывания оказался быстрее (18/100 секунды против 108/100 секунды), но это только половина истории. Я собирал статистики и данные об использовании защелок (тестовые прогоны выполнялись в однопользовательской системе, никакие другие сеансы не влияли на эти числа); эта информация позволяет обнаружить существенно больше различий между этими двумя тестами (см. листинг 7).

ЛИСТИНГ 7: статистики и защелки для тестирования с переменными связывания и без них

SQL> select a.name, b.value-a.value run1, c.value-b.value run2,   2  ( (c.value-b.value)-(b.value-a.value)) diff   3    from run_stats a, run_stats b, run_stats c   4   where a.name = b.name   5     and b.name = c.name   6     and a.runid = 'before'   7     and b.runid = 'after 1'   8     and c.runid = 'after 2'   9     and (c.value-a.value) > 0  10     and (c.value-b.value) <> (b.value-a.value)  11   order by abs( (c.value-b.value)-(b.value-a.value))  12  /   NAME                               RUN1  RUN2    DIFF ----------------------------      -----   -----   ----- ... LATCH.cache buffers chains         8621    8222    -399 STAT...recursive calls             2002    1184     -818 STAT...parse count (hard)          1007    5    -1002 STAT...opened cursors cumulative   1034    15    -1019 STAT...parse count (total)         1048    15    -1033 STAT...session pga memory max      6656    8524     1868 LATCH.row cache objects            6254    115    -6139 STAT...session pga memory          15144   8524   -6620 LATCH.library cache                21344   2269   -19075 LATCH.shared pool                  24098   131   -23967   51 rows selected.

Посмотрите на суммарные данные о защелках разделяемого пула (shared pool) и библиотечного кеша (library cache). В прогоне без использования переменных связывания (RUN1) было 45 442 защелки, а в прогоне с переменными связывания (RUN2) – 2 400 защелок, то есть почти в 20 раз меньше.

Имейте в виду, защелки - это блокировки, блокировки = механизм сериализации, сериализация = уменьшение параллельности, уменьшение параллельности = отсутствие масштабируемости. Очевидно, какой подход является более предпочтительным.

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

Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) с 1993 года работает в Oracle Services Industry Technology Group в Рестоне, штат Вирджиния.