Как проследить за выполнением программ в Oracle

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

Введение

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

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

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

Пакет DBMS_PROFILER

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

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

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

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

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

Ниже описаны действия в 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.

Создание профиля работы (состоящей из поочередного выполнения двух процедур):

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_* при желании можно ограничить средствами “виртуальных частных БД” (средством избирательного доступа к данным таблиц).

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