Cost based оптимизация sql запросов. Оптимизация SQL запросов

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

Даже если сервер базы данных использует самое мощное аппаратное обеспечение на свете, горсточка плохо себя ведущих запросов может плохо отразиться на его производительности. Фактически, даже один неудачный запрос (иногда их называют «вышедшими из-под контроля») может вызвать серьезное снижение производительности базы данных.

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

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

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

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

Существует несколько способов извлечения плана выполнения запроса:

• В Management Studio есть функции отображения реального и приблизительного плана выполнения, представляющие план в графической форме. Это наиболее удобная возможность непосредственной проверки и, по большому счету, наиболее часто используемый способ отображения и анализа планов выполнения (примеры из этой статьи я буду иллюстрировать графическими планами, созданными именно таким способом).
• Различные параметры SET, например, SHOWPLAN_XML и SHOWPLAN_ALL, возвращают план выполнения в виде документа XML, описывающего план в виде специальной схемы, или набора строк с текстовым описанием каждой операции.
• Классы событий профайлера SQL Server, например, Showplan XML, позволяют собирать планы выполнения выражений методом трассировки.

Хотя XML-представление плана выполнения не самый удобный для пользователя формат, эта команда позволяет использовать самостоятельно написанные процедуры и служебные программы для анализа, поиска проблем с производительностью и практически оптимальных планов. Представление на базе XML можно сохранить в файл с расширением sqlplan, открывать в Management Studio и создавать графическое представление. Кроме того, эти файлы можно сохранять для последующего анализа без необходимости воспроизводить их каждый раз, как этот анализ понадобится. Это особенно полезно для сравнения планов и выявления возникающих со временем изменений.

Оценка стоимости выполнения

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

Существует несколько распространенных, но неверных представлений о приблизительной стоимости выполнения. Особенно часто считается, что приблизительная стоимость выполнения является хорошим показателем того, сколько времени займет выполнение запроса и что эта оценка позволяет отличить хорошие планы от плохих. Это неверно. Во-первых, есть много документов касающихся того, в каких единицах выражается приблизительная стоимость и имеют ли они непосредственное отношение ко времени выполнения. Во-вторых, поскольку значение это приблизительно и может оказаться ошибочным, планы с большими оценочными затратами иногда оказываются значительно эффективнее с точки зрения ЦП, ввода/вывода и времени выполнения, несмотря на предположительно высокую стоимость. Это часто случается с запросами, где задействованы табличные переменные. Поскольку статистики по ним не существует, оптимизатор запросов часто предполагает, что в таблице есть всего одна строка, хотя их во много раз больше. Соответственно, оптимизатор выберет план на основе неточной оценки. Это значит, что при сравнении планов выполнения запросов не следует полагаться только на приблизительную стоимость. Включите в анализ параметры STATISTICS I/O и STATISTICS TIME, чтобы определить истинную стоимость выполнения в терминал ввода/вывода и времени работы ЦП.

Здесь стоит упомянуть об особом типе плана выполнения, который называется параллельным планом. Такой план можно выбрать при отправке на сервер с несколькими ЦП запроса, поддающегося параллелизации (В принципе, оптимизатор запроса рассматривает использование параллельного плана только в том случае, если стоимость запроса превышает определенное настраиваемое значение.) Из-за дополнительных расходов на управление несколькими параллельными процессами выполнения, связанными с распределением заданий, выполнением синхронизации и сведением результатов, параллельные планы обходятся дороже, что отражает их приблизительная стоимость. Тогда чем же они предпочтительнее более дешевых, не параллельных планов? Благодаря использованию вычислительной мощности нескольких ЦП параллельные планы обычно выдают результат быстрее стандартных. В зависимости от конкретного сценария (включая такие переменные, как доступность ресурсов с параллельной нагрузкой других запросов) эта ситуации для кого-то может оказаться желательной. Если это ваш случай, нужно будет указать, какие из запросов можно выполнять по параллельному плану и сколько ЦП может задействовать каждый. Для этого нужно настроить максимальную степень параллелизма на уровне сервера и при необходимости настроить обход этого правила на уровне отдельных запросов с помощью параметра OPTION (MAXDOP n).

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

