Декабрь 2005
Виталий Савенков,
“ФОРС – Центр Разработки”
Настраиваем сетевые ожидания при работе с СУБД Oracle
Введение
Когда администратор базы данных (АБД) сталкивается с сетевыми ожиданиями “SQL*Net message to/form client” и “SQL*Net message to/form dblink”, он обычно жалуется сетевому администратору на то, что сеть слишком медленная для запросов работающего приложения. Однако, как показала практика, возможность избавится от этого нежелательного ожидания лежит именно на плечах АБД Oracle и программистов, которым АДБ должен рассказать, как нужно правильно писать приложения.
Суть проблемы
Рассмотрим простой пример. Запрос от клиентского приложения трехзвенной архитектуры, где в качестве сервера приложений работает Oracle iAS, выполняется около 6 минут. Это просто убийственная, очень низкая скорость. Естественно, подозрения падают на неоптимальную логику запроса или неоптимальный план его выполнения, и поэтому в первую очередь надо настраивать сам запрос. Однако, используя механизм автортассировки autotrace при внимательном рассмотрении количества проделанной работы было замечено, что количество LIO, которое делал запрос, было слишком малым, чтобы он выполнялся в течение этих 6 минут. Естественно, что первые подозрения пали на сбои в сети, но это нужно было еще доказать. Включив трассировку, было замечено, что фаза EXECUTE запроса отрабатывалась на сервере за считанные секунды (7 сек). Остальное время было потрачено на передачу полученных данных по сети и, соответственно, на ожидания “SQL*Net message to/form client”.
То есть, Oracle расходовал (60х6=360 – 7) секунд просто на передачу данных на сервер приложений, а тот - на клиентскую машину. Каждый пакет данных, который передавался от сервера базы данных к серверу приложений, проходил в сети с большой скоростью - не более 400 микросекунд, однако этих пакетов было такое колоссальное количество (более 100 тысяч), что это и создавало такое большое время отклика на запрос клиентского приложения.
Каждый пакет, переданный по сети, проходит некоторую обработку (создается заголовок для пакета, пакет помещается в буфер сетевого протокола, размещение этого пакета в клиентском приложении и т.д.), что создает дополнительние накладные расходы. Приняв соответствующее решение, мы уменьшили количество передаваемых пакетов, и время ожидания ответа на клиентской машине снизилось с 6 минут до 20 секунд! И это было сделано без дополнительной настройки запроса и его плана выполнения.
Как это работает
Сначала несколько слов о том, как Oracle передает данные, что развеет магию этого примера.
Запрос в Oracle выполняется в несколько основных этапов:
- Синтаксический и семантический анализ предложения PARSE
- Привязка значений переменных BIND
- Выполнение запроса EXECUTE
- Доставка выбранных записей клиенту FETCH
Эти этапы хорошо известны программистам OCI, Pro*C, PLSQL, использующим пакет dbms_sql. Их можно увидеть в трассировочном файле, если начать трассировать сессию. О механизме этапов PARSE, BIND, EXECUTE достаточно понятно рассказал Т.Кайт в [5,6]. Нас интересует фаза FETCH.
После того, как запрос выбрал строки удовлетворяющие условию, Oracle передает клиентскому приложению эти строки не за один раз. Он передает строки группами (batch), разбивая набор выбранных строк на нужное количество групп. После того, как клиент получит и обработает первую группу строк ответа, он запрашивает передачу второй группы , потом третьей и т.д. Понятно, что чем меньше размер группы строк, то тем больше нужно циклов их пересылки по сети. Каждая группа строк оформляется в виде пакета. При создании пакета существуют некоторые накладные расходы, например, по обработке заголовков: сначала на уровне Oracle*Net8, потом TCP/IP, потом среды передачи, а также (в обратном порядке) при приеме пакетов на стороне клиента. Все накладные расходы по передаче групп по сети суммируются, создавая в сети дополнительные задержки по времени.
Поэтому, если на сетевом уровне есть задержки именно в доставке и обработке сетевых пакетов, то следует принять решение об увеличении размера передаваемой группы строк.
Более того, если при очередном запросе от клиента потребуется последовательно “допередавать” ему оставшиеся в курсоре данные, то Oracle будет читать их в режиме CONSISTENT READ со всеми накладными расходами на установление защелок (latchs) [1]. В результате увеличивается количество LIO, что является основным показателем проделанной работы при настройке оптимального запроса. Увеличивая количество передаваемых строк в группе, можно снизить количество LIO для данного запроса.
Рассмотрим влияние размера буфера на примере SQLPLUS,
параметр которого arraysize влияет на количество передаваемых данных в группе.
SQL> set autot trace stat
SQL> set arraysize 1000
SQL> select * from t1 where rownum<100000;
99999 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
881 consistent gets
300 physical reads
0 redo size
2247882 bytes sent via SQL*Net to client
1740 bytes received via SQL*Net from client
101 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99999 rows processed
Из этого примера видно, что данный запрос затребовал 51 (101 / 2) дополнительную передачу строк на клиентскую машину, проделав при этом работу в 881 consistent gets (согласованных чтений).
При уменьшении значения arraysize получим следующую картину для того же самого запроса:
SQL> set arraysize 10
SQL> /
99999 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10704 consistent gets
292 physical reads
0 redo size
4039782 bytes sent via SQL*Net to client
110640 bytes received via SQL*Net from client
10001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99999 rows processed
Теперь видно, что для передачи 99999 строк клиенту потребовалось 5001 раз (10001 / 2 ) обратится к серверу и что при каждом обращении к серверу был поиск данных в буферном кеше. Число же consistent gets увеличилось в 12 раз. Следовательно, в запросе можно сократить число consistent gets только за счет увеличения количества строк в передаваемой клиенту группе строк.
Устанавливать размер принимающего буфера надо в клиентском приложении. Именно приложение говорит Oracle, сколько строк оно может взять для клиента:
- в SQLPLUS за количество передаваемых строк в группе отвечает переменная arraysize;
- в программе с использованием OCI настраиваются атрибуты функцией
OCIAttrSet(): OCI_ATTR_PREFETCH_ROWSили OCI_ATTR_PREFETCH_MEMORY;
- в java есть функция SetFetchSize(N), если программа написана с использованием JDBC ;
- в программе на Pro*C - параметр компилятора PREFETCH=integer
- если используется PL/SQL, то используется Bulk Fetch.
Но и после того, как клиентский буфер наполнится строками, он не за один прием будет передан протоколом Oracle Net*8, поскольку этот протокол тоже налагает свои ограничения на размеры передаваемых пакетов. Пакет протокола Oracle Net*8 называется session data unit. Размер пакета, передаваемого Oracle Net*8, определяется параметром SDU, установленного в конфигурационных файлах Net*8, как на клиентской машине, так и на сервере. Значение SDU может быть от 512 до 32К, по умолчанию для SDU установлено значение 2К.
Поэтому, если размер передаваемого буфера, либо размер передаваемой строки, например, если это большого размера BLOB, равен несколько десятков килобайтов, а SDU выставлено по умолчанию на 2К, то пакет будет разбиваться протоколом Net*8 на несколько, опять разбивая передаваемый буфер строк на несколько пакетов. И может так случится, что весь выигрыш от увеличения размера принимающего буфера сойдет на нет.
Существует несколько рекомендаций по установке параметра SDU. Если клиентское приложение оперирует с такими большими данными, как BLOBы, или оперирует потоком данных, то SDU выставлять нужно в максимальное значение.
SDU прописывается в конфигурационных файлах сетевого протокола Oracle Net*8 и вступает в силу при подключении клиента к серверу, то есть при создании клиентской сессии. В этот момент клиент и сервер обменивается своими значениями параметров SDU и выставляют общее его значение - минимальное из двух.
В клиентской конфигурации SDU прописывается в строках соединений в файле tnsnames.ora. Это выглядит так:
connect_string=
(DESCRIPTION =
(SDU=14601)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = srv_name)
)
)
Если tnsnames.ora не используется, то SDU можно выставить в строке подключения:
sqlplus "username/password@ \
(DESCRIPTION=\
(SDU=32768) \
(ADDRESS_LIST=\
(ADDRESS=\
(PROTOCOL=TCP)(HOST=ip_addr)(PORT=1521)\
))\
(CONNECT_DATA=(SERVER=dedicated)\
(SID=sid_name)))"
Поэтому, если используется iAS в качестве промежуточного клиента, который соединяется с базой данных, то можно использовать строку соединения
url="jdbc:oracle:thin:@(DESCRIPTION=(SDU=32768)
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ip_addr)(PORT=1521)))
(CONNECT_DATA=(SERVER=dedicated)(SID=sid_name)))"
На стороне сервера SDU устанавливается в listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = Oracle_home)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU=32768)
(GLOBAL_DBNAME = global_dbname)
(ORACLE_HOME = Oracle_home)
(SID_NAME = sid_name)
)
)
Следует иметь ввиду, что для сервисов динамической регистрации [8] SDU таким образом не настраивается и по умолчанию принимает значение 2К. Поэтому, если вы хотите изменить SDU, то необходимо прописать сервис в файле listener.ora и в нем изменить параметр SDU для данного сервиса, либо прописать параметр DEFAULT_SUD_SIZE в sqlnet.ora, который, хотя и не описан в стандартной документации, предназначен для установки значения SDU по умолчанию.
Хотя, откровенно говоря, у меня не получилось использовать SDU через этот параметр для сервисов динамической регистрации, но из данных, размещенных на Metalink, следует, что он нужен именно для этого [7].
Посмотреть, правильно ли воспринимаются ваши параметры SDU, можно с помощью трассировки уровня ADMIN Oracle Net*8 . Для этого на серверной части в файле в sqlnet.ora выставляются параметры:
trace_directory_server=trace_dir
trace_level_server=admin
После соединения в этой директории будет находится файл, в котором надо найти строки, в которых есть слово nsconneg:
[26-OCT-2005 20:16:49:910] nsconneg: vsn=308, lov=300, opt=0x0, sdu=32767, tdu=32767, ntc=0x4f98
[26-OCT-2005 20:16:49:910] nsconneg: vsn=308, gbl=0x0, sdu=32767, tdu=32767
Это значит, что клиент и сервер обмениваются пакетами размером не более 32 К.
Увидеть значение принимающего буфера и принимаемые значения SDU можно следующим образом. Если у вас есть текст запроса, то его следует выполнить в SQL*Plus с включенным параметром autotrace. В этом случае можно увидеть сколько циклов “SQL*Net roundtrips to/from client” было сделано за время выполнения запроса. Но следует помнить, что в клиентской программе размер буфера может быть установлен другим способом. Кроме того, autotrace не показывает, был ли разбит буфер на несколько сетевых пакетов при пересылке его протоколом Oracle Net*8. То есть, только с помощью autotrace нельзя понять, правильно ли настроена величина SDU .
Наиболее точным в определении размера принимающего буфера, размера SDU, является трассировка клиентской сессии с уровнем трассировки 8 именно тогда в в трассировочном файле появляются информация об ожиданиях и, последующий анализ сырого (т.е. еще не обработанного утилитой TKPROF) файла трассировки. В этом файле отображаются фазы выполнения запроса, а в фазах FETCH указывается, сколько строк было записано в принимающий буфер.
FETCH #1:c=0,e=183,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=9849301778542
FETCH #1:c=0,e=171,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=9849301779513
FETCH #1:c=0,e=170,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=9849301803348
FETCH#1:c=1450000,e=1382840,p=1214,cr=0,cu=0,mis=0,r=7,dep=0,og=4,tim=9849303253678
значение r показывает количество переданных клиенту строк.
Если включить трассировку 8-го уровня, то можно увидеть тройки вида:
WAIT #c WAIT #c и FETCH #c ,
WAIT #c WAIT #c и FETCH #c
и т.д., где #c - это номер курсора к которому эти ожидания (WAIT) и операция (FETCH) относятся.
WAIT #1: nam='SQL*Net message from client' ela= 187 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=162,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=9849301869150
WAIT #1: nam='SQL*Net message from client' ela= 190 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=164,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=9849301868689
Здесь видно, что первый WAIT #1 означает что от клиентского сообщения на сервер пришел запрос о необходимости допередать оставшиеся в курсоре данные второй WAIT #1 означает что клиенту был послан буфер с данными. Только после этого сервер посчитал что фаза FETCH для этих 15 строк окончена и написал это в трассировочный файл. Потом, так как клиенту были переданы не все данные, то серверу пришел запрос от клиентского приложения о необходимости допередать оставшиеся в курсоре данные (третий WAIT #1). четвертый WAIT #1 означает что клиенту был послан буфер с данными. Только после этого сервер посчитал что фаза FETCH для этих 15 строк окончена и написал это в трассировочный файл. И так до тех пор пока клиенту не будут переданы все востребованные строки.
Если значение SDU предполагает разбиение локального буфера на несколько пакетов, то на каждом шаге можно увидеть следующую картину:
WAIT #1: nam='SQL*Net message from client' ela= 745 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 29 p1=1413697536 p2=2004 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 38 p1=1413697536 p2=1999 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 30 p1=1413697536 p2=1998 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 40 p1=1413697536 p2=2002 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 29 p1=1413697536 p2=2003 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 35 p1=1413697536 p2=2003 p3=0
FETCH #1:c=0,e=3909,p=0,cr=8,cu=0,mis=0,r=1000,dep=0,og=4,tim=9850100710743
Здесь можно увидеть, что для завершения фазы FETCH, которая выбирает 100 строк в буфере, было послано 7 пакетов клиенту. Ожидания WAIT показывают это. То есть, в передаваемый буфер было выбрано 1000 строк, но они были разбиты на несколько пакетов, потому что объем 1000 строк превысил значение SDU.
Сообщение 'SQL*Net more data to client' говорит нам о том, что имеют место дополнительные расходы при передаче 1000 строк клиенту, из чего следует возможность улучшения организации передачи пакетов путем увеличения SDU. Если же этого сообщения нет в трассировочном файле, то все выбираемые за один раз строки помещаются в одном SDU-пакете.
При возникновении частых ожиданий “SQL*Net message to/form client” следует еще обратить внимание вот на что. Клиентская программа принимающая строки от Oracle должна их обработать и где-то разместить. Клиентской программой может в этом смысле быть и Oracle iAS, отображающий строки в браузере, и приложение, являющееся интерфейсом пользователя. Если клиентское приложение отображает строки на экран, пишет их в файл и тому подобное (а это - долгий процесс), то все это время сервер будет ожидать отклика от клиента, и, следовательно, задержки при передаче по сети увеличится. Эти задержки не имеют ничего общего с ухудшением работы сети, они являются следствием ухудшения производительности клиентской программы!
Желательно подбирать передаваемого буфера и SDU так, чтобы пользователь не ощущал задержки в передаче данных. То есть, не рывками, все данные сразу, а плавно, учитывая специфику конечного приложения. Например, если предполагается, что пользователь будет читать, изучать, обрабатывать получаемые данные, то следует передать первую порцию данных так, чтобы они заполнили экран, и пользователеь был бы занят ими, а в это время допередавать следующие порции данных.
Из всего сказанного следует, что при увеличении сетевых задержек вследствие наличия ожиданий “SQL*Net message to/form client” причину следует искать, как на сервере по количеству переданных пакетов, так и на клиенте, как он быстро обрабатывает пришедшие строки данных.
Выводы.
В этой статье была рассмотрена часто игнорируемая при настройке производительности приложения АБД ситуация, связанная с сетевыми ожиданиями. Из сказанного можно сделать следующие выводы:
- Наиболее ответственным человеком за недопущение этих ожиданий является разработчик, потому что лучшим действием по устранению сетевых задержек является уменьшение количества выбираемых запросом строк. В первую очередь подумайте, нужно ли клиентскому приложению получать с сервера такое огромное количество строк с сервера, если конечный пользователь даже не будет их читать. Всегда старайтесь ограничить количество выбираемых строк, при чем это правило касается не только при настройке сетевых ожиданий.
- Весомым фактором в настройке сетевых ожиданий является размер буфера, в который складываются выбираемые строки.
- Значение параметра SDU, установленное по умолчанию в 2 К, разбивает передаваемый буфер на несколько пакетов, причем в в режиме autotrace этого не видно.
- Следует учитывать, что несправляющееся с большим передаваемым объемом данных клиентское приложение даже при оптимальных размерах буфера и SDU будет причиной задержек и увеличении времени по ожиданиям 'SQL*Net message from/to client'. Всегда обращайте внимание на производительность клиентской машины во время обработки принимаемых данных. Задержки в приеме и обработке этих данных будут влиять на увеличение времени в ожидании 'SQL*Net message from/to client'
И последнее:
Не забудьте выключить все трассировки после исследования вашего приложения. Включенные трассировки существенно замедляют передачу данных.
Список используемой литературы
- Cary Millsap Why You Should Focus on LIOs Instead of PIOs
- SQL*Plus Command Reference
- Oracle Call Interface Programmer's Guide
- Net Services Reference Guide
- T. Kyte Effective Oracle by Design
- T. Kyte Expert One-on one Oracle
- Note:1113588.8 New SQLNET.ORA parameter DEFAULT_SDU_SIZE
- Oracle 9i Net Services Administrator’s Guide (A96580-01)
- Configuring Service Registration
|
