Использование Oracle Enterprise Manager (EM) — SQL Tuning Advisor для оптимизации sql-запросов
By: Date: 18.04.2013 Categories: !RUS,ORACLE,PerformanceTuning
ENG: Use Oracle Enterprise Manager (EM) — SQL Tuning Advisor to optimize the sql queryes

Oracle 11gR2 (для 10gR2 всё аналогично).

По умолчанию ЕМ каждый час — делает снимок (snapshot) параметров, статистик, метрик БД, сравнивая этот снимок и предыдущий автоматически делает ADDM отчет и хранится всё это 7 дней. Т.е. в ЕМ можно посмотреть рекомендации по каждому часу работы БД за последние 7 дней.
Dmitry Bobrovsky
По любым двум снимкам (т.е. по какому-то интервалу времени по которому у вас есть снимки), можно вручную построить отчеты ADDM и AWR.
©Bobrovsky Dmitry
ADDM – отчет в котором Oracle дает рекомендации по улучшению производительности простым человеческим языком.
©Bobrovsky Dmitry
AWR – отчет для администратора. Администратор сам анализирует этот отчет и сам делает какие-то выводы.
Dmitry Bobrovsky
Так же в ЕМ имеются различные tuning advisor, т.е. некие «мастера» которые помогают решать конкретные задачи. Например, SQL Tuning Advisor, который может проанализировать sql-запрос (или запросы) и выдать рекомендации по улучшению производительности выполнения данного sql-запроса, например предложить более лучший план выполнения.

Когда использовать SQL Tuning Advisor для оптимизации sql запросов

Предположим у нас выполняется некий отчет или процесс, например некая выгрузка данных из БД, который идет около часа, хотя мы знаем что на другом сервере или другой версии БД этот отчет, процесс идет 3 минуты. Или вы просто перевели свою систему с Oracle 10g на 11g и пользователи стали жаловаться что система тормозит.

Необходимо просмотреть ADDM отчет (особенно интересны отчеты которые были сделаны в период особо сильного торможения или выполнения какого-то процесса, который стал выполняться медленнее). Если вы видите что для обнаруженных проблем (особенно те которые потребляют наибольшее количество ресурсов, они в начале списка) предлагается оптимизировать sql запросы, значит нужно воспользоваться SQL Tuning Advisor.
SQL Tuning Advisor может выдать рекомендации по оптимизации sql-запросов, рекомендации могут самые разные, не только изменения плана, а например добавление индекса и т.п.
SQL Tuning Advisor может менять план sql-запроса не меня при этом сам sql-запрос! (это очень важно, особенно когда нет возможности поменять sql-запросы системы, т.к. исходные коды закрыты).

ВНИМАНИЕ: Ни в коем случае не стоит заниматься никакой оптимизацией ради самой оптимизации. Только если это действительно является проблемой, т.е. вы точно знаете что процесс может выполняться быстрее или пользователи жалуются. Иначе, даже если отчет выполняться целый рабочий день, но при этом никто не жалуется — это не проблема и ничего делать не нужно.

Итак что делать чтобы улучшить время выполнения отчета или процесса:

Как использовать SQL Tuning Advisor для оптимизации sql запросов

Зайти в EM

Лучше использовать Internet Explorer 32-бита. Для SQL Tuning Advisor — нужны запросы, который создают высокую нагрузку на БД (high-load SQL statements). На главной странице может быть отчет ADDM и тогда их можно взять прямо здесь. Здесь на картинке его нет, т.к. нагрузка на БД в последнее время была не большая. Поэтому нужно пойти по ссылке — Advisor Central.

Oracle Enterprise Manager главное окно

Получить отчет ADDM за период выполнения отчета\процесса или использовать уже готовые отчеты

В Advisor Central собраны основные инструменты по оптимизации БД. На главной странице вы видите список ADDM отчетов и различные задания. Если вы четко знаете когда именно выполнялся ваш отчет\процесс то можете использовать один или несколько уже готовых ADDM отчетов из списка за время выполнения отчета\процесса. Advisor Central

Так же можно пройти по ссылке ADDM и увидеть график загрузки БД и использовать выявленные ADDM отчеты (если подвести мышку к иконке фотоаппарата внизу  графика, будет выедена подсказка, в которой указано время ADDM отчета). Прямо здесь можно получить ADDM за период выполнения отчета\процесса. Нужно выбрать пункт «Run ADDM to analyze past performance«. Затем выбрать пункт «Period Start Time«. Кликнуть на иконке фотоаппарата начала периода. Выбрать пункт «Period End Time«. Кликнуть на иконке фотоаппарата конца периода. Нажать «ОК«.

Run ADDM

Использовать выявленные проблемы в ADDM чтобы получить проблемные sql-запросы

Откроется следующее окно, внизу которого приведен список различных проблем которые были обнаружены (Finding). Нужно пройти по всем ссылка, т.к. проблемные sql-запросы могут быть в любом пункте, например «Использование CPU«, если sql-запрос потребляет большое количество CPU.

Automatic Database Diagnostic Monitor (ADDM)

Запустить задание SQL Tuning Advisor для выявленных sql-запросов

Зайти одну из ссылок и вижу в рекомендациях (Recommendations) проблемные sql-запросы. Здесь кнопка «Run Advisor Now» сразу запустит задание SQL Tuning Advisor для одного конкретного sql-запроса. Можно выделить галочками несколько sql-запросов и нажать «Schedule SQL Tuning Advisor» — тогда за один раз будут проанализированы все выбранные запросы.

Recommendations

Здесь для примера показано окно создания задания, если вы на «Schedule SQL Tuning Advisor«. Можете оставить всё как есть и просто нажать «Submit«. Начнется выполнение задания SQL Tuning Advisor. Можно не ждать завершения задания, а нажать «Cancel«.  Задание при этом не прерывается. Статус любого задания и его результат можно посмотреть в Advisor Central.

Schedule SQL Tuning Advisor

Применить рекомендации SQL Tuning Advisor

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

Select Recommendation

Дальнейшая работа с рекомендациями SQL Tuning Advisor

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

Select Recommendation
Запись Использование Oracle Enterprise Manager (EM) — SQL Tuning Advisor для оптимизации sql-запросов впервые появилась Dmitry Bobrovsky Blog