Настройка производительности бд oracle

Обновлено: 07.07.2024

Архитектура публикации Oracle аналогична архитектуре публикации MicrosoftSQL Server , поэтому первый шаг в настройке производительности репликации Oracle состоит в выполнении следующих общих рекомендаций по настройке, описанных в статье Enhance General Replication Performance.

Помимо этого, существуют еще две настройки для издателей Oracle, относящиеся к производительности:

Указание соответствующего параметра публикации: Oracle или Oracle Gateway.

Настройка задания наборов транзакций для обработки изменений на издателе в соответствующие интервалы.

Указание соответствующего параметра публикации

Параметр Oracle Gateway обеспечивает большую производительность по сравнению с параметром Oracle Complete. Тем не менее этот параметр нельзя использовать для публикации одной и той же таблицы в нескольких публикациях транзакций. Таблица может присутствовать только в одной публикации транзакций и в любом количестве публикаций моментальных снимков. Если необходимо опубликовать одну таблицу в нескольких публикациях транзакций, выберите параметр Oracle Complete. Укажите этот параметр при идентификации издателя Oracle на распространителе SQL Server . Дополнительные сведения см. в разделе Create a Publication from an Oracle Database.

Настройка задания наборов транзакций

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

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

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

Oracle Database 11g Настройка производительности

Причины неэффективного выполнения SQL выражений

SQL выражение может выполнятся неэффективно по следующим причинам:

Устаревшая статистика оптимизатора: Планы выполнения SQL генерируются стоимостным оптимизатором. Для того чтобы оптимизатор генерировал и использовал наиболее эффективный план выполнения, он нуждается в точной информации о хранении и распределении данных в таблицах и индексах, которые участвуют в обработке SQL выражения.
Отсутсвие путей доступа к данным (access path): Отсутствие структур доступа к данным, таких как индексы, материализованные представления или партиций - частая причина падения производительности SQL выражений. Создание соответствующих методов доступа к данным на порядок увеличит производительность SQL выражений.
Выбор не оптимального плана выполнения: В отдельных случаях оптимизатор может выбрать не оптимальный план исполнения для SQL выражения. Причиной выбора не оптимального плана может быть неправильно оцененная стоимость, кардинальность или селективность для предиката выражения.
Неправильно построенный SQL: Если SQL выражение спроектировано неправильно, оптимизатор не сможет построить оптимальный план выполнения. Отсутствующее условия соединения, ведущее к декартову произведению данных, или использование наиболее ресурсоёмких операций таких как UNION вместо менее затратной UNION ALL - некоторые примеры написания неэффективных SQL выражений.
Дополнительно на производительность выполнения SQL могут влиять проблемы с аппаратным обеспечением, таким как память, ввод/вывод CPU и.т.д.

Запрос определяет какие продукты имеют стоимость по прайс листу менее чем на 15% выше средней стоимости продукта. В данное выражение встроен подзапрос, который выполняется для каждой строки, найденой в результате основного запроса. Этот запрос можно переписать следующим образом: SELECT COUNT(*) FROM products p,(SELECT prod_id, AVG(unit_cost) ac FROM costsGROUP BY prod_id) cWHERE p.prod_id = c.prod_id ANDp.prod_list_price < 1.15 * c.ac
В данном запросе применяется функция к операции объединения, ограничивая тем самым возможность использования индекса для объединения. Для нормального выполнения запроса понадобится создать индекс основанный на функции.
Запрос включает в условии неявное преобразование типов данных.
Запрос использует функцию преобразования типов для приведения в соответствие типов данных обрабатываемых в предикате. Проблема заключается в том, что функция будет вызываться для каждой строки в таблице employees. Более оптимальным будет конвертировать условие предиката а не весь столбец: SELECT * FROM employees WHERE salary = TO_NUMBER(:sal)
В запросе используется оператор UNION вместо UNION ALL для того чтобы отбросить дублирующиеся строки в процессе выполнения запроса. Данная операция приведет к дополнительной сортировке для исключения не уникальных значений.

Решения Oracle Database для мониторинга производительности

Automatic Workload Repository (AWR): Собирает, обрабатывает и обслуживает статистику производительности для обнаружения проблем и задач самонастройки. Данные статистики хранятся в памяти и на диске в таблицах БД. собираемые данные могут быть просмотрены как при помощи представлений, так и в виде отчета за период.

