Давнее средство Oracle SQL Trace позволяет фиксировать «профиль запросов SQL», выдаваемых серверными процессами, обслуживающими сеансы связи с СУБД, и представляет из себя полезный инструмент для выявления проблемных запросов. До версии 10 оно могло включаться и выключаться только для конкретных сеансов связи с СУБД. Это далеко не всегда удобно, поскольку в жизни более насущны профилирование и отладка приложения , или даже его фрагментов, а между приложением и сеансом чаще всего нет взаимнооднозначной связи.
Новый для версии 10 пакет DBMS_MONITOR расширил ранее имевшуюся возможность трассировки действий в рамках сеанса (своего или чужого) возможностью отслеживания действий отдельных приложений и их частей. Для последней цели используется модель «служба БД» - «модуль» - «действие». Между этими понятиями нет формальной зависимости, но методически предлагается связывать с «модулем» приложение, работающее с данными, предоставляемыми «службой БД», а с «действием» - работу фрагментов приложения. Точнее, средствами пакета можно отслеживать выдачу запросов SQL со стороны следующих единиц:
- «служб», отождествляемых значением SERVICE_NAME,
- «модулей», отождествляемых значением MODULE,
- «действий», отождествляемых значением ACTION,
- сеансов, отождествляемых значением CLIENT_IDENTIFIER,
- сеансов, отождествляемых значениями <sid, serial#>,
- экземпляров СУБД, отождествляемых значением INSTANCE_NAME.
Просмотр и установка единиц слежения рассматриваются ниже.
Вдобавок пакет DBMS_MONITOR позволяет собирать обобщенную статистику выполняемых сеансами или приложениями запросов.
Установка единиц слежения для DBMS_MONITOR и просмотр существующих значений
Упомянутые значения единиц слежения за работой приложения наблюдаемы либо из полей таблиц словаря-справочника, либо из стандартного контекста сеанса с именем USERENV:
| Значение | Поле таблиц | Параметр контекста USERENV сеанса |
| SERVICE_NAME | V$SESSION, V$SERVICES | SERVICE_NAME |
| MODULE | V$SESSION | MODULE |
| ACTION | V$SESSION | ACTION |
| CLIENT_IDENTIFIER | V$SESSION | CLIENT_IDENTIFIER |
| SID | V$SESSION | SID |
| SERIAL# | V$SESSION | |
| INSTANCE_NAME | V$INSTANCE | INSTANCE_NAME |
Например, все значения, кроме SERIAL#, наблюдаемы в соответствующих параметрах контекста USERENV сеанса связи с СУБД.
Для дальнейшего просмотра этих параметров в SQL*Plus удобно подготовить файл с параметризованным обращением к контексту USERENV посредством системной функции SYS_CONTEXT:
SELECT SYS_CONTEXT ( 'userenv', '&1' ) AS &1 FROM dual . SAVE userenv REPLACE SET VERIFY OFF
Установка и пример просмотра SERVICE_NAME
Значение SERVICE_NAME выставляется автоматически при установлении соединения или программно с помощью вызова DBMS_SERVICE.CREATE_SERVICE. Пример просмотра:
SQL> CONNECT scott/tiger@prima.class Connected. SQL> @userenv SERVICE_NAME SERVICE_NAME -------------------------------------------------------------------- prima.class
Возможные значения SERVICE_NAME указываются в сетевых установках Oracle и должны быть зарегистрированы в качестве службы БД процессом listener . Значения зарегистрированных в рамках БД служб можно наблюдать в таблице V$SERVICES.
Упражнение. Просмотреть список служб, зарегистрированных для рабочей БД. Дать попытку интерпретации служб SYS$USERS и SYS$BACKGROUND. Проверить предположение.
Пример установки и просмотра MODULE и ACTION
Значения MODULE и ACTION устанавливаются программно с помощью пакета DBMS_APPLICATION_INFO. Пример:
SQL> EXECUTE DBMS_APPLICATION_INFO.SET_MODULE - 2 ( 'some module', 'some action' ) PL/SQL procedure successfully completed. SQL> @userenv MODULE MODULE -------------------------------------------------------------------- some module SQL> @userenv ACTION ACTION -------------------------------------------------------------------- some action SQL> EXECUTE DBMS_APPLICATION_INFO.SET_ACTION ( '' ) PL/SQL procedure successfully completed. SCOTT> / ACTION --------------------------------------------------------------------- SQL>
Пример установки и просмотра CLIENT_IDENTIFIER
Значение CLIENT_IDENTIFIER выставляется программно с помощью пакета DBMS_SESSION или же вызовом OCI setClientIdentifier (только JDBC в версии 9). Пример:
SQL> @userenv CLIENT_IDENTIFIER CLIENT_IDENTIFIER --------------------------------------------------------------------- SQL> EXECUTE DBMS_SESSION.SET_IDENTIFIER ( 'Web client' ) PL/SQL procedure successfully completed. / CLIENT_IDENTIFIER ------------------------------------------------------------------- Web client
Назначение CLIENT_IDENTIFIER - «метить» сеансы конечных пользователей, организованные по схеме использования общих серверных процессов (shared server), в частности, через сервер приложений (например, приложений для web).
Пример отслеживания запросов SQL со стороны приложения и его элементов
Процедура SERV_MOD_ACT_TRACE_ENABLE пакета DBMS_MONITOR позволяет следить за выдачей запросов SQL отдельными «приложениями», «модулями» в составе приложений и «действиями» в рамках модулей. Пример:
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE ( service_name => 'prima.class' , module_name => 'SQL*Plus' , action_name => DBMS_MONITOR.ALL_ACTIONS , waits => TRUE , binds => TRUE , instance_name => NULL ); /* * выжидаем, пока продолжается работа */ DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE ( service_name => 'prima.class' , module_name => 'SQL*Plus' , action_name => DBMS_MONITOR.ALL_ACTIONS , instance_name => NULL );
Для параметра MODULE_NAME по аналогии можно указать ALL_MODULES.
Таким же образом включается трассирование запросов, поступающих из текущего или чужого сеансов (SESSION_TRACE_ENABLE/DISABLE), сеансов, помеченых CLIENT_IDENTIFIER (CLIENT_ID_TRACE_ENABLE/DISABLE) и из всех сеансов подряд, или же порождаемых определенным экземпляром СУБД, например из конфигурации RAC (DATABASE_TRACE_ENABLE/DISABLE).
Просмотр профиля выдачи запросов SQL выполняется с помощью программ tkprof и trcsess . Последняя программа стала поставляться в ПО версии Oracle 10 и как раз-таки и выбирает из трассировочных файлов разных процессов данные, относящиеся к указанным единицам слежения. Вот пример, как может выглядеть совместное использование этих программ:
>trcsess output=out.txt module=SQL*Plus c:\oracle\admin\prima\udump\* >tkprof out.txt final.txt
Упражнение. Включить трассирование всех запросов SQL, поступающих от SQL*Plus, и наблюдать накопление профиля выдачи запросов. Отключить трассирование.
Пример сбора статистики о запросах SQL в приложении
Для сеансов, помеченых CLIENT_IDENTIFIER, и для приложений, помеченых комбинациями SERVICE_NAME - MODULE - ACTION, пакет DBMS_MONITOR позволяет не только собирать профиль выдачи запросов SQL, но и статистику затрат СУБД на обработку запросов.
Пример со сбором статистики о работе определенных клиентов. Выдадим:
CONNECT / as sysdba EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE ( 'Web client' ) HOST sqlplus scott/tiger EXECUTE DBMS_SESSION.SET_IDENTIFIER ( 'Web client' ) SELECT COUNT ( * ) FROM dual; EXIT
Здесь с помощью команды HOST программы SQL*Plus был осуществлен короткий запуск сеанса, помеченного значением CLIENT_IDENTIFIER = 'Web client'.
Продолжим в изначальном сеансе от имени SYS:
SYS> SELECT aggregation_type, primary_id 2> FROM dba_enabled_aggregations; AGGREGATION_TYPE PRIMARY_ID --------------------- -------------------------------------------- CLIENT_ID Web client SYS> COLUMN client_identifier FORMAT A20 SYS> COLUMN stat_name FORMAT A35 SYS> COLUMN value FORMAT 99999999 SYS> SELECT client_identifier, stat_name, value 2> FROM v$client_stats 3> ; CLIENT_IDENTIFIER STAT_NAME VALUE -------------------- ----------------------------------- --------- Web client user calls 4 Web client DB time 3807 Web client DB CPU 3807 Web client parse count (total) 2 Web client parse time elapsed 421 Web client execute count 4 Web client sql execute elapsed time 1940 Web client opened cursors cumulative 2 ... SYS> EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE ( 'Web client' ) PL/SQL procedure successfully completed.
Вот перечень системных таблицы, позволяющих контролировать сбор статистики и наблюдать результаты:
DBA_ENABLED_AGGREGATIONS
V$CLIENT_STATS
V$SERVICE_STATS
V$SERV_MOD_ACT_STATS
V$SERVICEMETRIC
V$SERVICEMETRIC_HISTORY
Последние две дают подробную информацию о расходовании процессорного времени.
Это был пример с использованием процедур CLIENT_ID_STAT_ENABLE/DISABLE. Процедуры SERV_MOD_ACT_STAT_ENABLE/DISABLE используются аналогично.
Упражнение. Включить сбор обобщенной статистики выполнения запросов SQL всех соединений к СУБД по SQL*Plus и наблюдать ее накопление. Отключить сбор статистики.