Причины неэффективности SQL-запросов в Oracle. Оптимизация производительности SQL-запросов::Журнал СА 6.2015
www.samag.ru
     
Поиск   
              
 www.samag.ru    Web  0 товаров , сумма 0 руб.
E-mail
Пароль  
 Запомнить меня
Регистрация | Забыли пароль?
Журнал "Системный администратор"
Журнал «БИТ»
Наука и технологии
Подписка
Где купить
Авторам
Рекламодателям
Магазин
Архив номеров
Вакансии
Контакты
   

  Опросы

Какие курсы вы бы выбрали для себя?  

Очные
Онлайновые
Платные
Бесплатные
Я и так все знаю

 Читать далее...

1001 и 1 книга  
20.12.2019г.
Просмотров: 5080
Комментарии: 0
Dr.Web: всё под контролем

 Читать далее...

04.12.2019г.
Просмотров: 6322
Комментарии: 0
Особенности сертификаций по этичному хакингу

 Читать далее...

28.05.2019г.
Просмотров: 7577
Комментарии: 2
Анализ вредоносных программ

 Читать далее...

28.05.2019г.
Просмотров: 7901
Комментарии: 1
Микросервисы и контейнеры Docker

 Читать далее...

28.05.2019г.
Просмотров: 6954
Комментарии: 0
Django 2 в примерах

 Читать далее...

Друзья сайта  

Форум системных администраторов  

sysadmins.ru

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

Архив номеров / 2015 / Выпуск №6 (151) / Причины неэффективности 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-версию данного номера можно приобрести в нашем магазине.


Комментарии отсутствуют

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

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

               Copyright © Системный администратор

Яндекс.Метрика
Tel.: (499) 277-12-41
Fax: (499) 277-12-45
E-mail: sa@samag.ru