2004 г.

Oracle_trace - лучшее встроенное средство диагностики?

Джонатан Льюис, www.jlcomp.demon.co.uk
Перевод Валерия Кравчука

В сервер Oracle встроено множество диагностического кода. Часть его, например, sql_trace, хорошо описана в документации, а часть, например, представление x$trace, не документирована вовсе. Я люблю периодически посвящать некоторое время повторному анализу такого кода, чтобы узнать, насколько расширены его возможности, получили ли они официальное признание и описаны ли в документации. Недавно, работая с сервером Oracle 9i, я с удивлением обнаружил существенное расширение возможностей oracle_trace, которое произошло за последних пару релизов. Эта статья представляет собой краткое введение в oracle_trace и описание его возомжностей.

Как... ?

Как найти объект, являющийся источником всех событий buffer busy waits, которые можно увидеть в представлении v$waitstat?

Все мы читали руководства по настройке производительности: "Если вы видите ... может потребоваться увеличить количество списков свободных мест (freelists) для проблемной таблицы". Но там не сказано, как найти эту самую проблемную таблицу.

Вариант 1: выполнять непрерывный поток запросов к представлению v$session_wait и проверять значения столбцов p1, p2, p3 при возникновении этого события. Статистически, рано или поздно вы получите таким образом обоснованное представление о том, какой объект или объекты являются причиной проблемы. Этот вариант - достаточно болезненный, и результат его отчасти зависит от везения.

Вариант 2: включить событие 10046 на уровне 8 и получить поток информации об ожиданиях в трассировочных файлах. Весьма серьезно нагружает систему и тоже требует некоторого везения.

Вариант 3: есть событие (10240), которое должно порождать в трассировочном файле список адресов блоков, которые мы ожидаем (ура!), но мне еще не удавалось заставить это событие работать. Если вы знаете, как это сделать, сообщите мне, поскольку данное решение, безусловно, является оптимальным.

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

Что такое oracle_trace?

oracle_trace - это компонент сервера, собирающий информацию о событиях, используя сравнительно небольшие ресурсы.

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

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

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

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

Использование средств oracle_trace

Ну, и как oracle_trace поможет ответить на исходный вопрос?

