Триггеры AFTER SUSPEND в PL/SQL: создание и работа на примере

Doc

Doc

АйТишник со стажем... Профиль автора.

Триггеры AFTER SUSPENDВ Oracle9i Release 1 появился новый тип триггеров, запускаемых в ответ на приостановле­ние выполняемых команд. Приостановление может быть вызвано разными причинами — например, превышением квоты выделенного процессу табличного пространства. Триггер AFTER SUSPEND должен решить проблему, чтобы выполнение команды было возобновлено. Это просто подарок для разработчиков, которым надоели проблемы с нехваткой памяти, и для администраторов базы данных, которым приходится эти проблемы решать. Синтаксис создания триггера AFTER SUSPEND сходен с синтаксисом создания триггеров PL/SQL для DDL и триггеров событий базы данных Oracle. В нем объявляется инициирующее событие (suspend), время запуска (after) и область действия (database или schema):


Оглавление статьи[Показать]


1 CREATE [OR REPLACE] TRIGGER имя_триггера
2 AFTER SUSPEND
3 ON {DATABASE | SCHEMA}
4 BEGIN
5 ... код ...
6 END;

В знакомстве с AFTER SUSPEND нам поможет пример типичной ситуации, требующей создания этого вида триггеров.

Допустим, некоему разработчику приходится сопровождать сотни программ, которые выполняются в течение ночи, выполняют продолжительные транзакции для обобщения информации и ее перемещения между разными приложениями. По крайней мере дважды в неделю разработчик получает ночью сообщения на пейджер, потому что в одной из его программ возникла ошибка Oracle:

ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

Следующий неизбежный шаг — звонить администратору базы данных и просить уве­личить пространственную квоту. Администратор спрашивает: «А сколько нужно?», на что разработчик может ответить лишь неубедительным: «Даже не знаю, объем данных сильно изменяется». Оба остаются весьма недовольными, потому что администратору нужен контроль за выделяемым пространством в целях планирования, а разработчику не хочется так часто просыпаться от служебных сообщений.

 

 

Настройка для триггера AFTER SUSPEND

К счастью, триггер AFTER SUSPEND может решить проблемы обоих героев. Посмотрим, как же решается эта задача. Разработчик обнаруживает конкретную точку в коде, в которой чаще всего встречается ошибка. Это внешне непримечательная команда INSERT в конце программы, выполняющейся несколько часов:

INSERT INTO monthly_summary (
   acct_no, trx_count, total_in, total_out)
VALUES (
   v_acct, v_trx_count, v_total_in, v_total_out);

Больше всего бесит то, что на вычисления уходит несколько часов, а вычисленные значения немедленно теряются при сбое завершающей команды INSERT. По меньшей мере разработчик хочет, чтобы программа приостанавливалась, пока он обращается к администратору за дополнительным пространством. Он обнаруживает, что это можно сделать простой командой ALTER SESSION:

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'Monthly Summary';

Это означает, что каждый раз, когда сеанс Oracle обнаруживает ошибку нехватки про­странства, он переходит в приостановленное состояние на 3600 секунд (1 час) — если повезет, то с возможностью продолжения. Это дает необходимое время, чтобы система мониторинга могла отправить сообщение, разработчик обратился к администратору, а тот выделил дополнительное пространство. Система не идеальная, но по крайней мере часы, потраченные за вычисления, не будут теряться понапрасну.

Другая проблема заключается в том, что при попытках разрешить ситуацию в середине ночи и разработчик и администратор чувствуют такую усталость и раздражительность, что на разрешение недоразумений уходит лишнее время. На помощь приходит другая особенность приостановленных команд: представление DBA_RESUMABLE. В нем содержит­ся информация обо всех сеансах, зарегистрированных для продолжения приведенной командой ALTER SESSION.


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

Недопустимые операции DDL в системных триггерах

 

Теперь когда программа переходит в приостановленное состояние, разработчику остается лишь позвонить администратору и пробормотать в трубку: «Проверь представление dba_resumable». Администратор запрашивает информацию со своей учетной записи и смотрит, что же происходит:

SQL> SELECT session_id,
2            name,
3            status,
4            error_number
5   FROM dba resumable

SESSION_ID      NAME	                STATUS     ERROR_NUMBER
--------------  -----------------------  ------  -----------------
         8      Monthly Summary         SUSPENDED          1536

1 row selected.
  

Из результатов видно, что сеанс 8 приостановлен из-за ошибки ORA-01536 (превыше­ние пространственной квоты). По прошлому опыту администратор знает, какая схема и табличное пространство виноваты в случившемся, он устраняет проблему и бормочет в трубку: «Готово». Приостановленная команда в коде разработчика немедленно воз­обновляет выполнение, а разработчик и администратор могут спать спокойно.

 

Код триггера

Через несколько недель и разработчику, и администратору надоедают повторяющиеся (хотя и с меньшей частотой) ночные переговоры, и администратор решает автомати­зировать свою работу при помощи триггера AFTER SUSPEND. Фрагмент кода, который он пишет и устанавливает под административной учетной записью:


