How to solve PL/SQL: ORA-02287: sequence number not allowed here (решение,оптимизация).

Для генерации уникальных значений ключа в Oracle используется объект - последовательность (sequence). Однако, на использование последовательности наложен ряд ограничений, например - запрет на использование в подзапросах. В данной заметке мы рассмотрим как обойти это ограничение и как произвести его оптимизацию.

Иногда, когда совсем этого не ждешь, появляется ошибка ORA-02287 - использование sequence там, где этого делать нельзя. Если посмотреть что скаано по этому повода на OraDoc, видим:

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:

• A subquery in a DELETE, SELECT, or UPDATE statement

• A query of a view or of a materialized view

• A SELECT statement with the DISTINCT operator

• A SELECT statement with a GROUP BY clause or ORDER BY clause

• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

• The WHERE clause of a SELECT statement

• The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

• The condition of a CHECK constraint

Причем, самое неприятное из перечисленного - невозможность использования последовательности в подзапросах.

Например:

          INSERT ALL WHEN
          MOD(id, 2) = 0 THEN INTO t1(id, VALUE)
          VALUES
            (id, VALUE)
          WHEN MOD (id, 2) = 1 THEN INTO t2(id, VALUE)
          VALUES
            (id, VALUE)
          SELECT sq$t_pk.NEXTVAL id, VALUE FROM TABLE(tbl_values);
 

где tbl_values - переменная типа коллекции, содержащей поле value. Т.е. в данном случае, пример иллюстрирует ситуацию, когда необходимо генерируемое уникальное значение ключа вставлять в одну таблицу и в другую. Условие разделение записи между двумя таблицами - в данном случае четное значение в одну таблицу, нечетное в другую.

Решить данную проблему можно использовав функцию вместо последовательности, которая уже в свою очередь обращается к последовательности. Ora Doc это не запрещает.

function get_sq_value return number
is
Result number;
begin                
           select sq$t_pk.nextval into Result from dual;                    
           return Result;
end;

INSERT ALL WHEN

MOD(id, 2) = 0 THEN INTO t1(id, VALUE)
VALUES
  (id, VALUE)

WHEN MOD (id, 2) = 1 THEN INTO t2(id, VALUE)
 VALUES
  (id, VALUE)
SELECT get_sq_value id, VALUE FROM TABLE(tbl_values);

Данный вариант хорош, когда записей в таблице источнике (будь то коллекция tbl_values или другая таблица) немного. Однако если их много, скажем больше 1000, то в данном случае внутренний запрос:

           select sq$t_pk.nextval into Result from dual; 

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

Можно размышлять следующим образом - что такое переключение контекста? Это когда PL/SQL движок останавливает свое выполнение и отдает команду SQL движку. Получается нужно избавиться от команды SELECT в самой функции. Совсем избавиться от нее, понятно, не получиться (надо же как то достать nextval), но можно свести к минимуму. Как? Выполним select...nextval один раз.

Объекты для сравнительного тестирования.

-- необходимые объекты
create table t1(id number primary key,value varchar2(1000));
create table t2(id number primary key,value varchar2(1000));
create sequence SQ$T_PK minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 100 order;

-- спека пакета
create or replace package pkg_test_insert is

    function get_sq_value return number;

    function get_sq_value(i_count in number) return number;

    procedure insert_batch(tbl_values in tbl_t_type);

    procedure insert_batch_cache_sq(tbl_values in tbl_t_type);

end pkg_test_insert;
/
-- тело пакета
create or replace package body pkg_test_insert is
-- тип для хранения идентификаторов.

type sq_values is table of number index by binary_integer;

-- коллекция заданного типа.
g_sq_values sq_values;
 

g_pointer number:=1;

function get_sq_value(i_count in number) return number
is
begin
        if g_pointer = 1 then

               select sq$t_pk.nextval

            bulk collect into g_sq_values
                         from (select rownum as l_date
                                  from (select 'x' as con from dual) q
                                connect by q.con = q.con
                                ) ql
                         where rownum <= i_count;                       
                         g_pointer:=g_pointer+1;
                         return g_sq_values(g_pointer-1)

         else
                       g_pointer:=g_pointer+1;
                       return g_sq_values(g_pointer-1);
         end if;

