Оптимизация Oracle EBS. Мемуары "настройщика"

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

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

Эта статья о том, как в проекте "ВымпелКома" строился процесс по оптимизации производительности Oracle E-Business Suite (EBS).

"Тушение пожаров"

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

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

• сбор гистограмм по некоторым колонкам;
• удаление неправильных хинтов и "кастомизированного" кода. Хинты в SQL-запросах, поставленные в 2004 г., на тот момент были эффективными. Но по прошествии нескольких лет картина данных в БД поменялась, и зачастую от таких "подсказок для оптимизации" было больше вреда, чем пользы. Большинство подобных "оптимизированных" запросов исправлялись простым удалением хинта. Конечно, предварительно был налажен регулярный сбор статистики для оптимизатора Oracle;
• переписывание запросов. Обнаружив плохо написанный запрос, мы назначали его на автора этого запроса. Это позволило "убить двух зайцев": проблема производительности у пользователей устранялась еще до того, как пользователь успевал за вести инцидент, а разработчик волей-неволей учился на своих ошибках, опять же, без вреда для клиента;
• замена переменных привязок их литеральными значениями и наоборот - с целью объяснения этого способа впоследствии бы ли организованы семинары для всех разработчиков проекта;
• поиск и установка исправлений (patches) для EBS, улучшающих производительность, - этот ме тод применим только к стандарт ному (не "кастомизированному") коду;
• создание индексов. Стоит отме тить, что к созданию "кастомизированных" индексов в проекте относились крайне непривет ливо. Однако результаты пер вого подобного опыта были охарактеризованы фразой "два индекса, которые перевернули STATSPACK". Всего парой ин дексов размером 65 кбайт уда лось значительно ускорить око ло десятка самых "тяжелых" "кастомизированнных" запросов модуля "Запасы". Тем не менее, мы по-прежнему не приветствуем подобных решений по оптимизации, если предварительно не проверены другие способы.

Работа с администраторами

Параллельно с устранением "тяжелых" запросов налаживалось взаимодействие "настройщика" с администраторами для выполнения ряда работ. Иногда договориться удавалось сразу, а порой приходилось использовать весь свой дар убеждения.

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

1. Налаживание регулярного сбора статистики для оптимизатора БД. По традиции статистика для оптимизатора собиралась время от времени, по мере возникновения проблем и лишь по отдельным схемам. Это привело к тому, что оптимизатор при выборе планов запросов постоянно ошибался, так как опирался на устаревшую информацию. После первого сбора статистики из первой десятки самых "тяжелых" запросов практически исчезли все стандартные, не "кастомизированные" запросы. К сожалению, пользователи системы перестают заводить инциденты, когда их отчеты начинают работать значительно быстрее - они тут же привыкают к хорошему. С этим приходится мириться и оценивать эффект техническими способами.
2. Перевод БД с версии 9.2.0.5 на версию 9.2.0.8. Читатели пра вы - "на дворе" уже версия 10.2. Но полтора года назад вводить резкие изменения никто не хотел, тем более что Oracle до сего дня продолжает оказывать техподдержку 9i.
3. Переход версии БД с 32-битной на 64-битную. Это был знаменательный шаг, после него, можно сказать, началась эра ста бильности. Шутка ли: на сервере с 60 Гбайт оперативной памяти работал 32-битный Oracle, у кото рого максимальный размер SGA составляет всего 3,75 Гбайт.
4. Увеличение параметра INITRANS для некоторых сегментов. Редкий администратор, не задумываясь, ответит, что такое 1ТГ Waits. А это название ожида ния, одной из причин простоя серверов, аналога пробки на дорогах, когда и асфальт прекрасный, и машины дорогие, но никто не едет. Такие ожидания можно увидеть, если собирать отчет STATSPACK седьмого уровня, а не пятого (по умолчанию). Неправильный параметр INITRANS может стать серьезной проблемой в большом проекте. Например, российская локализация Oracle создает пару индексов на таблицу GL_JE_LINES с параметром INITRANS по умолчанию. Для сервера Oracle это означает, что в одном блоке индекса гарантированно могут сосуществовать только две активные транзакции, а остальные станут смиренно ждать в очереди (enqueue), что в представлении V$SEGSTAT идентифицируется как ITL Waits.
5. Изменение количества экстентов в сегментах базы данных путем изменения значения STORAGE для некоторых сегментов. Первый анализ карты экстентов произвел яркое впечатление: 1% всех сегментов состоял из 95% общего количества экстентов в БД.
6. Правильное решение пре дыдущей проблемы - переход на новую модель табличных про странств (OATM). Однако это требует большого "даунтайма", поэтому процесс реализуется по степенно, приложение за прило жением.
7. Настройка буферного кэша. Не вдаваясь в подробности, отме чу, что были настроены три вида буферного кэша - KEEP, RECYСГЕ и DEFAULT.
8. Сжатие некоторых таблиц. Данный способ оптимизации известен пока не широко, да и применять его надо с осторож ностью. Несмотря на это, метод позволяет достичь довольно не ожиданных результатов. Напри мер, сжатием всего шести таблиц удалось за один вечер добиться сокращения общего количества дисковых чтений на продуктив ном сервере в два-три раза. Хочу предупредить читателя, что не стоит применять этот метод, пока вы не изучите его негативные сто роны, а также не проштудируете списки обнаруженных ошибок сжатия в вашей версии Oracle. Консультации с технической под держкой Oracle по данному во просу также будут уместны.

