Oracle mechanics

26.03.2011

Ожидание virtual circuit wait

Filed under: Oracle,shared server,wait events — Игорь Усольцев @ 18:43
Tags:

in english

При использовании shared servers (MTS) на инстансах с большими выборками могут быть заметными ожидания virtual circuit wait (Oracle 11.1.0.7)

                                                           Avg                 
                                                          wait   % DB          
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                           41,456          24.1          
db file scattered read           16,480,224      40,887      2   23.7 User I/O 
virtual circuit wait                674,827      39,310     58   22.8 Network  
db file sequential read           1,820,163      27,374     15   15.9 User I/O 
direct path read temp               166,394       3,653     22    2.1 User I/O

Внятное описание события можно найти в  Bug 6653834 — Split «virtual circuit status» into idle / non-idle waits [ID 6653834.8]:

This is a performance monitoring enhancement to split the ‘virtual circuit status’ wait event into two new wait events:

«shared server idle wait» — for when the shared server is idle waiting for something to do
«virtual circuit wait»    — for when the shared server is blocked waiting on a specific circuit / message

ок, т.е. начиная с версий 11.1.0.7/11.2.0.1 событие virtual circuit status разделено на shared server idle wait (класс ожиданий idle) и virtual circuit wait не-idle ожидание (класс — network, «shared server ожидает определённого ресурса — circuit / message«)

SQL> select name, wait_class
2    from v$event_name
3   where name in ('virtual circuit wait', 'shared server idle wait');

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
virtual circuit wait                                             Network
shared server idle wait                                          Idle

Далее — результаты тестирования запроса с большим объёмом возвращаемых данных и переписки с техподдержкой с целью уточнения значения этого ожидания

Итак, при использовании shared server:

SQL> show arraysize
arraysize 15
SQL> SELECT
...
8  /

169227681 rows selected.

Elapsed: 08:54:28.07
...
Plan hash value: 3415772365
...
Statistics

----------------------------------------------------------
...
11281848  SQL*Net roundtrips to/from client
...

Запрос громоздкий по количеству выбираемых строк (к слову, запускался не для выяснения причин этого ожидания), статистика выполнения очень показательна (при arraysize 15, значение по умолчанию в SQL*Plus):

EVENT                       WAIT_CLASS    TOTAL_WAITS TOTAL_TIMEOUTS    TIME_WAITED    AVERAGE_WAIT,CS
virtual circuit wait        Network          22538592              0        2420249            0,11
db file scattered read      User I/O            69400              0         237079            3,42
gc cr multi block request   Cluster            402246              0          56108            0,14
SQL*Net message from client Idle             11281894              0          38453               0
...
SQL*Net message to client   Network          11281871              0           2439               0
...

Обращает внимание, кроме того, что основное время потрачено на virtual circuit wait, отношение ожиданий virtual circuit waitSQL*Net message from client : SQL*Net message to client приблизительно равно 2 : 1 : 1. Все эти три ожидания фиксируются Oracle на этапе fetch и отражаются в трейсе 10046 таким образом:

FETCH #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3415772365,tim=1298897604041737  -- r=15: ARRAYSIZE
WAIT #6: nam='virtual circuit wait' ela= 20 circuit#=76 type=2 p3=0 obj#=942592 tim=1298897604055373
WAIT #6: nam='virtual circuit wait' ela= 1758 circuit#=76 type=2 p3=0 obj#=942592 tim=1298897604057143
WAIT #6: nam='SQL*Net message from client' ela= 41 driver id=1297371904 #bytes=1 p3=0 obj#=942592 tim=1298897604057158
WAIT #6: nam='SQL*Net message to client' ela= 2 driver id=1297371904 #bytes=1 p3=0 obj#=942592 tim=1298897604057181
FETCH #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3415772365,tim=1298897604041737

действительно, на пару ожиданий virtual circuit wait (одно из которых длинное с затраченным временем (ela=) 1758 микросекунд ~ 1,8 мс, что согласуется со средним average_wait,cs из статистики ожиданий запроса) приходится по одному SQL*Net message from client + SQL*Net message to client

Важно, что на каждую операцию обмена данными с клиентом (SQL*Net roundtrips to/from client) приходится два ожидания virtual circuit wait, по словам техподдержки:

it is a shared server and therefore some synchronisation needs to be done. This is shown in the wait event ‘virtual circuit wait’. Most of the time shown there is also considered idle time and will be reported in future release accordingly

Очевидно, поэтому Oracle записывает два ожидания virtual circuit wait: первое, короткое (ela= 20) — собственно ожидание, связанное с использованием shared server (синхронизация, ожидание ресурса circuit / message) и второе, длинное (ela= 1758), которое реально фиксирует idle-ожидание, которое при выделенном (dedicated) соединении фиксируется как SQL*Net message from client (класс idle, как будет видно в след. примерах). Интересно, что пара этих, по сути разных, ожиданий фиксируются в трейсе с одинаковыми параметрами circuit#=76 type=2, т.е. не различаются по параметрам, но записываются отдельно

