Причины неэффективности SQL-запросов в Oracle. Оптимизация производительности SQL-запросов

 ВАЛЕРИЙ МИХЕИЧЕВ, эксперт Oracle, ОСАО «Ингосстрах», Valery.Mikheitchev@ingos.ru

Причины неэффективности SQL-запросов в Oracle
Оптимизация производительности SQL-запросов

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

Диагностика эффективности выполнения запросов

Существуют разнообразные методы диагностики производительности работы запросов, например, использование трассировочных файлов, средства Oracle DBMS_SQLTUNE (с выдачей рекомендаций по оптимизации запроса), AWR (Автоматический репозиторий рабочей нагрузки) и др.

Среди средств диагностики производительности работы запросов наиболее простым и довольно действенным является просмотр планов выполнения запросов через инструменты Toad, PL/SQL Developer и др., а также на основе Oracle-представлений, например, таких как v$sql_plan (v$sql_plan_monitor появился в Oracle 11g) и v$sql_bind_capture – последний показывает значения переменных, используемых в запросе.

При этом следует заметить, что, если через Oracle-представления v$sql_plan и v$sql_plan_monitor мы получаем реальный план выполнения запроса, то через подстановку текста запроса в Toad, PL/SQL Developer и другие аналогичные средства можем получить предполагаемый, а не реальный план выполнения.

Анализ планов выполнения запросов

В плане выполнения запроса рекомендуется обратить внимание на следующие показатели, определяющие эффективность работы запроса:

• Cost – стоимость выполнения;
• CPU Cost – процессорная стоимость выполнения;
• IO Cost – стоимость ввода-вывода;
• Temp Space – показатель использования запросом временного пространства.

Чем больше значение первых трех показателей, тем менее эффективен запрос.

Наличие ненулевых значений в показателе Temp Space говорит об использовании временного пространства (например, для проведения сортировок, группировок, hash-join и т.д.), при этом с большой вероятностью можно говорить о неэффективности работы такого запроса, и начинатьискать проблемы следует с анализа строк, где стоят Temp Space.

Опыт оптимизации запросов показал, что анализ планов выполнения имеет определенную последовательность действий:

• план выполнения начинают просматривать снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими значениями Cost и CPU Cost;
• помимо поиска больших Cost и CPU Cost, в плане следует обратить внимание на наличие в нем полного сканирования таблиц и индексов: FULL – для таблиц и FULL SCAN или FAST FULL SCAN – для индексов, в том числе, обратить внимание на SKIP SCAN индексов (поскольку там тоже могут таиться проблемы). В плане, полученном из v$sql_plan, для выявления наличия полного сканирования таблиц или индексов следует исследовать столбец Options (Plan_Options для v$sql_plan_monitor);
• следует также обратить внимание на наличие в плане фразы Hash_Join. Соединение по Hash_Join приводит к соединению таблиц в памяти и, казалось бы, более эффективно, чем вложенные соединения Nested Loops. Вместе с тем Hash_Join эффективно при наличии таблиц, хотя бы одна из которых помещается в память базы данных, или при наличии соединения таблиц с низко селективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса (появится в плане показатель Temp Space). В связи с чем при наличии высокоселективных индексов целесообразно посмотреть, а не улучшит ли план выполнения запроса хинт (подсказка оптимизатору) Use_NL, приводящий к соединению по вложенным циклам Nested Loops. Если план будет лучше, то нужно оставить этот хинт. В плане, полученном из v$sql_plan, для выявления Hash_Join следует исследовать столбец Operations;
• стоит обратить внимание также на наличие в плане фразы Merge Join Cartesian, которая говорит о том, что между какими-то таблицами нет полной связки. Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

К вышеуказанныму следует добавить детализирующие параметры, такие как Elapsed_time, CPU_time, Executions, Disk Read и Buffer Gets. Первый показывает полное время выполнения запроса, второй – процессорное время выполнения запроса, третий – число выполнений запроса, четвертый и пятый – соответственно интенсивность использования дисков и памяти.

Как показывает опыт, при значении Disk Reads более 250 000 имеем интенсивное использование дисков, а при Buffer Gets более 10 000 000 на одно выполнение имеем интенсивное использование памяти, т.е. в том и другом случаях имеем проблемы в запросе.

Проблемы в запросе также имеются, если число Elapsed_time существенно превосходит CPU_time (что, как правило, связано с событиями ожидания). Значения параметров можно получить из представления Oracle v$sql или v$sql_monitor по sql_id (уникальный идентификатор запроса), а если sql_id неизвестен, то по уникальным элементам текста запроса из этих представлений. Для поиска указанных параметров в долго работающем запросе удобно представление v$sql_monitor, которое в отличие от старого представления v$sql имеет столбец sid-сессии, позволяющий выявить долго работающие запросы, выполняющиеся в данной сессии, и их sql_id.

Широкий спектр информации по всем четырем параметрам можно получить из такого мощного средства диагностики запросов, как AWR (его можно запустить из Toad – меню Database – подменю Monitor – ADDM/AWR Reports).

Информацию, аналогичную AWR (правда, в менее удобном виде, начиная с шестисотых строк), можно получить из запроса:

Select * from TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT((select dbid from V$DATABASE), 1, (select max(snap_id)-1 from sys.WRM$_SNAPSHOT), (select max(snap_id) from sys.WRM$_SNAPSHOT)));

Статью целиком читайте в журнале «Системный администратор», №6 за 2015 г. на страницах 47-51.

PDF-версию данного номера можно приобрести в нашем магазине.

Комментарии могут оставлять только зарегистрированные пользователи