2004 г.
Статистическая информация уровня сегмента в событии 10046 Oracle 9.2
Энди Ривенес, AppsDBA Consulting
Перевод Валерия Кравчука
Версия 1.1
Copyright © 2003 by AppsDBA Consulting, All Rights Reserved.
Введение
В версии 9.2 в Oracle добавилась возможность получать "статистическую информацию
уровня сегмента". К словарю данных было добавлено несколько представлений v$,
и для сбора соответствующей информации можно избирательно включать сбор статистики на
уровне сегмента. Однако при выполнении трассировки с помощью события 10046 эта статистическая
информация тоже выдается1. По-видимому, хотя я и не нашел еще
никакой определенной информации об этом, она появилась в наборе исправлений 9.2.0.2. Поскольку
это - статистическая информация "источника строк" ("row source" statistics), она будет
входить в строки STAT. Дополнительная информация - часть информации об операции
доступа к источнику строк, а именно - поле "op=". В этой статье мы разберемся,
как найти эту информацию и как ее можно использовать. В выдаваемых данных есть определенные
рассогласования, но, в общем случае, эта статистическая информация уровня сегмента дает
специалисту по анализу производительности дополнительную важную информацию для изучения
времени выполнения транзакции и влияющих на него факторов.
Формат строки STAT
В файле трассировки события 10046 статистическая информация уровня сегмента выдается в
строках STAT. Это - статистическая информация "источника строк", и дополнительная
информация входит в общую информацию об операции доступа к источнику строк, в поле
"op=". Вот два примера, один - из файла трассировки до 9.2, а другой -
из файла трассировки 9.2.
Файл трассировки до версии 9.2:
STAT #5 id=1 cnt=1 pid=0 pos=0 obj=43135
op='TABLE ACCESS CLUSTER SEG$ '
Новый формат файла трассировки 9.2:
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0
op='SORT AGGREGATE (cr=167 r=4 w=0 time=31888 us)'
Обратите внимание на новую информацию, "(cr=167 r=4 w=0 time=31888 us)".
Значение "cr=" соответствует количеству операций логического ввода-вывода
consistent reads, значение "r=" - количеству физических чтений,
значение "w=" - количеству физических записей, а значение "time="
определяет время выполнения и единицу измерения времени (например, us -
микросекунды). Учтите, что логический ввод-вывод "current mode" (т.е. cu= в
трассировочном файле) не выдается.
Статистическая информация уровня сегмента, по-видимому, выдается только для операций
EXEC и FETCH. В контексте строк STAT для конкретного оператора, данные
"накапливаются" ("roll-up") в иерархии плана выполнения, по крайней мере, в большинстве
случаев 2. При интерпретации информации строка STAT
с ненулевым значением "obj=" идентифицирует статистическую информацию о доступе
к соответствующему сегменту. Эти значения будут "накапливаться" по ходу плана выполнения,
и обычно строки STAT с нулевыми значениями "obj=" будут суммировать
статистическую информацию подчиненных источников строк.
Представление статистической информации
Итак, что же можно определить по этой информации? В трассировочных файлах версий
до 9.2 типичная итоговая информация в строке STAT будет иметь вид
(Release 8.1.7.2):
STATEMENT TEXT
select count(*) from dba_users
STATEMENT EXECUTION PLAN
Rows Row Source Operation
------- -----------------------------------------------
1 SORT AGGREGATE
12,047 MERGE JOIN
12,048 SORT JOIN
12,047 NESTED LOOPS
12,048 NESTED LOOPS
12,048 NESTED LOOPS
12,048 MERGE JOIN
10 SORT JOIN
9 NESTED LOOPS
10 TABLE ACCESS FULL USER_ASTATUS_MAP
9 TABLE ACCESS BY INDEX ROWID PROFILE$
162 INDEX RANGE SCAN (object id 43224)
12,056 SORT JOIN
12,047 TABLE ACCESS FULL USER$
24,094 TABLE ACCESS CLUSTER TS$
24,094 INDEX UNIQUE SCAN (object id 43126)
24,094 TABLE ACCESS CLUSTER TS$
24,094 INDEX UNIQUE SCAN (object id 43126)
12,047 TABLE ACCESS BY INDEX ROWID PROFILE$
216,846 INDEX RANGE SCAN (object id 43224)
12,047 SORT JOIN
7 TABLE ACCESS FULL PROFNAME$
Сейчас, в версии 9.2, информация имеет следующий вид (Release 9.2.0.2):
STATEMENT TEXT
select count(*) from dba_users
STATEMENT EXECUTION PLAN
Rows Row Source Operation
------- -------------------------------------------------
1 SORT AGGREGATE (cr=167 r=4 w=0 time=31888 us)
36 MERGE JOIN (cr=167 r=4 w=0 time=31863 us)
36 SORT JOIN (cr=164 r=3 w=0 time=30441 us)
36 TABLE ACCESS BY INDEX ROWID PROFILE$
(cr=164 r=3 w=0 time=29994 us)
649 NESTED LOOPS (cr=163 r=3 w=0 time=25712 us)
36 NESTED LOOPS (cr=161 r=3 w=0 time=22979 us)
36 NESTED LOOPS (cr=87 r=3 w=0 time=21223 us)
36 MERGE JOIN (cr=13 r=3 w=0 time=19489 us)
9 SORT JOIN (cr=6 r=3 w=0 time=18519 us)
9 TABLE ACCESS BY INDEX ROWID PROFILE$
(cr=6 r=3 w=0 time=18313 us)
163 NESTED LOOPS (cr=5 r=2 w=0 time=11319 us)
9 TABLE ACCESS FULL USER_ASTATUS_MAP
(cr=3 r=1 w=0 time=1278 us)
153 INDEX RANGE SCAN I_PROFILE
(cr=2 r=1 w=0 time=9769 us) (object id 139)
36 SORT JOIN (cr=7 r=0 w=0 time=837 us)
36 TABLE ACCESS FULL USER$ (cr=7
r=0 w=0 time=420 us)
36 TABLE ACCESS CLUSTER TS$ (cr=74
r=0 w=0 time=1535 us)
36 INDEX UNIQUE SCAN I_TS# (cr=2
r=0 w=0 time=206 us) (object id 7)
36 TABLE ACCESS CLUSTER TS$ (cr=74
r=0 w=0 time=1563 us)
36 INDEX UNIQUE SCAN I_TS# (cr=2
r=0 w=0 time=145 us) (object id 7)
612 INDEX RANGE SCAN I_PROFILE (cr=2
r=0 w=0 time=1219 us) (object id 139)
36 SORT JOIN (cr=3 r=1 w=0 time=1283 us)
1 TABLE ACCESS FULL PROFNAME$ (cr=3
r=1 w=0 time=936 us)
На основе этой информации мы можем получить статистику по сегментам и определить вклад каждого сегмента
в использование ресурсов на этапах EXEC и FETCH выполнения оператора. С учетом
представленного выше примера, для каждого сегмента получаем следующую статистику:
Elapsed
Object ID LIO (cr=) Physical Reads Physical Writes Time(sec)
------------ --------------- --------------- --------------- ---------------
94 2 1 0 0.011276
139 4 1 0 0.010988
16 144 0 0 0.002747
280 3 1 0 0.001278
95 3 1 0 0.000936
22 7 0 0 0.000420
7 4 0 0 0.000351
------------ --------------- --------------- --------------- ---------------
Total 167 4 0 0.027996
Эти итоговые данные были получены с помощью описанного ранее подхода. В строке STAT каждого
оператора будет идентификатор (параметр id=) строки и идентификатор родительской строки
(параметр pid=). Самая верхняя строка в иерархии будет иметь идентификатор 1 и идентификатор
родительской строки, равный 0. Строки STAT с ненулевым значением "obj=" будут содержать
статистическую информацию о доступе к соответствующему сегменту. Эти значения будут накапливаться по
иерархии плана выполнения, и, обычно, строки STAT со значениями 0 у параметра "obj="
будут суммировать статистические показатели своих порожденных источников строк.
Проверка статистической информации
Пример 1
Если сравнить полученную в предыдущем примере итоговую статистическую информацию по объектам с
представленной ниже реальной статистической информацией уровня оператора, можно увидеть, что общее
количество логических чтений для всех сегментов равно количеству логических чтений в ходе операций
EXEC и FETCH при выполнении оператора. То же самое верно и для физических чтений. Это
не всегда верно, но в итоговую информацию заведомо войдет подмножество соответствующих значений,
которые будут не больше и, возможно, равны суммарным значениям в статистической информации для
оператора.
STATEMENT STATISTICS
Action Count CPU Elapsed PIO Blks LIO Blks Consistent Current Rows
----- ----- --------- --------- -------- -------- ---------- ------- ------
PARSE 1 0.090000 0.142649 6 149 149 0 0
EXEC 1 0.000000 0.000465 0 0 0 0 0
FETCH 2 0.020000 0.031909 4 167 167 0 1
------ --- ---------- --------- -------- -------- ---------- ------- -----
Total 4 0.110000 0.175023 10 316 316 0 1
Проверка реального времени выполнения (elapsed time) для статистической информации уровня сегмента
представляет собой интересную проблему. Если посмотреть на реальное время выполнения операций
EXEC и FETCH, и вычесть процессорное время (CPU), получаем значение 0.012374 секунды,
или 12374 us. Если затем посмотреть на общее время выполнения в нашей статистике по сегментам,
там можно увидеть значение 0.027996 секунды, или 27996 us, но, если вернуться и проанализировать
общее время по всем источникам строк (т.е. time=31888 us), то получается немного меньше, чем общее
время выполнения операций EXEC и FETCH, 0.032374 секунды, или 32374 us. В этом
есть смысл, если учесть, что общее время выполнения в статистической информации уровня сегментов включает
процессорное время (CPU time), необходимое для доступа к источнику строк (т.е. логического ввода-вывода),
а не только время выполнения физического ввода-вывода. Следует также отметить, что в этом случае не было
ввода-вывода "current mode", но подробнее об этом - в следующем примере.
Давайте рассмотрим еще пару примеров. Что произойдет, если есть логический ввод-вывод current mode
(т.е. cu= в операциях EXEC или FETCH), или в статистической информации на уровне
оператора будут ненулевые значения и для EXEC, и для FETCH?
Пример 2
Следующий пример показывает использование ввода-вывода как consistent mode (cr=), так и
current mode (cu=) в ходе операции EXEC при выполнении оператора. При изучении
статистической информации уровня оператора и итогов по сегментам можно увидеть, что сервер Oracle на уровне
сегмента сообщает только про ввод-вывод consistent mode. В этом примере ненулевые значения будут
только в операциях EXEC, а в предыдущем примере они были только в операциях FETCH. Похоже,
для операции PARSE статистическая информация уровня сегмента не выдается.
STATEMENT TEXT
delete from dependency$ where d_obj#=:1
STATEMENT STATISTICS
Action Count CPU Elapsed PIO Blks LIO Blks Consistent Current Rows
------- ----- ------- --------- -------- -------- ---------- ------- ----
PARSE 6 0.020000 0.012211 1 43 43 0 0
EXEC 6 0.010000 0.019214 3 38 16 22 4
FETCH 0 0.000000 0.000000 0 0 0 0 0
------- ----- -------- --------- -------- -------- ---------- ------- -----
Total 12 0.030000 0.031425 4 81 59 22 4
STATEMENT EXECUTION PLAN
Rows Row Source Operation
------- --------------------------------------------------
0 DELETE (cr=3 r=2 w=0 time=7656 us)
1 TABLE ACCESS BY INDEX ROWID DEPENDENCY$
(cr=3 r=0 w=0 time=63 us)
1 INDEX RANGE SCAN I_DEPENDENCY1
(cr=2 r=0 w=0 time=37 us) (object id 127)
Elapsed
Object ID LIO (cr=) Physical Reads Physical Writes Time(sec)
------------ --------------- --------------- --------------- ---------------
127 2 0 0 0.000037
96 1 0 0 0.000026
------------ --------------- --------------- --------------- ---------------
Total 3 0 0 0.000063
Пример 3
Третий, и последний, пример показывает, что выдаваемая статистическая информация не всегда соответствует
предполагаемой. В этом примере обратите внимание, что в SQL-операторе использована конструкция
"FOR UPDATE", и в источниках строк мы получаем ситуацию, когда информация не накапливается,
как можно было ожидать.
STATEMENT STATISTICS
Action Count CPU Elapsed PIO Blks LIO Blks Consistent Current Rows
------- ----- ------- ---------- -------- -------- ---------- ------- ------
PARSE 1 0.000000 0.001438 0 0 0 0 0
EXEC 1 0.000000 0.000430 0 4 3 1 0
FETCH 2 0.000000 0.000143 0 3 3 0 1
------- ----- -------- ---------- -------- -------- ---------- ------- ------
Total 4 0.000000 0.002011 0 7 6 1 1
STATEMENT TEXT
select mynum from andy
where mynum = 99
for update
STATEMENT EXECUTION PLAN
Rows Row Source Operation
------- -------------------------------------------------
1 FOR UPDATE (cr=3 r=0 w=0 time=113 us)
2 TABLE ACCESS FULL ANDY (cr=6 r=0 w=0 time=276 us)
Elapsed
Object ID LIO (cr=) Physical Reads Physical Writes Time(sec)
------------ --------------- --------------- --------------- ---------------
30536 6 0 0 0.000276
------------ --------------- --------------- --------------- ---------------
Total 6 0 0 0.000276
Обратите внимание, что теперь у нас есть ввод-вывод consistent read как для операции EXEC,
так и для FETCH. Мы также выполнили один логический ввод-вывод current mode в ходе операции
EXEC, который снова проигнорирован в статистической информации уровня сегмента.
Вывод
Как было продемонстрировано, статистика уровня сегмента при трассировке с помощью события 10046
в версии 9.2 дает тому, кто занимается анализом производительности, важную дополнительную информацию,
помогающую выявить, какие компоненты замедляют транзакцию. Надеюсь, эта информация вдохновит на
дополнительные исследования и поможет понять эти статистические показатели, а также их применение
при оптимизации времени выполнения операторов.
Ссылки
Oracle9i
Database Performance Tuning Guide and Reference, Release 2 (9.2), Oracle Corporation, March 2002, Part No. A96533-02
Oracle9i
Database Reference, Release 2 (9.2), Oracle Corporation, March 2002, Part No. A96536-02
Note: 39817.1, Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output, Oracle Corporation, 14-AUG-1998
1 Поскольку статистическая информация уровня сегмента
выдается в трассировке события 10046 любого уровня, она выдается и в простой трассировке
SQL_TRACE.
2 Мы представим пример оператора
SELECT ... FOR UPDATE, когда это неверно, но, конечно, могут быть и другие случаи.
Эта статья
первоначально была опубликована на сайте
AppsDBA Consulting.
Перевод публикуется с разрешения автора.
🔥 VPS до 5.7 ГГц под любые задачи с AntiDDoS в 7 локациях
Виртуальные VPS серверы в РФ и ЕС