Active Session History (ASH): Предоставляет выборочную статистику по активности сессий в экземпляре. Активные сессии выбирающиеся каждую секунду хранятся в цикличном буфере в SGA.

Проактивный мониторинг

Automatic Database Diagnostic Monitor (ADDM) автоматически выявляет узкие места в работе Oracle Database. Дополнительно, работая с другими компонентами управления, ADDM выдает рекомендации для устранения узких мест, используя доступные опции.
Oracle Database 11g в дальнейшем автоматизирует процесс настройки SQL, идентифицирует проблемные SQL выражения, запускает для них SQL Tuning Advisor и применяет профиль, сгенерированный в результате работы SQL advisor к SQL выражению, повышая тем самым скорость его выполнения без вмешательства пользователя. Для этого, при помощи среды AUTOTASK создается пакетное задание для автоматического исследования производительности (Automatic SQL tuning task). Задание Automatic SQL Tuning task запускается по умолчанию каждую ночь.

Реактивный мониторинг

Предупреждения генерируемые сервером: База данных Oracle может автоматически определять проблемные ситуации. В качестве реакции на обнаруженную проблему, СУБД Oracle отправляет предупреждение о ней с возможными действиями для её устранения
В качестве инструмента для реактивного мониторинга производительности БД также может использоваться Oracle Enterprise manager.

Инструменты мониторинга и настройки производительности Oracle Database 11g

Automatic Database Diagnostic Monitor: Постоянно анализирует данные производительности, собираемые экземпляром БД.
SQL Tuning Advisor: Анализирует SQL выражения, определенные как проблемные, и пытается из настроить. По умолчанию задание на анализ производительности включено. Вы можете также вручную запустить SQL Tuning advisor для анализа производительности определенного SQL, или группы SQL используя SQL Tuning set.
SQL Tuning Sets : Служит репозиторием для хранения набора SQL выражений. Может использоваться SQL Tuning advisor для анализа производительности группы SQL выражений или например для переноса SQL из одной БД в другую для выявления потенциальных проблем с производительностью, которые могут возникнуть после миграции.
SQL Access Advisor: Анализирует SQL выражение и выдает рекомендации по созданию материализованных представлений, индексов, логов материализованного представления или секционированию таблиц и индексов.
SQL Monitoring: Позволяет в режиме реального времени наблюдать за процессом исполнения SQL и просматривать план и статистику его выполнения.
SQL Plan Management (SPM): Может быть использован для контроля и стабилизации планов исполнения SQL выражений при помощи создания опорных линий. При использовании опорной линии SQL будет использовать план исполнения с наилучшим временем выполнения, остальные планы, сгенерированные для этого SQL будут помечены как исторические, будут хранится в опорной линии и постоянно анализироваться. В случае нахождения более оптимального плана выполнения, этот план будет применен к SQL выражению, а предыдущий будет помещен в историю.

Определение высоко нагруженных SQL является одной из наиболее важных задач которые вы должны выполнять. ADDM - идеальный инструмент для выполнения данной задачи.
По умолчанию СУБД Oracle собирает статистику оптимизатора автоматически. Для сбора статистики работает задание планировщика, которое запускается в maintenance window.
Статистика операционной системы предоставляет информацию и производительности основных аппаратных компонентов также как и производительность системы в целом.
Часто перестройка индексов оказывает положительное влияние на производительность. Например удаление не селективных индексов для увеличения скорости обработки DML или добавление новых столбцов в индекс для увеличения его селективности.
Вы можете сохранить существующие планы выполнения SQL выражений используя сохранив статистику или создав опорную линию для этого SQL.

