Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Апрель 2004   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : Статистическая информация уровня сегмента в событии 10046


Информационный Канал Subscribe.Ru

Выпуск 66

Уважаемые подписчики рассылки!

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

Статистическая информация уровня сегмента в событии 10046 Oracle 9.2

Энди Ривенес (Andy Rivenes), AppsDBA Consulting

13 марта 2003 года

Версия 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. Перевод публикуется с разрешения автора.

Прежние выпуски

Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google...)

В следующем выпуске

Уже апрель, а сайт не изменился. Я все еще собираюсь публиковать примеры из книги Тома Кайта "Oracle для профессионалов" ("Expert one-on-one Oracle") с моими комментариями, исправленными опечатками и привязкой к страницам перевода. Но когда это будет - пока не знаю...

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу

В избранное