И азъ же иного не имам чим чтити его, но развее пишу дабрая его деяния..
Смиренного инока Фомы слово похвальное о благоверном великом князе Борисе Андреевиче.
Аннотация
Рассматривается применение пакета DBMS_PROFILER для отслеживания работы приложения. Показано, как можно пользоваться пакетом для определения того, какие строки прикладных программ исполнялись, сколько раз, и сколько на их выполнение ушло времени.
Введение
В разработке и в эксплуатации приложений иногда возникает желание отследить, что делает приложение и сколько при выполнении расходуется ресурсов СУБД. При разработке для этой цели можно употребить диалоговый отладчик из числа нескольких популярных (SQL Developer, TOAD и др.). Но если есть уже готовое приложение или требуется автоматизация, диалоговый отладчик не годится. Для таких случаев есть иное решение: использование системных пакетов из состава ПО Oracle, DBMS_PROFILER и DBMS_TRACE.
Основная разница между последними двумя в том, что пакет DBMS_PROFILER сведения о выполнении программ размещает в таблицах БД, а пакет DBMS_TRACE - в трассировочные файлы сеанса. Есть, конечно, и определенные функциональные различия.
Здесь рассказывается о слежении за выполнением приложения с помощью пакета DBMS_PROFILER. Показано, как им можно пользоваться непосредственно, однако заочно читатель, возможно, с этим пакетом уже знаком: обращения к нему нередко встроены в системы диалоговой разработки для PL/SQL.
Пакет DBMS _ PROFILER
Две функции пакета (существующие также в варианте процедур), имена которых характеризуют способ их употребления:
Функция |
Описание |
|
START_PROFILER |
Стартует создание профиля текущего сеанса. Профиль будет сохранен в специальных таблицах. При старте профиль можно именовать. |
|
STOP_PROFILER |
Завершает профилирование сеанса |
Профиль исполняемых в промежутке между их вызовами подпрограмм автоматически фиксируется в особых служебных таблицах. Просмотр профилей - путем запрашивания данных этих таблиц.
Употребление пакета обеспечивается следующими файлами:
Файл |
Описание |
|
dbmspbp.sql [rdbms] |
Создает внешнее определение пакета DBMS_PROFILER |
|
prvtpbp.sql [rdbms] |
Создает тело пакета DBMS_PROFILER (текст файла - объектный код вместо исходного) |
|
profload.sql [rdbms] |
Запускает файлы dbmspbp.sql и prvtpbp.sql и делает необходимые проверки. Должен выполняться от имени SYS. |
|
proftab.sql [rdbms] |
Сценарий создания рабочих таблиц для сбора данные профилей исполнения подпрограмм на PL/SQL: - PLSQL_PROFILER_RUNS - PLSQL_PROFILER_UNITS - PLSQL_PROFILER_DATA и создания генератора номеров PLSQL_PROFILER_RUNNUMBER |
|
profrep.sql [pls] |
Создает набор производных таблиц и пакет PROF_REPORT_UTILITIES, который можно использовать для более удобного извлечения информации из рабочих таблиц. |
|
profsum.sql [pls] |
Набор специальных запросов к данным профиля с использованием попрограмм из PROF_REPORT_UTILITIES. |
|
profdemo.sql [pls] |
Демонстрационный пример употребления. |
[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) содержат и другую полезную информацию, например:
|
PLSQL_PROFILER_RUNS.RUN_TOTAL_TIME PLSQL_PROFILER_RUNS.RUN_COMMENT |
Общее время работы задания Комментарий пользователя |
|
PLSQL_PROFILER_UNITS.TOTAL_TIME PLSQL_PROFILER_UNITS.UNIT_TIMESTAMP |
Общее время работы подпрограммы Момент трансляции подпрограммы (для учета смены версий) |
|
PLSQL_PROFILER_DATA.MIN_TIME PLSQL_PROFILER_DATA.MAX_TIME |
Минимальное и максимальное время исполнения конкретной строки |
Эти поля также можно использовать в запросах для получения более общих или более подробных сведений.
Запуск профилирования действий пользователя можно сделать автоматическим, если включить обращение к DBMS_PROFILER.START_PROFILER в тело триггерной процедуры AFTER LOGON.
Эксплуатация
Ввиду того, что таблицы с профилями контролируемых программ будут регулярно пополняться необходимо выработать регламент их чистки.
Чистка таблиц, ввиду имеющихся связей, выполняется определенном порядке:
DELETE FROM plsql_profiler_data;
DELETE FROM plsql_profiler_units;
DELETE FROM plsql_profiler_runs;
Эту последовательность тоже удобно оформить в виде сценария, либо процедуры.
Таблицы создаются в умолчательном табличном пространстве пользователя. Возможно вы захотите перенести их в иное место.
Наконец, для таблиц с данными профилей и генератора номеров можно создать особую схему, одну на всю БД, предоставив пользователям свободный к ней доступ при помощи публичных синонимов. Доступ к только «собственным» строкам в общих таблицах PLSQL_PROFILER_* при желании можно ограничить средствами «виртуальных частных БД» (средством избирательного доступа к данным таблиц).
Если вы ведете активную коллективную разработку приложения, то вся эта организаторская работа окупится.