Наиболее часто на пользовательских системах встречаются следующие ошибки:
Неправильно настроенное подключение: Приложение подключается и отключается при каждой операции взаимодействия с БД. Эта проблема часто встречается в случае работы с stateless сервером приложений.
Некорректное использование курсоров и разделяемого пула: Не используются результаты хранимые в курсоре при повторном разборе. Если не используются BIND переменные, может выполнятся жесткий разбор при каждом выполнении одного SQL. В случае использования BIND переменных, открытые ранее курсоры могут использоваться повторно необходимое количество раз. Очень часто проблемы с версионностью и большим количеством жестких разборов выражения возникают в приложениях, генерирующих SQL динамически.
Некорректно спроектированный SQL: Плохой SQL - это SQL который использует больше ресурсов, чем уместно при его выполнении. Это может быть например DSS запрос, который выполняется более чем 24 часа или запрос к онлайн приложению, который выполняется более минуты. SQL который потребляет значительную часть системных ресурсов должен также быть обследован на предмет его потенциального улучшения. ADDM идентифицирует высоко нагруженные SQL и SQL Tuning advisor может быть использован в дальнейшем для увеличения его производительности.
Использование нестандартных параметров инициализации: Установка некорректных значений параметров может быть выполнена на основании неправильного совета или решения. Большинство систем показывает приемлемую производительность с использованием стандартных параметров экземпляра. В частности, недокументированные параметры оптимизатора могут вызвать серьезные проблемы, которые потом тяжело будет определить и устранить. Также, параметры оптимизатора установленные в файле инициализации могут поменять существующие планы выполнения запросов. Исходя из этого схемы, статистика схем и настройки оптимизатора должны управляться вместе для обеспечения целостности конфигурации и производительности.
Некорректная настройка ввода/вывода: Система хранения данных должна конфигурироваться из расчета доступности, а также пропускной способности, а не легкости хранения и максимизации размера разделов.
Проблемы с настройкой Redo логов: Часто создается недостаточное количество логов Redo. Маленький размер Redo лог файлов может привести к частым операциям checkpoint, что приведет в свою очередь к высокой нагрузке на буферный кэш и систему ввода/вывода. При избыточном количестве Redo логов не сможет выполняться архивирование и БД будет ждать выполнения процесса создания архивных логов.
Избыточная сериализация ресурсов: Сериализация блоков данных в буферном кэше из за нехватки undo сегментов является частой проблемой для приложений с большим количеством активных пользователей и маленьким количеством undo сегментов. Использование автоматического управления сегментами (ASSM) и автоматического управления UNDO чаще всего решает данную проблему.
Длительное полное сканирование таблиц: Большое количество продолжительных сканирований таблиц говорит о некорректном дизайне транзакций, возможном отсутствии индексов или не оптимизированном SQL. Операция full table scan вызывает большую продолжительную нагрузку на систему ввода/вывода.
Большое количество рекурсивного SQL: Большое количество рекурсивного SQL выполняемого SYS может обозначать активность, связанную с управлением пространством, например выделением экстентов. Для исключения рекурсивного SQL рекомендуется использовать табличные пространства с локальным управлением экстентами. В этом случае рекурсивный SQL выполняется от пользователей с разными ID и нагрузка распределяется более равномерно.
Ошибки внедрения и миграции: В большинстве случаев приложение использует слишком много ресурсов, поскольку схема в которой хранятся таблицы необходимые объекты не была должным образом смигрирована из среды разработки или из более поздней версии схемы. Типичными ошибками в процессе миграции являются отсутствие индексов или неактуальная статистика. Данные ошибки являются причиной формирования неоптимальных планов выполнение и как следствие, причиной падения производительности. Когда мигрируется какой либо из разработанных или сущевствующих компонентов, для обеспечения стабильности планов исполнения, необходимо вместе с объектами переносить статистику при помощи пакета DBMS_STATS.

Данная статья предназначена для руководителей и специалистов IT-подразделений, занимающихся эксплуатацией промышленных систем на основе СУБД Oracle, и желающих получить максимальную производительность своей ИС. Впрочем, если у вас используется отличная от Oracle СУБД, не спешите отложить эту статью в сторону: многие факты и советы, данные в этом материале, с равным успехом применимы и для других СУБД.

Если в основе информационной системы (ИС) лежит СУБД Oracle и возникает недовольство пользователей недостаточной производительностью ИС, то, как правило, усилия по исправлению ситуации направляются на оптимизацию СУБД. При этом иногда совершается следующая ошибка — усилия сосредотачиваются на изменении параметров CУБД, а не на уменьшении времени отклика для конечного пользователя. Администраторы ИС изменяют параметры СУБД, но желательного ускорения работы пользователи не получают.
Большое количество статей, обучающих материалов и документации описывают традиционный подход к оптимизации СУБД Oracle, основанный на знании большого количества значений коэффициентов производительности (Ratio tuning). Дальнейшая оптимизация БД связана с улучшением этих коэффициентов, однако с ростом сложности ИС такой подход становится все менее эффективным.
Возникает парадокс, когда все коэффициенты находятся в границах требуемых диапазонов, а недовольство пользователей производительностью своей ИС (временем отклика) растет все больше и больше.
На практике оказывается гораздо важнее уметь оценивать производительность ИС как единого целого, включая аппаратный комплекс, системное программное обеспечение, а также воздействие со стороны сетевого окружения.