Просто: один из классов событий, которые можно трассировать, - ожидания. Надо проверить, что сервер запущен в режиме, позволяющем включить трассировку, а затем потребовать от него (либо с помощью PL/SQL, либо из командной строки) начать трассировать ожидания (waits). При этом мы ограничиваем набор событий одижания только событием 92 (это buffer busy waits в Oracle 9i, но проверьте, на всякийц случай, значения столбцов event# и name из представления v$event_name в вашей системе). Затем остается сидеть и ждать примерно час в период, когда проблема ощущается острее всего. Когда получим достаточно большой файл трассировки, прекращаем трассировку, помещаем данные из файла трассировки в базу и выполняем SQL-оператор, запрашивающий, скажем, следующее:

Для каких объектов возникали события buffer busy waits, сколько приходилось ждать, как часто возникали ожидания и кто более всего пострадал?

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

Собираем все вместе

Сначала надо установить ряд параметров инициализации, чтобы на сервере можно было включить трассировку, но не включать ее сразу. Список этих параметров представлен на рис. 1.

Обязательные      
oracle_trace_enable = true 
oracle_trace_collection_name = ** 
       
Стандартные значения
oracle_trace_collection_size = 5242880
oracle_trace_collection_path =
 ?/otrace/admin/cdf
oracle_trace_facility_path = 
?/otrace/admin/fdf
oracle_trace_facility_name = oracled

Рисунок 1: Параметры инициализации, связанные с oracle_trace

Параметру oracle_trace_collection_name нужно явно задать пустое значение "", ибо его стандартное значение - "oracle", а если имя набора указано и трассировка включена, сервер Oracle выполняет трассироку на уровне экземпляра с момента запуска (ого!).

Параметр oracle_trace_collection_path задает каталог, в котором будут размещаться файлы. В каталоге oracle_trace_facility_path размещаются списки событий, которые можно трассировать (facility definition files - файлы определения средств, предоставляемые Oracle Corporation). Параметр oracle_trace_facility_name задает список событий, которые нас интересуют. Наконец, можно ограничить размер (в байтах) файла с трассировочной информацией, задав значение параметра oracle_trace_collection_size.

После запуска сервера можно начинать сбор трассировочной информации.

В этой статье я буду использовать только средства командной строки, хотя есть и альтернативный PL/SQL-интерфейс (пакет dbms_oracle_trace_agent - прим. переводчика), и даже графический интерфейс, если купить соответствующий модуль для Oracle Enterprise Manager. Мы будем использовать команду следующего вида:

otrccol start 1 otrace.cfg

Команда otrccol - основной интерфейс для oracle_trace. Есть и другие команды, но большинство их возможностей были добавлены в otrccol. Очевидно, что параметр start требует начать трассировку (а параметр stop - ее остановить). Значение "1" - произвольно выбранный идентификатор задания, а otrace.cfg  - файл конфигурации. Пример файла конфигурации представлен на рис. 2.

Используется для сбора данных 
       
col_name = jpl 
cdf_file = jpl 
dat_file = jpl 
fdf_file = waits.fdf 
max_cdf = -10485760 
buffer_size = 1048576 
regid = 1 192216243 7 92 5 d901 
       
Используется для форматирования

username = otrace 
password = otrace 
service = d901 
full_format = 1 

Рисунок 2: Пример файла конфигурации oracle_trace

Этот файл требует от сервера создать файл с набором данных по имени jpl.dat, с файлом определения набора (collection definition file) по имени jpl.cdf и идентификатором набора jpl. Определение трассируемых средств находится в файле waits.fdf (этот файле предоставляется корпорацией Oracle и содержит только события ожидания). Размер файла трассировки будет ограничен 10 Мбайтами, но он будет использоваться повторно, так что, всегда будет содержать 10 Мбайт последних данных. Перед сбросом данных в этот файл сервер Oracle будет накапливать их в буфере размером 1 Мбайт.

Возможность задать regid - одна из наиболее мощных возможностей oracle_trace. "Стандартное" значение этой строки содержит '0 0' вместо моих '7 92', и треубет, чтобы oracle_trace трассировал весь экземпляр Oracle, который задается идентификатором d901 в конце строки. Я же попросил трассировать только средство номер 7 (события ожидания) элемент 92 (ожидания buffer busy waits).

При необходимости можно указывать в файле несколько строк regid. Для первого набора экспериментов я использовал две строки regid в файле конфигурации, задающие трассировку '7 129' и '7 130' - последовательные (sequential) и выборочные (scattered) чтения, соответственно, поскольку эти типы ожиданий легко сгенерировать.

Раздел, задающий особенности форматирования, я прокомментирую далее.

После того, как система поработает некоторое время, выполним:

otrccol stop 1 otrace.cfg
otrccol format otrace.cfg

Первая команда останавливает трассировку, вторая - читает файл и сбрасывает данные в ряд таблиц Oracle.

Однако прежде чем вы сможете сформатировать набор, надо создать схему, в которой будут находиться таблицы, используемые при форматировании. В качестве имени и пароля пользователя мы используем значения, представленные ранее на рис. 2. Строка full_format=1 в файле конфигурации приводит к тому, что в таблицы будет сброшен весь файл; установка full_format=0 приведет к сбросу только новых данных. Обратите внимание также на имя службы (service) - оно задает базу данных, в которой находится соответствующая учетная запись. Чтобы использовать команду format, надо запустить процесс прослушивания TNS (TNS listener), даже если данные сбрасываются в локальную базу.

На рис. 3 представлен небольшой сценарий, создающий учетную запись и предоставляющий ей необходимые привилегии.

create user otrace identified by otrace 
  default tablespace users 
  -- если не используется 9i: 
  -- temporary tablespace temp 
  quota 100m on users; 
  
grant create session to otrace; 
grant create table to otrace; 
grant create sequence to otrace; 
grant create synonym to otrace; 

Рисунок 3: Создание пользователя, в схеме которого будут находиться таблицы трассировки

При указании опции format программа автоматически (по крайней мере, в новых версиях Oracle) создаст необходимые таблицы в указанной схеме. Часть этих таблиц бцдет иметь вполне осмысленные имена, например:

EPC_COLLECTION

Имена других будут лишены всякого смысла:

V_192216243_F_5_E_9_9_0

Проблему с неудобными именами можно решить, запусив сценарий otrcsyn.sql в каталоге $ORACLE_HOME/otrace/demo.

Этот сценарий создает синонимы для таблиц, давая им осмысленные имена, например:

WAIT
CONNECTION

(Имена отличаются в разных версиях Oracle.)

Я обнаружил небольшую проблему с автоматической генерацией таблиц. Если указать один из очень избирательных списков средств (например, waits.fdf), создаются только таблицы, необходимые для представления соответствующих результатов при форматировании. Если затем вы решите использовать более полный список трассируемых средств (например, oracle.fdf) при форматировании произойдет сбой, поскольку часть таблиц есть, а других - нет. Так что, может иметь смысл поработать несколько секунд, трассируя oracle.fdf, сформатировать данные, а затем выбросить (truncate) данные из всех таблиц. Это грубый, но эффективный метод настройки соответствующей схемы.

Некоторые результаты

Итак, что же мы сделали:

• Создали файл конфигурации
• Начали сбор данных
• Выполнили определенные действия в базе данных
• Остановили сбор данных
• Сформатировали набор данных

И что теперь?

Предположим, мы использовали файл конфигурации, представленный на рис. 2. Подключившись от имени учетной записи otrace, мы обнаружим строки в таблицах connection, disconnect и wait. Строки в таблице wait расскажут на все о событиях buffer busy waits, произошедших за время трассировки.

Например, мы могли выполнить SQL-оператор, представленный на рис. 4:

select   
  p1 file_id, 
  p2 block_id, 
  p3 reason_code, 
  count(*) ct, 
  sum(time_waited)/100 secs 
    
from   
  wait 
group by   
  p1, p2, p3 
order by   
  sum(time_waited) desc 
; 

Рисунок 4: Пример запроса, позволяющий выявить наиболее продолжительные ожидания занятых блоков

Если необходимо большая точность, можно выдать все ожидания с временными отметками, и столбцом, (довольно оптимистично) названным timestamp_nano.

Если необходимо выяснить, каким пользователям пришлось ждать дольше всего, измените запрос, и суммируйте по столбцам session_index (SID) и session_serial (serial#). Для получения по значениям (session_index, session_serial) имени пользователя, имени машины, времени регистрации и т.п. можно использовать таблицу (синоним) connection.

Конечно, ничего (кроме снижения производительности) не мешает соединять эту таблицу с представлением dba_extents для преобразования идентификаторов файла и блока в типы и имена объектов.

А если необходимо выявить конкретные SQL-операторы, при выполнении которых пришлось ждать, всегда, хотя и ценой затраты еще больших ресурсов, можно перейти на использование файла sql_waits.fdf, который приводит к заполнению трассировочной информацией еще нескольких таблиц, которые затем можно соединять по столбцам session_index, session_serial, timestamp и timestamp_nano.

Наконец, если вы думаете, что затраты на загрузку данных в таблицы и построение отчетов отрицательно скажутся на системе, всегда можно перенести файлы cdf и dat на другую машину и обрабатывать их в другой базе данных. Мне удалось даже, с небольшими исправлениями, сгенерировать набор данных на экземпляре версии 9i, а затем обработать их на экземпляре версии 8i, просто чтобы доказать эту возможность. Это, конечно, затруднит возможность по номерам блоков определять объекты.

Будущее

Возможности бесконечны - например, один из файлов определения средств называется oraclec.fdf и позволяет отслеживать действия в буферном кэше. После трассировки этих действий можно, с точностью до микросекунды, определить, какие блоки были загружены в кэш, в каком порядке, и какие блоки пришлось вытеснить из кэша, чтобы можно было их загрузить. (Я подозреваю, что дополнительные расходы далеко не всегда позволят использовать этот метод.)

Еще одна возможность трассировки, которая многим пригодиться, представлена файлом connect.fdf. Он перехватывает подключения и отключения сеансов, во многом аналогично тому, как работает команда audit session. Однако в трассировочном файле накапливается еще полдюжины дополнительных статистических показателей (таких как записи повторного выполнения), которые в таблицу aud$ не попадают; и в процессе накопления запись в базу данных не выполняется.

Можно добраться и до отдельного пользователя: можно нацелить oracle_trace на трассировку действий одного пользователя. Можно даже написать SQL-оператор, читающий результирующие таблицы и генерирующий файл, аналогичный создаваемому sql_trace. При этом можно будет также отслеживать момент регистрации, перехода с одного разделяемого сервера на другой и, наконец, выхода.

Заключение

Это всего лишь краткое введение в oracle_trace, затронувшее основы его использования. Надо еще поработать над оценкой стабильности работы и побочных эффектов использования oracle_trace, не говоря уже про влияние на производительность.

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

В конечном итоге, oracle_trace позволяет получать точные ответы на ряд сложных вопросов, беспокоящих АБД многие годы.

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

Проблема

Есть много отличий, обычно связанных только с именами, между реализациями oracle_trace в версиях Oracle 8i и Oracle 9i. Эта статья написана исключительно на базе Oracle 9i.

Ссылки

--

Эта статья первоначально была опубликована на сайте DBAzine.com, сетевом портале, посвященном проблемам различных СУБД и их решениям.

Джонатан Льюис (Jonathan Lewis) - независимый консультант с более чем 15-летним опытом работы с СУБД Oracle. Он специализируется на физическом проектировании баз данных и стратегиях использования возможностей СУБД Oracle, является автором книги "Practical Oracle 8I - Designing Efficient Databases", опубликованной издательсвом Addison-Wesley, а также одним из наиболее широко известных лекторов по Oracle в Великобритании. Подробнее об опубликованных им статьях, презентациях и семинарах можно узнать на сайте www.jlcomp.demon.co.uk, где также поддерживается список ЧаВО The Co-operative Oracle Users' FAQ по дискуссионным группам Usenet, связанным с СУБД Oracle.