Как при применении ручных методов оптимизации SQL наподобие утилит EXPLAN PLAN, SQL Trace и TKPROF, так и при использовании более совершенных методов вроде инструмента SQL Tuning Advisor, необходимо понимать, что оптимизация SQL-операторов может значительно улучшать производительность базы данных Oracle. В этой заметке моего блога описана простая методика, к которой можно прибегать для настройки SQL-операторов.
Выявление проблемных операторов SQL
В наших блогах показано много способов, которыми можно выявлять SQL-операторы, выполняющиеся слишком медленно или потребляющие слишком много ресурсов. Например, для выявления наихудших в этом плане SQL-операторов можно использовать динамические представления производительности наподобие V$SQL
, как показывалось ранее. Операторы с высокими показателя по количеству операций buffer gets
будут представлять собой те операторы, которые потребляют больше всего ресурсов ЦП, а операторы с высокими показателями по количеству операций disk reads
— те операторы, которые потребляют больше всего ресурсов подсистемы ввода-вывода. В качестве альтернативного варианта, можно полагаться на отчет AWR или анализ ADDM и выяснять, какие из SQL-операторов нуждаются в более эффективном написании. Очевидно, что начинать (и, возможно, заканчивать) имеет смысл с настройки этих проблемных операторов.
Определение источника неэффективности SQL
Следующим шагом является определение причины неэффективности SQL-операторов. Это подразумевает сбор информации о том, как оптимизатор выполняет их, т.е. изучения вывода EXPLAIN PLAN. Данный шаг помогает выяснять, нет ли очевидных проблем, наподобие выполнения операций полного сканирования таблиц из-за отсутствия индексов.
Помимо анализа вывода EXPLAIN PLAN и использования представления V$SQL_PLAN
, следует собирать информацию по производительности, если это возможно, с помощью утилит SQL Trace и TKPROF.
Нужно очень тщательно изучать каждый вывод EXPLAIN PLAN и удостоверяться в том, что методы доступа, методы соединения и порядок соединения являются оптимальными. В частности, при анализе EXPLAIN PLAN следует принимать во внимание перечисленные ниже вопросы.
- Не выполняются ли какие-нибудь неэффективные операции полного сканирования таблиц?
- Не выполняются ли какие-нибудь недостаточно избирательные операции сканирования диапазонов?
- Являются ли индексы подходящими для запросов?
- Являются ли индексы достаточно избирательными?
- Если есть индексы, все ли из них используются?
- Не выполняются ли какие-нибудь поздние операции фильтрации?
- Используется ли для управляющей таблицы в соединении наилучший фильтр?
- Применяется ли метод и порядок правого соединения?
- Отвечают ли SQL-операторы базовым требованиям по написанию эффективных SQL-операторов (о которых рассказывалось ранее в этой главе, в разделе “Написание эффективного SQL-кода”).
В большинстве случаев анализ структуры запроса будет выявлять причину его неэффективности.
Настройка оператора SQL
Далее можно использовать предлагаемый в составе Database Control инструмент SQL Access Advisor для получения рекомендаций по созданию индексов и материализованных представлений. Следует обязательно просматривать используемый в операторе путь доступа к таблицам и порядок соединения. Еще можно рассматривать вариант использования подсказок для принуждения оптимизатора применять более эффективный план выполнения, а также прибегать к помощи SQL Tuning Advisor для получения рекомендаций по настройке SQL-операторов.
Сравнение показателей по производительности SQL-кода
После генерации альтернативного SQL-кода нужно снова пройти три первых шага. В частности, следует сравнить вывод EXPLAIN PLAN и статистические данные по производительности нового оператора со старым. После получения уверенности в том, что новые операторы работают лучше, можно заменить ими прежний неэффективный SQL- код. В Oracle Database 11g предлагается гораздо более широкий спектр возможностей для автоматической настройки SQL, чем когда-либо. После освоения различных автоматических средств настройки, наподобие SQL Tuning Advisor и ADDM, вы очень легко справитесь с “непокорными” SQL-операторами.