Теперь рассмотрим простой запрос, его план выполнения и некоторые способы повышения производительности. Предположим, что я выполняю этот запрос в Management Studio с включенным параметром включения реального плана выполнения в примере базы данных Adventure Works SQL Server 2005:

SELECT c.CustomerID, SUM(LineTotal) FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID GROUP BY c.CustomerID

В итоге я вижу план выполнения, изображенный на рис. 1 . Этот простой запрос вычисляет общее количество заказов, размещенных каждым клиентом в базе данных Adventure Works. Глядя на этот план, вы видите, как ядро базы данных обрабатывает запросы и выдает результат. Графические планы выполнения читаются сверху вниз, справа налево. Каждый значок соответствует выполненной логической или физической операции, а стрелки - потокам данных между операциями. Толщина стрелок соответствует количеству переданных строк (чем толще, тем больше). Если поместить курсор на один из значков оператора, появится желтая подсказка (такая, как на рис. 2 ) со сведениями о данной операции.

Рис. 1 Пример плана выполнения

Глядя на операторы, можно анализировать последовательность выполненных этапов:

1. Ядро базы данных выполняет операцию сканирования кластеризированных индексов с таблицей Sales.Customer и возвращает столбец CustomerID со всеми строками из этой таблицы.
2. Затем оно выполняет сканирование индексов (не кластеризированных) над одним из индексов из таблицы Sales.SalesOrderHeader. Это индекс столбца CustomerID, но подразумевается, что в него входит столбец SalesOrderID (ключ кластеризации таблицы). Сканирование возвращает значения обоих столбцов.
3. Результаты обоих сеансов сканирования объединяются в столбце CustomerID с помощью физического оператора слияния (это один из трех возможных физических способов выполнения операции логического объединения. Операция выполняется быстро, но входные данные приходится сортировать в объединенном столбце. В данном случае обе операции сканирования уже возвратили строки, рассортированные в столбце CustomerID, так что дополнительную сортировку выполнять не нужно).
4. Затем ядро базы данных выполняет сканирование кластеризированного индекса в таблице Sales.SalesOrderDetail, извлекая значения четырех столбцов (SalesOrderID, OrderQty, UnitPrice и UnitPriceDiscount) из всех строк таблицы (предполагалось, что возвращено будет 123,317 строк. Как видно из свойств Estimated Number of и and Actual Number of Rows на рис. 2 , получилось именно это число, так что оценка оказалась очень точной).
5. Строки, полученные при сканировании кластеризованного индекса, передаются оператору вычисления стоимости, умноженной на коэффициент, чтобы вычислить значение столбца LineTotal для каждой строки на основе столбцов OrderQty, UnitPrice и UnitPriceDiscount, упомянутых в формуле.
6. Второй оператор вычисления стоимости, умноженной на коэффициент, применяет к результату предыдущего вычисления функцию ISNULL, как и предполагает формула вычисленного столбца. Он завершает вычисление в столбце LineTotal и возвращает его следующему оператору вместе со столбцом SalesOrderID.
7. Вывод оператора слияния с этапа 3 объединяется с выводом оператора стоимости, умноженной на коэффициент с этапа 6 и использованием физического оператора совпадения значений хэша.
8. Затем к группе строк, возвращенных оператором слияния по значению столбца CustomerID и вычисленному сводному значению SUM столбца LineTotal применяется другой оператор совпадения значений хэша.
9. Последний узел, SELECT - это не физический или логический оператор, а местозаполнитель, соответствующий сводным результатам запроса и стоимости.

