Рассматривается применение пакета
DBMS_PROFILER для отслеживания работы приложения. Показано,
как можно воспользоваться пакетом для определения того, какие
строчки прикладных программ исполнялись, сколько раз, и сколько
на их выполнение ушло времени.

Введение

В разработке и в эксплуатации приложений
время от времени появляется желание отследить, что делает приложение и
сколько при выполнении расходуется ресурсов СУБД. При
разработке для этой цели можно употребить диалоговый отладчик
из числа нескольких фаворитных (SQL Developer, TOAD и др.). Но
если есть уже готовое приложение либо требуется автоматизация,
диалоговый отладчик не годится. Для таких случаев имеется другое
решение: внедрение системных пакетов из состава ПО Oracle,
DBMS_PROFILER и DBMS_TRACE.

Основная разница меж последними 2-мя в
том, что пакет DBMS_PROFILER сведения о выполнении программ
располагает в таблицах БД, а пакет DBMS_TRACE – в трассировочные
файлы сеанса. Есть, естественно, и определенные многофункциональные
различия.

Тут рассказывается о слежении за
выполнением приложения при помощи пакета DBMS_PROFILER.
Показано, как им можно воспользоваться конкретно, но
заочно читатель, может быть, с этим пакетом уже знаком:
воззвания к нему часто интегрированы в системы диалоговой
разработки для PL/SQL.

Пакет DBMS_PROFILER

Две функции пакета (имеющиеся также в
варианте процедур), имена которых охарактеризовывают метод их
потребления:

Профиль исполняемых в промежутке меж их
вызовами подпрограмм автоматом фиксируется в особенных
служебных таблицах. Просмотр профилей – методом запрашивания
данных этих таблиц.

Употребление пакета обеспечивается
последующими файлами:

[rdbms] Файл находится в
%ORACLE_HOME%rdbmsadmin.

[pls] Файл находится в
%ORACLE_HOME%plsqldemo.

Ниже описаны деяния в SQL*Plus,
выполняемые на сервере (так как там находится ПО Oracle с
применяемыми в примере сценариями). Фактически внедрение
пакета, естественно, может быть и на клиенте.

Подготовка к работе с пакетом

Установка пакета DBMS_PROFILER от имени SYS
с выполнением нужных проверок: CONNECT / AS SYSDBA
@?/rdbms/admin/profload

Создание таблиц для хранения служебных
данных о прогонах программ: CONNECT scott/tiger
@?/rdbms/admin/proftab

Пример потребления

Сделаем пару обычных процедур: CREATE OR REPLACE PROCEDURE first
AS
n NUMBER := 1;
BEGIN
FOR i IN 1 .. 1000 LOOP
n := n + 1;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE second
AS
BEGIN
DBMS_OUTPUT.PUT_LINE ( SIN ( 1 ) );
first;
END;
/

Направьте внимание:
процедура SECOND обращается к FIRST
в текстах имеются пустые строчки
в процедуре SECOND есть воззвание к “системной” функции
SIN и к “встроенному” пакету DBMS_OUTPUT.

Создание профиля работы (состоящей из
последовательного выполнения 2-ух процедур): EXECUTE dbms_profiler.start_profiler ( ‘Run@ ‘ || SYSTIMESTAMP )
EXECUTE first
EXECUTE second
EXECUTE dbms_profiler.stop_profiler

Профиль работы получил свой номер.
Его можно выявить запросом: COLUMN run_comment FORMAT A60 WORD
COLUMN runid  FORMAT 9999
SELECT
runid
, run_comment
, run_date
FROM
plsql_profiler_runs
ORDER BY
runid
, run_date
;

Этот текст комфортно расположить в файле,
к примеру seeprofiles.sql в текущем каталоге.

Полученый так номер употребляется в запросе
фактически профиля, к примеру в таком: SET VERIFY OFF
COLUMN owner FORMAT A10
COLUMN name FORMAT A10
COLUMN text FORMAT A45 WORD
COLUMN line FORMAT 999
COLUMN occured FORMAT 99999
SELECT
u.unit_owner AS owner
, u.unit_name AS name
, s.line
, total_occur occured
, TRUNC ( d.total_time / 1000000 ) AS «TIME(ms)»
, s.text
FROM
all_source s
, plsql_profiler_data d
, plsql_profiler_units u
WHERE
u.runid = &1
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND s.name = u.unit_name
AND s.type = u.unit_type
AND s.line = d.line#
ORDER BY
unit_owner
, name
, line
;
SET VERIFY ON

В этом примере SQL*Plus запросит номер в
диалоге.

Приведенный текст комфортно расположить в файле,
к примеру seeprofile.sql в текущем каталоге.

Пример потребления запросов о профиле может
смотреться так: SQL> @seeprofiles
RUNID RUN_COMMENT RUN_DATE
—– —————————————————- ———
10 Run@ 17-JAN-07 03.56.55.613000000 PM +03:00 17-JAN-07
1 rows selected.
SQL> @seeprofile 10
OWNER NAME LINE OCCURED TIME(ms) TEXT
—— —— —- ——- ——– ———————————–
SCOTT FIRST 1 0 3 PROCEDURE first
SCOTT FIRST 3 2 0 n NUMBER := 1;
SCOTT FIRST 6 2002 105 FOR i IN 1 .. 1000 LOOP
SCOTT FIRST 7 2000 194 n := n + 1;
SCOTT FIRST 9 2 17 END;
SCOTT SECOND 1 0 3 PROCEDURE second
SCOTT SECOND 5 1 6108 DBMS_OUTPUT.PUT_LINE ( SIN ( 1 ) );
SCOTT SECOND 6 2 3 first;
SCOTT SECOND 7 1 0 END;
9 rows selected.

Другие способности

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

Столбцы таблиц с данными профилей
(PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS,
PLSQL_PROFILER_DATA) содержат и другую полезную информацию,
к примеру:

Эти поля также можно использовать в запросах
для получения более общих либо более подробных сведений.

Пуск профилирования действий юзера
можно сделать автоматическим, если включить воззвание к
DBMS_PROFILER.START_PROFILER в тело триггерной процедуры AFTER
LOGON.

Эксплуатация

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

Очистка таблиц, ввиду имеющихся связей,
производится определенном порядке:

DELETE FROM plsql_profiler_data;
DELETE
FROM plsql_profiler_units;
DELETE FROM plsql_profiler_runs;

Эту последовательность тоже комфортно оформить
в виде сценария, или процедуры.

Таблицы создаются в умолчательном табличном
пространстве юзера. Может быть вы возжелаете перенести их в
другое место.

В конце концов, для таблиц с данными профилей и
генератора номеров можно сделать необыкновенную схему, одну на всю БД,
предоставив юзерам свободный к ней доступ с помощью
общественных синонимов. Доступ к только “своим” строчкам в
общих таблицах PLSQL_PROFILER_* при желании можно ограничить
средствами “виртуальных личных БД” (средством избирательного
доступа к данным таблиц).

Если вы ведете активную коллективную
разработку приложения, то вся эта организаторская работа
окупится.