Если вам приходилось иметь дело с "долгоиграющими" PL/SQL процедурами, которые выполняются от десятков минут до многих часов, то перед вами вставал вопрос, сколько работы уже сделано на данный момент и сколько еще осталось. Как заглянуть внутрь выполняющейся PL/SQL процедуры?
Если процедура изменяет данные в таблицах и периодически выполняет COMMIT, то понять, сколько работы уже сделано, можно с помощью запросов к изменяемым процедурой таблицам. Но это не всегда работает. Например, если выполняется сложный анализ, по итогам которого изменения делаются не во всех случаях, то с помощью запросов к таблицам не получится узнать, какая часть данных уже проанализирована.
В этом случае нужно предусмотреть механизм, который обеспечит видимость прогресса работы процедуры из другого сеанса работы с БД. Это может быть специальная таблица, в которую PL/SQL процедура будет вставлять записи о завершении отдельных этапов работы и о прогрессе каждого этапа. Как вариант, записи могут добавляться и обновляться в рамках автономных транзакций. Тогда, делая запрос к этой таблице в отдельном сеансе, можно видеть текущий прогресс выполнения PL/SQL процедуры.
В большинстве случаев нет необходимости изобретать велосипед и реализовавать собственное решение для отслеживания прогресса "долгоиграющих" операций. СУБД Oracle предоставляет несколько средств, которыми можно вопользоваться для этой цели (а также для целей отладки, профилирования, и других, которые подскажет ваша фантазия) - это пакеты DBMS_APPLICATION_INFO, DBMS_ALERT и DBMS_PIPE. Все эти средства позволяют тем или иным образом организовать передачу данных из одного сеанса работы с СУБД Oracle в другой.
Ниже я покажу, как можно организовать слежение за работой выполняющейся PL/SQL процедуры при помощи пакета DBMS_APPLICATION_INFO.
Пакет DBMS_APPLICATION_INFO позволяет публиковать информацию о работе приложения в системных таблицах Oracle, а именно:
Процедура пакета dbms_application_info
| Устанавливаемые столбцы |
|---|---|
set_module | v$session.module |
set_action | v$session.action |
set_client_info | v_session.client_info |
set_session_longops | Столбцы таблицы v$session_longops |
Столбец module предназначен для установки имени текущего выполняемого модуля (до 48 байт), столбец action - текущего выполняемого действия (до 32 байт), и столбец client_info - произвольной информации (до 64 байт). Такие жесткие ограничения на размер данных, вероятно, связаны с тем, что v$session размещается в оперативной памяти. Отсюда же высокое быстродействие операций с данной таблицей.
Для начала, продемонстрирую работу со столбцами v$session в рамках одного сеанса.
SQL> BEGIN
2 dbms_application_info.set_module('Прохожий', 'Приветствую');
3 dbms_application_info.set_client_info('Привет мир!');
4 END;
5 /
PL/SQL procedure successfully completed
SQL> SELECT audsid, module, action, client_info
2 FROM v$session
3 WHERE audsid = USERENV('SESSIONID');
AUDSID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
40833659 Прохожий Приветствую Привет мир!
SQL> BEGIN
2 dbms_application_info.set_action('Прощаюсь');
3 dbms_application_info.set_client_info('Пока-пока!');
4 END;
5 /
PL/SQL procedure successfully completed
SQL> -- другой способ получения параметров текущего сеанса
SQL> SELECT sys_context('userenv' , 'sessionid') sessionid,
2 sys_context('userenv' , 'module') module,
3 sys_context('userenv', 'action') action,
4 sys_context('userenv', 'client_info') client_info
5 FROM dual;
SESSIONID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
40833659 Прохожий Прощаюсь Пока-пока!
SQL>
SQL> BEGIN
2 -- очистить module, action и client_info
3 dbms_application_info.set_module(NULL, NULL);
4 dbms_application_info.set_client_info(NULL);
5 END;
6 /
PL/SQL procedure successfully completed
SQL> SELECT audsid, module, action, client_info
2 FROM v$session
3 WHERE audsid = USERENV('SESSIONID');
AUDSID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
40833659
Следующий пример имитирует работу длительной процедуры в одном сеансе и показывает чтение данных о прогрессе ее работы в другом сеансе.
SQL> -- сеанс 1
SQL> BEGIN
2 dbms_application_info.set_module('Счетчик', 0);
3 FOR i IN 1..10 LOOP
4 -- делать что-то небыстрое и полезное
5 dbms_lock.sleep(5);
6 dbms_application_info.set_action(i);
7 END LOOP;
8 dbms_application_info.set_module(NULL, NULL);
9 END;
10 /
Запустив на выполнение PL/SQL блок в первом сеансе, быстро переключимся во второй сеанс и многократно выполним запрос к таблице v$session:
SQL> -- сеанс 2
SQL> SELECT audsid, module, action, client_info
2 FROM v$session
3 WHERE module = 'Счетчик';
AUDSID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
40833659 Счетчик 0
SQL> /
AUDSID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
40833659 Счетчик 1
SQL> /
AUDSID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
40833659 Счетчик 2
...
...
...
SQL> /
AUDSID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
40833659 Счетчик 9
SQL> /
AUDSID MODULE ACTION CLIENT_INFO
---------- -------------------- -------------------- --------------------
Как видим, от запроса к запросу изменяется значение в столбце action, что отражает прогресс работы PL/SQL блока в первом сеансе. Когда блок заканчивает работу, очистив module и action, запрос не возвращает ничего.
Теперь рассмотрим работу с таблицей v$session_longops, которая предназначена специально для организации слежения за прогрессом длительных операций.
В следующем примере, функция set_session_longops_nohint резервирует строку в таблице v$session_longops для использования в текущем сеансе (поскольку v$session_longops - необычная таблица с ограниченным числом строк). Полученный идентификатор строки присваивается переменной rindex и далее используется для обновления значений в этой строке с помощью функции set_session_longops.
SQL> -- сеанс 1
SQL> DECLARE
2 rindex BINARY_INTEGER;
3 slno BINARY_INTEGER;
4 totalwork number;
5 sofar number;
6 obj BINARY_INTEGER;
7 BEGIN
8 rindex := dbms_application_info.set_session_longops_nohint;
9 sofar := 0;
10 totalwork := 10;
11
12 WHILE sofar < 10 LOOP
13 -- делать медленно важное дело
14 dbms_lock.sleep(5);
15
16 sofar := sofar + 1;
17 dbms_application_info.set_session_longops(
18 rindex => rindex, -- ид. строки в v$session_longops
19 slno => slno, -- зарезервировано Oracle
20 op_name => 'Having coffee', -- имя длительной операции
21 target => obj, -- ид. обрабатываемого объекта
22 context => 0, -- произвольное число, которое мы хотим публиковать
23 sofar => sofar, -- сколько уже сделано
24 totalwork => totalwork, -- сколько всего нужно сделать
25 target_desc => 'cup', -- одна единица работы
26 units => 'cups' -- единицы работы (мн. число)
27 );
28 END LOOP;
29 END;
30 /
Запустив на выполнение PL/SQL блок в первом сеансе, переключимся во второй сеанс и многократно выполним запрос к таблице v$session_longops. Условие запроса time_remaining > 0 позволяет видеть только операции, которые еще не завершены:
SQL> -- сеанс 2
SQL> SELECT opname,
2 target_desc,
3 sofar,
4 totalwork,
5 units,
6 context,
7 time_remaining,
8 elapsed_seconds
9 FROM v$session_longops
10 WHERE
11 time_remaining > 0;
OPNAME TARGET_DESC SOFAR TOTALWORK UNITS CONTEXT TIME_REMAINING ELAPSED_SECONDS
--------------- --------------- ------ --------- --------------- ------- -------------- ---------------
SQL> /
OPNAME TARGET_DESC SOFAR TOTALWORK UNITS CONTEXT TIME_REMAINING ELAPSED_SECONDS
--------------- --------------- ------ --------- --------------- ------- -------------- ---------------
Having coffee cup 2 10 cups 0 20 5
SQL> /
OPNAME TARGET_DESC SOFAR TOTALWORK UNITS CONTEXT TIME_REMAINING ELAPSED_SECONDS
--------------- --------------- ------ --------- --------------- ------- -------------- ---------------
Having coffee cup 3 10 cups 0 23 10
...
...
...
SQL> /
OPNAME TARGET_DESC SOFAR TOTALWORK UNITS CONTEXT TIME_REMAINING ELAPSED_SECONDS
--------------- --------------- ------ --------- --------------- ------- -------------- ---------------
Having coffee cup 9 10 cups 0 4 40
SQL> /
OPNAME TARGET_DESC SOFAR TOTALWORK UNITS CONTEXT TIME_REMAINING ELAPSED_SECONDS
--------------- --------------- ------ --------- --------------- ------- -------------- ---------------
В таблице v$session_longops есть замечательный столбец message, в котором собираются воедино данные о текущем прогрессе длительной операции. Еще раз запустим PL/SQL блок в первом сеансе и выполним следующий запрос во втором:
SQL> -- сеанс 2
SQL> SELECT message FROM v$session_longops WHERE time_remaining > 0;
MESSAGE
--------------------------------------------------------------------------------
SQL> /
MESSAGE
--------------------------------------------------------------------------------
Having coffee: cup : 2 out of 10 cups done
SQL> /
MESSAGE
--------------------------------------------------------------------------------
Having coffee: cup : 3 out of 10 cups done
...
...
...
SQL> /
SQL> /
MESSAGE
--------------------------------------------------------------------------------
Having coffee: cup : 9 out of 10 cups done
SQL> /
MESSAGE
--------------------------------------------------------------------------------
Остается добавить, что, поскольку v$session_longops необычная таблица, то удаление строк из нее не предуcмотрено. Но это не предмет для беспокойства. Строки со временем повторно используются для новых длительных операций.
Обратите внимание, что данные, публикуемые в таблицах v$session и v$session_longops с помощью DBMS_APPLICATION_INFO, становятся доступны другим сеансам до завершения транзакции! Ни в одном из приведенных примеров не использована команда COMMIT.
В заключение, предлагаю познакомиться с процедурами и функциями пакета DBMS_APPLICATION_INFO по официальной документации СУБД Oracle 11gR2.
Хорошая статья, спасибо! :)
ОтветитьУдалитьздорово все расписано и показано примерами очень доходчиво. спасибо вам большое
ОтветитьУдалитьСпасибо большое! То, что нужно!
ОтветитьУдалить