Для проверки аналогии с SQL*Net message from client по классическому пути увеличиваю arraysize до 5000 — максимального значения, доступного в SQL*Plus, и повторяю запрос:

SQL> set arraysize 5000
SQL> SELECT
...
8  /

169227681 rows selected.

Elapsed: 01:20:34.92
...
Plan hash value: 3415772365
...
Statistics

----------------------------------------------------------
...
33848  SQL*Net roundtrips to/from client
...
169227681  rows processed

При том же плане выполнения (естественно) запрос выполнился почти в 7 раз быстрее, по статистике запроса ожидание virtual circuit wait ушло далеко на второе место

EVENT                         WAIT_CLASS    TOTAL_WAITS    TOTAL_TIMEOUTS    TIME_WAITED    AVERAGE_WAIT
db file scattered read          User I/O          69417                 0         195355            2,81
virtual circuit wait             Network          75810                 0          61933            0,82
gc cr multi block request        Cluster         312639                 0          52799            0,17
SQL*Net message from client         Idle          33892                 0          11521            0,34
...

Теперь видно, что ожидание virtual circuit wait в большей части похоже на классическое SQL*Net message from client, связанное с количеством SQL*Net roundtrips to/from client, уменьшившимся точно в соответствии с увеличением arraysize

До этого момента использовалось shared server соединение, что изменится при использовании dedicated server?

SQL> show arraysize
arraysize 15
SQL> SELECT
...
8  /

169227681 rows selected.

Elapsed: 07:13:10.32

Execution Plan
----------------------------------------------------------
Plan hash value: 3415772365

Statistics
----------------------------------------------------------
...
11281847  SQL*Net roundtrips to/from client
...

Запрос выполнился немного быстрее ~ 20%, но эта разница получена не за счёт разницы ожиданий virtual circuit wait (2,420,249 cs при shared server и arraysize 15 в первом примере) и SQL*Net message from client (2,454,168 cs при dedicated server и arraysize 15), вероятно влияние различной текущей нагрузки, т.к. цели сравнить скорость выполнения при использовании shared/dedicated соединений у меня не было

EVENT                        WAIT_CLASS    TOTAL_WAITS    TOTAL_TIMEOUTS    TIME_WAITED    AVERAGE_WAIT
SQL*Net message from client    Idle    11281870    0    2454168    0,22
gc cr multi block request       Cluster          36584                 5          32780            0,9
direct path read               User I/O          77241                 0           9931            0,13
db file scattered read         User I/O            854                 0           2560            3
SQL*Net message to client       Network       11281871                 0           2260            0
...

Как и предполагалось, в отсутствии virtual circuit wait, в качестве основного события видим SQL*Net message from client (класс ожиданий idle [from the database perspective], т.е. бд ожидает выполнения внешнего по отношению к Oracle события) — что ожидаемо для запроса с большим возвращаемым data set‘ом

Получается, что использование dedicated server может давать (а может и не дать — как а приведённых примерах) преимущество перед shared server на больших выборках, в отличие от распространённых рекомендаций.  Этот выигрыш может быть получен за счёт экономии незначительной доли времени, занимаемого «действительным» ожиданием/синхронизацией разделяемых ресурсов (строка трейса virtual circuit wait…ela= 20) — а это относительно короткая часть ожидания, фиксируемого как virtual circuit wait ~ 1% (20/1758)

Полная картина эволюции ожиданий, связанных с virtual circuit, вероятно выглядит так:

                          11.1.0.7/11.2.0.1                                Oracle 12(?)
virtual circuit status -> shared server idle wait (Idle)
                       -> virtual circuit wait (Network, Idle+non-Idle) -> ?virtual circuit wait (Idle)
                                                                        -> virtual circuit wait (Network, non-Idle)

т.е. ожидание virtual circuit wait в будущих релизах будет разделено на Idle и non-Idle (Network) части, если я правильно понимаю слова тех.поддержки

Интересно проверить (оценить по ash), какое значение arraysize используется разработчиками в запросах на инстансе с большим количеством ожиданий virtual circuit wait (AWR с которого был приведён в начале поста)

SQL> select--+ RULE
2         sql_id,
3         --sql_text,
4         fetches,
5         rows_processed,
6         round(rows_processed / decode(fetches, 0, 1, fetches)) as rows_per_fetch
7    from gv$sql
8   where (inst_id, sql_id) in (select inst_id, sql_id
9                                 from (select inst_id, sql_id, count(*) ccount
10                                         from gv$active_session_history
11                                        where EVENT = 'virtual circuit wait'
12                                        group by inst_id, sql_id
13                                        order by 3 desc)
14                                where rownum < = 10)
15   and COMMAND_TYPE = 3 --SELECTs only
16   and rows_processed > 0
17  /

