Введение
Интерфейс событий ожидания продолжает оставаться бесценным инструментом администраторов базы данных из-за широты и глубины предоставляемой им информации, которая помогает локализовать и устранять проблемы производительности системы. На всем протяжении этой статьи мы предполагаем, что читатель знаком с концепциями событий ожидания и интерфейсом событий ожидания в сервере базы данных Oracle. В частности, эта статья предназначена для администраторов базы данных, которые имеют опыт использования интерфейса событий ожидания в сервере Oracle 9i или более ранних версиях и хотят изучить усовершенствования, появившиеся в сервере Oracle 10g. есть существенные пробелы, которые осложняют изучение изменений в сервере Oracle 10g, касающихся событий ожидания и интерфейса событий ожидания. Например, в справочном руководстве Oracle 10g Database Reference все еще приводится список событий ожидания сервера Oracle 9i. Поэтому приложение к этой статье может быть полезно. В нем перечислены все имена событий ожидания для сервера Oracle 10g Release 10.1.0.3, а также их параметры.
Несмотря на то, что события ожидания появились еще в сервере Oracle 7, до сервера Oracle 9i включительно, в интерфейс событий ожидания было внесено не очень много изменений. Сервер Oracle 7.3 имел только 106 типов событий ожидания, в сервере Oracle 9i их число превысило 400. К тому же в сервере Oracle 9i в отчетах утилиты TKPROF появилась информация о событиях ожидания, а в v$-представлениях значения времени ожидания стали показываться в микросекундах. Но на самом деле это – несущественные изменения. В сервере Oracle 10g, с другой стороны, в интерфейсе событий ожидания появилось больше существенных изменений, чем за все предыдущие годы. Теперь имеется более 800 типов событий ожидания, их имена стали более описательными, события ожидания группируются по классам, добавлено несколько новых v$-представлений, к существующим v$-представлениям добавлены полезные столбцы, появились встроенные средства сбора статистических данных – Active Session History (история активных сеансов) и Automatic Workload Repository (автоматически управляемый репозиторий рабочей нагрузки), появилась новая концепция модели времени для наблюдения за тем, на что тратят сеансы свое время, были усовершенствованы средства трассировки сеансов.
Имена событий ожидания
До сервера Oracle 10g некоторые имена событий ожидания были весьма нечеткими и не очень полезными без просмотра значений параметров конкретного события ожидания. Например, событие ожидания "enqueue" (блокировка с очередью) могло указывать на различные ситуации от конкуренции за строки таблицы до ожидания блокировки, определенной пользователем. Имена событий ожидания в сервере Oracle 10g – более описательны по части защелок, блокировок с очередью и ожиданий занятых буферов.
В сервере Oracle 10g все еще осталось событие ожидания "latch free" (освобождение защелки), но также появилось более 26 специфических событий, связанных с защелками. Они охватывают наиболее общие защелки, за которые возможна конкуренция. Раньше, если бы мы увидели, что сеанс ждет события "latch free", то мы получили бы следующую информацию:
SQL> SELECT event, state, p1, p2, p3
2 FROM v$session_wait
3 WHERE sid = 162;
EVENT STATE P1 P2 P3
------------- ------- ----------- ------ -----
latch free WAITING 15113593728 97 5
Для определения смысла параметров p1, p2 и p3 мы должны были бы выполнить запрос к представлению v$event_name:
SQL> SELECT * FROM v$event_name WHERE name = 'latch free';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
------ ---------- --------------- --------------- ---------------
3 latch free address number tries
Увидев, что параметр p2 – номер защелки, для того чтобы определить ожидаемую защелку, мы должны были бы выполнить запрос к представлению v$latch:
SQL> SELECT name
2 FROM v$latch
3 WHERE latch# = 97;
NAME
--------------------
cache buffers chains
В сервере Oracle 10g мы видим просто:
SQL> SELECT event, state
2 FROM v$session_wait
3 WHERE sid = 162;
EVENT STATE
------------------------------ -------
latch: cache buffers chains WAITING
Описательное имя события избавляет нас от двух шагов для определения, какая защелка стала причиной ожидания. Более детальное описание позволяет нам быстрее понять основную причину ожидания.
Имена событий ожидания, связанных с блокировками, стали в сервере Oracle 10g также более описательными. Нет больше события ожидания "enqueue" – оно было заменено 184 событиями с более детализированными именами. Раньше, если сеансы ждали блокировок с очередями, то мы должны были по параметру p1 декодировать тип блокировки:
SQL> SELECT event, state, seconds_in_wait siw
2 FROM v$session_wait
3 WHERE sid = 96;
EVENT STATE SIW
----------------------------------- ------------------- ----------
enqueue WAITING 24
SQL> SELECT sid,
2 CHR (BITAND (p1,-16777216) / 16777215) ||
3 CHR (BITAND (p1, 16711680) / 65535) enq,
4 DECODE (CHR (BITAND (p1,-16777216) / 16777215) ||
5 CHR (BITAND (p1, 16711680) / 65535),
6 'TX', 'Transaction (RBS)',
7 'TM', 'DML Transaction',
8 'TS', 'Tablespace and Temp Seg',
9 'TT', 'Temporary Table',
10 'ST', 'Space Mgt (e.g., uet$, fet$)',
11 'UL', 'User Defined',
12 CHR (BITAND (p1,-16777216) / 16777215) ||
13 CHR (BITAND (p1, 16711680) / 65535)) enqueue_name,
14 DECODE (BITAND (p1, 65535), 1, 'Null', 2, 'Sub-Share',
15 3, 'Sub-Exclusive', 4, 'Share', 5, 'Share/Sub-Exclusive',
16 6, 'Exclusive', 'Other') lock_mode
17 FROM v$session_wait
18 WHERE sid = 96;
SID ENQ ENQUEUE_NAME LOCK_MODE
----- ---- ------------------------------ ----------
96 TX Transaction (RBS) Exclusive
В сервере Oracle 10g мы получаем подробную информацию непосредственно из имени блокировки:
SQL> SELECT event, state, seconds_in_wait siw
2 FROM v$session_wait
3 WHERE sid = 143;
EVENT STATE SIW
----------------------------------- ------------------- ----------
enq: TX - row lock contention WAITING 495
Кроме того, в некоторых случаях параметры p1, p2 и p3 содержат дополнительную информацию, смысл которой зависит от конкретных событий ожидания, связанных с блокировками. (См. в приложении список событий ожидания и их параметров.)
В сервере Oracle 10g, кроме имен событий ожидания защелок и блокировок, более описательные имена имеют и некоторые другие события ожидания. Однако их трудно перечислить полностью из-за состояния документации сервера Oracle 10g на момент написания этой статьи. Один пример изменения другого описательного имени – событие ожидания "buffer busy waits" (ожидания занятых буферов). Ситуации, когда один сеанс ждет другой сеанс, чтобы можно было прочитать с диска нужный блок данных (код причины 130), дали более описательное имя события ожидания – "read by other session" (чтение в другом сеансе).
Классы событий ожидания
В сервере Oracle 10g события ожидания группируются по категориям (классам), которые могут помочь администраторам баз данных быстрее определить вероятную основную причину ожидания. Введены следующие категории:
| Administrative (администрирование) | Idle (простой) |
| Application (приложение) | Network (сеть) |
| Cluster (кластер) | Scheduler (планировщик) |
| Commit (фиксация) | System I/O (системный ввод-вывод) |
| Concurrency (конкурентный доступ) | User I/O (пользовательский ввод-вывод) |
| Configuration (конфигурирование) | Other (другие) |
Около 70% событий ожидания имеют категорию Other (другие) (557 из 811 в версии 10.1.0.3), в классах ожидания наиболее часто встречаются события с полезными именами. Рассмотрим в качестве примера имена некоторых событий типа "enqueue":
SQL> SELECT wait_class, name
2 FROM v$event_name
3 WHERE name LIKE 'enq%'
4 AND wait_class <> 'Other'
5 ORDER BY wait_class;
WAIT_CLASS NAME
------------------------------ ----------------------------------------
Administrative enq: TW - contention
Administrative enq: DB - contention
Application enq: PW - flush prewarm buffers
Application enq: RO - contention
Application enq: RO - fast object reuse
Application enq: TM - contention
Application enq: TX - row lock contention
Application enq: UL - contention
Concurrency enq: TX - index contention
Configuration enq: ST - contention
Configuration enq: TX - allocate ITL entry
Configuration enq: SQ - contention
Configuration enq: HW - contention
Мы видим, что блокировки TX (блокировки строк) и ТМ (блокировки таблиц) находятся в классе Application (приложение), что разумно, так как эти события ожидания обычно возникают из-за поведения приложений. Тогда как блокировки ST (управление пространством), HW (перемещение маркера максимального уровня заполнения) и SQ (номер последовательности) находятся в классе Configuration (конфигурирование), поскольку их возникновение обычно можно уменьшить изменением параметров настройки сервера базы данных и объектов.
В класс User I/O (пользовательский ввод-вывод) входят, как и можно было ожидать, события ожидания "db file scattered read" (чтение "вразброс" в кеш буферов из файлов базы данных), "db file sequential read" (последовательное чтение из файлов базы данных), "direct path read" (чтение в режиме прямого доступа) и "direct path write" (запись в режиме прямого доступа); в то время как класс System I/O (системный ввод-вывод) включает в себя многие события ожидания, связанные с чтением и записью в оперативные и архивные журнальные файлы. Класс Commit (фиксация) имеет только один тип событий ожидания, "log file sync" (синхронизация журнальных файлов), поскольку причиной этих ожиданий являются фиксации транзакций. А класс Idle (простой) составляют различные событий ожидания, которые традиционно называются "событиями простоя", такие, как "SQL*Net message from client" (SQL*Net-сообщение от клиента). Следует заметить, что иногда такие "события простоя" фактически могут быть симптомами основной причины низкой производительности, поэтому они не должны игнорироваться без анализа.
Вообще говоря, классы ожидания помогают администратору базы данных быстрее определять основные причины проблем производительности.
Усовершенствование v$-представлений
В сервере Oracle 10g появилось довольно много новых v$-представлений, которые имеют отношение к событиям ожидания, а также были добавлены новые полезные столбцы к существующим v$-представлениям. В этом разделе мы обсудим отдельные усовершенствования. Вместе с тем, некоторые вновь появившиеся v$-представления – часть важных новых функциональных возможностей сервера Oracle 10g, поэтому они будут рассмотрены позже в соответствующих разделах.
V$EVENT_NAME
В сервере Oracle 10g к представлению v$event_name было добавлено три столбца: wait_class_id (идентификатор класса ожидания), wait_class# (номер класса ожидания) и wait_class (имя класса ожидания). Эти столбцы показывают, к какому классу ожидания относится данное событие ожидания. В предыдущем разделе мы говорили о том, как можно использовать эту информацию. Теперь в представлении v$event_name имеются следующие столбцы:
SQL> DESCRIBE v$event_name
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENT# NUMBER
EVENT_ID NUMBER
NAME VARCHAR2(64)
PARAMETER1 VARCHAR2(64)
PARAMETER2 VARCHAR2(64)
PARAMETER3 VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
V$SQL и V$SQLAREA
Представления v$sql и v$sqlarea в сервере Oracle 10g имеют шесть новых столбцов, которые относятся к событиям ожидания:
| application_wait_time (время ожидания в классе Application) |
| concurrency_wait_time (время ожидания в классе Concurrency) |
| cluster_wait_time (время ожидания в классе Cluster) |
| user_io_wait_time (время ожидания в классе User I/O) |
| plsql_exec_time (время выполнения PL/SQL-кода) |
| java_exec_time (время выполнения Java-кода) |
Эти столбцы предназначены для определения времени, которое SQL-операторы тратят на выполнение PL/SQL- или Java-кода, а также на ожидание в четырех конкретных классах ожидания. Документация сервера Oracle версии 10.1.0.3 не предоставляет почти никакой информации об этих новых столбцах (следует отметить, что данный недостаток устранён в следующей редакции документации – прим. В. Бегун), тем не менее, они могут быть очень важны для диагностирования проблем производительности. Для демонстрации мы воспользуемся примером, который позволит нам восполнить пробелы в документации и проиллюстрирует смысл и режим работы новых столбцов. Предположим, что мы создаем таблицу с именем testtab, приблизительно с миллионом строк. Затем в одном сеансе мы выполняем следующий оператор без фиксации транзакции, а затем выполняем этот же оператор в другом сеансе:
SQL> UPDATE testtab SET numcol = numcol + 1 WHERE ROWNUM < 1000;
Очевидно, второй сеанс будет ждать завершения события ожидания "enqueue" (блокировка с очередью) до тех пор, пока первый сеанс не зафиксирует или не откатит транзакцию. Через некоторое время мы выполним откат транзакции в первом сеансе, а затем и во втором. После этого в третьем сеансе мы выполняем следующий оператор:
SQL> UPDATE testtab SET numcol = numcol + 1;
Используемый нами экземпляр сервера базы данных имеет маленький кеш буферов, поэтому для этого оператора будет выполнено некоторое количество операций физического ввода-вывода. После завершения выполнения операторов UPDATE мы просматриваем представление v$sqlarea:
SQL> SELECT sql_id, application_wait_time appl, concurrency_wait_time concurr,
2 user_io_wait_time user_io
3 FROM v$sqlarea
4 WHERE sql_text LIKE 'UPDATE testtab SET numcol%';
SQL_ID APPL CONCURR USER_IO
------------- --------- --------- -----------
038m56cp4am0c 178500000 0 20000
fd5mxhdbf09ny 0 10000 105040000
SQL> SELECT sql_id, sql_text
2 FROM v$sqlarea
3 WHERE sql_id IN ('fd5mxhdbf09ny','038m56cp4am0c');
SQL_ID SQL_TEXT
------------- -------------------------------------------------------------
038m56cp4am0c UPDATE testtab SET numcol = numcol + 1 WHERE ROWNUM < 1000
fd5mxhdbf09ny UPDATE testtab SET numcol = numcol + 1
Итак, мы видим, что первый оператор (блокирующий строки) затратил 178.5 секунды (178 500 000 микросекунд), ожидая завершения событий в классе ожидания Application (приложение), и 0.02 секунды, ожидая завершения событий в классе ожидания User I/O (пользовательский ввод-вывод). Вспоминая обсуждение классов ожидания, заметим, что блокировки TX (блокировки строк) находятся в классе ожидания Application, а операции чтения из файлов данных – в классе ожидания User I/O. Для второго оператора, которому потребовалось больше дисковых чтений, показаны ~105 секунд в классе User I/O и очень небольшие затраты на конкурентный доступ.
Хотя доступная в настоящее время документация Oracle не предоставляет много информации об этих новых столбцах в представлениях v$sql и v$sqlarea, они, кажется, имеют большие перспективы для диагностирования проблем производительности выполнения запросов.
V$SESSION_WAIT_HISTORY
До сервера Oracle 9i включительно v$-представления показывали нам только самое последнее событие ожидания каждого сеанса. Хотя время ожидания может накапливаться и существенно замедлять процесс, многие ожидания по отдельности очень коротки (с человеческой точки зрения). Поэтому часто трудно перехватить информацию о произошедшем событии ожидания. Новое представление v$session_wait_history в сервере Oracle 10g помогает нам, показывая последние десять событий ожидания, которые происходили в каждом сеансе. В качестве примера приведем следующий запрос, показывающий самые последние десять событий ожидания сеанса 154:
SQL> SELECT sid, seq#, event, wait_time, p1, p2, p3
2 FROM v$session_wait_history
3 WHERE sid = 154
4 ORDER BY seq#;
SID SEQ# EVENT WAIT_TIME P1 P2 P3
--- ---- ------------------------ ---------- ------ ------ ------
154 1 db file sequential read 28 4 3547 1
154 2 log buffer space 18 0 0 0
154 3 log buffer space 36 0 0 0
154 4 db file sequential read 0 4 3559 1
154 5 db file sequential read 0 4 1272 1
154 6 db file sequential read 0 4 3555 1
154 7 log buffer space 9 0 0 0
154 8 db file sequential read 0 4 3551 1
154 9 db file sequential read 6 4 1268 1
154 10 log buffer space 8 0 0 0
Столбец seq# должен показывать хронологическую последовательность (sequence) событий ожидания, где 1 указывает на самое последнее событие ожидания в сеансе. В нашем сервере базы данных версии 10.1.0.3 на платформе Solaris столбец seq# ведет себя по-другому (bug #4429113 – прим. В. Бегун), затрудняя определение самого последнего события ожидания. В любом случае, обратите внимание: значение этого столбца seq# отличается от значения столбца seq# в представлении v$session, которое увеличивается для каждого ожидания, произошедшего в сеансе в течение "срока жизни" сеанса.
В вышеприведенном запросе мы видим, что в самых последних ожиданиях сеанса чередуются события "db file sequential read" (последовательное чтение из файлов базы данных) и "log buffer space" (пространство в журнальном буфере). Это имеет смысл, так как SQL-операторы, которые выполнялись в сеансе, выглядели так:
INSERT INTO table1 (column1, column2)
SELECT column1, column2
FROM table2
WHERE ...
Используя этот список самых последних ожиданий, мы можем также получить более подробную информацию. Значения параметров p1 и p2 события "db file sequential read" указывают номера файла и прочитанного блока, поэтому мы можем быстро определить, из какого сегмента читались данные.
Столбцы представления v$session_wait_history:
SQL> DESCRIBE v$session_wait_history
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_TIME NUMBER
WAIT_COUNT NUMBER
V$SESSION
К представлению v$session в сервере Oracle 10g добавлено несколько очень полезных столбцов. Добавлены столбцы из представления v$session_wait, связанные с событиями ожидания. В предыдущих версиях сервера Oracle для получения более подробной информации о сеансе, в котором имели место ожидания (такой, как текст выполнявшегося SQL-оператора), мы должны были соединить представления v$session_wait и v$session:
SQL> SELECT s.sid, w.state, w.event, w.seconds_in_wait siw,
2 s.sql_address, s.sql_hash_value hash_value, w.p1, w.p2, w.p3
3 FROM v$session s, v$session_wait w
4 WHERE s.sid = w.sid
5 AND s.sid = 154;
В сервере Oracle 10g мы можем получит всю эту информацию из представления v$session:
SQL> SELECT sid, state, event, seconds_in_wait siw,
2 sql_address, sql_hash_value hash_value, p1, p2, p3
3 FROM v$session
4 WHERE sid = 154;
SID STATE EVENT SIW SQL_ADDRESS HASH_VALUE P1 P2 P3
--- ------- ----------------------- --- ---------------- ---------- --- ---- ---
154 WAITING db file sequential read 1 000000038551E820 3625097388 4 9813 1
К представлению v$session добавлены два дополнительных столбца, полезные для анализа событий ожидания: blocking_session (блокирующий сеанс) и blocking_session_status (статус столбца blocking_session). Столбец blocking_session содержит идентификатор сеанса (SID), удерживающего ресурс, который ждет ожидающий сеанс. Столбец blocking_session_status указывает, достоверно ли содержимое столбца blocking_session. Если значение столбца blocking_session_status – VALID, то в столбце blocking_session содержится действительный SID. Раньше, если мы видели, что сеанс ожидает ресурс, то для определения, что удерживает этот ресурс, мы должны были выполнить дополнительные запросы. Если ресурс был блокировкой "enqueue", то для определения, что удерживает ее, мы должны были выполнить запрос к представлению v$lock (иногда очень дорогостоящий запрос). Теперь мы можем узнать это намного быстрее:
SQL> SELECT sid, blocking_session, username,
2 blocking_session_status status
3 FROM v$session
4 WHERE blocking_session_status = 'VALID';
SID BLOCKING_SESSION USERNAME STATUS
--- ---------------- -------- -----------
154 157 TSUTTON VALID
Если мы объединим это с информацией о событии ожидания, доступной теперь в представлении v$session, мы увидим:
SQL> SELECT sid, blocking_session, username,
2 event, seconds_in_wait siw
3 FROM v$session
4 WHERE blocking_session_status = 'VALID';
SID BLOCKING_SESSION USERNAME EVENT SIW
--- ---------------- -------- ------------------------------ ---
154 157 TSUTTON enq: TX - row lock contention 318
V$SYSTEM_EVENTПредставление v$system_event для каждого имени события ожидания показывает (на уровне всей системы, начиная с запуска экземпляра) количество ожиданий, общее и среднее время ожидания. Однако такое объединение данных может исказить истинную картину, поскольку небольшое количество продолжительных ожиданий может "перекосить" эти данные. Рассмотрим следующий запрос к представлению v$system_event:
SQL> SELECT event, total_waits, time_waited, average_wait
2 FROM v$system_event
3 WHERE event = 'enq: TX - row lock contention';
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
----------------------------- ----------- ----------- ------------
enq: TX - row lock contention 17218 2101966 122
Мы видим, что было 17 218 ожиданий, а среднее время ожидания равно 1.22 секунды, но мы не имеем понятия, как это время ожидания распределено. Все ли эти ожидания имели примерно одинаковую продолжительность? Может быть, большинство из них длилось менее одной секунды, а несколько действительно продолжительных ожиданий существенно увеличили это среднее время? Мы не можем сказать этого, исходя из представления v$system_event. Однако в сервере Oracle 10g благодаря представлению v$event_histogram мы можем увидеть более полную картину:
SQL> SELECT event, wait_time_milli, wait_count
2 FROM v$event_histogram
3 WHERE event = 'enq: TX - row lock contention';
EVENT WAIT_TIME_MILLI WAIT_COUNT
----------------------------- --------------- ----------
enq: TX - row lock contention 1 833
enq: TX - row lock contention 2 635
enq: TX - row lock contention 4 372
enq: TX - row lock contention 8 395
enq: TX - row lock contention 16 781
enq: TX - row lock contention 32 3729
enq: TX - row lock contention 64 3050
enq: TX - row lock contention 128 410
enq: TX - row lock contention 256 47
enq: TX - row lock contention 512 46
enq: TX - row lock contention 1024 37
enq: TX - row lock contention 2048 3
enq: TX - row lock contention 4096 6880
Мы видим, что длительность очень немногих из ожиданий была в какой-то степени равна 1.22 секунды. Почти 60% ожиданий имели длительность меньше 0.128 секунды (большинство из них – между 16 и 64 миллисекундами), а большинство из оставшихся ожиданий – между 2.048 и 4.096 секунды (в какой-то момент времени некоторые из них завершались по тайм-ауту и начинались вновь). Таким образом, представление v$event_histogram дает нам более точную картину распределения времени ожидания, чем просто суммирование по именам событий.
Столбцы представления v$event_histogram:
SQL> DESCRIBE v$event_histogram
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENT# NUMBER
EVENT VARCHAR2(64)
WAIT_TIME_MILLI NUMBER
WAIT_COUNT NUMBER
V$SYSTEM_WAIT_CLASS и V$SESSION_WAIT_CLASS
Два новых представления v$system_wait_class и v$session_wait_class позволяют нам получить информацию об ожиданиях на уровне системы и сеанса соответственно, суммированную по классам ожидания. Это дает нам общее представление (без фокусирования на конкретных событиях) о том, что происходит в системе или сеансе. Эти представления примерно эквивалентны представлениям v$system_event и v$session_event, разница лишь в том, что они суммируют данные о событиях по классам ожидания. Время ожидания выражается в сотых долях секунды, начиная с запуска экземпляра для представления v$system_wait_class и создания сеанса для представления v$session_wait_class.
Следующие запросы показывают, какое время (в сотых долях секунды) было затрачено на ожидание в каждом классе для системы (начиная с запуска экземпляра) и одного конкретного сеанса (с его начала):
SQL> SELECT wait_class, time_waited
2 FROM v$system_wait_class
3 ORDER BY time_waited DESC;
WAIT_CLASS TIME_WAITED
------------- -----------
Idle 777450022
System I/O 1261584
User I/O 116667
Configuration 116481
Application 72301
Other 12432
Commit 3496
Concurrency 319
Network 1
SQL> SELECT wait_class, time_waited
2 FROM v$session_wait_class
3 WHERE sid = 154
4 ORDER BY time_waited DESC;
WAIT_CLASS TIME_WAITED
------------- -----------
Idle 612453
User I/O 1500
Configuration 28
Commit 11
Other 0
Application 0
Network 0
Так как значение времени ожидания в каждом классе, показанное в этих представлениях, является накопленным значением с момента запуска системы или сеанса, эти представления лучше всего использовать для определения ожиданий в течение какого-то периода времени, делая выборки из них и сравнивая полученные результаты. Например, вы могли получить данные для всего экземпляра в момент времени T1:
DROP TABLE swc_snap;
CREATE TABLE swc_snap
AS
SELECT wait_class, total_waits, time_waited
FROM v$system_wait_class;
А затем, в момент времени T2, немного позже, получить суммарные ожидания в интервале от T1 до T2:
SELECT a.wait_class, (a.time_waited - b.time_waited) tm_waited
FROM v$system_wait_class a, swc_snap b
WHERE a.wait_class = b.wait_class
AND a.total_waits > NVL (b.total_waits, 0)
ORDER BY tm_waited DESC;
WAIT_CLASS TM_WAITED
--------------- ----------
Idle 255767
Application 171
System I/O 156
User I/O 44
Other 21
Commit 13
Network 1
V$ACTIVE_SESSION_HISTORY
В предыдущих версиях сервера Oracle подробная информация, показываемая в представлении v$session_wait, могла бы оказаться чрезвычайно полезной при диагностировании проблем производительности, если бы вы запрашивали это представление в нужный момент времени. Представление v$session_wait_history в сервере Oracle 10g облегчает перехват подробной информации, сохраняя последние десять событий ожидания для каждого сеанса. Но что, если вам нужна подробная информация об ожиданиях сеансов за более длительный период, имевший место в прошлом, какое-то время назад? Для этого в сервере Oracle 10g предназначены средства Active Session History (ASH, история активных сеансов). Средства ASH собирают подробную информацию об ожиданиях всех сеансов, доступную для нас в течение очень долгого времени.
В сервере Oracle 10g новый фоновый демон-процесс MMNL каждую секунду выполняет запрос к представлению (я не думаю, что это сделано именно так "запрос к представлению" – прим. В. Бегун) v$session и сохраняет информацию обо всех активных сеансах в повторно используемом буфере оперативной памяти, доступном через новое представление v$active_session_history. Время доступности этой информации зависит от активности сеансов и объема памяти, которую сервер Oracle выделил для средств ASH. Цель сервера Oracle – достичь того, чтобы, по крайней мере, несколько часов данные о сеансах были доступны в этом представлении. Представление v$active_session_history содержит большую часть детальной информации о событиях ожидания, показываемой в представлении v$session:
SQL> DESCRIBE v$active_session_history
Name Null? Type
----------------------------------------- -------- ----------------------------
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3)
SESSION_ID NUMBER
SESSION_SERIAL# NUMBER
USER_ID NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_PLAN_HASH_VALUE NUMBER
SQL_OPCODE NUMBER
SERVICE_HASH NUMBER
SESSION_TYPE VARCHAR2(10)
SESSION_STATE VARCHAR2(7)
QC_SESSION_ID NUMBER
QC_INSTANCE_ID NUMBER
EVENT VARCHAR2(64)
EVENT_ID NUMBER
EVENT# NUMBER
SEQ# NUMBER
P1 NUMBER
P2 NUMBER
P3 NUMBER
WAIT_TIME NUMBER
TIME_WAITED NUMBER
CURRENT_OBJ# NUMBER
CURRENT_FILE# NUMBER
CURRENT_BLOCK# NUMBER
PROGRAM VARCHAR2(48)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)
Как видите, в представлении v$active_session_history собираются важные данные из представления v$session, связанные с ожиданиями. Также собирается полезная информация о SQL-операторе, выполняемом в данное время, а также текущие номера объекта, файла и блока, к которым осуществляется доступ. Когда ожидание завершается, сервер Oracle вставляет в столбец time_waited представления v$active_session_history фактическую продолжительность ожидания.
Автоматически управляемый репозиторий рабочей нагрузки, который мы рассмотрим в следующем разделе, регулярно записывает на диск данные из представления v$active_session_history, сохраняя для каждого активного сеанса одну выборку каждые десять секунд. Поэтому информация об активных сеансах остается доступной (хотя с меньшим количеством деталей) даже после того, когда данные будут вытеснены по времени из представления v$active_session_history.
Средства ASH всегда включены, поэтому вы всегда имеете доступ к подробной информации об ожиданиях, случившихся в сеансах за прошедшие несколько часов. Это означает, что, если пользователь жалуется на проблему производительности, вы можете выполнить запрос к представлению v$active_session_history и получить сведения об этой проблеме, не включая расширенную трассировку SQL-операторов или не усиливая наблюдение за представлением v$session, дожидаясь воспроизведения проблемы.
Тем не менее, важно иметь в виду, что представление v$active_session_history заполняется выборкой из представления v$session один раз в секунду. В сеансе в течение одной секунды может произойти много разных событий ожидания, но только одно последнее событие будет зарегистрировано в представлении v$active_session_history. Поэтому средства ASH полезны для общих агрегированных запросов, а не для точного измерения конкретных событий или определения минимального или максимального времени ожидания. Говоря на языке статистики, данные, собранные средствами ASH, более точны, вероятно, на большом временном интервале и/или числе сеансов.
Например, вы могли бы выполнить запрос к представлению v$active_session_history, чтобы определить, какой процент времени за прошедшие два часа конкретная группа сеансов ждет завершения дисковых чтений. Однако же использование этого представления для определения количества ожиданий дисковых чтений за прошедшую минуту, возможно, не будет приводить к очень точным результатам.
Даже притом, что ASH-данные – это только выборка для активных сеансов, полученная информация может оказаться весьма полезной. Например, следующий запрос показывает, что сеансы, в которых выполняется приложение ARXENV, за последние два часа столкнулись с большой конкуренцией за блокировки строк:
SQL> SELECT DECODE (session_state, 'WAITING', event, NULL) event,
2 session_state, COUNT(*), SUM (time_waited) time_waited
3 FROM v$active_session_history
4 WHERE module = 'ARXENV'
5 AND sample_time > SYSDATE - (2/24)
6 GROUP BY DECODE (session_state, 'WAITING', event, NULL),
7 session_state;
EVENT SESSION_STATE COUNT(*) TIME_WAITED
------------------------------ ------------- -------- -----------
ON CPU 124 0
log file sync WAITING 2 52686
db file scattered read WAITING 2 28254
db file sequential read WAITING 1 6059
control file sequential read WAITING 1 9206
SQL*Net break/reset to client WAITING 1 9140
enq: TX - row lock contention WAITING 922 930864016
Дополнительно к выполнению запросов к представлению v$active_session_history вы для подготовки отчетов по ASH-данным можете использовать Enterprise Manager.
Хотя средства ASH по умолчанию работают на всех серверах базы данных Oracle 10g, вам запрещается выполнять запросы к представлению v$active_session_history (или подготавливать соответствующие отчеты в среде Enterprise Manager), если вы не лицензировали диагностический пакет Diagnostic Pack.
AWR
В сервере Oracle 10g появилось еще одно важное средство, имеющее отношение к интерфейсу событий ожидания. Оно называется автоматически управляемым репозиторием рабочей нагрузки Automatic Workload Repository (AWR). Репозиторий AWR представляет собой, по существу, пакет Statspack следующего поколения. По умолчанию репозиторий AWR собирает почасовые моментальные копии информации о производительности сервера базы данных, сохраняя данные в таблицах схемы SYS. Репозиторий AWR конфигурируется автоматически, когда вы создаете сервер базы данных Oracle 10g. Вы можете также вызывать пакет dbms_workload_repository, чтобы собирать данные по требованию, очищать моментальные копии или диапазоны этих копий, изменять интервалы копирования или время хранения информации. (По умолчанию моментальные копии собираются в начале каждого часа и очищаются через семь дней.)
Репозиторий AWR собирает такие же данные, что и пакет Statspack, включая статистические данные на уровне системы, ресурсоемкие SQL-операторы, и, конечно, информацию на уровне экземпляра о событиях ожидания. Репозиторий AWR также собирает новые в сервере Oracle 10g данные, такие, как статистические данные модели времени (которую мы рассмотрим в следующем разделе). Отклоняясь от темы, скажем, что версия пакета Statspack в сервере Oracle 10g также собирает много этой новой информации, включая статистические данные модели времени.
Вы можете сгенерировать отчет репозитория AWR об активности сервера базы данных между двумя моментальными копиями, выполняя в каталоге $ORACLE_HOME/rdbms/admin скрипт awrrpt.sql. Этот скрипт генерирует отчеты, сформатированные как простой текст или в формате HTML. Если прежде вы использовали пакет Statspack, отчеты будут выглядеть привычно. Вы можете также использовать для этого Enterprise Manager.
Репозиторий AWR имеет много преимуществ по сравнению с пакетом Statspack. Например, он значительной частью интегрирован в ядро сервера Oracle, что уменьшает потребности в ресурсах и накладные расходы при сборе моментальных копий. В моментальные копии репозитория AWR в дополнение к сбору данных на уровне системы, знакомому пользователям пакета Statspack, также включаются ASH-данные из представления v$active_session_history, обеспечивая информацию на уровне сеанса.
Данные, собранные репозиторием AWR, легко доступны через представления, имена которых начинаются с DBA_HIST. Это позволяет вам генерировать собственные отчеты, которые содержат только данные, нужные в конкретной ситуации (если по каким-то причинам вы не находите в стандартном отчете репозитория AWR то, что вам нужно). Например, следующий запрос показывает идентификаторы двух самых последних моментальных копий:
SQL> SELECT snap_id, begin_interval_time, end_interval_time
2 FROM (
3 SELECT snap_id, begin_interval_time, end_interval_time
4 FROM dba_hist_snapshot
5 ORDER BY end_interval_time DESC
6 )
7 WHERE ROWNUM <= 2;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------- -------------------------
362 10-MAR-05 04.00.02.018 PM 10-MAR-05 05.00.36.581 PM
361 10-MAR-05 03.00.25.885 PM 10-MAR-05 04.00.02.018 PM
Точно так же, как и средства ASH, репозиторий AWR по умолчанию работает на всех серверах базы данных Oracle 10g. Так же, как и в случае со средствами ASH, вам запрещено выполнять запросы к AWR-представлениям (или генерировать AWR-отчеты), если вы не лицензировали диагностический пакет Diagnostic Pack. Репозиторий AWR при сборе моментальных копий потребляет системные ресурсы и расходует память в табличном пространстве SYSAUX, если у вас не лицензировано использование репозитория AWR, вы можете выключить сбор моментальных копий AWR. Это можно сделать с помощью пакета dbms_workload_repository. Если репозиторий AWR не доступен вам, пакет Statspack остается хорошим способом работы с сервером Oracle 10g.
Статистические данные модели времени
В сервере Oracle 10g введена новая концепция – статистические данные модели времени (Time Model Statistics). Эта информация обеспечивает еще один способ наблюдения за затратами времени с большими подробностями, чем это был доступно раньше. Представление v$sys_time_model показывает статистические данные модели времени на уровне всей системы, начиная с запуска экземпляра, тогда как представление v$sess_time_model показывает статистические данные модели времени для каждого сеанса с его начала. Столбцы в этих представлениях следующие:
SQL> DESCRIBE v$sys_time_model
Name Null? Type
---------------------------------------- -------- ---------------------------
STAT_ID NUMBER
STAT_NAME VARCHAR2(64)
VALUE NUMBER
SQL> DESCRIBE v$sess_time_model
Name Null? Type
---------------------------------------- -------- ---------------------------
SID NUMBER
STAT_ID NUMBER
STAT_NAME VARCHAR2(64)
VALUE NUMBER
Пример запроса к представлению v$sys_time_model:
SQL> SELECT stat_name, value / 1000000 seconds
2 FROM v$sys_time_model
3 ORDER BY seconds DESC;
STAT_NAME SECONDS
------------------------------------------------ ----------
DB time 80970.190
sql execute elapsed time 75057.271
DB CPU 44448.628
background elapsed time 29333.160
PL/SQL execution elapsed time 8824.538
background cpu time 5170.311
parse time elapsed 1270.147
hard parse elapsed time 838.068
PL/SQL compilation elapsed time 176.731
sequence load elapsed time 112.334
connection management call elapsed time 44.644
failed parse elapsed time 11.946
hard parse (sharing criteria) elapsed time 5.579
hard parse (bind mismatch) elapsed time 4.610
failed parse (out of shared memory) elapsed time 0.000
Java execution elapsed time 0.000
inbound PL/SQL rpc elapsed time 0.000
Этот запрос по сравнению с запросами к представлениям v$sysstat и v$sesstat дает нам намного больше информации о том, на что сеансы сервера Oracle тратят свое время (по категориям). Конечно, прежде, чем мы сможем заставить эту информацию работать на нас, мы должны знать, как интерпретировать ее. Значения в этих представлениях показываются в микросекундах, и фоновые процессы не учитываются, если только в имени статистического показателя не появляется слово "background". Статистический показатель "DB time" показывает общее затраченное время на вызовы сервера базы данных (только пользовательские процессы). В него входит время, затраченное на работу центрального процессора, и события ожидания, которые не являются событиями простоя. Описание других статистических данных модели времени см. в листинге представления v$sess_time_model в справочном руководстве Oracle 10g Database Reference.
Из этого запроса видно, среди многих других полезных фактов, что никакого времени не было затрачено на выполнение Java-кода (Java execution elapsed time), очень мало времени было затрачено на полный синтаксический разбор или компиляцию PL/SQL-кода (hard parse elapsed time или PL/SQL compilation elapsed time), фоновые процессы использовали приблизительно 10% времени центрального процессора (background cpu time), и приблизительно 11% общего затраченного времени пользовательских сеансов были истрачены на выполнение PL/SQL-кода (PL/SQL execution elapsed time).
Усовершенствования средств трассировки
Расширенное средство SQL-трассировки, называемое также отладочным событием 10046, позволяет нам собирать в трассировочном файле подробную информацию о каждом событии ожидания, случившемся в сеансе сервера базы данных. Эта средство было доступно в сервере Oracle в течение длительного времени. В сервере Oracle 10g в этой области предлагаются некоторые полезные усовершенствования.
Включение расширенной SQL-трассировки всегда было немного неудобным. В ранних версиях сервера Oracle для этого вы должны были использовать неуклюжий оператор ALTER SESSION SET EVENTS или, что еще хуже, недокументированную процедуру dbms_system.set_ev, чтобы включить событие 10046 в другом пользовательском сеансе. В сервере Oracle 8i, чтобы облегчить этот шаг, появился пакет dbms_support, но пакет отсутствовал во многих выпусках сервера Oracle и обычно не инсталлировался по умолчанию.
В сервере Oracle 10g появился новый пакет dbms_monitor. Этот пакет позволяет, среди многого другого, очень легко включать и выключать расширенную SQL-трассировку для любого сеанса сервера Oracle. Легко запоминающиеся вызовы процедур этого пакета позволяют включать и выключать расширенную SQL-трассировку с возможностью записи в трассировочный файл информации о событиях ожидания и/или переменных связывания:
SQL> DESCRIBE dbms_monitor
...
PROCEDURE SESSION_TRACE_DISABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT
PROCEDURE SESSION_TRACE_ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
Если параметр session_id (идентификатор сеанса) не указывается или имеет NULL-значение, то будет трассироваться ваш собственный сеанс. Таким образом, следующие два оператора должны быть эквивалентными:
- ALTER SESSION SET events '10046 trace name context forever, level 12';
- EXECUTE dbms_monitor.session_trace_enable (waits=>TRUE, binds=>TRUE);
В сервере Oracle 9i и более ранних версиях расширенную SQL-трассировку было удобно использовать, если ваше приложение соединялось с сервером базы данных Oracle через соединение выделенного сервера. Если использовалась архитектура разделяемого сервера, то каждый процесс разделяемого сервера, который обслуживал запрос трассируемого сеанса, писал свои данные в отдельный трассировочный файл. Кроме того, трассировать сеансы в среде пула соединений было трудно, потому что один сеанс сервера Oracle мог обрабатывать запросы многих разных сеансов конечных пользователей.
Пакет dbms_monitor в сервере Oracle 10g позволяет разрешить эту проблему. Вместо включения расширенной SQL-трассировки конкретного сеанса сервера Oracle вы можете включить ее для конкретного идентификатора клиента или комбинации сервиса, модуля и действия. Каждый раз, когда какой-то сеанс сервера Oracle имеет указанные идентификатор клиента или комбинацию сервиса, модуля и действия, он будет трассироваться. Сеансы могут устанавливать и сбрасывать свои идентификаторы клиента с помощью пакета dbms_session, а свои модули и действия – с помощью пакета dbms_application_info.
Предположим, веб-приложение для обслуживания запросов пользователей использует пул 30 соединений с сервером базы данных и поддерживает таблицу текущих сеансов current_sessions для отслеживания состояния сеансов конечных пользователей. Когда какой-то пользователь в окне своего браузера щелкает по кнопке, сервер приложений получает HTTP-запрос и передает его процессу сервера приложений. Этот процесс захватывает свободное соединение с сервером базы данных из пула соединений и обращается к этому серверу для выполнения запроса. Вероятно, что последующие запросы этого же пользователя будут обработаны с использованием разных соединений с сервером базы данных.
В сервере Oracle 9i и более ранних версиях в этой среде было очень трудно трассировать выполнение действий в сервере базы данных для одного конкретного пользователя. Обращения пользователя к базе данных разбрасывались в пуле соединений по многим серверным процессам. Кроме того, каждый из этих процессов сервера Oracle обрабатывал запросы многих разных конечных пользователей.
С помощью пакета dbms_monitor в сервере Oracle 10g можно модифицировать приложение, чтобы существенно упростить расширенную SQL-трассировку. Мы упоминали, что приложение использует таблицу current_sessions для отслеживания состояния каждого из сеансов конечных пользователей. Каждый раз, когда сервер приложений захватывает соединение с сервером базы данных в пуле соединений, оно может (перед выполнением любого доступа к базе данных для сеанса конечного пользователя) установить идентификатор клиента для сеанса сервера Oracle, выбирая идентификатор сеанса session_id из таблицы current_sessions. Затем, перед возвратом соединения с сервером базы данных в пул соединений, приложение может очистить этот идентификатор клиента. Это можно сделать следующим образом:
EXECUTE dbms_session.set_identifier ('session_id174837492748');
...выполнение запроса этого сеанса конечного пользователя...
EXECUTE dbms_session.clear_identifier
Теперь для трассировки этого сеанса конечного пользователя мы можем вызвать пакет dbms_monitor:
SQL> EXECUTE dbms_monitor.client_id_trace_enable -
> ('session_id174837492748', waits=>TRUE, binds=>TRUE);
Этот вызов пакета dbms_monitor заставит каждый процесс сервера Oracle писать данные расширенной SQL-трассировки в трассировочный файл для всех операций, выполняемых от имени клиента с указанным значением идентификатора. Однако каждый процесс сервера Oracle пишет в свой собственный трассировочный файл. Поэтому данные трассировки будут разбросаны по разным файлам. Для решения этой проблемы в сервере Oracle 10g появилась новая утилита trcsess, работающая в режиме командной строки. Она читает разные трассировочные файлы и объединяет записи из различных файлов, удовлетворяющие заданным критериям, в одном трассировочном файле, который может быть обработан утилитой TKPROF. Чтобы объединить данные трассировки для нашего текущего примера, мы можем использовать следующие команды:
$ cd $ORACLE_BASE/admin/$ORACLE_SID/udump
$ trcsess output=/home/rschrag/case1403/case1403-trial1.trc \
clientid=session_id174837492748
Таким образом, пакет dbms_monitor и утилита trcsess в сервере Oracle 10g намного упрощают сбор данных расширенной SQL-трассировки сеансов конечных пользователей, когда в звене сервера приложения используется пул соединений или другие способы агрегирования сеансов.
Заключение
В сервер Oracle 10g включено много усовершенствований интерфейса событий ожидания, которые как никогда раньше упрощают оптимизацию производительности с помощью методики событий ожидания. Некоторые усовершенствования, такие, как описательные имена событий ожидания, классы ожидания и процедура включения трассировки сеансов session_trace_enable в пакете dbms_monitor, облегчают нашу работу. Заметим, они не дают нам никакой информации, которую мы не имели прежде. Однако другие усовершенствования, такие, как статистические данные модели времени, представление v$event_histogram и новые столбцы в представлениях v$sql и v$sqlarea, предоставляют нам полезную информацию о распределении затрат времени и ожиданиях, ранее недоступную.
Хотя на момент написания данной статьи эти новые возможности были документированы неполно, а обсуждение на сайте Metalink проводилось удивительно редко, многие из этих усовершенствований окажутся, вероятно, весьма полезными для администраторов баз данных Oracle, ответственных за оптимизацию производительности сложных систем баз данных Oracle.