мифы и легенды

Оптимизация ИС всегда была окружена слухами и легендами, например, о магических параметрах БД, способных привести к ускорению работы пользователей в десятки раз или о SQL-запросах, которые вдруг начинают формировать отчеты с космической скоростью.
Стоит поблагодарить авторов-фантастов за создание и развитие таких легенд, они делают все возможное, чтобы сотрудники подразделений, занимающиеся оптимизацией ИС, никогда не оставались без работы.
Но некоторые заблуждения являются просто вредными для общего понимания ситуации. На них стоит обратить внимание!

миф параметра fast=true

Часто можно услышать высказывание о том, что ускорить работу БД в несколько раз можно только с помощью настроек БД. Более того, часто возникают вопросы во сколько раз ускорится работа БД, если увеличить, например, кэш БД или журнальный буфер. К сожалению, изменение параметров самой БД практически не влияет на производительность ИС, за исключением случаев, когда были допущены грубейшие ошибки. Настройки могут повлиять на скорость выполнения отдельных процедур, но в целом поведение всей системы не сильно изменится (примерно на 10-15%). Правда состоит в том, что не существует универсального параметра, который позволил бы решить все проблемы разом.
Вместо поиска магических параметров БД в файле настроек init.ora следует сосредоточиться на общем времени отклика системы, а также на определении составляющих времени ожидания — ведь пользователя беспокоит большое время выполнения бизнес-процедур, а не размер кэша БД.
Таким образом, перед изменением любого параметра необходимо знать ответы на следующие вопросы: почему и зачем нужно его изменить, в чем ожидается выигрыш производительности и каким образом измерить произошедшие количественные изменения?

миф более быстрых ЦПУ

миф числа пользователей

Вопрос о том, "сколько процессоров мне необходимо?" звучит от пользователей очень часто. При этом характеризуя свою ИС часто говорят — "у меня будет 200 пользователей". Но число пользователей не может служить оценкой числа необходимых процессоров (например, аналитический отчет способен загрузить систему гораздо сильнее, чем интерактивные пользователи).
Для определения необходимого количества процессоров нужно знать архитектуру построения ИС и используемые средства для ее построения. Эти данные могут помочь наметить пути возможной оптимизации.
Какие же методы существуют для оценки необходимого аппаратного обеспечения? Если система не промышленная (например, такая, как Oracle Application), необходимо самостоятельно проводить нагрузочное тестирование. Эмулируя работу интерактивных пользователей и одновременно получая и анализируя системную статистику, можно с высокой точностью получить оценку необходимой процессорной мощности и требования к дисковой подсистеме.
Не экономьте на оценках производительности системы! Вложения в нагрузочное тестирование окупятся на этапе выбора аппаратного обеспечения!

миф однократной настройки

Руководители подразделений, отвечающие за сопровождение ИС, часто задают вопросы: "Ну хорошо, мы настроем систему — на сколько мне хватит этой настройки? Неужели опять придется вызывать консультантов через год, а то и быстрее?".
Ответ крайне прост: все зависит от того, насколько данная ИС изменится за это время — сколько появится новых пользователей, как изменится состав данных, сколько появится новых форм и отчетов.
Очень полезно отслеживать и затем отображать на графике все изменения ИС (возможно за прошедший год сильно изменилась ИС, обновлено оборудование). И поэтому, если проблемы с производительностью возникли вновь — необходимо повторить исследование ИС!
Здесь уместно провести аналогию с техническим обслуживанием (ТО) автомобиля. Никто из водителей не приходит в ужас от необходимости регулярной замены масла и фильтров, прохождения обязательной процедуры техосмотра. Относитесь к настройке ИС, как к ТО вашего автомобиля — просто придется определить частоту настройки самостоятельно, на основании изменений, происходящих с ИС и, что важно, заложить в бюджет средства на данную процедуру!

