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

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

Мы наблюдаем постоянное появление и ввод новых технологий. Это здорово! А в это же время имеющиеся «старые» технологии требуют массу внимания и времени для поддержки. «Океан» данных, «море» баз данных, больше распределенных систем. Остается меньше времени на настройку и оптимизацию. Сокращение окон для модификации, поддержки и внесения изменений осложняет задачу увеличить непрерывность работы систем на имеющемся оборудовании.

В области настройки оптимизации баз данных, часто встречаются ситуации, когда трудно выбрать «правильное» решение. В таких случаях приходится полагаться на различные инструменты, которые помогают оценить ситуацию и найти пути ее улучшения. Освоив такие инструменты, часто становится проще найти лучшее решение, если в дальнейшем возникает подобная ситуация.
В подтверждение этой мысли приведу перевод любопытной статьи из блога bulldba.com/db-optimizer


В новых релизах DB Optimizer компании Embarcadero, начиная с версии 3.0, имеется отличная новая возможность: наложить на диаграмму VST explain plan запроса!
[Примечание переводчика:
Диаграмма визуальной оптимизации Visual SQL Tuning (VST) превращает текстовый SQL-код в графическую SQL-диаграмму, показывает индексы и ограничения в таблицах и представлениях с использованием статистических сведений, а также операции соединения, используемые в инструкции SQL, такие как прямые и подразумеваемые декартовы произведения и отношения «многие ко многим». ]


Возьмем для примера следующий запрос:

SELECT COUNT (*) 
FROM   a,  b,  c
WHERE
       b.val2 = 100 AND
       a.val1 = b.id AND
       b.val1 = c.id;

По колонкам b.id и c.id созданы индексы. В окне DB Optimizer этот запрос выглядит так:



Красные линии связей такого вида в соответствии с определениями говорят, что отношения могут быть типа «многие ко многим».
Вопрос: «какой план выполнения этого запроса является оптимальным?».

Один из возможных оптимальных планов выполнения этого «дерева запроса» может быть таким:
  1. Начать с наиболее селективного фильтра
  2. Выполнить JOIN с подчиненными таблицами, если возможно
  3. Если нет – то выполнить JOIN с таблицей верхнего уровня


На этой диаграмме единственный фильтр. Он обозначен символом F зеленого цвета на таблице B. У этой таблицы в запросе присутствует критерий отбора “b.val2 = 100”.
Хорошо, начнем с таблицы B. Куда же мы направим план нашего дальнейшего выполнения? Кто здесь является «подчиненным», а кто «главным»? Как определить?.. Oracle при планировании запроса встречает те же трудности. Как понять, почему Oracle принял то решение, а не иное? Здесь на помощь приходят новые возможности DB Optimizer.
В DB Optimizer есть супер классная возможность наложить на VST диаграмму актуальный план выполнения (мне это так нравится!).



Теперь мы видим, что Oracle начинает с таблицы B и соединяет ее с таблицей A. Результат соединяется с таблицей C. Является ли этот план оптимальным?
Оставим имеющиеся индексы и добавим пару ограничений целостности (constraints)
alter table c add constraint c_pk_con unique (id);
alter table b add constraint b_pk_con unique (id);

Проанализируем запрос в DB Optimizer еще раз:



Теперь мы можем увидеть, кто главный, а кто подчиненный, на основе этого определить оптимальный план выполнения запроса, который начинается с фильтрации B, затем соединения с C, затем с A. Посмотрим, как Oracle обработает добавленные ограничения целостности.



Видите, план выполнения изменился с учетом наличия ограничений, и Oracle перевел план выполнения из суб-оптимального в оптимальный.

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



Я планирую написать еще об этой возможности. Это по-настоящему здорово.
Вот еще пример из статьи Jonathan Lewis www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach
В ней Jonathan обсуждает запрос: 

SELECT order_line_data
FROM customers cus
         INNER JOIN
         orders ord
         ON ord.id_customer = cus.id
         INNER JOIN
         order_lines orl
         ON orl.id_order = ord.id
         INNER JOIN
         products prd1
         ON prd1.id = orl.id_product
         INNER JOIN
         suppliers sup1
         ON sup1.id = prd1.id_supplier
   WHERE
         cus.location = 'LONDON' AND
         ord.date_placed BETWEEN '04-JUN-10' AND '11-JUN-10' AND
         sup1.location = 'LEEDS' AND
    EXISTS (SELECT NULL
            FROM
                 alternatives alt
                 INNER JOIN
                 products prd2
                 ON prd2.id = alt.id_product_sub
                 INNER JOIN
                 suppliers sup2
                 ON sup2.id = prd2.id_supplier
           WHERE
                  alt.id_product = prd1.id AND
                  sup2.location != 'LEEDS')


Который на диаграмме VST выглядит так:



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



Теперь мы можем определить вариант лучшего пути выполнения оптимизации. Использовал ли его Oracle?



Вы смогли увидеть ошибку оптимизатора?
Темно-зеленым обозначено место, где выполнение начинается. Здесь – в двух местах: в теле главного запроса и в подзапросе. Красным цветом обозначена конечная точка запроса.
Еще один пример (Karl Arao):



Здесь выполнение начинается в 4 местах. Обратите внимание, как результирующие наборы с каждого старта соединяются с каждым последующим результатом соединения таблиц. Финал обозначен красным цветом.
[Конец перевода]

Оптимизация систем БД в современных средах часто требует расширенных знаний, но, по-прежнему, большей частью остается искусством.

Скажем, человеку практически невозможно протестировать все допустимые варианты хинтов и индексов, чтобы найти наиболее оптимальное решение. Приходится полагаться на интеллектуальные инструменты вроде Embarcadero DB Optimizer, которые проведут по пути настройки, и помогут выбрать наилучший вариант из предложенных.
В приведенных примерах было показано, как его расширенные возможности помогли не только быстро обнаружить направление оптимизации запросов, но получить объяснения решениям, принятыми «штатным» оптимизатором Oracle, найти недостающие описания, которые обеспечили более «правильную» работу оптимизатора в дальнейшем.
Более подробно о работе с VST-диаграммами можно узнать по ссылке на статью Jonathan Lewis или на сайте компании Embarcadero.