end;

-- получение идентификатора (контекст)
function get_sq_value return number
is
Result number;
begin                
           select sq$t_pk.nextval into Result from dual;                    
           return Result;
end;

-- вставка записей (переключение контекста)
procedure insert_batch(tbl_values in tbl_t_type)
is
begin
          INSERT ALL WHEN
          MOD(id, 2) = 0 THEN INTO t1(id, VALUE)
          VALUES
            (id, VALUE)
          WHEN MOD (id, 2) = 1 THEN INTO t2(id, VALUE)
          VALUES
            (id, VALUE)
          SELECT get_sq_value id, VALUE FROM TABLE(tbl_values);
end;

-- вставка записей (переключение контекста - один раз)
procedure insert_batch_cache_sq(tbl_values in tbl_t_type)
is
l_number number;
begin
          g_pointer:=1;
          l_number:=tbl_values.count;       
          INSERT ALL WHEN
          MOD(id, 2) = 0 THEN INTO t1(id, VALUE)
          VALUES
            (id, VALUE)
          WHEN MOD (id, 2) = 1 THEN INTO t2(id, VALUE)
          VALUES
            (id, VALUE)
          SELECT get_sq_value(l_number) id, VALUE FROM TABLE(tbl_values);
end;

end pkg_test_insert;
/

А теперь протестируем два варианта (сравним два варианта с помощь runstats):

1. Вставка записей с использованием метода с большим переключением контекста (insert_batch). На каждую запись будет вызывать внутрений select для получения nextval.
2. Вставка записей с использованием метода с одним переключением контекста (insert_batch_cache_sq).

declare
  tbl_values tbl_t_type:=tbl_t_type();
  -- количество строк в коллекции (равно количеству обрабатываемых строк)
  l_cnt_values number:=50000;
begiN
  tbl_values.extend(l_cnt_values);
  -- заполняем коллекцию.
  for xxx in 1..l_cnt_values loop          
      tbl_values(xxx):=t_type(to_char(xxx));
  end loop;
        
 runStats_pkg.rs_start;
                    
 pkg_test_insert.insert_batch(tbl_values => tbl_values);

 runStats_pkg.rs_middle;

 pkg_test_insert.insert_batch_cache_sq(tbl_values => tbl_values);

 runStats_pkg.rs_stop(p_difference_threshold => 0,p_output => 'WORKLOAD');

 commit;
end;

Для 1000 записей в tbl_values:

Run1 ran in 11 hsecs
Run2 ran in 4 hsecs
run 1 ran in 275% of the time
   
Name                                            Run1        Run2        Diff
STAT...Elapsed Time                               16          10          -6
STAT...CPU used by this session                   18           9          -9
STAT...recursive cpu usage                        18           9          -9
STAT...session logical reads                     138          88         -50
STAT...redo size                              16,172       9,652      -6,520
STAT...session pga memory                          0      65,536      65,536
   
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
      19,565       9,805      -9,760    199.54%

Для 50000 записей в tbl_values:

Run1 ran in 580 hsecs
Run2 ran in 187 hsecs
run 1 ran in 310.16% of the time
   
Name                                            Run1        Run2        Diff
STAT...CPU used by this session                  584         194        -390
STAT...recursive cpu usage                       584         194        -390
STAT...Elapsed Time                              586         193        -393
STAT...session logical reads                   5,063       2,550      -2,513
STAT...redo size                             654,652     328,812    -325,840
STAT...session uga memory                          0   3,597,440   3,597,440
STAT...session pga memory                          0   3,670,016   3,670,016
   
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
   1,266,940     482,741    -784,199    262.45%

Из результатов видно, что второй вариант легче (используется меньше процессорных ресурсов, выполняется быстрее в 2-4 раза), да и защелок аж в 2-3 раза меньше первого варианта.