Чаще всего вопросы производительности возникают уже во время работы, поэтому стоит рассматривать ситуацию функционирующей информационной системы.
Информационная система работает успешно, группа системных администраторов справляется с ежедневными задачами, но все чаще и чаще возникают жалобы пользователей о том, что их не устраивает время отклика, из бухгалтерии сообщают, что подготовка квартального отчета занимает целый день.
На технических совещаниях, которые теперь проводятся одно за другим, администраторы БД считают, что виноваты разработчики системы, разработчики обвиняют во всем администраторов. Пользователи выражают свое недовольство все сильнее. Моральная обстановка на предприятии ухудшается, и как правило, крайними становятся администраторы БД. Скорее всего, все согласны с этим мнением — ведь кажется очевидным, что данную ситуацию должен исправлять администратор БД.
Наверное всех сильно удивит тот факт, что администраторы БД вообще не отвечают за производительность ИС! Они отвечают только за оптимальную настройку СУБД, и это не всегда означает, что после настройки ИС в целом начнет работать производительно.
Важно также знать разницу между производительностью ИС и производительностью СУБД, а также стоит разобраться, кто же должен найти причину низкой производительности ИС. Для этого определим, что входит в понятие оптимизации СУБД и уточним обязанности администратора БД.

обязанности администратора БД

Вообще говоря, нет документа, в котором обязанности администратора СУБД собраны воедино в формальном виде, тем не менее стоит попытаться сформулировать их, используя Руководство администратора БД и некоторые статьи известных специалистов по Oracle.

Рисунок 1. Распределение времени DBA (из обзора на конференции IOUG Live! 2001)

оптимизация СУБД

Процесс оптимизации СУБД Oracle описан в учебном курсе Oracle 9i Perfomance Tuning. В нем подробно рассматриваются необходимые настройки СУБД и приводится оценка того, насколько оптимально выполнены эти настройки. Некоторые из них приведены в Табл. 1.

Таблица 1. Некоторые коэффициенты производительности БД

Администратор СУБД отвечает за то, чтобы были выполнены все необходимые настройки СУБД. Как оценить, что такие настройки выполнены оптимально? Во-первых, параметры должны соответствовать значениям в таблице 1, и, во-вторых (возможно это более важная оценка), нужно убедиться в отсутствии значительного времени соответствующих событий ожиданий (waits events) на уровне пользовательской сессии или экземпляра БД в целом. Так, например, правильно выбранный размер журнального файла (Redo log space request) должен привести к отсутствию событий ожиданий.
Исходя из приведенного примера может показаться, что оптимизация БД крайне проста. Измеряем соответствующие параметры, смотрим, попадают ли они в необходимые диапазоны, если нет, то действуем согласно вышеприведенным инструкциям.
Но даже оптимально настроенная СУБД (с точки зрения администратора) не обязательно означает максимальную производительность ИС! Почему ранее упоминалось, что администраторы СУБД не отвечают за производительность ИС? Да потому, что у них нет для этого необходимых средств и часто знаний (не по их вине)!
Возвращаясь к примеру выше, даже уменьшив время ожидания для события space request, скорее всего, не удастся решить все проблемы производительности, связанные с журнальным файлом. Скорее всего потребуется перенести журнальные файлы Redo log space request на более быстрые или менее загруженные диски. Для этого нужно знать ответы на следующие вопросы: какие диски и как загружены в системе, что такое вообще "загруженный диск", знать, как ОС работает с подсистемой ввода-вывода, как включить в ОС асинхронный ввод-вывод и т.д. — т.е. знать дополнительно ОС и аппаратные средства. Может ли штатный администратор БД знать все это? Вероятнее всего нет. Это не входит в программу курсов, и на это у него практически нет времени.
Администраторам СУБД достаточно знать, что buffer hit ratio в течение дня имеет значение не менее 99.78%, что означает, что проблем с чтениями в СУБД нет. Но так ли это на самом деле? Не совсем. Cary Millsap в своей работе "Why You Should Focus on LIOs Instead of PIOs" предупреждает о том, что опасность логических чтений часто недооценивается. В этом можно убедиться на реальных примерах. Большое число логических чтений ведет к использованию большого числа защелок (latches) и, следовательно, увеличивает время ожидания серверного процесса на процессоре и время ожидания для конечного пользователя.
Так что нам дает тот факт, что у работающей ИС высокий процент попаданий наших запросов в кэш БД? Было минимизировано число дисковых чтений, но как это сказалось на времени отклика системы? Ведь если присутствует огромное количество логических чтений (из кэша БД), то наше приложение все равно работает медленно. Таким образом, получается, что для оптимизации производительности ИС данный параметр не дает практические ничего! А ведь это один из основных параметров оптимизации в классическом представлении.
Тем не менее, автор придерживается мнения, что это нормальный подход, когда администратор СУБД должен отвечать только за настройку СУБД.
Следует ли из вышеперечисленного, что вообще не нужно обращать внимание на параметры СУБД? Ни в коем случае. Правильный вывод — не останавливайтесь только на изменении параметров СУБД!