В созданном на моем ноутбуке плане выполнения приблизительная стоимость равнялась 3,31365 (как видно на рис. 3 ). При выполнении с включенной функцией STATISTICS I/O ON отчет по запросу содержал упоминание о 1,388 логических операциях чтения из трех задействованных таблиц. Процентное значение под каждым оператором - это его стоимость в процентах от общей приблизительной стоимости всего плана. На плане на рис. 1 видно, что большая часть общей стоимости связана со следующими тремя операторами: сканирование кластеризованного индекса таблицы Sales.SalesOrderDetail и два оператора совпадения значений хэша. Перед тем как приступить к оптимизации, хотелось отметить одно очень простое изменение в моем запросе, которое позволило полностью устранить два оператора.

Поскольку я возвращал из таблицы Sales.Customer только столбец CustomerID, и тот же столбец включен в таблицу Sales.SalesOrderHeaderTable в качестве внешнего ключа, я могу полностью исключить из запроса таблицу Customer без изменения логического значения или результата нашего запроса. Для этого используется следующий код:

SELECT oh.CustomerID, SUM(LineTotal) FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID GROUP BY oh.CustomerID

Получился другой план выполнения, который изображен на рис. 4 .

Полностью устранены две операции - сканирование кластеризированного индекса таблицы Customer и слияние Customer и SalesOrderHeader, а совпадение значений хэша заменено на куда более эффективную операцию слияния. При этом для слияния таблиц SalesOrderHeader и SalesOrderDetail нужно вернуть строки обеих таблиц, рассортированные по общему столбцу SalesOrderID. Для этого оптимизатор кластера выполнил сканирование кластеризованного индекса таблицы SalesOrderHeader вместо того, чтобы использовать сканирование некластеризованного индекса, который был бы дешевле с точки зрения ввода/вывода. Это хороший пример практического применения оптимизатора запроса, поскольку экономия, получающаяся при изменении физического способа слияния, оказалась больше дополнительной стоимости ввода/вывода при сканировании кластеризованного индекса. Оптимизатор запроса выбрал получившуюся комбинацию операторов, поскольку она дает минимально возможную примерную стоимость выполнения. На моем компьютере, несмотря на то, что количество логических считываний возросло (до 1,941), временные затраты ЦП стали меньше, и приблизительная стоимость выполнения данного запроса упала на 13 процентов (2,89548).

Предположим, что я хочу еще улучшить производительность запроса. Я обратил внимание на сканирование кластеризованного индекса таблицы SalesOrderHeader, которое теперь является самым дорогим оператором плана выполнения. Поскольку для выполнения запроса нужно всего два столбца, можно создать некластеризованный индекс, где содержатся только эти два столбца. Таким образом, вместо сканирования всей таблицы можно будет просканировать индекс гораздо меньшего размера. Определение индекса может выглядеть примерно так:

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

Обратите внимание, что в созданном индексе есть вычисленный столбец. Это возможно не всегда - все зависит от определения такого столбца.

Создав этот индекс и выполнив тот же запрос, я получил новый план, который изображен на рис. 5 .

Сканирование кластеризованного индекса таблицы SalesOrderDetail заменено некластеризованным сканированием с заметно меньшими затратами на ввод/вывод. Кроме того, я исключил один из операторов вычисления стоимости, умноженной на коэффициент, поскольку в моем индексе уже есть вычисленное значение столбца LineTotal. Теперь приблизительная стоимость плана выполнения составляет 2,28112 и при выполнении запроса производится 1,125 логических считываний.

Упражнение. Запрос заказа покупателя

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

Ответ. Я предложил рассчитать оптимальный индекс покрытия для создания таблицы Sales.SalesOrderHeader на примере запроса из моей статьи. При этом нужно в первую очередь отметить, что запрос использует только два столбца из таблицы: CustomerID и SalesOrderID. Если вы внимательно прочли эту статью, то заметили, что в случае с таблицей SalesOrderHeader индекс покрытия запроса уже существует, это индекс CustomerID, который косвенно содержит столбец SalesOrderID, являющийся ключом кластеризации таблицы.