Об истории перехода на 64-битный Oracle можно написать отдельную статью. В переговорах о применении этого решения не участвовал разве что генеральный директор "ВымпелКома". Но мы добивались этого не "ради искусства": дополнительная память дает широкий простор для администраторов. Например, если возникает проблема с производительностью какой-либо параллельной программы в момент закрытия периода, когда уже нет времени на исследования и оптимизацию, то за счет использования оперативной памяти можно резко поднять производительность проблемного приложения. Сделать это можно, например, поместив в память (KEEP BUFFER POOL) наиболее читаемые этим приложением сегменты. В частности, пока не нашлось более эффективного решения, мы таким образом ускоряли расчет амортизации с двух часов до 15 минут. В дальнейшем мы ускорили расчет амортизации другими методами, и теперь для этой задачи не требуется много памяти.

С того дня, когда произошла миграция на 64-битный Oracle, не было ни одного дефекта первого приоритета по производительности, который нельзя было бы устранить до конца рабочего дня. У администраторов появилось мощное средство, позволяющее временно ускорить выполнение той или иной задачи, не прерывая ее работы, чтобы позднее, не торопясь и не нервируя клиента, разобраться с проблемой и устранить причину ее возникновения.

Взаимодействие с разработчиками

В проекте "ВымпелКома" очень интересно работать тем, кто не любит ежедневного однообразия. Бизнес ставит новые задачи с завидной скоростью. На той же скорости функциональные консультанты и разработчики выдают новые решения. Постоянно внедряются новые модули, на EBS мигрируют новые регионы, разрабатываются новые "кастомизации".

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

Поскольку решать проблему на продуктивном сервере БД - дело неспокойное, мы разработали ряд действенных мер:

• жизнь по стандартам. Были сформулированы правила оформления разработок. Например, при создании таблиц и индексов разработчики теперь не забывают указывать параметр INITRANS. Созданы автоматические проверки для часто повторяющихся ошибок. Скажем, если разработчик решит собрать статистику с помощью команды analyze (в то время как Oracle EBS требует сбора статистики пакетом FND_STATS), то он не сможет выложить разработку даже в тестирование - проблема будет обнаружена автоматически;
• обязательная проверка кода. Код каждой разработки просматривается руководителем группы или опытным коллегой. Свежий взгляд помогает обнаружить многие проблемы еще наэтапе разработки;
• перед установкой на тестирование администратор третьего уровня технической поддержки просматривает обработанные утилитой tkprof трассировки. Он, например, может контролировать такие моменты, как:
  • количество уникальных SQL-запросов. Если по ошибке кто- то из разработчиков в модуле Payroll укажет предикат для person_id без переменной привязки, то сразу после запуска отчета продуктивная БД резко снизит свою производительность, а администраторы в STATSPACK увидят на первом месте ожидания library cache lock и library cache pin. Для предотвращения такой проблемы достаточно по смотреть отчет tkprof и не тра тить ресурсы на нагрузочное те стирование;
  • общее количество логических чтений. Если его поделить на 131 072, то можно получить количество гигабайт, прочитанных из БД при выполнении задачи (размер блока в БД EBS должен составлять 8 кбайт, 131 072 блока в одном гигабайте). Например, если для подготовки справки НДФЛ-2 по одному человеку считывается 20 Гбайт, в то время как общее количество данных по этому модулю не превышает 10 Гбайт по всем компаниям и за все периоды, то сразу становится понятно, что либо в алгоритме, либо в планах запросов что-то не в порядке.

Опытный взгляд быстро определит проблему в top-запросе отчета tkprof. Таким образом, администратору не требуется просматривать весь код. Анализ пары самых "тяжелых" запросов даст нужный эффект. Если вы несколько лет подряд ежедневно анализировали отчеты STATSPACK, то поймете, о чем идет речь.

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

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

Поэтому мы организовали семинары для разработчиков.

Семинары

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

Екклесиаст написал: "Составлять много книг - конца не будет, и много читать - утомительно для тела". Поэтому мы не стали ставить в обязанность всем разработчикам прочтение всех существующих книг по оптимизации, а решили структурировать и преподать в виде семинаров 10% знаний о производительности Oracle, которые позволяют эффективно решать 99% задач в нашем проекте.

Тестовые серверы

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

• это слишком дорого, тем более что в большом проекте (разра ботка, тестирование, тестирова ние пользователями и т. д.) од ним тестовым сервером не обойтись;
• даже если поставить для тестов точную копию продуктивного сервера, то 100%-ного совпаде ния времени обработки задачи в тестовой и продуктивной сре дах никто никогда не получит, поскольку нагрузка на эти сер веры совершенно разная.

Каков же выход? Технически проблема тестирования производительности без использования продуктивного сервера решается просто, что и происходит в нашем проекте. Время отработки задачи на любом сервере делится на две основные составляющие: время работы процессоров (CPU) и время различных ожиданий (время простоя). Среди известных ожиданий можно назвать дисковые чтения (db file sequential reads), latch free и др. Зная разницу в мощности процессоров, можно оценить, во сколько раз изменится составляющая CPU на продуктивном сервере. Как правило, каждое конкретное ожидание на продуктивном сервере либо сокращается (это норма для дисковых чтений), либо возрастает (как часто происходит с ожиданием latch free). Итак, определив, сколько времени было потрачено на каждое ожидание на тестовом сервере, можно достаточно точно спрогнозировать время выполнения данной задачи на продуктивном.

Приведу пример оптимизации процессов расчета зарплаты. Однажды один из менеджеров обеспокоился длительным (около восьми часов по большой операционной единице) расчетом зарплаты. Мы оптимизировали эту задачу на самом слабом тестовом сервере и маленькой операционной единице. После окончания оптимизации время расчета на тестовом сервере увеличилось с 15 до 18 минут. Как вам результат? Вы готовы платить премии своим сотрудникам за такую работу? Нет? А напрасно. На продуктивном сервере расчет вместо восьми часов завершился за 1 час и 35 минут. Дело в том, что были сокращены ожидания latch free, но увеличены db file sequential reads. В тестовой среде медленные диски, а в продуктивной latch free всегда возрастают - вот и весь секрет.

Что дальше

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

Таким образом, оптимизацию производительности не следует ограничивать техническими средствами Oracle.

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

• Обратиться в "Интерфейс" за дополнительной информацией/по вопросу приобретения продуктов
• Подробнее о продуктах Oracle
• Приобрести продукты Oracle в ITShop.ru
• Курсы обучения по продуктам компании Oracle
• Подписаться на рассылку "СУБД Oracle "с нуля"