TRIGGER after_suspend 
AFTER SUSPEND 
ON DATABASE 
DECLARE
...
BEGIN

   -- Если это ошибка, связанная с доступным пространством...
 IF ORA_SPACE_ERROR_INFO ( error_type => v_error_type,
                           object_type => v_object_type, 
                           object_owner => v_object_owner, 
                           table_space_name => v_tbspc_name, 
                           object_name => v_object_name, 
                           sub_object_name => v_subobject_name ) THEN

   -- Если произошло превышение квоты табличного пространства...
 IF v_error_type = 'SPACE QUOTA EXCEEDED' AND 
      v_object_type = 'TABLE SPACE' THEN

      -- Получение имени пользователя 
      OPEN curs_get_username;
      FETCH curs_get_username INTO v_username;
      CLOSE curs_get_username;

      -- Получение текущей квоты для имени пользователя и табличного пространства 
      OPEN curs_get_ts_quota(v_object_name,v_username);
      FETCH curs_get_ts_quota INTO v_old_quota;
      CLOSE curs_get_ts_quota;

      -- Создание команды ALTER USER и ее отправка заданию 
      -- (потому что при попытке выполнить ее "на месте"
      -- произойдет ошибка ORA-30511)
      v_new_quota := v_old_quota + 40960;
      v_sql := 'ALTER USER '   || v_username  || ' ' ||
               'QUOTA '        || v_new_quota || ' ' ||
               'ON '           || v_object_name;
      fixer.fix_this(v_sql);

    END IF; -- Превышение квоты табличного пространства
 
   END IF; -- Ошибка, связанная с пространством

END;

Этот фрагмент создает триггер, который срабатывает каждый раз, когда команда пере­ходит в приостановленное состояние, и пытается решить проблему. (Обратите внимание: в этом конкретном примере исправляются только ошибки превышения квот табличного пространства.)

Теперь при возникновении проблем с квотами табличного пространства триггер AFTER SUSPEND уровня базы данных срабатывает и помещает команду SQL в таблицу при помощи служебного пакета fixer. Задание fixer, работающее в фоновом режиме, вы­бирает команду SQL из таблицы и выполняет ее, решая проблему с квотой без лишних телефонных звонков.

Полный код триггера AFTER SUSPEND и пакета fixer содержится в файле fixer.sql на сайте github.

 

Функция ORA_SPACE_ERROR_INFO

Информацию о причине приостановки команды можно получить при помощи функ­ции ORA_SPACE_ERROR_lNFO, представленной в предшествующих примерах. Рассмотрим синтаксис вызова этой функции; ее параметры перечислены в табл. 1.

  

Таблица 1. Параметры ORA_SPACE_ERROR_INFO
 

Строки описание
тип_ошибки
Тип ошибки, связанной с пространственной ошибкой; одно из следующих значений:
SPACE QUOTA EXCEEDED: пользователь превысил свою квоту табличного пространства
MAX EXTENTS REACHED: объект пытается выйти за пределы заданного максимального количества сегментов
NO MORE SPACE: в табличном пространстве не хватает места для сохранения новой информации

тип_объекта Тип объекта, для которого произошла пространственная
владелец_объекта Владелец объекта, для которого произошла пространственная ошибка
имя_табличного_пространства Табличное пространство, для которого произошла пространственная ошибка
имя_объекта Имя объекта, для которого произошла пространственная
имя_подобъекта Имя подобъекта, для которого произошла


Функция возвращает логическое значение TRUE, если приостановка происходит из-за одной из ошибок, перечисленных в таблице, или FALSE в противном случае.

Функция ORA_SPACE_ERROR_INFO не исправляет проблемы с пространством в вашей систе­ме; она всего лишь предоставляет информацию, необходимую для выполнения дальней­ших действий. Вы уже видели, как решалась проблема с квотой в предыдущем примере. Ниже приводятся два дополнительных примера SQL, которые могут использоваться для решения проблем с пространством, выявляемых функцией ORA_SPACE_ERROR_INFO:

  •  Таблица (или индекс) достигла максимального количества сегментов, и дополни­тельные сегменты недоступны:
ALTER тип_объекта владелец_объекта. имя_объекта STORAGE (MAXEXTENTS UNLIMITED);
  •  В табличном пространстве полностью закончилось место:
/* Предполагается использование Oracle Managed Files
   (Oracle9i Database и выше), так что явное объявление файла данных 
   не обязательно */
ALTER TABLESPACE имя_табличного_пространства ADD DATAFILE;

 

Пакет DBMS_RESUMABLE

Если функция ORA_SPACE_ERROR_lNFO возвращает FALSE, значит, ситуация, вызвавшая приостановку команды, не может быть исправлена, а следовательно, разумных причин оставаться в приостановленном состоянии тоже нет. Такие команды можно прервать из триггера AFTER_SUSPEND при помощи процедуры ABORT из пакета DBMS_RESUMABLE. Пример:


TRIGGER after_suspend 
AFTER SUSPEND 
ON SCHEMA 
DECLARE
   CURSOR curs_get_sid IS 
   SELECT sid
      FROM v$session
   WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID'); 
  v_sid	NUMBER;
  v_error_type VARCHAR2(30);