Конечно, я объяснял и то, почему оптимизатор запроса не стал использовать этот индекс. Да, можно заставить оптимизатор запроса использовать этот индекс, но это решение было бы менее эффективным, чем существующий план с операторами сканирования кластеризованного индекса и слияния. Дело в том, что оптимизатор запроса пришлось бы принудить либо выполнить дополнительную операцию сортировки, необходимую для использования слияния, либо откатиться назад, к использованию менее эффективного оператора совпадения значений хэша. В обоих вариантах приблизительная стоимость выполнения выше, чем в существующем плане (версия с оператором сортировки работала бы особенно плохо), поэтому оптимизатор запроса не будет их использовать без принуждения. Итак, в данной ситуации лучше сканирования кластеризованного индекса будет работать только некластеризованный индекс в столбцах SalesOrderID, CustomerID. При этом нужно отметить, что столбцы должны идти именно в таком порядке:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

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

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

Индекс покрытия

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

Создание индексов покрытия наиболее частых запросов - один из самых простых и распространенных способов тонкой настройки запроса. Особенно хорошо он работает в ситуациях, когда в таблице несколько столбцов, но запросы часто ссылаются только на некоторые из них. Создав один или несколько индексов покрытия, можно значительно повысить производительность соответствующих запросов, так как они будут обращаться к заметно меньшему количеству данных и, соответственно, количество вводов/выводов сократится. Тем не менее, поддержка дополнительных индексов в процессе модификации данных (операторы INSERT, UPDATE и DELETE) подразумевает некоторые расходы. Следует четко определить, оправдывает ли увеличение производительности эти дополнительные расходы. При этом учтите характеристики своей среды и соотношение количества запросов SELECT и изменений данных.

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

С моим примером запроса можно сделать еще кое-что. Создав индекс покрытия таблицы SalesOrderHeader, можно дополнительно оптимизировать запрос. При этом будет использовано сканирование некластеризованного индекса вместо кластеризованного. Предлагаю вам выполнить это упражнение самостоятельно. Попробуйте получить определение индекса: выясните, наличие каких столбцов превратит его в индекс покрытия данного запроса и повлияет ли порядок столбцов на производительность. Решение см. в боковой панели "Упражнение. Запрос заказа покупателя".

Индексированные представления

Если выполнение моего примера запроса очень важно, я могут пойти немного дальше и создать индексированное представление, в котором физически хранятся материализованные результаты запроса. При создании индексированных представлений существуют некоторые предварительные условия и ограничения, но если их удастся использовать, производительность сильно повысится. Обратите внимание, что расходы на обслуживание индексированных представлений выше, чем у обычных индексов. Их нужно использовать с осторожностью. В данном случае определение индекса выглядит примерно так:

CREATE VIEW vTotalCustomerOrders WITH SCHEMABINDING AS SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID GROUP BY oh.CustomerID

Обратите внимание на параметр WITH SCHEMABINDING, без которого невозможно создать индекс такого представления, и функцию COUNT_BIG(*), которая потребуется в том случае, если в нашем определении индекса содержится обобщенная функция (в данном случае SUM). Создав это представление, я могу создать и индекс:

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID ON vTotalCustomerOrders(CustomerID)

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

Если перезапустить запрос, то результат будет зависеть от используемой версии SQL Server. В версиях Enterprise или Developer оптимизатор автоматически сравнит запрос с определением индексированного представления и использует это представление, вместо того чтобы обращаться к исходной таблице. На рис. 6 приведен пример получившегося плана выполнения. Он состоит из одной-единственной операции - сканирования кластеризованного индекса, который я создал на основе представления. Приблизительная стоимость выполнения составляет всего 0,09023 и при выполнении запроса производится 92 логических считывания.

Это индексированное представление можно создавать и использовать и в других версиях SQL Server, но для получения аналогичного эффекта необходимо изменить запрос и добавить прямую ссылку на представление с помощью подсказки NOEXPAND, примерно так:

SELECT CustomerID, OrdersTotalAmt FROM vTotalCustomerOrders WITH (NOEXPAND)

Как видите, правильно использованные индексированные представления могут оказаться очень мощными орудиями. Лучше всего их использовать в оптимизированных запросах, выполняющих агрегирование больших объемов данных. В версии Enterprise можно усовершенствовать много запросов, не изменяя кода.

Поиск запросов, нуждающихся в настройке

Как я определяют, что запрос стоит настроить? Я ищу часто выполняемые запросы, возможно, с невысокой стоимостью выполнения в отдельном случае, но в целом более дорогие, чем крупные, но редко встречающиеся запросы. Это не значит, что последние настраивать не нужно. Я просто считаю, что для начала нужно сосредоточиться на более частых запросах. Так как же их найти?

К сожалению, самый надежный метод довольно сложен и предусматривает отслеживание всех выполненных запросов к серверу с последующий группировкой по подписям. При этом текст запроса с реальными значениями параметров заменяется на замещающий текст, который позволяет выбрать однотипные запросы с разными значениями. Подписи запроса создать тяжело, так что это сложный процесс. Ицик Бен-Ган (Itzik Ben-Gan) описывает решение с использованием пользовательских функций в среде CLR и регулярных выражений в своей книге «Microsoft SQL Server 2005 изнутри: запросы T-SQL».

Существует еще один метод, куда более простой, но не столь надежный. Можно положиться на статистику всех запросов, которая хранится в кэше плана выполнения, и опросить их с использованием динамических административных представлений. На рисунке 7 есть пример запроса текста и плана выполнения 20 запросов из кэша, у которых общее количество логических считываний оказалось максимальным. С помощью этого запроса очень удобно быстро находить запросы с максимальным количеством логических считываний, но есть и некоторые ограничения. Он отображает только запросы с планами, кэшированными на момент запуска. Не кэшированные объекты не отображаются.

Рис. 7 Поиск 20 самых дорогих с точки зрения ввода/вывода при считывании запросов.

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qt.encrypted=0 ORDER BY qs.total_logical_reads DESC

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

Удачной настройки!

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

AND

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

OR

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

Еще одним условием для оптимизации можно считать тот факт, что если одинаковые колонки располагаются рядом, запрос выполняется быстрее. Например, запрос ".. WHERE column1 = 1 OR column2 = 3 OR column1 = 2" будет выполняться медленней, чем запрос "WHERE column1 = 1 OR column1 = 2 OR column2 = 3" . Даже если вероятность истинности условия column2 = 3 выше, чем column1 = 2.

Еще в школе мне рассказывали про распределительный закон. Он гласит, что A AND (B OR C) - то же самое, что и (A AND B) OR (A AND C ). Опытным путем было установлено, что запрос вида "...WHERE column1 = 1 AND (column2 = "A" OR column2 = "B")" выполняется несколько быстрее, чем "...WHERE (column1 = 1 AND column2 = "A") OR (column1 = 1 AND column2 = "B")" . Некоторые БД сами умеют оптимизировать запросы такого типа, но лучше перестраховаться.

NOT

Эту операцию всегда следует приводить к более "читабельному" виду (в разумных пределах, конечно). Так, запрос "...WHERE NOT (column1 > 5)" преобразуется в "...WHERE column1 <= 5" . Более сложные условия можно преобразовать используя правило де Моргана, которое ты тоже должен был изучить в школе. Согласно этому правилу NOT(A AND B) = (NOT A) OR (NOT B) и NOT(A OR B) = (NOT A) AND (NOT B) . Например, условие "...WHERE NOT (column1 > 5 OR column2 = 7)" преобразуется в более простую форму: ...WHERE column1 <= 5 AND column2 <> 7 .

IN

