1602

Прожение А

scott@TKYTE816> create or replace procedure analyze my tables

2 as

3 begin

4 for x in (select table name from user tables)

5 loop

6 execute immediate

7 analyze table x.table name compute statistics;

8 end loop;

9 end; 10 /

Procedure created.

Чтобы запланировать ее выполнение сегодня ночью в 3 часа (точнее, завтра утром), а затем ежесуточно в 3 часа ночи, можно использовать следующий вызов:

scott@TKYTE816> declare

2 l job number;

3 begin

4 dbms job.submit(job => l job,

5 what => analyze my tables;,

6 next date => trunc(sysdate)+l+3/24,

7 interval => trunc(sysdate)+l+3/24);

8 end;

PL/SQL procedure successfully completed.

scott@TKYTE816>select job, to char(sysdate,dd-mon),

2 to char(next date,dd-mon-yyyy hh24:mi:ss) ,

3 interval, what

4 from user jobs

JOB TO CHA TO CHAR(NEXT DATE, D INTERVAL WHAT

33 09-jan 10-jan-2001 03:00:00 trunc(sysdate)+l + 3/24 analyze my tables;

Итак, в следующий раз это задание выполнится в 3 часа ночи 10 января. Для этого мы передали реальную дату, а не строку, как для параметра interval. Мы использовали функций для работы с датами, так что при выполнении, независимо от времени вызова, всегда будет возвращаться 3 часа следующего утра. Это важно. Точно такую же функцию, но в виде строки мы передали в качестве значения параметра INTERVAL. Используется функция, всегда возвращающая 3 утра завтрашнего дня, независимо от времени ее выполнения. Это предотвращает смещение заданий (jobs sliding). Может показаться, что, поскольку первый раз задание выполняется в 3 часа утра, можно задать интервал sysdate+l. Если выполнить это вычисление в 3 утра во вторник, в результате мы получим 3 утра среды. Получим, если задание гарантированно выполнится в указанное время, но это вовсе не обязательно. Задания в очереди обрабатываются последовательно, в соответствии с указанным временем выполнения. При наличии одного процесса обработки очереди сообщений и двух заданий, назначенных на 3 утра, очевидно, что одно из них не выполнится точно в 3 утра. Придется подождать, пока завершится выполнение первого задания. Даже при отсутствии заданий, назначенных на то же вре-

Пакет DBMS JOB 1603

мя, очереди заданий просматриваются периодически, например каждые 60 секунд. Задание, назначенное на выполнение в 3 утра, может быть выбрано из очереди в 3:00:45 утра. Если использовать функцию sysdate+l, в следующий раз задание может быть поставлено на выполнение в 3:00:46 утра. На следующий день в 3:00:45 утра задание еще не будет готово для выполнения и выполнится при следующем просмотре очереди, в 3:01:45 утра. Время выполнения задания медленно сдвигается. Однако это не самое худшее. Предположим, на следующий день в 3 утра с таблицами будут работать и проанализировать их не удастся. Хранимая процедура не сработает и будет возвращена в очередь для выполнения. Теперь это задание окажется смещенным на несколько минут позже 3 утра. Поэтому, чтобы предотвратить смещение времени выполнения заданий, необходимо использовать функцию, возвращающую фиксированн1й момент времени, если выполнение в конкретн1й момент времени является существенным. Если важно, чтобы задание выполнялось именно в 3 утра, надо использовать функцию, всегда возвращающую время 3 утра, независимо от времени ее выполнения.

Многие из таких не смещающихся функций реализовать очень легко, другие - намного сложнее. Например, однажды меня попросили создать задание, собирающее статистическую информацию с помощью STATSPACK c понедельника по пятницу ровно в 7 часов утра и 3 часа дня. Значение параметра INTERVAL для этого задания задать непросто, но давайте рассмотрим для начала псевдокод:

if время - до 15:00

then

вернуть 15:00 СЕГОДНЯ

(другой словами, если мы выполняем это в 7 утра, надо выполнить задание сегодня в 3 часа дня)

else

вернуть 7 утра через 3 дня (если сегодня пятница) либо 1 день (в противном случае) end if

Осталось реализовать эту логику в виде симпатичной функции DECODE или, если для вас это слишком сложно, в виде PL/SQL-функции. Я использовал интервал:

decode(sign(15-to char(sysdate,hh24)), 1, trunc(sysdate)+15/2 4,

trunc(sysdate+decode(to char(sysdate,d), 6, 3, l))+7/24)

Функция decode начинается с вычисления значения SIGN(15-TO CHAR(SYSDATE,HH24)).

SIGN - это функция, возвращающая -1, 0 или 1, если переданное ей выражение имеет, соответственно, отрицательное, нулевое и положительное значение. Если значение было положительным, предполагается, что вызов произошел до 3 часов дня (до 15 часов), поэтому в следующий раз надо будет выполнить задание в TRUNC(SYSDATE)+15/24 (сегодня в 15 часов). С другой стороны, если sign возвращает 0 или -1, надо вернуть значение TRUNC(SYSDATE + DECODE(TO CHAR(SYSDATE,D), 6, 3, l))+7/24. Здесь с помощью функции DECODE мы определяем день недели, чтобы узнать, сколько добавлять дней - три (в пятницу, чтобы вернуть понедельник) или один (в остальные рабочие дни). Полученное количество дней мы добавляем к значению SYSDATE, усекаем время до полуночи и добавляем 7 часов.

1604 Приложение

Бывает, что смещение даты вполне допустимо и даже желательно. Например, если необходимо собирать статистическую информацию из представлений V$ каждые 30 минут в процессе работы сервера, вполне допустимо использовать интервал SYSDATE+l/24/2, добавляющий к текущему моменту времени полчаса.

Нетривиальное планирование

Бывает, как в рассмотренном выше примере, что значение NEXT DATE вычислить одним оператором SQL сложно или время следующего выполнения задания определяется сложным алгоритмом. В этом случае можно определить время следующего выполнения в самом задании.

В начале раздела было сказано, что задание выполняется в следующем PL/SQL-блоке:

DECLARE

job BINARY INTEGER := :job;

next date DATE := :mydate; broken BOOLEAN := FALSE;

BEGIN

WHAT

:mydate := next date;

IF broken THEN :b := 1; ELSE :b := 0; END IF;

END;

Вы уже видели (в подразделе Однократное выполнение задания ), как использовать доступное в блоке значение JOB. Его можно использовать как первичный ключ для таблицы параметров, чтобы в максимальной степени обеспечить совместное использование SQL-операторов сеансами. Можно воспользоваться и значением переменной NEXT DATE. Как демонстрирует представленный выше блок кода, сервер Oracle использует для установки значения переменной NEXT DATE связываемую переменную :mydate в качестве входного параметра процедуры, но он также получает ее значение после выполнения WHAT (заданной процедуры). Если процедура изменит значение переменной NEXT DATE, сервер Oracle будет использовать его в качестве времени следующего выполнения задания. Для иллюстрации этого приема создадим небольшую процедуру P, которая будет выдавать сообщение в таблицу T и устанавливать значение NEXT DATE:

tkyte@TKYTE816> create table t (msg varchar2(80));

Table created.

tkyte@TKYTE816> create or replace

2 procedure p(p job in number, p next date in OUT date)

3 as

4 l next date date default p next date;

5 begin

6 p next date := trunc(sysdate)+l+3/24;

8 insert into t values

9 (Next date имела значение

10 to char(l next date,dd-mon-yyyyhh24:mi:ss)