...

BEGIN

   IF ORA_SPACE_ERROR_INFO(...
      ...Пытаемся исправить...
ELSE -- can't fix the situation 
   OPEN curs_get_sid;
   FETCH curs_get_sid INTO v_sid;
   CLOSE curs_get_sid;
   DBMS_RESUMABLE.ABORT(v_sid);
END IF;

END;

Процедура ABORT получает один аргумент: идентификатор прерываемого сеанса. Это позволяет вызывать ABORT из триггеров AFTER SUSPEND из уровня DATABASE или SCHEMA. Прерванный сеанс получает ошибку ORA-01013 (пользователь запросил операцию отмены текущей операции).

Действительно, отмена была запрошена пользователем — но каким именно? Кроме процедуры ABORT, пакет DBMS_RESUMABLE содержит функции и процедуры для чтения и назначения тайм-аута:

  •  GET_SESSION_TIMEOUT — возвращает значение тайм-аута приостановленного сеанса по идентификатору сеанса:
FUNCTION DBMS_RESUMABLE.GET_SESSION_TIMEOUT (sessionid IN NUMBER)
RETURN NUMBER;
  •  SET_SESSION_TIMEOUT — задает значение тайм-аута приостановленного сеанса по идентификатору сеанса:
PROCEDURE DBMS_RESUMABLE.SET_SESSION_TIMEOUT (sessionid IN NUMBER,
TIMEOUT IN NUMBER);
  •  GET_TIMEOUT — возвращает значение тайм-аута текущего сеанса:
FUNCTION DBMS_RESUMABLE.GET_TIMEOUT RETURN NUMBER;
  •  SET_TIMEOUT — задает значение тайм-аута текущего сеанса:
PROCEDURE DBMS_REUSABLE.SET_TIMEOUT (TIMEOUT IN NUMBER);

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

 

Многократное срабатывание

Триггеры AFTER SUSPEND срабатывают при каждой приостановке команды. Следователь­но, они могут многократно сработать во время выполнения одной команды. Например, представьте, что в системе реализован следующий жестко запрограммированный триггер:


TRIGGER after_suspend 
AFTER SUSPEND ON SCHEMA 
DECLARE
   -- Получение нового максимума (текущий плюс 1)
   CURSOR curs_get_extents IS 
   SELECT max_extents + 1 
      FROM user_tables
     WHERE table_name = 'MONTHLY_SUMMARY'; 
v_new_max NUMBER;

BEGIN
   - Выборка нового максимального количества сегментов 
   OPEN curs_get_extents;
   FETCH curs_get_extents INTO v_new_max;
   CLOSE curs_get_extents;
   -- alter the table to take on the new value for maxextents 
   EXECUTE IMMEDIATE 'ALTER TABLE MONTHLY_SUMMARY ' ||
                     'STORAGE ( MAXEXTENTS '        ||
                     v_new_max                      || ')';
   DBMS_OUTPUT.PUT_LINE('Incremented MAXEXTENTS to ' || v_new_max);
END;

Если начать с пустой таблицы со значением MAXEXTENTS (максимальное количество сегментов), равным 1, вставка данных объемом 4 сегмента дает следующий результат:

SQL> @test

Incremented MAXEXTENTS to 2 
Incremented MAXEXTENTS to 3 
Incremented MAXEXTENTS to 4

PL/SQL procedure successfully completed.

 

Исправлять или не исправлять?

Вот в чем вопрос! Предшествующие примеры показали, как ошибки нехватки места ис­правляются на ходу — команды приостанавливаются до того, как вмешательство (ручное или автоматизированное) позволит им продолжить работу. Если довести происходящее до крайности, этот метод позволяет устанавливать приложения с минимальными тре­бованиями к табличному пространству, квотам и сегментам, а затем наращивать их по мере надобности. Слишком старательному администратору базы данных такая ситуация покажется идеальной, но у нее есть свои недостатки:

  •  Нерегулярные паузы — паузы с приостановкой команд могут породить хаос в круп­номасштабных оперативных системах обработки транзакций (OLTP), требующих высокой пропускной способности. Ситуация дополнительно усугубляется, если на исправление уходит много времени.
  •  Конкуренция за ресурсы — приостановленные команды удерживают блокировку таблиц. Это может привести к простоям или сбоям при выполнении других команд.
  •  Непроизводительные затраты — ресурсы, необходимые для частого добавления сегментов или файлов данных или увеличения квот, могут оказаться неприемлемо высокими по сравнению с ресурсами, необходимыми для непосредственной работы приложения.

По этим причинам я рекомендую осторожно использовать триггеры AFTER SUSPEND. Они идеально подходят для долгих процессов, которые должны перезапускаться после сбоя, а также для пошаговых процессов, для перезапуска которых необходима отмена внесенных изменений командами DML. В приложениях OLTP они работают недостаточно хорошо.

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6925 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3070 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5680 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 10771 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Войдите чтобы комментировать