(Myths & Folklore About Oracle8i Performance Tuning,
by Gaja Krishna Vaidyanatha, Quest Software Inc., gajav@yahoo.com)
(Часть I)
Гаджа Кришна Вайдианата,
Quest Software Inc
Oracle Magazine RE - Май 2002
Источник: Oracle OpenWorld, San Francisco, December 2001.
Трудно поверить, что РСУБД Oracle существует свыше 22 лет: Поразительно, но это истинный факт. За этот период она претерпела существенные изменения. Прошедшие годы (когда осуществлялась трансформация существенной части функциональных возможностей) превратили ее в безапелляционную, вынуждающую нас следить за изменениями, систему. Каждый выпуск новой основной версии Oracle заставлял некоторых из нас чувствовать, что мы должны вновь изучать все концепции. Лично я с появлением каждой основной версии Oracle чувствовал себя совершенно новым АБД. [Прим.А.Бачина: я же, наоборот, видя, что основные архтитектурные концепции сохраняются и развиваются, достаточно уверенно переходил с одной версии Oracle на следующую.]
В мире, который требует от нас сопровождения хорошо спроектированных и имеющих хорошую производительность коммерческих приложений, работающих 24x7x"вечность", существует насущная необходимость - не отставать от времени. Неспособность делать это заканчивается для нас распространением старых и потенциально неприменимых технических решений. Все мифы и фольклор, рассматриваемые в этой статье, связаны с вопросами производительности. Хотя рассмотренный список не является всеобъемлющим, он охватывает некоторые общие вопросы, находящиеся в пределах контекста оптимизации производительности Oracle. Так что вы возьмете из легенды о Лох-Нессе? Действительно ли Несси (так ее нежно называют) существует?
[Прим. пер. В оригинале автор приводит определение мифа по "New Webster's Dictionary and Thesaurus", мы же воспользуемся аналогичным определением из "Малого энциклопедического словаря Брокгауза и Ефрона": "древнее предание и повествование о божествах и обладающих сверхчеловеческой силой героях, в котором в конкретной форме выразились верования и все вообще миросозерцание древних народов". Кроме того, в толковых словарях миф определяется как недостоверный рассказ, выдумка, вымысел.]
Коротко говоря, миф - это что-то, чему можно верить, но это необязательно будет правдой. Обычно это что-то не имеет никакого научного обоснования и, следовательно, не имеет никакого смысла. Приведем несколько примеров:
Фольклор - это совокупность историй, обычно связанных с мифами. Эти истории могут также относиться к сказаниям или представлениям, которые передаются от одного поколения другому. В мире оптимизации производительности Oracle причиной появления фольклора является чья-то неспособность держаться в ногу со временем. Приведем несколько образчиков фольклора:
Одна из первичных причин воздействия на нас мифов и фольклора естественна: мы сопротивляемся изменениям. Человеческая природа в том, чтобы противостоять изменениям, происходящим постоянно и неустанно; большинство людей принимает их с трудом. Сегодняшний мир предполагает парадоксальную точку зрения: изменения - наиболее постоянная вещь.
Как настройщики производительности, мы имеем тенденцию быть подверженными мифам, потому что мы склонны к ложному восприятию, имеющемуся у любого из нас, потому что все мы работали с базой данных Oracle в течение долгого времени (определение продолжительности оставляем вам в качестве упражнения). Именно это является причиной моего сопротивления слову "эксперт". Я полагаю, что знание - обширный океан, а то, что мы знаем, вероятно, разместится в нескольких областях памяти. Называть кого-то экспертом - ошибка, поскольку все познается в сравнении. Как практикующие АБД, которые занимаются настройкой, мы становимся беспомощными жертвами мифов нашего мира. И если мы не поторопимся с проверкой самых смехотворных вещей, нас просветят эксперты. Дезинформация безудержно растет и распространяется через многочисленные собрания групп пользователей, семинары, презентации и фолианты печатного материала.
Прежде всего, мы должны удостовериться, что мы можем проверять каждое техническое заявление, используя разумные тесты или опубликованные научные данные. Слово эксперта само по себе не должно быть воспринято как Евангелие. Более важно то, что мы должны анализировать данные, которые представляются в контексте базы данных Oracle.
Во-вторых, мы должны воздержаться от выполнения настройки, используя несоответствующие показатели, такие, как коэффициенты попадания в кеш, потому что они не помогают в нашей работе. Они не обеспечивают никакой информацией, которая вскрывала бы причины наших проблем производительности. Настройка Oracle, использующая коэффициенты попадания в кеш, может быть сравнена с врачом, требующим анализ крови от каждого пациента, потому что курс лечения определяется исключительно по результатам анализа крови. Если вы посещаете этого опытного врача с переломом большой берцовой кости (с выходом фрагментов кости через поверхность кожи на вашей ноге), но он настаивает на взятии крови для проверки различных показаний анализа, чтобы определить курс лечения, удачи вам и этому опытному врачу. Почему? Потому что этот врач собирается вам сообщить, что ваш анализ крови превосходный и у вас нет никаких проблем. Это - из-за недостаточного внимания к факту, что вы корчитесь от боли и говорите этому врачу, что ваша нога сломана. По очевидным причинам, результаты анализа крови не будут прояснять источник вашей боли и не будут даже показывать, что у вас сломана кость. Оптимизация производительности систем Oracle с помощью коэффициентов попадания в кеш аналогична вышеупомянутой ситуации.
Началась новая эра оптимизации производительности Oracle. Некоторые из нас называют это революцией в оптимизации производительности Oracle. Это - освобождение от иллюзий, которое дает нам правильную и нужную информацию. Это время дано нам, чтобы выбрать управляемый здравым смыслом рациональный путь к оптимизации производительности Oracle: смотреть на узкие места, которые "мучают" нашу систему. Мы должны перед назначением курса лечения определить первопричину болезни. Я пойду несколько дальше сказанного выше: вы ничего не потеряете, если решите в вашей жизни АБД не проверять какие-либо бесславные коэффициенты попадания в кеш Oracle. Самое время предпринять попытку разоблачения некоторых нехороших мифов об оптимизации производительности.
Если вы модернизируете вашу систему, добавив более быстрые ЦП, вы незамедлительно получите лучшую производительность.
Факт
Модернизация вашей системы с добавлением более быстрых ЦП для получения лучшей производительности в целях разрешения существующей проблемы производительности (когда в действительности ЦП не являются узким местом) приведет к существенной деградации производительности. Причина заключается в повышении быстродействия ЦП без соответствующего повышения пропускной способности других подсистем (например, подсистемы ввода-вывода).
Например, если вы удваиваете скорость ваших ЦП и в вашей системе уже имеются узкие места, связанные с вводом-выводом, конкуренция за ввод-вывод возрастет в два раза. ЦП будут выполнять машинные команды быстрее, увеличивая нагрузку на узкое место ввода-вывода в два раза [Gunther, 3]. Делаете ли вы "домашнюю работу" перед наращиванием ресурсов ЦП? [Millsap, 4]. Не занимайтесь повышением быстродействия ЦП, если у вас есть сомнения, что ЦП действительно являются "узким местом".
Если коэффициенты использования вашего ЦП постоянно показывают отсутствие резервных мощностей, это означает наличие в вашей системе узких мест по ЦП.
Факт
Позвольте сначала задать вопрос: испытывает ли в настоящее время ваша система проблемы с производительностью? Если нет, то ничего делать и не нужно. Если имеются проблемы, связанные с временами реакции приложений, начните с проверки процентов времени ЦП, затрачиваемого на ожидание ввода-вывода (%wio в команде sar -u в Unix, %Interrupt Time в объекте Processor в Windows NT). Затем найдите время на проверку накладных расходов операционной системы (%sys в команде sar -u в Unix, %Privileged Time в объекте Processor в Performance Monitor ОС Windows NT). Здесь для нашего обсуждения также очень важны такие накладные расходы ОС, как высокая частота контекстных переключений (десятки или тысячи за секунду), большая интенсивность подкачки страниц (скажем, 20000+ страниц за секунду), - все это нужно исследовать. Если значения %wio и %sys постоянно превышают 15%, требуется дополнительное исследование для определения причины возникновения таких высоких значений. Помните, цель - вылечить от болезни, а не только изучать симптомы.
Например, если %wio в выводе sar -u (или в соответствующем показателе Windows NT) постоянно превышает 40%, это говорит о том, что 40% времени ЦП в вашей системе теряется и не используется, так как ЦП ожидает выполнения запросов на ввод-вывод. Значит, в действительности производительность системы ограничивается узкими местами ввода-вывода, а не узкими местами ЦП.
Другой случай: %sys в выводе sar -u постоянно превышает, скажем, 30%. В этом случае сначала нужно исследовать причину потребности в таких больших накладных расходах операционной системы. Вы должны осознавать, что 30% мощности ЦП в вашей системе потребляется операционной системой. Даже до постановки вопроса о какой-либо модернизации ЦП следует определить причины проблем ввода-вывода или больших накладных расходов операционной системы.
После устранения этих причин нужно для уменьшения объема логического ввода-вывода переписать приложения, интенсивно использующие ЦП (например, с коррелированными подзапросами), используя встраиваемые представления. Это нужно сделать потому, что существенная доля потребления времени ЦП в сеансах Oracle затрачивается на выполнение логического ввода-вывода. После разрешения всех перечисленных выше проблем следует выполнить мониторинг очереди активных задач, чтобы проверить, является ли ЦП по-прежнему узким местом. Если в очереди активных задач постоянно находится в 2-3 раза больше задач чем количество ЦП в системе, следует рассмотреть вопрос о модернизации ЦП. Очередь активных задач можно проверить в выводе vmstat -S в Unix или в счетчике Queue Length в объекте Server Work Queues в Windows NT [Vaidyanatha, 8].
Для уменьшения нагрузки на ЦП в вашей системе баз данных можно предпринять некоторые меры:
ПЕРЕД:
Запрос:
select outer.* from emp outer where outer.sal > (select avg(inner.sal) from emp inner where inner.deptno = outer.deptno);
Вывод Tkprof:
call count cp elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ------- Parse 1 0.02 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3278 14.94 33.95 1666 5946 80 49152 ------- ------ -------- ---------- ---------- ---------- ---------- ------- total 3280 14.96 33.97 1666 5946 80 49152
План выполнения:
Rows Row Source Operation
-------- ---------------------------------------------------
49152 FILTER
114689 TABLE ACCESS FULL EMP
6 SORT AGGREGATE
114688 TABLE ACCESS FULL EMP
Rows Execution Plan
-------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
49152 FILTER
114689 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'
6 SORT (AGGREGATE)
114688 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'
Замечание:
Досадно смотреть на текст плана выполнения приведенного выше запроса, не обнаружив в нем даже никаких намеков на количество фактически выполненных коррелированных подзапросов. Если вы хотите направить в Oracle Corporation запрос на совершенствование РСУБД, вот хороший совет: добавьте в текст плана выполнения количество выполненных коррелированных подзапросов.
ПОСЛЕ:
Запрос:
select emp.*
from emp, (select deptno, avg(sal) avg_sal
from emp group by deptno) davg_sal
where emp.deptno = davg_sal.deptno
and emp.sal > davg_sal.avg_sal;
Вывод Tkprof:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- -------- ---------- Parse 1 0.02 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3278 11.94 20.53 844 4602 40 49152 ------- ------ -------- ---------- ---------- ---------- -------- ---------- total 3280 11.96 20.55 844 4602 40 49152
План выполнения:
Rows Row Source Operation
--------- ---------------------------------------------------
49152 HASH JOIN
3 VIEW
3 SORT GROUP BY
114688 TABLE ACCESS FULL EMP
114688 TABLE ACCESS FULL EMP
Rows Execution Plan
-------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
49152 HASH JOIN
3 VIEW
3 SORT (GROUP BY)
114688 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'
114688 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'
Замечание:
Для небольшой таблицы в базе данных 8.1.7 моего настольного компьютера с Windows NT 4.0 с одним одновременно работающим пользовательским сеансом фактически сбережено 3 секунды времени ЦП (14.96 - 11.96). Теперь пересчитайте это для вашей промышленной базы данных с большим количеством одновременно работающих пользователей. После переписывания коррелированных подзапросов, используя встраиваемые представления, вы получите, по крайней мере, два результата:
Чем больше памяти вы выделите Oracle, тем выше будет производительность системы. Попробуйте, выделите различным кешам Oracle столько памяти, сколько это возможно.
Факт
Различные конфигурируемые кеши Oracle имеют такие же ограничения, как и любые другие кеши. Если вы найдете время для измерения производительности поведения кешей, вы быстро обнаружите, что производительность подчиняется "закону сокращающихся доходов". По мере роста размера кеша производительность возрастает с убывающей скоростью. А начиная с какой-то точки выигрыш производительности будет равен нулю и начнется рост накладных расходов системы. Мораль этой истории: для устранения физического ввода-вывода не пытайтесь кешировать в памяти всю базу данных.
Для оценки правильности установки размера кеша буферов базы данных существуют простые способы в Oracle и в операционной системе (в Oracle8 и выше для этого можно использовать v$buffer_pool_statistics). Осознать этот важный аспект Oracle можно с помощью мало кому известного правила, называемого "правилом пяти минут". Оно выводится из следующего уравнения:
Частота = ((Стоимость байта памяти - Стоимость байта диска) * Размер объекта)/ Стоимость секунды доступа к объекту
Было определено, что по ценам дисков, памяти и подсистемы ввода-вывода 1997 года точка "сокращающихся доходов" приблизительно была равна пяти минутам [Gray, J., Reuter, A., 1]. В современных ценах названных выше компонентов она, в зависимости от вашей платформы, возможно, находится в пределах 8-10 минут.
Что все это означает в контексте Oracle? Любой объект (в пределах разумного и размеров), к которому возможно хотя бы одно обращение в следующие 10 минут, следует рассматривать как кандидата для кеширования в кеше буферов базы данных. Данные, к которым не будет ни одного обращения в следующие 10 минут, не следует принудительно размещать в памяти, так как, начиная с некоторого размера кеша, его производительность существенно не увеличится. В таком случае дешевле и гораздо более эффективно выполнять физический ввод-вывод с дисков.
Такое же логическое обоснование следует использовать при определении размеров различных компонентов разделяемого пула; установку чрезмерных размеров различных пулов можно оценить по статистике free memory (свободная память) в v$sgastat.
Низкие значения статистики free memory в v$sgastat для различных пулов в области разделяемого пула и в v$buffer_pool_statistics для различных пулов кеше буферов базы данных указывают, что для этих структур требуется больше памяти.
Факт
Наоборот, высокие значения (100 MB и выше) статистики свободной памяти для различных пулов в области разделяемого пула указывают на проблему выделения чрезмерного пространства для этих пулов. То же самое может относиться и к различным пулам буферов в кеше буферов базы данных. Низкие значения (несколько мегабайтов) могут указывать на примерно правильную установку размеров.
SGA всегда следует конфигурировать для размещения в одном сегменте разделяемой памяти, чтобы добиться максимальной производительности базы данных. Установка параметров ОС для размещения всей SGA в одном сегменте разделяемой памяти должно быть первоочередной задачей при разрешении проблем производительности базы данных.
Факт
Многие АБД в среде UNIX стараются разместить всю SGA в одном сегменте разделяемой памяти, надеясь за счет этого повысить производительность. Однако в большинстве сред отсутствует измеримая деградация производительности, даже если SGA размещена в нескольких сегментах разделяемой памяти. Процессы Oracle почти всегда имеют одинаковое время доступа к различным компонентам SGA независимо от количества сегментов разделяемой памяти, содержащих SGA. Проведенное тестирование производительности многих UNIX-систем разоблачает этот миф. Одни и те же компоненты приложений запускались до и после модификации ядра UNIX, и при этом не было обнаружено абсолютно никакого измеримого повышения или снижения производительности. Практический результат: на большинстве UNIX-платформ в действительности не имеет никакого значения, используется ли для SGA один или более сегментов разделяемой памяти.
Говоря об этом, вы должны быть осведомлены о паре исключений. На некоторых аппаратных платформах, поддерживающих конфигурации доступа к слабосвязанной неоднородной памяти (NUMA, non-uniform memory access), взаимодействие различных узлов системы выполняется через "соединения" (interconnect) или коммутаторы, при этом сегменты памяти могут быть созданы в различных узлах, - в результате Oracle и ОС для чтения различных частей SGA будут постоянно использовать эти коммутаторы. Такое использование может создать проблемы производительности, если объем передаваемых данных превышает пропускную способность этих коммутаторов. Кроме того, если вы в среде Sun Solaris используете Intimate Shared Memory (ISM), параметр SHMMAX следует устанавливать так, чтобы вся SGA размещалась в одном сегменте разделяемой памяти.
Если коэффициент попаданий в кеш буферов базы данных Oracle (КПК) превышает 90% (например, 99.999), производительность Oracle будет самой лучшей.
Факт
Это не так. Очень высокий коэффициент попаданий в кеш буферов базы данных может вводить в заблуждение. Часто используемые операторы SQL, которые выполняют просмотры индексов или полные просмотры таблиц одной и той же маленькой таблицы, а также коррелированные подзапросы (снова и снова читающие один и тот же набор блоков) могут искусственно повышать КПК до очень высоких уровней. Это может заставить вас поверить, что Oracle работает на пике производительности, тогда как на самом деле "собираются тучи". Если сеансы пользователей ожидают доступа к свободным блокам в кеше буферов базы данных или ожидают cache buffers chains latch (защелка цепочек кеша буферов) или cache buffers lru chain latch (защелка цепочки LRU-списка кеша буферов) [Millsap, 5], то не имеет никакого значения, что КПК равен 99.999. А вы должны понимать, что имеете проблему производительности. Я пойду дальше и скажу, что в большинстве "реальных систем" высокий КПК (90% и выше) обычно указывает на в высшей степени неэффективный SQL в приложениях. Для того, чтобы добиться приемлемого времени реакции, вы должны локализовать и оптимизировать эти SQL-операторы, нарушающие нормальную работу. Существует много подходов к настройке Oracle, в которых коэффициенты не используются совсем. Краеугольным камнем настройки систем на базе Oracle являются события ожидания, а не коэффициенты.
Коэффициент попаданий в кеш буферов базы данных (КПК), не превышающий 60%, указывает на плохую производительность базы данных Oracle.
Факт
И снова это совсем неправильно. Каков характер изменений в базе данных, таким будет и значение КПК. Например, во время нормального рабочего дня высокое значение КПК может поддерживаться транзакционными операциями, если в этих транзакциях регулярно осуществляется доступ к одному и тому же набору блоков. Но ночью, когда запускаются пакетные задания, обрабатывающие большой диапазон блоков данных, можно ожидать снижения КПК. Имейте в виду: блоки, которые потребовались днем, могут по-прежнему находиться в кеше, при этом запрашивается много новых блоков. Это приведет к снижению КПК. Если сеансы пользователей не ждут завершения ввода-вывода, не ждут свободных блоков, не испытывают проблем с производительностью, 60% КПК будет безусловно подходящим значением. Настройка - это то, что обеспечивает уровень производительности, который требуется пользователям для выполнения их работы, а не достижение произвольно выбранных значений коэффициентов. Совершенно абсурдно даже предполагать, что снижение КПК может рассматриваться как причина деградации производительности. И вновь, не растрачивайте свое время на проверку не относящихся к делу чисел.
Низкие статистики (не превышающие 99%) коэффициентов попаданий в библиотечный кеш или кеш словаря данных являются симптомом плохой производительности при работе с областью разделяемого пула, которая может быть повышена путем увеличения размера разделяемого пула.
Факт
Хорошо, давайте сначала зададим вопрос: что заставляет вас думать, что с разделяемым пулом есть проблемы? "Откопали" вы какие-либо события ожидания, связанные с разделяемым пулом? Обнаружили вы какую-либо конкуренцию за различные защелки, используемые в библиотечном кеше? Просто увеличение размера разделяемого пула произвольным образом вряд ли разрешит какие-либо проблемы производительности, связанные с этим пулом. Заметим, что позитивный эффект большего размера разделяемого пула (сверх определенного размера, зависящего от приложений) будет заметен только на протяжении короткого периода после запуска экземпляра. Кроме этого, чем больше памяти вы выделите области разделяемого пула, тем больше вероятность увеличения потребления времени ЦП, затрачиваемого на управление этим кешем, и больше шансов, что процессы будут удерживать защелки более продолжительное время, создавая таким образом конкуренцию за доступ к разделяемому пулу. Если вы продолжите действовать в такой же манере, выделяя все больше и больше памяти, вы, в конечном счете, существенно подорвете производительность.
Как почти со всеми проблемами производительности, только выделение дополнительных ресурсов (в данном случае, дополнительной памяти для области разделяемого пула) дает немногим больше, чем откладывание решения проблемы на будущее. А в некоторых случаях добавление памяти может создать другие проблемы производительности, которые вы не предвидели. Вы должны понимать, что большинство проблем разделяемого пула связано с характером доступа к этой структуре памяти, кроме того, с отсутствием осмысленного и упреждающего сопровождения пространства в этой структуре.
Помимо всего прочего, важное значение имеют: устранение полных разборов (hard parses) - в Oracle 8.1.7 и выше рекомендуется устанавливать CURSOR_SHARING = FORCE, уменьшение количества частичных разборов (soft parses) соответствующей установкой параметра SESSION_CACHED_CURSORS, разделение больших и маленьких операторов SQL с использованием резервной области разделяемого пула, идентификация часто используемого хранимого SQL (пакеты, процедуры, функции). В равной степени критично выделение адекватного пространства для области большого пула, используемой для обеспечения работы Recovery Manager (RMAN), Parallel Query, Java и Oracle multithreaded server (MTS). В число лучших методов организации сопровождения этой структуры памяти входят: увеличение повторного использования операторов SQL, уменьшение количества полных разборов, уменьшение количества частичных разборов (путем кеширования курсоров в сеансах), сопровождение пространства различных пулов (shared (разделяемого), large (большого), java). Все это поможет снизить конкуренцию и обеспечить соответствующую производительность.