Многие наивно полагают, что запрос "... WHERE column1 = 5 OR column1 = 6" равносилен запросу "...WHERE column1 IN (5, 6)" . На самом деле это не так. Операция IN работает гораздо быстрее, чем серия OR. Поэтому всегда следует заменять OR на IN, где это возможно, несмотря на то, что некоторые БД сами производят эту оптимизацию. Там, где используется серия последовательных чисел, IN следует поменять на BETWEEN. Например, "...WHERE column1 IN (1, 3, 4, 5)" оптимизируется к виду: …WHERE column1 BETWEEN 1 AND 5 AND column1 <> 2 . И этот запрос действительно быстрее.

LIKE

Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах. К сожалению, я вынужден направить тебя за информацией о поиске на просторы всемирной паутины.

CASE

Сама эта функция может использоваться для повышения скорости работы запроса, когда в нем есть более одного вызова медленной функции в условии. Например, чтобы избежать повторного вызова slow_function() в запросе "...WHERE slow_function(column1) = 3 OR slow_function(column1) = 5" , нужно использовать CASE:

... WHERE 1 = CASE slow_function(column1)

WHEN 3 THEN 1

WHEN 5 THEN 1

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

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

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

Существует ряд программ позволяющих автоматизировать и упростить эту задачу. Данный материал ориентирован на работу с сервером Oracle , но и для других баз данных есть аналогичные средства анализа и оптимизации «тюнинга». Первым нашим помощником станет программа мониторинга работы сервера Oracle с названием « Spotlight on Oracle » фирмы Quest software (http://www.quest.com). Это очень мощный инструмент, предназначенный для контроля функционирования вашего сервера. Данная программа выполнена в необычной цветовой палитре, что резко выделяет ее от других продуктов. После запуска данной программы необходимо создать учетную запись пользователя для чего потребуется учетная запись SYS или запись с системными привилегиями DBA. Помощник создания новой учетной записи вызывается из меню “ File > User Wizard ”.

После создания учетной записи пользователя и соединением с сервером Oracle нам представляется визуальная картинка, которая отображает компоненты и процессы работы сервера. Если один, или несколько компонентов сервера работает не оптимально или с перегрузкой, то его цвет изменяется от зеленого до красного, в зависимости от степени перегрузки. Возможен мониторинг сразу нескольких серверов, список которых отображается в левой панели и так же меняет цвет. Иконка в панели задач также меняет цвет синхронно с программой, что позволяет оперативно реагировать при “свернутом” в приложении. Пример мониторинга показан на рисунке 1.

Очень полезной особенностью данной программы является система авто-рекомендаций решения проблем. Достаточно кликнуть мышкой по красному участку изображения, чтобы получить развернутое описание проблемы и возможные методы ее устранения. Если же все нормально, то благодаря данной программе можно подстроить параметры запуска сервера для уменьшения используемых им системных ресурсов. Например, по рисунку 1 можно сделать вывод, что размер табличного пространства файла базы данных можно смело уменьшить в два раза, и желательно выделить дополнительную память под “ Shared Pool ”.

Но это все проблемы администратора базы данных. Разработчиков же больше волнует, как работают их творения и сколько ресурсов «кушают» запросы к базе данных. Для этого вызываем пункт меню “ Navigator > Top Sessions ”. После заполнения параметров фильтра отбора данных нам будет показан список текущих запросов к серверу базы данных. Предварительно отсортировав запросы по требованиям к ресурсам, можно выделить самые “прожорливые”. В этом же окне можно посмотреть план выполнения запроса, пример которого показан на рисунке 2. Причем план запросов можно представить в виде графа, дерева или словесного описания. Здесь так же используется цветовая маркировка проблемных участков.

После выявления проблемных SQL запросов настал черед их оптимизации. Для автоматизации этого процесса воспользуемся программой SQL Expert фирмы LECCO (http://www.leccotech.com). Вызываем окно SQL редактора и добавляем в него скрипт запроса. Здесь так же можно посмотреть план выполнения запроса. Но нас больше всего интересует функция меню “SQL-> Optimize ”, которая генерирует список альтернативных вариантов построения заданного SQL скрипта. А функция “SQL-> Butch Run ” позволяет проанализировать время выполнения всех запросов на “живых” данных и вывести результирующую таблицу, которую можно отсортировать по требуемому параметру. Выбрав наиболее оптимальный запрос, его можно сравнить с оригиналом и принять решение о возможности дальнейшего его использования в своем приложении. Пример работы по оптимизации запроса показан на рисунке 3.

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

Оптимизация неэффективного SQL, несомненно, важнейшее средство повышения производительности в арсенале аналитика по производи­тельности Oracle. Если вызов базы данных порождает более десятка вызовов LIO для каждой строки, возвращаемой из каждой таблицы фразы FROM соответствующей команды SQL, то, скорее всего, произво­дительность такой команды можно повысить. Например, операция со­единения трех таблиц, возвращающая две строки, вероятно, должна требовать не более 60 вызовов LIO.

Любая относительная оценка при определенных условиях мо­жет оказаться неверной. Одним из таких условий является фор­мирование результата запроса как итога агрегирования. Напри­мер, запрос, возвращающий сумму (одну строку) для таблицы из миллиона строк, вполне законно может требовать выполне­ния более десяти вызовов LIO.

Приложения, выполняющие код SQL, порождающий множество вызо­вов LIO, создает серьезные препятствия для масштабируемости систем с большим количеством пользователей. Лишние вызовы LIO не только отнимают мощность процессора, но и могут вызвать большое количе­ство событий latch free для защелок cache buffers chains . Получение и освобождение защелок само по себе может при­вести к избыточному потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для _SPIN_COUNT по умолчанию (обычно не стоит этого делать).

В настоящее время есть ряд полезных ресурсов по оптимизации SQL: .1 Участники разнообразных списков рассылки, например Oracle-L (http://www.cybcon.com/~jkstill), также делают замечательное дело, помогая друг другу писать эффективный код. Все эти ресурсы содер­жат хорошие советы о том, как писать эффективный SQL, применяя методы, часть из которых перечислена ниже:

Диагностика выполнения команд SQL посредством таких инстру­ментов, как tkprof, EXPLAIN PLAN, и отладочных событий 10032, 10033,10046,10079,10104 и 10241.

Диагностика поведения оптимизатора запросов Oracle при помощи отладочного события, подобного 10053.

Работа с текстом SQL, направленная на использование более эффек­тивных планов выполнения.

Выбор эффективной стратегии индексирования с тем, чтобы обес­печить сокращение объема данных для запросов без создания до­полнительной нагрузки в операциях INSERT, UPDATE, MERGE и DELETE.

Применение хранимых планов выполнения с целью заставить оп­тимизатор запросов Oracle использовать выбранный вами план.

Создание приемлемой статистики для таблиц, индексов и базы дан­ных с тем, чтобы наилучшим образом информировать оптимизатор запросов Oracle о ваших данных.

Разработка таких физических моделей данных, которые облегчают хранение и выборку в контексте приложения.

Разработка логических моделей данных, облегчающих хранение и выборку в контексте приложения.

Оптимизация разбора

Избыточный разбор - это верный путь к невозможности обеспечить масштабируемость приложения для работы с большим количеством пользователей . Студенты обычно приходят к нам, считая, что полные разборы (hard parses) значительно замедля­ют обработку транзакций, а вот в частичном разборе (soft parse) нет ничего страшного. Более того, иногда люди считают, что полного раз­бора можно избежать, а частичного - нет. Оба мнения верны лишь на­половину. Полные разборы действительно так ужасны, как о них ду­мают, и их можно избежать, используя в коде SQL переменные связы­вания вместо литералов. Однако частичный разбор столь же ужасен, и часто без него тоже можно обойтись.

Многие авторы употребляют словосочетание «частичный разбор» (soft parse) как синоним для «вызова разбора» (parse call). Мне больше нра­вится термин «вызов разбора», т. к. он обращает наше внимание на приложение, внутри которого в действительности может быть пред­принято спасительное действие. Если же говорить о «частичном разбо­ре», то внимание акцентируется на базе данных, которая не является местом решения нашей проблемы. И вот почему. Каждый раз, когда серверный процесс Oracle получает вызов разбора от приложения, это­му процессу необходимо использовать процессор сервера базы данных. Если будет обнаружен подходящий для этого запроса разделяемый курсор в кэше курсоров сеанса или библиотечном кэше Oracle, то вызов разбора никогда не приведет к полному разбору, и окажется, что затра­ты на разбор совсем не так велики, как могли бы быть. Однако отсут­ствие разбора обходится еще дешевле, чем частичный разбор. Наилуч­шую масштабируемость для большого количества пользователей име­ют приложения, в которых разбор происходит как можно реже. Следу­ет по возможности избавиться от всех ненужных вызовов разбора.

Для обеспечения масштабируемости лучше всего, чтобы прило­жения осуществляли минимально возможное количество вызовов базы данных. Именно в этом направлении развивается Oracle Call Interface (OCI). Например, в версии 8 OCI предприняты меры для снижения количества пересылок между клиентом и сервером (http://otn.oracle.com/tech/oci/htdocs/Developing_apps.html). Версия 9.2 OCI идет еще дальше, делая так, что многие вызовы базы данных приложения вообще не достигают базы дан ных (http://otn.oracle.com/tech/oci/htdocs/oci9ir2_new_features).

В системах с высокой конкурентностью и неоправданно многочислен­ными вызовами разбора большое количество событий CPU service зачас­тую коррелирует с большим количеством событий ожидания latch free для библиотечного кэша, разделяемого пула и других защелок. Само по себе получение и освобождение защелок может привести к избыточно­му потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для SPINCOUNT по умолчанию (опять-таки, обычно не стоит этого делать). Более того, избыточные вызовы разбора могут привести к ненужным задержкам SQL*Net message from client, способным добавить до нескольких секунд лишнего време­ ни отклика на каждую секунду реальной работы, выполняемой в базе данных. Наконец, вызовы разбора для длинных фрагментов SQL созда­ют ненужные задержки SQL*Net more data from client, которые также мо­гут внести существенный вклад в увеличение времени отклика.

Если ухудшение производительности вызвано большим количеством вызовов разбора, то можно воспользоваться следующими способами снижения нагрузки:

Старайтесь обходиться без строковых литералов в инструкциях WHERE. Заменяйте их переменными связывания (заполнителями), особенно когда строковый литерал имеет высокую кардинальность (т. е. строка может иметь множество значений). Использование строковых литералов вместо переменных связывания приводит к затратам процессорного времени (CPU service), а также вызывает в системах с высокой конкурентностью ненужные события ожида­ния освобождения защелок для библиотечного кэша, разделяемого пула и объектов кэша строк.

Пример 11.2. Разбор внутри цикла серьезно препятствует масштабируемости

Отключите те функции интерфейсного драйвера, которые приводят к увеличению количества вызовов разбора по отношению к их чис­лу в исходном коде приложения. Например, Perl DBI содержит ат­рибут уровня prepare под названием ora_check_sql. Его значение по умолчанию равно 1, что означает два вызова разбора для каждого вызова функции Perl prepare. Первый вызов разбора выполняется с тем, чтобы помочь SQL-разработчику приложения быстрее отла­дить свой исходный код за счет предоставления более подробной диагностической информации для неудачных вызовов разбора. Од­ нако в промышленных системах такую функцию следует отключить, т. к. она приводит к излишним вызовам разбора.

ВАЛЕРИЙ МИХЕИЧЕВ , эксперт Oracle, ОСАО «Ингосстрах», [email protected]

Причины неэффективности 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-версию данного номера можно приобрести в нашем магазине .

Вконтакте