кто должен заниматься оптимизацией СУБД?

Теперь стоит остановиться на вопросе — кто же должен заниматься оптимизацией ИС? Автор придерживается мнения, что "в обычном штатном расписании такая позиция просто не предусмотрена". Проще говоря, в штате не должно быть специалиста по оптимизации ИС. Почему? Дело в том, что это специфичный род деятельности, во многом основанный на опыте. И достаются эти знания путем исследования достаточно большого количества различных систем. Формализовать такой опыт практически невозможно, а саму эту работу можно сравнить с детективной работой. Кажется все просто — собери улики (данные о производительности), опроси свидетелей (пользователей и администраторов) и поймай преступника (причину низкой производительности системы). Однако почти каждое дело (ИС) уникально по-своему. И тут очень важен полученный ранее опыт.
Хотелось бы подчеркнуть следующее: работа по оптимизации ИС никак не связана с ежедневной поддержкой ИС, т.е. с тем, чем обязаны заниматься ваши администраторы.
Оказывается, в фирмах-поставщиках ОС и СУБД инженеры, отвечающие за производительность, также выделены в отдельные подразделения. Так в Oracle существует Oracle Support Services Centers of Expertise, в Sun Microsystems Sun's Enterprise Engineering Group. Статьи инженеров вышеперечисленных подразделений представляют особенную ценность для получения знаний о внутреннем мире БД и аппаратной платформы.
Мне кажется, что были приведены достаточно убедительные аргументы, что персонал не виноват в сложившейся ситуации. Перейдем теперь к более интересному вопросу: что же делать? Какой есть выход из этой ситуации?

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

В этой статье были даны теоретические основы оптимизации ИС вообще и баз данных в частности. Для тех, кого живо заинтересовала эта тематика, в следующем номере «СР» будет предложено продолжение темы – статья «Оптимизация информационной системы на базе БД Oracle с помощью пакета Jump-Jet», в которой будет детально описан указанный программный продукт, методология обследования, а также приведены примеры успешной оптимизации ИС с его помощью.

Дмитрий Волков, ORACLE 9i OCP, группа программных решений "Инфосистемы Джет".

Сетевые решения. Статья была опубликована в номере 07 за 2004 год в рубрике software

Александр Кондиайн, главный архитектор по хранилищам данных департамента аналитических систем, R-Style Softlab

Через несколько лет после внедрения автоматизированных систем организация может оказаться в ситуации, когда загрузка оборудования приблизилась к максимуму, производительность выполнения ежедневных штатных процедур упала, как следствие, встал вопрос о покупке более мощного железа. Можно ли максимально отсрочить этот момент? В большинстве случаев можно.

Каждый ИТ-специалист, занимающийся поддержкой инфраструктуры, знает, что через несколько лет эксплуатации система, работающая на базе данных (например, на БД Oracle), теряет производительность. Например, запрос к серверу, который изначально выполнялся буквально одну секунду, стал отрабатывать за 15 или даже 20 секунд. При этом довольно часто отчет о производительности (AWR-отчет) может свидетельствовать о нормальном функционировании ПО. Что же делать? Конечно, выявлять такие запросы и оптимизировать их.

Как вы знаете, АБС и системы на основе хранилищ данных (то есть OLTP- и OLAP-системы) по-разному используют базы данных, поэтому и методики оптимизации у них разные. На примере простейших графиков мы расскажем, в чем их отличие, и, опираясь на собственный опыт, посоветуем, как с помощью инструментов Oracle Enterprise Manager и SQL Monitoring найти ошибки в проектировании и коде, а за счет параллельного выполнения, секционирования и других приемов «выжать» из оборудования максимум его возможностей. Следуя этим рекомендациям, вам удастся продлить срок его службы.

Загрузка сервера в системе координат

Для начала представим себе, что делает сервер БД, когда обрабатывает запрос. Для этого построим график, где по оси X — время, а по оси Y — мощность (или загрузка) сервера (рис. 1). В реальности загрузка состоит из нескольких компонентов, включая загрузку процессора, памяти и системы хранения данных, но для простоты в нашем случае будем ориентироваться на некоторую «общую» загрузку.

Рис. 1. Одиночный запрос, выполненный на сервере

На рисунке 1 показан одиночный запрос. Его выполнение затратило какое-то время и некоторую мощность сервера, поэтому физический смысл нашего запроса — работа. Объем этой работы можно измерить в абстрактных «условных единицах». Именно в этих единицах оптимизатор Oracle измеряет стоимость запроса. То, что мы видим в разделе Cost в плане запроса, является оценочной стоимостью работы сервера по выполнению этого запроса.

Оптимизация OLTP-системы

Нагрузка в OLTP-системах

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

По мере роста количества одновременно запущенных процессов каждый из них выполняется все медленнее. Причина в том, что сервер затрачивает дополнительные ресурсы на переключение между процессами, а кроме этого, обычно СХД является более «тонким» местом, чем процессоры. Если такая «кирпичная» стена достигнет в высоту 100%, то сервер «встанет» — новые процессы будут помещаться в очередь (если у системы предусмотрены ресурсы на это). Таким образом, глобальная задача оптимизации — как можно дальше отодвинуть момент полной загрузки сервера.

Для уменьшения нагрузки мы рекомендуем выбрать некоторое количество одинаковых процессов, которые можно оптимизировать. На нашем графике (рис. 3) они окрашены зеленым.

Рис. 3. Выбор одинаковых процессов с целью оптимизации

Давайте сократим эти блоки в 2-3 раза и посмотрим, что получится (рис. 4).

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

Упомянутая «общая площадь фигуры» примерно соответствует такому важному показателю AWR-отчета, как DB Time. По сути, это общее время работы сервера за период формирования AWR-отчета — The wall time, или Estimated. Например, если отчет выпускался за 1 час, количество процессоров в системе 16, а DB Time в отчете равен 8 часам, это означает, что средняя загрузка сервера составила 50%.

Показатель DB Time удобно использовать как меру произведенной оптимизации (конечно, с учетом отклонений нагрузки, потому что в промышленной среде редко бывают одинаковые по нагрузке дни). Но в целом, если сегодня это время в два раза меньше, чем вчера, день был примерно таким же, и перед снятием сегодняшнего отчета были установлены обновления по оптимизации, то можно предположить, что эти обновления сыграли свою роль.

Ищем «узкие» места

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

Другой метод — тестовые базы. На более слабом, чем промышленное, оборудовании «внезапно» начинают медленно работать отдельные рутинные операции. В промышленной среде они выполняются еще «терпимо», например 2 секунды, а на тестовой — 10, а то и 20 секунд, что уже становится заметным. И выясняется, что какая-то маленькая незаметная функция уже давно работает с плохим планом запроса.

Более инструментальный подход — AWR-отчет. В специальной секции он показывает top queries — какие из запросов заняли наибольшее DB Time, а также количество выполнений.

Могу привести еще более действенный метод — использование Oracle Enterprise Manager (EM).

Внимание! Для работы со сколько-нибудь серьезными системами использование Enterprise Manager не только желательно, но и строго необходимо!

Решаем проблемы с производительностью

Исследование проблем с производительностью в Oracle без этого инструмента сродни изучению клеток без микроскопа. EM предоставляет возможность наблюдать нагрузку в реальном времени, в разрезе самых тяжелых запросов и пользовательских сессий. Кроме того, он умеет сам выделять top неоптимальных, по его мнению, запросов. Для них (а также для любых произвольных запросов по требованию) можно провести профилирование и получить советы по оптимизации — построить определенный индекс, изменить план запроса с возможностью его зафиксировать (baseline) и так далее.

Взглянем на одно из окон EM (рис. 5). Зеленая площадь — CPU, синяя — дисковый ввод-вывод. Хорошо, когда практически вся работа заключается в этих двух видах загрузки, как в нашем случае.

Рис. 5. Отражение разных видов нагрузки в Oracle Enterprise Manager