SQL_ID           FETCHES ROWS_PROCESSED ROWS_PER_FETCH
------------- ---------- -------------- --------------
136fmurj44n9h        955        9550000          10000
38xhnjzwkp91s       1459       14580000           9993
2kmm08qg8rrrb       1099       10990000          10000
b1yp9p84xgxqf       6090       60890548           9998
g8vthx4t6cm9v       1004       10028942           9989
grsbfbbvg6tpq       4357       43560748           9998
58jks1y60urf8       1257       12563447           9995
bcmfsj5w3bn4a       1018       10180000          10000
f5q7u9rpsz8cb       1418       14180000          10000
8zdmfzpbh4j1x       1196       11953562           9995

Видно, что разработчики используют большой размер массива (arraysize 10000), просто выборки большие :)

P.S. Интересный факт по теме shared server из блога Joel Goodman (Oracle University EMEA)

После обсуждения с коллегами из поддержки … было подтверждено, что [Oracle] kernel работает с SYSOPER / SYSDBA сессиями иначе [чем, с обычными сессиями], эти сессии не разделяют серверные процессы при использовании shared server

5 комментариев »

  1. Добрый день,
    статья понравилась, хотел бы задать вопрос.
    На Oracle 11.2.0.2 Patch3 (Win x86_64) неактивные сессии в режиме shared server «висят» с ожиданием «virtual circuit wait» и не отпускают занятые shared server’а(они со статусом WAIT(RECEIVE) в v$shared_server). Фактически получается dedicated mode. Как настроить нормальную работу shared server? Буду признателем за помощь.

    комментарий от Alex — 09.04.2011 @ 09:53 | Ответить

  2. Приветствую,
    насколько я понимаю, ваш случай похож на сценарий, описанный Алексом Фаткулиным для версии 10.2, при выполнении которого на Oracle 11.2.0.1 (Windows 7 32 bit) получается аналогичный результат при использовании workarea_size_policy=AUTO:

    SYSTEM@orcl112 SQL> show parameter
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -----
    max_shared_servers                   integer     1
    shared_servers                       integer     1
    workarea_size_policy                 string      AUTO
    
    SYSTEM@orcl112 SQL> create or replace procedure rc_fetch(
      2    p_rc  in out sys_refcursor
      3  ) is
      4    l_row user_objects%rowtype;
      5  begin
      6    fetch p_rc into l_row;
      7  end;
      8  /
    Procedure created.
    
    SYSTEM@orcl112 SQL> variable rc refcursor;
    
    SYSTEM@orcl112 SQL> exec open :rc for select * from user_objects order by object_id;
    PL/SQL procedure successfully completed.
    
    SYSTEM@orcl112 SQL> exec rc_fetch(:rc);
    PL/SQL procedure successfully completed.

    После чего попытка (другой сессии) соединиться с использованием shared server не удаётся (max_shared_servers=1):

    C:\oracle\product\11.2.0\dbhome_1\BIN>sqlplus system/mann@orcl112mts
    SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 13 01:53:30 2011
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    ERROR:
    ORA-28547: connection to server failed, probable Oracle Net admin error

    при этом единственный (в этом случае) разделяемый сервер находится в статусе WAIT(RECEIVE)

    SQL> select name, status from v$shared_server;
     NAME STATUS
    ---- ----------------
    S000 WAIT(RECEIVE)

    с выделенной областью памяти (workarea) для сортировки:

    SQL> select operation_type, work_area_size
      2  from v$sql_workarea_active
      3  where sid=131;
    OPERATION_TYPE                                                         WORK_AREA_SIZE
    -------------------------------------------------------------------------------- --------------
    SORT (v2)                                                                            75776

    Первая сессия — неактивна, в ожидании virtual circuit wait, точнее, «длинной» части этого ожидания (аналог ожидания SQL*Net message from client)

    SQL> select status, event, seconds_in_wait from v$session where sid = 131;
    STATUS EVENT                                                        SECONDS_IN_WAIT
    -------- ---------------------------------------------------------------- ---------------
    INACTIVE virtual circuit wait                                                      16

    способ решения проблемы описан Алексом Фаткулиным в вышеуказанном блоге — установить workarea_size_policy=MANUAL на уровне сессии или системы

    комментарий от Igor Usoltsev — 13.04.2011 @ 01:03 | Ответить

  3. Interesting. Thanks !

    комментарий от Аноним — 21.07.2011 @ 14:40 | Ответить

  4. Спасибо за интересное исследование!
    Тоже наблюдаем эту картину после каждого рестарта системы.
    Хорошее описание работы и диагностики shared servers — Shared Server (MTS) Diagnostics [ID 1005259.6]

    комментарий от Деев И. — 28.03.2012 @ 17:39 | Ответить

    • Спасибо, Илья, за ссылку

      комментарий от Igor Usoltsev — 28.03.2012 @ 18:52 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: