06.10.2015

Расследование медленно работающих запросов при использовании СУБД Oracle Database

Иногда расследование проблем производительности приводит к определенному запросу, который выполняется медленно, и не совсем понятно, что делать дальше с таким запросом. В данной статье предлагается способ расследования производительности одного запроса при работе с СУБД Oracle Database.

Работа запроса в любой СУБД состоит из двух частей:

• Выработка плана запроса.
• Исполнение запроса по выработанному плану.

Средства, предоставляемые платформой "1С:Предприятие" не позволяют разделить время выполнения запроса на выработку плана запроса и на время исполнения запроса по выработанному плану. Для того чтобы узнать, какое время тратится на каждую из процедур, необходимо использовать инструменты, предоставляемые СУБД.

Следует отметить, что выработка плана запроса выполняется один раз для совпадающих текстов запросов. Это значит, что текст запроса может быть одним, а значения параметров – разными, при этом запрос будет исполняться по одному и тому же плану. Данный факт может быть полезен для понимания, почему иногда запрос выполняется сначала медленно, а последующие попытки воспроизвести ситуацию приводят к быстрому исполнению запроса.

Для СУБД Oracle Database команда сброса кэша планов запросов выглядит так:

    alter system flush shared_pool;

Исполнение данной команды приводит к тому, что все планы запросов будут вырабатываться заново. Выполнение команды на системе с большим количеством работающих пользователей чревато замедлением времени отклика системы (ведь СУБД должна выработать планы запросов для всех запросов), которое со временем возвращается в норму.

Команда сброса кэша полезна для тестовой системы, когда нужно посчитать время выработки плана запроса. После сброса кэша планов запросов, запрос будет исполняться за время равное выработке плана плюс время на исполнение запроса. Когда же план исполнения закеширован в памяти СУБД, время выполнения уходит только на время исполнения запроса, но не на выработку плана исполнения.

Подчеркнем, что если текст SQL-запроса изменяется даже малейшим образом, выработка плана запроса будет сделана заново. В общем случае, затруднительно предсказать при каких изменениях запроса на языке "1С" запрос к СУБД (SQL-запрос) изменится. Поэтому, при изменении запроса на языке "1С" или его параметров не следует предполагать ни то, что SQL-запрос останется идентичным, ни то, что SQL-запрос изменится. В числе прочего, на результирующий SQL-запрос могут влиять параметры сеанса, если их значения используются в запросе, в том числе неявно (в условиях RLS или в общих реквизитах, являющихся разделителями).

Рассмотрим, как использовать средства, предоставляемые СУБД, для анализа работы конкретного запроса. Сначала нам потребуется узнать номер соединения, в котором исполняются запросы. Соединение идентифицируется двумя числами: SID и SERIAL. Получить все соединения, инициированные с процесса rphost.exe, можно следующим запросом:

    select * from v$session where program = 'rphost.exe'

В работающей информационной базе может быть много соединений, поэтому сначала нужно найти то соединение, в котором будет исполняться интересующий нас запрос или же включить трассировку для всех соединений. Найти одно конкретное соединение, в котором исполняется запрос, при работающих пользователях довольно затруднительно, но если есть база данных, где пользователи не работают и на этой базе можно воспроизвести работу проблемного запроса, то найти соединение обычно труда не составляет. Cервер "1С:Предприятия" открывает два соединения с СУБД Oracle Database, одно из которых используется для выполнения запросов одного пользователя; найти его можно, исполнив любой запрос, а затем обратить внимание на столбец sql_text в представлении v$sql, после чего сопоставить колонки sql_id в представлениях v$sql и v$session. Другими словами, использовать соединение двух представлений.

    select sid, serial#, sql_text, sql_fulltext    
	 from v$session s join v$sql q
           on s.sql_id = q.sql_id
    where program = 'rphost.exe'

Если допустимо использование толстого клиента, то можно создать временную таблицу, а менеджер временных таблиц присвоить глобальной переменной - тогда это соединение будет считаться захваченным, и все запросы будут происходить из него. Необходимо отметить два момента:

1) никогда не используйте данный подход в продакшен-системе, поскольку в этом случае производительность ухудшается, а сервер платформы и сервер СУБД начинают тратить больше ресурсов, чем необходимо;

2) соединение, создающее временную таблицу - это не то же соединение, в котором будет использоваться временная таблица (специфика работы платформы с СУБД Oracle Database).

Номер соединения с СУБД также пишется в технологическом журнале, в свойстве dbpid. Но для СУБД Oracle Database нам еще нужно знать серийный номер подключения, а не только номер соединения, поэтому использовать технологический журнал платформы можно как помощь, но не как основной источник информации.

Для включения трассировки запроса по времени выполнения каждого шага можно использовать трассировку 10046. Для включения трассировки по выработке плана запроса можно использовать трассировку 10053. Для включения трассировки нам необходимо знать, какое соединение будет исполнять запрос.

Чтобы включить трассировку для известного соединения с базой данных следует подключиться к базе данных с ролью SYSDBA (это может делать пользователь SYS, например так: sqlplus sys as sysdba) и выполнить команду (sid и serial следует заменить идентификаторами соединения):

  exec dbms_system.set_ev(sid, serial, 10046, 12, '');

В утилитах, отличных от sqlplus, команда exec может не поддерживаться. В случае, если утилита не поддерживает установку роли при соединении с БД попробуйте в качестве имени пользователя указать sys as sysdba. Если команда exec не поддерживается - можно использовать такой код:

begin
  dbms_system.set_ev(sid, serial, 10046, 12, '');
end;
/

Для выключения трассировки - 12 следует заменить на 0. Число 12 - обозначает максимальный уровень детализации трассировки 10046. Для трассировки 10053 (трассировка выработки плана исполнения запроса) разницы между разными уровнями трассировки - нет, поэтому проще использовать то же число 12 - для включения, и 0 - для выключения.

Файлы трассировки открываются СУБД и остаются незакрытыми до тех пор, пока соединение, запросившее трассировку, не отключит ее исполнение. Впрочем, иногда этого недостаточно и в зависимости от версии используемой СУБД может понадобиться закрыть соединение с базой данных. Сейчас это происходит при отключении последнего пользователя, использующего информационную базу; не исключено, что платформа "1С:Предприятие" перестанет следовать этому принципу и тогда единственным гарантированным способом отключения сервера платформы от сервера СУБД будет перезапуск рабочего процесса сервера платформы.

Расположение файлов трассировки можно узнать, выполнив следующий запрос

    select value from v$diag_info where name = 'Diag Trace'

Из имени файла трассировки сложно понять, когда он был записан. Для определения какой из файлов содержит интересующую информацию можно использовать косвенные признаки, такие как объем файла и время его последней модификации.

Полученную трассировку 10046 в сыром виде читать затруднительно. Обработка трассировки делается с помощью следующей команды (в отличие от всех остальных запросов в этой статье, эта команда выполняется не в сессии работы с СУБД, а в консоли операционной системы).

    tkprof file.trc file.txt sort=exeela

Опциональный параметр sort=exeela означает, что запросы будут отсортированы в порядке убывания времени исполнения.

Трассировку 10053 читать непросто, но возможно. Это дает понимание о том, как оптимизатор принял решение о способах соединения всех таблиц и подзапросов в общем запросе. В частных случаях удается использовать полученную информацию для сбора более детальной статистики по тем таблицам, где оптимизатор СУБД промахивается с оценкой кардинальности.

Иногда запись трассировочных файлов – существенно замедляет время выполнения запроса, поэтому судить о скорости выполнения запроса, когда записывается трассировка, неправильно. Трассировки нужны для анализа и понимания что и почему происходит в «голове» у СУБД, и в них содержится достаточно информации на эту тему.

В случае, если удастся определить, что СУБД тратит основное время на выработку плана запроса, а не на его исполнение, может оказаться полезным дать указание оптимизатору СУБД снизить количество вариантов перебора для выработки оптимального плана запроса.

По умолчанию, значение параметра "_optimizer_max_permutations" равно 2000. Изменить значение параметра можно следующей командой.

   alter system set "_optimizer_max_permutations" = 200 scope=both;

Уменьшение количества вариантов, перебираемых оптимизатором (значение параметра "_optimizer_max_permutations") может привести к ухудшению производительности в том случае, когда оптимизатор не смог найти более оптимальный план запроса за 200 попыток (но смог бы за 2000). Тем не менее, накопленный опыт использования систем на базе платформы "1С:Предприятие" больше склоняет к предлагаемому уменьшению параметра до значения 200.

После изменения значения данного параметра имеет смысл сбросить кэш планов исполнения, чтобы следующий запрос был исполнен с выработкой плана. Команда сброса (уже упоминалась ранее в статье).

    alter system flush shared_pool;

Важно отметить, что параметры оптимизатора, начинающиеся с подчеркивания, не документированы вендором и их изменение может приводить к непредвиденным проблемам. Поэтому изменение параметров рекомендуется только в том случае, когда есть полное и ясное понимание, на что влияют эти параметры и как и в каком случае следует их модифицировать.