Однако метрики использования памяти во втором варианте больше, причем они повышаются с увеличением числа входных строк. Что вполне объяснимо, с учетом того, что используем коллекцию, которая храниться в uga memory, которая в свою очередь (dedicated server) храниться в pga memory.

Сделать так, чтобы рост числа памяти был закономерен и всегда контролировался - это уже дело техники. Можно например в функции генерации идентификаторов:

    function get_sq_value(i_count in number) return number

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

 

В примере, в качестве источника строк используется коллекция (например, внешнее приложение передает в БД Oracle набор строк, через коллекцию). Когда же источником является другая таблица, то выгоднее использовать аналитику для подсчета количества обрабатываемых строк, чем выполнять count(1) по таблице - будет один проход по таблице. Создадим другую функцию, где с помощю аналитики выполним подсчет строк (при этом, будем так же выполнять select из коллекции - для нас не важно из чего на данном этапе выполнять выборку):

procedure insert_batch_cache_sq_over(tbl_values in tbl_t_type)
is
begin
        
          INSERT ALL WHEN
          MOD(id, 2) = 0 THEN INTO t1(id, VALUE)
          VALUES
            (id, VALUE)
          WHEN MOD (id, 2) = 1 THEN INTO t2(id, VALUE)
          VALUES
            (id, VALUE)
          SELECT get_sq_value(cnt) id, VALUE
          FROM
          (select count(1) over() cnt, value from TABLE(tbl_values) );
end;


А теперь, произведем сравнение работы новой функции и функции  insert_batch_cache_sq примерно таким образом:



declare
  tbl_values tbl_t_type:=tbl_t_type();
  l_cnt_values number:=50000;  
begin

  tbl_values.extend(l_cnt_values);

  for xxx in 1..l_cnt_values loop          
      tbl_values(xxx):=t_type(to_char(xxx));
  end loop;

  runStats_pkg.rs_start;
                   pkg_test_insert.insert_batch_cache_sq(tbl_values  => tbl_values);    

   runStats_pkg.rs_middle;

 pkg_test_insert.insert_batch_cache_sq_over(tbl_values  => tbl_values);

   runStats_pkg.rs_stop(p_difference_threshold  => 0,p_output => 'WORKLOAD');

  commit;
end;



Вот результаты:

Run1 ran in 189 hsecs
Run2 ran in 206 hsecs
run 1 ran in 91.75% of the time
   
Name                                            Run1        Run2        Diff
STAT...CPU used by this session                  195         212          17
STAT...Elapsed Time                              195         212          17
STAT...recursive cpu usage                       195         212          17
STAT...session logical reads                   2,582          37      -2,545
STAT...sorts (rows)                            7,279      57,278      49,999
STAT...redo size                             329,048       2,996    -326,052
STAT...session uga memory                  4,840,192     -65,408  -4,905,600
STAT...session pga memory                  4,915,200           0  -4,915,200
   
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     482,702         437    -482,265##########%

Видно, что время выполнения увеличилось (что понятно, т.к. нужна дополнительная сортировка), однако, количество защелок в варианте с over - значительно уменьшилось.

И получается, как говорил Том Кайт "Если второй метод работает немного дольше, но количество защелок используется меньше, я лучше выберу второй метод, т.к. он обеспечит большую маштабируемость".

Кстати, скорее всего, второй метод (который с over) использует меньше защелок за счет того, что данные из коллекции извлекаются "разом", т.к. в плане выполнения, из за наличия аналитической функции, происходит предварительное получение строк. Получается, Oracle как бы кэширует данные из коллекции, одновременно вычисляет аналитическую функцию и затем уже  для  закешированных результатов выполняет вызов функции, генерирующей идентификаторы. Но это только предположение, здесь необходимо исследование. К тому же, для текущего эксперимента нам это не особенно то и нужно.

Выводы.

1. Ограничение ORA-02287 достаточно легко обходиться вполне стандартными средствами.
2. Избавиться от переключения контекста в вызываемой функции генерации уникальных значений можно предварительно закешировав их в памяти.