Одна из самых мощных возможностей EM — SQL Monitoring (рис. 6). С его помощью мы можем в режиме онлайн отслеживать запросы, которые при настройках по умолчанию отвечают одному из двух критериев: либо длятся более 5 секунд, либо осуществляются в параллели. В окне мониторинга видно, на какой стадии выполнения находится запрос, таким образом, можно прогнозировать время его завершения. Окно является активным Flash Plugin и позволяет изучать практически все аспекты обработки запроса — от общего количества считанных/записанных байтов до тонкостей параллельного выполнения. При желании SQL Monitoring запроса можно сохранить в файл для последующего изучения.

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

Вот как в SQL Monitoring выглядит пример «плохого» запроса (по отбору данных для работы в OLTP-системе), который постепенно стал работать более 20 секунд (рис. 7).

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

Иногда бывают и совсем неожиданные результаты. Так, в одной большой промышленной среде замена в маленькой функции выражения «sUser:=user» на «select user into sUser from dual» дала снижение общей нагрузки на 10%. Правда, речь шла о 8 версии Oracle.

Оптимизация OLAP-системы

И снова графики

Рассмотрим типичный вид нагрузки на OLAP-систему (рис. 8).

Сразу замечу, что все сказанное выше про оптимизацию в OLTP сохраняет свою актуальность и для OLAP. Но есть и отличия: в OLAP меньше запросов, но они более сложные и требуют большего времени на выполнение. Кроме того, зачастую их обработка осуществляется в параллельном режиме.

Кстати, о времени ожидания: в OLAP может считаться нормальным запрос, который работает порядка часа, и пользователи готовы ждать его окончания. Процесс получения финальных данных обычно требует многочасовой работы сервера и включает этапы загрузки, перегрузки, валидации, расчета и т.д. Здесь ожидания те же — чем раньше получены данные, тем лучше, но оптимизации требует весь критический путь в целом. Проще говоря, пользователи будут счастливы, если ежедневно будут получать готовые данные не в 15:00, а, скажем, в 11:00, в идеале же — к моменту начала рабочего дня.

На нашем рисунке (см. рис. 8) выделен запрос SQL-3, который лежит на критическом пути и выполняется некоторое время. Преобразуем его из горизонтального «кирпичика» в вертикальный и получим совершенно другую картину (рис. 9).

Общее время выполнения всех четырех запросов значительно сократилось (сдвинулось влево). Это и есть главный результат оптимизации OLAP.

Интересно, что обычно после такого «переворачивания» общая площадь «кирпичиков» увеличивается, то есть сервер выполняет больше работы (накладные расходы на управление параллельными процессами). Но если ресурсы для такого запроса есть, и в это время нет нужды выполнять другой критически важный запрос, то объем работы не важен — важно меньшее время выполнения.

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

Примеры модификаций

Рассмотрим пример подобной простой модификации (рис. 10). Как видите, изначально запрос выполнялся 3,6 минуты.

После простого распараллеливания на 8 потоков запрос начал выполняться 34 секунды. При этом его DB Time сохранился прежним и составляет 3,8 минуты. В целом соотношение 228 сек / 34 сек = 6,7 и близко к искомым 8 потокам, поэтому можно считать распараллеливание успешным (рис. 11). Интересно, что второй запрос потребовал меньше ресурсов для ввода-вывода данных, что не вполне типично.

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

Обратите внимание на еще один пример (рис. 12). Идет вставка данных во временную таблицу. Здесь уже была предпринята попытка распараллеливания, но эффективна ли она? Мы видим «одиноких копателей», которые заняты, например, задачей Load table conventional. И общая эффективность 1,7 минут wall time против 3,6 минут DB-Time никак не дает коэффициент, хоть сколько-нибудь похожий на 8.

В данном случае пришлось удалить ненужный индекс, перевести insert на прямую работу с PGA (load as select). Результат не заставил себя ждать (рис. 13). Теперь в одиночку, как положено, работает только один начальник — PX Coordinator. Все подчиненные работают группами. Время выполнения — 7 секунд, общая эффективность 57/7 — даже чуть больше 8 (ошибка округления в wall time). Как видим, запрос работает уже достаточно эффективно, и дальнейшие попытки его оптимизации большого эффекта не дадут.

И в завершение приведу еще один пример, довольно забавный. Взгляните на SQL Monitoring запроса, на который пожаловались, что он «висит» (рис. 14). На самом деле он не висел, а очень активно работал на протяжении 20 часов. За это время бедный сервер «перелопатил» 57 Тбайт! И это при общем объеме базы всего лишь 80 Гб.

Читайте также: