введение

Настоящий перевод проводился автором в процессе изучения СУБД Oracle, когда чтение англоязычной документации по такой многодетальной теме выявило ухудшение понимания - надо еще отвлекаться на перевод. Поэтому в итоге было принято решение провести перевод - конспект отдельных глав руководства по тюнингу Oracle 11g, представлявших интерес. Главы переводились в разное время, и планируется перевод еще нескольких глав. Однако нужно обратить внимание читателя на тот момент, что данный материалне есть мой художественный перевод, но только лишь заметки наиболее важных для автора моментов

Выложен он как обычно по аналогии с моим же переводом руководства по тюнингу 9 версии - без какой либо ответственности и с надежной, что кому нибудь оно поможет. Использовать этот перевод можно, но вся ответственность не на авторе, но на использующем. Ну и не забываем при использовании указывать ссылку на настоящий материал и моё имя - как автора перевода. Где еще можно получить информацию - в родной документации от Oracle, правда на английском

Что нового - новые представления AWR и отчйты AWR, аннотации таблиц, расширение RESULT_CACHE аннотаций для функций PL/SQL, хинты определяющие параллелизм, параллельное исполнение в памяти, хинты обновления (на конфликт UPDATE/INSERT), расширение SQL Tuning Advisor, миграния с хранимых кракасов на baselines

Обзор

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

Выделяют два вида тюнинга - локализация узких мест, подразумевающая, что тюнинг должен проводиться не сам по себе, но решать конкретные выявленные проблемы с производительность и проактивный мониторинг, подразумевающий периодический сбор и аналитику статистик. Настройка подразумевает изменения в приложении, СУБД или конфигурации железа, и тюнинг приложения является наиболее эффективным. Для разработки эффективных запросов необходимо понимать особенности работы движка, а также оптимизатора, поирающегося на собираемую статистику. Oracle предоставляет различные инструменты для сбора и анализа статистик, а именно: AWR - собирает статистики, ADDM анализирует собранное AWR, SQL Tuning Advisor обеспечивает тюнинг запроса без изменения текста, SQL Access Advisor обеспечивает расширения индексов, материализованных представлений, их журналов, "End to End Application tracing" обеспечивает сквозной трэйсинг для пользователя, сервиса или компонента приложения, также поддерживаются механизмы генерации оповещений, дополнительные аналитические представления и сводную страницу производительности для GRID Control. Кроме того поддерживаются V$ - представления

2. Дизайн и разработка для производительности

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

2.4 Системная архитектура является предметом рассмотрения и делится на аппаратную и программную составляющие. В аппаратной внимание обращают на CPU, память, подсистему ввод/вывода и сеть. В программной важна реализация пользовательских интерфейсов, бизнес - логики, управление запросами пользователей и размещением ресурсов, управление данными и транзакциями. Системная архитектура должна разрабатываться и настраиваться под специфику конкретного проекта

2.5 Принципы построения приложений - другой аспект. Считаем, что непонятная таблица - плохая таблица, долгий неоптимизируемый запрос - плохой запрос, транзакция или дизайн таблицы, повторяющееся индексирование по одному полю - плохое индескирование, запрос без учёта быстрого ответа пользователю - плохой пользовательский интерфейс или дизайн транзакций, если запрос к базе передаётся через множество слоев приложениё - это плохой метод разработки приложений. Важно моделировать данные. Дизайн таблиц - компромисс между производительностью, требующей нормализации и простоты определений, и гибкостью, местами требующей денормализации

Дизайн индексов также процесс интерактивный, однако можно сразу задать индексы для ограничений, например первичных ключай, а также для известных поисковых паттернов. Важными приёмами является добавление колонок и индекс (чтобы исключить обращения к таблицам, если все поля есть в индексе) или использование IOT таблиц - что не всегда улучшает I/O, использованиеразличных типов индексов b-tree основной тип, хорош для первичных ключей и высокоселективных запросов, применяется как индекс соединения, и используется базой, когда нужны сортированные данные поля (полей); bitmap хорош для низкой кардинальности, когда технология компрессии отдаёт много ROWID с минимальным I/O, на низкокардинальных запросах с AND или OR выигрыш велик, как и на COUNT(); основанные на функции индексы имеют некоторые ограничения и эффективны на запросах с применением функций; партицированный индекс мнижает I/O при работе с партициями, индекс реверсированного ключа более эффективен в операциях INSERT, но не может быть использован для сканирования по диапазону. Важно учитывать, что использование индекса грузит систему при вставках и изменениях, например вставка в таблицу с тремя индексами в 10 (десять) раз медленнее, чем в таблицу без индексов, так что для часто модифицируемых таблиц наличие индексов должно быть оправдано

Использование последовательностей или таймстампов для генерации ключей может создать нагрузку в виде монотонно растущего ключа и, как следствие - растущего вправо индекса. Для обхода можно генерировать ключи за пределами полного диапазона индекса, для чего можно использовать реверсивный индекс и циклический префикс к последовательности. Последовательность полей создают по алгоритму более селективные поля первыми, особо востребованно для первичных ключей и высокоселективных запросов, или же по алгоритму минимизации I/O при кластеризации и сортировках, когда вначале идут наименее селективные поля или порядок полей соответствует порядку сортировки окончательного набора данных. При использовании представлений (views) нужно учитывать, что они могут порождать неоптимальные ресурсоёмкие запросы, и, хотя они упрощают разработку, часто можно обойтись без них, учитывая, что они тяжелы для оптимизации. Запросы при разработке должны учитывать: частые подключения ресурсоёмки, нужно стремиться к использованию мягких запросов, для чего показано использование связанных переменнных и вредно использование вместо них строковых литералов

2.6 Моделирование, тестирование и реализация запланированной рабочей нагрузки

2.7 При размещении нового приложения стратегией может быть как разовый переход, так и постепенный перевод пользователей, что позволяет отследить масштабируемость. Для новой базы рекомендуется выставлять повыше лимиты (MAXFILE и т.п.), минимально менять параметры инициализации по умолчанию, истользовать LMT и UNDO TBS, оптимизированные запросы и биндинги, оптимизацию middleware, сбор baseline статистик и отработку первых узких мест

3

Методология Oracle рекомендует вносить изменения только после подтверждения узких мест, тюнинг итеративен и после отработки одного узкого места может вылезти другое. Проблемы производительности проявляются в медленном ответе пользователю или времени отработки, и их можно локализовать в модулях. При тюнинге нужно чётко локализовать проблему - без абстракций - и чётко - без абстракций - сформулировать цель тюнинга. В разделе 3.1.3 руководства по тюнингу описан десяток наиболее частых засад - управление соединениями на каждый запрос, отсутствие оптимизации под связанные переменнные и повторяемые запросы, неэффективные SQL, нетиповые инициализационные параметры, неоптимальная архитектура ввода/вывода, неадекватная настройка redo, неиспользование LMT ASSM, продолжительный full scan от неоптимальных SQL, транзакций или отсутствия индексов, большой объём рекурсивных запросов под SYS сигнализирует о проблемах (выделение экстентов и т.п.), под другими пользователями это не проблема, ошибки размещения и миграции приложений

Возникающие мгновенные проблемы с производительностью требуют немедленного ответа. Важно понять, что изменилось в системе перед возникновением проблемы, проблема полной отработки (времени отработки) или времени отклика, также рекомендуют использовать автотюнинг и вычислять тяжёлые сессии и запросы. Кроме того рекомендуется анализировать статистики утилизации ОС, и события ожидания экземпляра, стабилизировать среду перезагрузкой или остановкой заданий

4

Рекомендуется изначально проводить ряд мероприятий, а именно выставить нужные параметры (в т.ч. памяти), выставить UNDO в автомат и REDO так, чтобы журнал переключался раз в 20 минут. Также рекомендуется использовать несколько LMT табличных пространств, разделяемых по задачам, а также ExtManLocal временные табличные пространства. Относительно таблиц рекомендуется использовать ASSM и компрессию. Важно освобождать место, используя SHRINK и COMPACT. Также можно использовать разделяемые сервера

5. AWR

Для тюнинга важен сбор статистик, доступных в виде кумулятивных статистик. При рестарте они сбрасываются, но есть пакет, собирающий статистики и дельты AWR. Кроме того AWR собирает метрики, которые есть скорость роста кумулятивных статистик по разным основам, например времени, числу пользовательских вызовов или транзакций. Кроме того Оракл собирает примеры (sampled data). Удобным инструментом являются эталоны метрик (baselines), собираемый обычно в пик нагрузки и сравниваемый далее с проблемным временем, причём поддерживается сбор данных baselines по указанным параметрам (двум снапшотам), и последующее сравнение сравнение по двум диапазонам. Рекомендованной утилитой доступа является OEM

Важными аспектами являются статистики событий ожидания, статистика моделей времени (насколько я понял, представления V$SYS_TIME_MODEL раскладывают параллельное время сессий в общую сумму, что позволяет оценить продолжительность работы с этого аспекта), ASH (выборка каждую секунду в циклический буфер SGA, ждущие нефиктивные события сессиям учитываются наравне с активными и попадают в выборку, являющуюся набором строк, причём V$ACTIVE_SESSION_HISTORY отдаёт по одной строке для каждой сессии в выборке. Чем выше активность экземпляра (как понимаю в сессиях), тем меньшее время будет помещаться в циклический буфер. В виде снапшота эти данные сохраняются на диск, для экономии лишь порция. Выборки могут сравниваться $ACTIVE_SESSION_HISTORY с DBA_HIST_ACTIVE_SESS_HISTORY. Возможно построение отчетов по ASH, а также сбор в паряти для экземпляров AMS и Active Data Guard), и статистики экземпляра и сессий.

Интерпретация статистик включает контроль коэффициентов попадания (кеша буферов, защёлок, мягких разборов), учитиываемых только как индикаторы, анализ событий ожидания с учётом TIMED_STATISTICS и без него, сравнение с другими параметрами, как статистики ОС и адекватность производительности плшатформы, контроль вычисляемые статистики и показатели, например нормализованные по транзакциям

AWR собирает, обрабатывает и анализирует статистики на предмет выявления проблем и для целей автотюнинга. Данные доступны в памяти и в базе через представления и отчёты. Доступна объектная статистика, в т.ч. по сегментам, статистика тайм модели, доступная из представлений V$SYS_TIME_MODEL и V$SESS_TIME_MODEL, системная и сессионная статистика, наиболее тяжёлые по общему времени и CPU time, статистика активных сессий. Сбор статистик AWR включен по умолчанию, если значение параметра STATISTICS_LEVEL выставлено в TYPICAL или ALL. Выставлять этот параметр в BASIC не рекомендуется, ибо он отключает многие фича, в т.ч. сбор показателей статистики в памяти

Снапшоты собирают данные для сравнения производительности механизмом ADDM, по умолчанию один раз в час и хранятся по умолчанию 8 (восемь) дней. AWR выявляет наиболее тяжёлые запросы (сравонением с прежними снапшотами), что уменьшает размер сохраняемой информации. Baselines содержат данные о производительности, сравнение с которыми других периодов позволяет выявлять проблемы с производительностью. Baseline доступен в вариантах фиксированных данных, пдавающего окна и шаблонов. Данные baselines исключаются из автоматических задач AWR по очистке и устареваниям

Фиксированные baselines соотносятся с непрерывным промежутком времени и могут быть сравнены в будущем с другим периодом для выявления проблем. Для плавающего окна выборкой являются все данные в AWR retention period. Эту особенность можно использовать адаптивные пороги метрик, вычисляя их из данных AWR. По умолчанию система формирует плавающее окно длиной 8 дней, но лучше увеличивать retention period для более аккуратного вычисления порогов. Причём размер плавающего окна можно выставить в период меньше или равным периоду хранения данных в AWR. С помощью шаблонов можно создавать baselines из данных в будущем, причём шаблоны есть разовые и периодические. Разовые шаблоны применимы, если известен период времени, например тестирование в будущие выходные. Периодические шаблоны можно использовать для сбора и удаления baselines в известные будущие промежутки на постоянной основе, например собирать в каждый понедельник месяца, и удалять данные старше, например, месяца

Адаптивные пороги включаются для мониторинга и выявления проблем с производительностью при минимизации нагрузки, они могут автоматически выставить пороги предупреждений и критики, основываясь на собранных baselines данных. Статистика для вычисления порогов пересчитывается еженедельно для выявления новых значений, и, кроме этого вычислять значения различных порогов в различное время дня и недели, основываясь на данных нагрузки, например OLTP нагрузка днём и пакетная ночью, когда потранзакционные метрики могут использоваться для дневной нагрузки, но на долгих пакетных заданиях это приведёт к фальшивым сообщениям о превышении порогов. Адаптивные пороги позволяют вычислить и установить различные пороги для различного времени (временные группы до Oracle 11.2 требуют ручной активации администратором)

Поддерживается два типа адаптивных порогов. Процент от максимума в плавающем окне и уровень важности (учитывающей сколько необычных значений, превышающих порог, должно быть в выборке High,Very High,Severe,Extreme). Порог типа процента от максимума в системах с пиковой нагрузкой для выявления превышения от прежних пиков (например скорость роста redo logs). Порог типа уровня важности используется для метрик, значения которых с штатно функционирующей системе стабильны, но при проблемах с производительностью может меняться по разному

Требования к месту в AWR зависят от количества активных сессий, интервала сбора и длительности окна хранения данных. По умолчанию снапшоты собираются каждый час и хранятся 8 (восемь) дней, что для 10 активных сессий потребует 200-300 Мбайт. При желании уменьшить требуемую ёмкость можно увелисить интервалы сбора или именьшить окно хранения, однако внутренние опции СУБД могут начать работать менее аккрратно. Речь от ADDM,SQL tuning advisor,Undo advisor,Segment advisor. Рекомендуется выбирать окно хранения, охватывающее хотя бы один цикл рабочей нагрузки, например неделю и ли месяц. В случае выключения автоматического сбора статистики выставлением интервала сбора в 0 (ноль) механизмы самонастройки СУБД работать не смогут, и создавать снапшоты вручную также будет невозможно, так что Oracle строго рекомендует не отключать автоматический сбор статистик

Управление AWR обеспечивает пакет DBMS_WORKLOAD_REPOSITORY. NULL в параметре сохраняет старые значения, при опускании DBID используется ID локальной базы, а текущие настройки можно увидеть в представлени DBA_HIST_WR_CONTROL. Этот же пакет управляет baselines, но основным интерфейсом является OEM.

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT () ;
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
     (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047) ;
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
     ( retention => 43200, interval => 30, topnsql => 100, dbid => 3310949047) ;
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE
     (start_snap_id => 270, end_snap_id => 280, baseline_name => 'peak baseline',
      dbid => 3310949047, expiration => 30);
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE 
     (baseline_name => 'peak baseline', cascade => FALSE, dbid => 3310949047);
DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE 
     ( old_baseline_name => 'peak baseline', new_baseline_name => 'peak mondays',
     dbid => 3310949047);
DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRIC 
     ( baseline_name => 'peak baseline', dbid => 3310949047, instance_num => '1');
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE 
     ( window_size => 30, dbid => 3310949047);
-- одиночный и периодический baselines шаблоны
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE 
     ( start_time => '2009-04-02 17:00:00 PST', end_time => '2009-04-02 20:00:00 PST',
       baseline_name => 'baseline_090402', template_name => 'template_090402', 
       expiration => 30, dbid => 3310949047);
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE 
     ( day_of_week => 'monday', hour_in_day => 17, duration => 3, expiration => 30,
       start_time => '2009-04-02 17:00:00 PST', end_time => '2009-12-31 20:00:00 PST',
       baseline_name_prefix => 'baseline_2009_mondays_',
       template_name => 'template_2009_mondays', dbid => 3310949047);
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE 
     ( template_name => 'template_2009_mondays', dbid => 3310949047);

Скрипты ?/rdbms/admin/awrextr.sql и ?/rdbms/admin/awrload.sql производят выгрузку и загрузку даных AWR. Данные AWR обычно используются через OMS или отчёты, но можно обращаться к представлениям напрямую. ASH - V$ACTIVE_SESSION_HISTORY, V$METRIC отражает метрики, скомпонованные в группы, доступные через V$METRICGROUP, DBA_HIST представления

• DBA_HIST_ACTIVE_SESS_HISTORY отражает хранящиеся в памяти значения ASH
• DBA_HIST_BASELINE отражает собранные системой baselines, в т.ч. время и типы
• DBA_HIST_BASELINE_DETAILS отражает детали baseline
• DBA_HIST_BASELINE_TEMPLATE отражает детали шаблонов baseline
• DBA_HIST_DATABASE_INSTANCE отражает информацию об окружении БД
• DBA_HIST_DB_CACHE_ADVICE отражает информацию о количестве физических чтений с учётом размера кеша
• DBA_HIST_DISPATCHER отражает информацию по каждому процессу диспетчера на момент среза
• DBA_HIST_DYN_REMASTER_STATS отражает информацию динамических процессов ремастеринга
• DBA_HIST_IOSTAT_DETAIL отражает статистику ввода/вывода по файлам и типам
• DBA_HIST_SHARED_SERVER_SUMMARY отражает информацию о разделяемых серверах, активности, общих очередях и диспетчерезированных очередях
• DBA_HIST_SNAPSHOT отражает информацию о снапшотах в системе
• DBA_HIST_SQL_PLAN отражает информацию по планам выполнения SQL
• DBA_HIST_WR_CONTROL отражает установки AWR

Отчёты AWR запускаются следующими скриптами ?/rdbms/admin/awrrpt.sql (отчёт по двум запросам), ?/rdbms/admin/awrgrpt.sql (RAC), ?/rdbms/admin/awrrpti.sql (указанный экземпляр), ?/rdbms/admin/awrgrpti.sql (RAC + экземпляр), ?/rdbms/admin/awrsqrpt.sql (отчёт SQL), ?/rdbms/admin/awrsqrpi.sql (отчёт SQL для экземпляра). Кроме того AWR позволяет сравнить два диапазона ?/rdbms/admin/awrddrpt.sql (отчёт по одиночному экземпляру), ?/rdbms/admin/awrgdrpt.sql (сравнение периодов в RAC), ?/rdbms/admin/awrddrpi.sql (сравнение по указанному экземпляру), ?/rdbms/admin/awrgdrpi.sql (сравнение для RAC + экземпляр). Следующей возможностью является построение отчётов ASH /rdbms/admin/ashrpt.sql (общий), /rdbms/admin/ashrpti.sql (для указанного экземпляра, он же для RAC)

6. ADDM

ADDM автоматически анализирует данные, собираемые AWR, выявляет проблемы, формирует рекомендации и выявляет беспроблемные области. Важно обеспечить итеративность процесса тюнинга. Для RAC ADDM реализует режимы анализа отдельного экземпляра, всей БД (database mode, учитывающий все экземпляры, рекомендуется для RAC) или нескольких экземпляров. Анализ производится при каждом съёме снапшотов по двум последним, и доступен через OEM или SQLplus. Анализируются падения производительности через выборку симптомов и поиску основных классов причин проблем, целью является снижение метрики DB time, являющейся суммой ожидающих и непростаивающих процессов, и отражается в представлениях V$SESS_TIME_MODEL и V$SYS_TIME_MODEL. Классами проблем являются - утилизация CPU, неадекватное конфигурирование памяти, подсистемы ввода/вывода, жручие SQL запросы, а также Pl/SQL и Java процедуры, типичные для RAC проблемы - горячие блоки в глобальном кэше и пропускная способность шины, засады приложений - частые соединения, нагружающий парсинг и блокировки приложением, особенности конфигурации БД - некорректный размер оперативных журналов, архивирование, нагружающие контрольные точки, неоптимальные параметры конфигурации, проблемы конкуренции - buffer busy, горячие объекты и запросы SQL

Отчет ADDM представлен блоками (sets finding) - (блок проблем) описание основного класса проблем БД, блок симптомов, блок информации содержит вспомогательные непроблемные данные, блок предупреждений (например о потере данных AWR). Выдаваемые по результатам рекомендации могут относиться к изменениям аппаратной конфигураци, модификации инициализационных параметров БД, модификациям схемы (использование партицирования или ASSM), модификациям приложения (например использование кэширования последовательностей или связанных переменных), использование дополнительных прогностических представлений. Списки рекомендаций могут содержать несколько вариантов оптимизации с ожидаемой выгодой

ADDM включен по умолчанию и регулируется параметрами STATISTICS_LEVEL (TYPICAL|ALL) и CONTROL_MANAGEMENT_PACK_ACCESS = (NONE|DIAGNOSTIC|DIAGNOSTIC+TUNING). Для проведения аналитики ADDM использует параметр DBIO_EXPECTED (default 10000 microsecunds), который отражает среднюю скорость чтения одиночного блока данных, и который можно изменить командой EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 7000)

По умолчанию ADDM анализирует каждый снапшот AWR в сравнении с предыдущим. Если нужны кастомизированные работы, рекомендуется использовать OMS, при невозможности - пакет DBMS_ADDM (требует привелегии ADVISOR). Для RAC поддерживается аналитика экземпляров, но только запущенных ранее анализируемых снапшотов и без ошибок при сборе снапшотов. Сами команды таковы: DBMS_ADDM.ANALYZE_DB ( task_name, begin_snapshot, end_snapshot[, db_id]) ; DBMS_ADDM.ANALYZE_INST ( task_name, begin_snapshot, end_snapshot[, instance_number][, db_id]); DBMS_ADDM.ANALYZE_PARTIAL ( task_name, instance_numbers, begin_snapshot, end_snapshot[, db_id]) ; где instance_numbers = '1,4,8' SET LONG 1000000 PAGESIZE 0; SELECT DBMS_ADDM.GET_REPORT(task_name) FROM DUAL;

Кроме прочего результаты работы ADDM можно получить из административных представлений DBA_ADVISOR_FINDINGS (все прогносты - адвизоры), DBA_ADDM_FINDINGS (подмножество ADDM), DBA_ADVISOR_FINDING_NAMES (список зарегистрированных имён), DBA_ADVISOR_RECOMMENDATIONS (рекомендации, нужно сортировать по полю RANK и сверяться с полем BENEFITS), DBA_ADVISOR_TASKS (базовая информация по задачам)

7. Память

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

Основными кэшами являются - разделяемый пул, большой пул, пул буферов, журнальный буффер, пул жабы, streams пул, и частные пулы процессов, например использование памяти при сортировках и соединениях. Оракт строго рекомендует использовать автоматическое управление памятью (AMM), балансирующее SGA и PGA, и включаемое параметрами MEMORY_TARGET и MEMORY_MAX_TARGET. Если управлять памятью нужно вам, рекомендуется использовать прогносты (адвизоры) памяти. Режим автоматического управлния разделяемой памятью (ASMM) включается параметром SGA_TARGET с выставлением STATISTICS_LEVEL в TYPICAL|ALL, и управляет кешем буферов (default), пулами жабы и стримсов, разделяемым пулом.

Это изменяемый параметр, он не может быть больше SGA_MAX_SIZE, и при его выборе рекомендуется ориентироваться на V$SGA_TARGET_ADVICE. Если динамически отключить ASMM, выставив параметр SGA_TARGET в 0, будут использованы текущие раммеры пулов, которые можно изменить параметрами DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, STREAMS_POOL_SIZE. Журнальный буффер, прочие буферные кеши (keep,recycled,недефолтный размер блока), fixed SGA и другие внутренние выделения памяти не охватываются ASMM и могут быть выставлены параметрами DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, LOG_BUFFER. Память, выделенная для этих "неохватываемых ASMM" пулов вычитается из общей доступной для SGA при расчётах, проводимых ASMM

Если AMM или ASMM не используются, вы можете выбирать размер динамически для кеша буферов, разделяемого пула, большого пула и памяти процессов параметрами DB_CACHE_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, LOG_BUFFER, SHARED_POOL_SIZE. Причём журнальный буффер и private-process memory статичны после старта экземпляра. Память для большого, жаба, разделяемого пулов и кэша буферов выделяется в гранулах (4Мб при размере SGA меньше 1Гб, иначе 16Мб). Размер гранулы не меняется после старта экземпляра, может быть подсмотрен в V$SGA_DYNAMIC_COMPONENTS и используется для всех динамических компонент SGA. Максимально доступная экземпляру память ограничена параметром SGA_MAX_SIZE, и если его выставить больше суммы кэшей, в дальнейшем можно будет увеличить какой либо кеш при необходимости

Следующие представления отдают информацию о памяти: - V$MEMORY_CURRENT_RESIZE_OPS операции расширения и сжатия компонент памяти (авто и ручные) "в процессе" - V$MEMORY_DYNAMIC_COMPONENTS информация о текущем размере всех изменяемых динамически компонентах памяти, включая общий размер SGA и PGA - V$MEMORY_RESIZE_OPS информация о последних 800 заверщённых операциях ресайзинга всей памяти, авто и ручные, операции "в процессе" не отражаются - V$MEMORY_TARGET_ADVICE рекомендации по установке параметра MEMORY_TARGET - V$SGA_CURRENT_RESIZE_OPS операции расширения и сжатия компонент SGA "в процессе" - V$SGA_RESIZE_OPS информация о последних 800 заверщённых операциях ресайзинга SGA, операции "в процессе" не отражаются - V$SGA_DYNAMIC_COMPONENTS суммарная информация о ресайзинге компонент SGA после старта экземпляра - V$SGA_DYNAMIC_FREE_MEMORY доступная для будущих операций ресайзинга память

После настройки памяти размер кэшей должен соответствовать потребностям приложений, но тюнинг использования кэшей приложениями (оптимизация на уровне приложений) может существенно снизить требования к памяти и связанным ресурсам (защёлкам, CPU и подсистеме I/O). Кэш должен максимально использовать ресурсы ОС и СУБД, а выделение памяти в структурах Oracle должно максимально соответствовать потребностям приложений. Добавление новых приложений может потребовать переконфигурирования памяти, что особенно относится к использованию java. Для большинства ОС необходимо - уменьшить пэйджинг, разместить структуры Оракла в основной памяти, предоставить адекватную память отдельным пользователям (серверным процессам и прочим приложениям). Можно использовать параметр LOCK_SGA, однако при этом MEMORY_TAREGT и MEMORY_MAX_TARGET при этом использоваться не будут. Посмотреть текущее распределение можно командой SHOW SGA в sqlplus. Также нужно понимать, что процесс конфигурирования памяти итеративен

Кэш буферов используется для многих типов операций для сохранения прочтённых с диска блоков. Для части операций, например сортировок или параллельного выполнения, не используется. Для эффективного использования полезно оптимизировать запросы, избегая использования ненужных ресурсов, первыми кандидатами на оптимизацию являются часто используемые запросы и запросы с большой величиной статистики чтения буферов (логических чтений ?). Для параллельных запросов при наличии дополнительной памяти можно сказать СУБД привлекать кэш буферов вместо прямых чтений в PGA. Для нового экземпляра нельзя сразу указать оптимальный размер буферного кэша, обычно выставляется подходящий и на типичной нагрузке собирается статистика V$DB_CACHE_ADVICE (наполняется при выставленном в ON DB_CACHE_ADVICE и влечёт небольшую дополнительную нагрузку на CPU) и коэффициентов попадания для тюнинга

После установки в ON параметра DB_CACHE_ADVICE и некоторого времени на нормализацию типичной нагрузки можно поинтересоваться, какова будет нагрузка на I/O при изменении размера кэша буферов и коэффициент попадания

COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads        FORMAT 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
       estd_physical_reads
       FROM V$DB_CACHE_ADVICE
       WHERE ( name = 'DEFAULT' OR name = 'KEEP' ) AND
             block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
             AND advice_status = 'ON';

SELECT NAME, VALUE FROM V$SYSSTAT
       WHERE NAME IN ('db block gets from cache', 'consistent gets from cache',
             'physical reads cache');
коэффициент попадания = 1 - (('physical reads cache') /
            ('consistent gets from cache' + 'db block gets from cache')
или
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
       1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
       FROM V$BUFFER_POOL_STATISTICS;

При низком коэффициенте попадания увеличение кэша буферов не факт что поможет, а высокий говорит, что кеша буферов достаточно, но не показывает избыточность. Повторяющееся сканирование больших таблиц или нидексов ухудшает коэффициент попадания, для таких запросов нужно убедиться в оптимальности плана и по возможности избегать повторных сканирований. По возможности избегайте повторяющихся запросов к одним нанным, используя кэширование на клиенте или промежуточном слое. Блоки БД при долгом полном сканировании таблиц перемещаются в конец списка LRU и не попадают в голову, однако устаревают они скорее, чем полученные по индексу или сканированием малэх таблиц. При анализе данных кеша низкий коэффициент попадания и валидных длительных полных просмотрах таблиц, этот момент также нужно учитывать. Короткие сканы - до 2% кэша, а долгие - больше 20% кеша. На OLTP системах повторное использование данных не практикуется - чаще всего строки используются не более одного раза, и коэффициент попадания заведомо будет низким. Часто начинают увеличивать размер кэша буферов, но это бессмысленно на долгих полных просмотрах или операциях без использования кэша буферов

В общем случае рассматривать увеличение кэша буферов нужно при низком коэффициенте попадания и проведённой оптимизации прикладной части по отказу от full scan. Сначала рекомендуется выставить V$DB_CACHE_ADVICE в ON, и, после стабилизации нагрузки, подсмотреть потимальные значения в V$DB_CACHE_ADVICE и выставить их в DB_CACHE_SIZE (DB_nK_CACHE_SIZE), это можно делать без рестарта экземпляра. При высоком коэффициенте попадания и благоприятных прогностических данных можно уменьшить размер буферного кэша. Также можно использовать дополнительные пулы, например небольшие горячие сегменты положить в KEEP (параметр DB_KEEP_CACHE_SIZE), или большие сегменты, связанные с отлаживаемыми запросами в RECYCLED (параметр DB_RECYCLE_CACHE_SIZE), чтобы меньше влиял на систему, ибо он меньше DEFAULT и обновление там производится гораздо чаще. Для размещения в том или ином пуле используется модификатор BUFFER_POOL для класса STORAGE сегмента. Для RAC каждый экземпляр можно конфигурировать с независимыми кешами буферов

Для определения объектов, использующих много буферов в кэше можно использовать запрос 
COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
       FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'
       GROUP BY o.OBJECT_NAME ORDER BY COUNT(*);

или, для вычисления процента утилизации одним сегментом

1. SELECT DATA_OBJECT_ID, OBJECT_TYPE FROM DBA_OBJECTS
          WHERE OBJECT_NAME = UPPER('segment_name');
2. SELECT COUNT(*) BUFFERS FROM V$BH WHERE OBJD = data_object_id_value; 
3. SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) FROM V$BUFFER_POOL
          GROUP BY NAME, BLOCK_SIZE HAVING SUM(BUFFERS) 0;
4. % cache used by segment_name = [buffers(Step2)/total buffers(Step3)]

Разделяемый пул имеет основными компонентами библиотечный кэш (исполнимая форма SQL и PS/SQL кода), кеш словаря и, опционально, кэш результатов. Многие кеши этого пула автоматически подстраивают свой размер, в т.ч. библиотечный и словарный. Также есть дополнительные пожиратели памяти этого кэша - например RMAN, shared server и паралельные запросы. Для таких пожирателей Оракл рекомендует конфигурировать large pool. Память в разделяемом пуле выдаётся кусками (chunk), что позволяет не париться дефрагментацией и размещать большие объекты фрагментированно. Нечасто запросы размещения напарываются на исчерпание пула, и для оптимизации таких случаев выделяется резервный пул

Библиотечный кэш хранит исполнимую форму SQL, PL/SQL и JAVA кода - прикладного кода, который Oracle пытается использовать повторно при наличии разобранного кода в библиотечном кэше, что известно как мягкий разбор или попадание в библиотечный кэш (soft parse, library cache hit). Если Oracle не может использовать кэшированный код (код отсутствует или устарел), он должен построить исполнимую форму кода заново, что известно как полный разбор или "промах" библиотечного кэша (hard parse, library cache miss). При этом мягкий разбор задействует CPU и защёлки библиотечного кэша, а для полного разбора добавляется также защёлки разделяемого пула

Наличие разобранного кода определяется автоматически. Запрос хэшируется, если хэш найден в библиотечном кеше, то проверяется посимвольно (если не выставить CURSOR_SHARING в SIMILAR|FORCE), далее сравниваются упомянаемые в тексте объекты, в первую очереь на предмет несовпадения схем, далее проверяется, что имена связанных переменных совпадают по имени, типу и длине, а в заключение сверяются окружения - например разнве цели оптимизатора не могут использовать один разделяемый код из библиотечного кеша. Благодаря повторному использованию прикладного кода можно избежать утилизации CPU, памяти и защёлок в процессе полного разбора. Однако для варехаусов рекомендуется использовать неразделяемые запросы с литералами вместо связанных переменнных, что позволит оптимизатору определить селективность и построить оптимальный план

Для OLTP есть разные стратегии. Использование разделяемых курсоров - использование связаных переменных, избегать неразделяемого динамического SQL (обычно хватает предустановленных запросов), запрет пользователям менять вид и цели оптимизатора в индивидуальных сессиях, установка политик разработки - унификация именования связанных переменных и отступов, и использование хранимых процедур. Если курсоры идентичны, но не разделяемы, узнать причину можно в представлении V$SQL_SHARED_CURSOR, например установки оптимизатора или несоответствия в связанных переменных. Применительно к логинам, если пользователи заходят под своими именами, то для уменьшения нагрузки на кеш словаря полезно квалифицировать имена объектов (явно указывать схемы), альтернативой является подключение к БД под владельцем объектов и разделение прав пользователей на среднем уровне архитектуры (middle tier). Использование пакетов PL/SQL позволяет снизить нагрузку на кеш словаря за счёт исполнения пакета владельцем, а не вызывающим. Также рекомендуется издегать DDL операций в пиковые часы на горячих сегментах. Для последовательностей рекомендуется выставлять модификатор CACHE, позволяющий снизить количество блокировок кеша словаря, что улучшает масштабируемость

В зависимости от используемых инструментов полезно контролизовать частоту вызовов разборов. Частота, с которой ваше приложение закрывает курсоры или использует их повторно для новых запросов, влияет на количество памяти сессии, и часто - на количество разборов сессией. Приложение, закрывающее курсоры или используещее их повторно требует меньше памяти, чем то, что держит курсоры открытыми. Напротив, приложению может потребоваться сделать больше разборов, задействуя ресурсы БД. Курсоры, асоциированные с SQL, не используемыми часто, могут быть закрыты или использованы с другими SQL предложениями, ибо вероятность перевыполнения (и переразбора) для таких запросов низка. Расширенный вызов разбора требуется, когда курсор с предложением, которое можно было перевыполнить, закрывается или используется для другого предложения. Если курсор оставался открытым, его можно использовать без расхода ресурсов на вызов разбора. Управление курсорами зависит от инструментария

Для OCI нужно избегать переоткрытия курсора, заменив литералы на связанные переменные и используя запросы/курсоры повторно, а также издегать использования одного SQL идентификатора для другого предложения SQL, если текущее было перезапущено с ошибкой. Для Oracle precompilers в командной строке или тексте программы устанавливаются классы прекомпилятора HOLD_CURSOR = YES, RELEASE_CURSOR= NO, MAXOPENCURSORS = desired_value. Оракл рекомендует на использовать режим ANSI, когда значения HOLD_CURSOR и RELEASE_CURSOR меняются. Для SQLJ необходимо подготовить запросы, и перевыполнять их с новыми значениями связанных переменных, сами курсоры остаются открытыми на протяжении сессии. Для JDBC избегать закрытия курсоров, которые могут быть использованы повторно, ибо литеральные значения могут быть связаны с курсором. JDBC обеспечивает кеш предложений внутри клиента (метод setStmtCacheSize()). Для FORMS возможно контролировать отдельные аспекты управления курсорами на уровне форм, триггеров и времени выполнения

Как обычно выставить оптимальный размер разделяемого пула сразу нельзя. Важно, что он критичен для OLTP гораздо больше, чем для DSS. Желательно, чтобы в разделяемый пул помещались все часто используемые объекты. Полезно контролировать следующее: V$LIBRARYCACHE (эти NAMESPACE отражают библиотечный кеш - SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER) - поля RELOADS (репарсинг, если хватает размера, показатель около 0) и INVALIDATION (например, в следствие DDL операций) также должен стремиться к 0, особенно на OLTP в часы пик. Также размер свободной памяти в разделяемом пуле в часы пик из V$SGASTAT, в оптимуме свободной памяти должно быть мало, исключая перезагрузку системы, а также - коэффициент попадания в библиотечный кеш

SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE ORDER BY NAMESPACE ; коэффициент попадания в библиотечный кеш = sum(pinhits) / sum(pins) SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool' ; Кроме того, полезно смотреть в представления V$SHARED_POOL_ADVICE, V$LIBRARY_CACHE_MEMORY, V$JAVA_POOL_ADVICE, V$JAVA_LIBRARY_CACHE_MEMORY.

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999

SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets - getmisses) /
       sum(gets)  pct_succ_gets, sum(modifications) updates
       FROM V$ROWCACHE WHERE gets 0 GROUP BY parameter ;
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE ;

Увеличение размера разделяемого пула (параметр SHARED_POOL_SIZE) увеличивает доступное для библиотечного, словарного и результирующего кешей место. Симптом V$LIBRARYCACHE.RELOADS отходит от нуля. Коэффициент НЕ - попадания в кеш словаря не должен превышать 15%. В случае, когда RELOADS около 0, и постоянно фиксируются излишки свободной памяти, её можно отдать под другое использвование, уменьшив SHARED_POOL_SIZE

В отличие от разделяемого пула в большом пуле не используется алгоритм LRU, и Oracle не пытается выяснить устаревание объектов. Большой пул стоит использовать, если привлекаются параллельные запросы, RMAN (буфера в несколько сотен килобайт) и разделяемые сервера. Если не использовать большой пул в архитектуре разделяемого сервера, количество памяти, выделенное библиотечному и словарному кэшам уменьшается, если использовать - разделяемый пул используется оптимально. Оракл рекомендует использовать большой пул (параметр LARGE_POOL_SIZE не сконфигурировано по умолчанию) для размещения специфичной для разделяемых серверов UGA в нём вместо разделяемого пула. Размер памяти UGA на одну сессию зависит от приложения, но даже при этом около 10 Кбайт будет размещено в разделяемом пуле

Для определения размера большого пула нужно вычислить размер типичной сессии, и умножить на количество сессий. КПараметр PRIVATE_SGA позволяет выставить лимит на использование SGA одной сессией. Кстати рекомендуется выставлять равными параметры SORT_AREA_SIZE и SORT_AREA_RETAINED_SIZE, тогда результаты сортировки будут сохраняться в большом пуле вместо диска. Память для сессии отражена в статистиках 'session UGA memory' и 'session UGA memory max', вот примеры запросов

SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS" FROM V$SESSTAT, V$STATNAME 
       WHERE NAME = 'session uga memory' AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC# ;
SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS" FROM V$SESSTAT, V$STATNAME 
       WHERE NAME = 'session uga memory max' AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC# ;

Если промахов в библиотечном кэше нет, то параметр CURSOR_SPACE_FOR_TIME в TRUE (по умолчанию false), при этом освобождение курсора будет запрошено только после того, как будут закрыты все связанные с курсором приложения, и проверять наличие курсора в кеше не нужно, что даёт экономию времени. Однако при недостатке места возможна истуация, когда новый курсор не загрузится, т.к. нельзя выгрузить старые, и возникает ошибка - поэтому нельзя включать при промахах библиотечного кеша. Если памяти для пользователей не хватает. это также предотвращает возможность выгрузки SQL private area, связанных с курсорами, что в результате приводит к исчерпанию памяти и выводу ошибки

Если сессия периодически инициирует разбор определённых, одних и тех же запросов, это может влиять на производительность. Такие курсоры могут быть закрыты и после использованы повторно, и их можно сохранять в кеше курсоров. Это применимо, например, к FORMS, когда переключение от формы к форме закрывает сессионные курсоры. Движок находит запросы с количеством разборов от трёх (3), и перемещает их в сессионный кеш курсоров, и при последующих запросах находит в сессионном кеше. Включается динамическим параметром SESSION_CACHED_CURSORS, отражающих количество курсоров, вытесняемых по алгоритму LRU. Проверить достаточность можно сессионной статистикой session cursor cache hits, и сравнением с общим количеством "total parse call" сессии

Резервный пул. Хотя движок бьёт большие запросы к памяти на чунки, в некоторых случаях может возникнуть запрос на выделение непрерывного чунка, например размером 5Кбайт. Движок выделяет небольшую часть памяти под резервный пул (минимальное выделение в резервном пуле по умолчанию 4400 байт). Если памяти в разделяемом пуле не хватает, движок ищет, что освободить, что приводит к расходу защёлок и ухудшению ситуации с выделением памяти конкурентными запросами. Потому резервируется небольшая часть разделяемого пула (резервный пул), которая позволяет выделять большие куски памяти более эффективно, по умолчанию размер небольшой (меняется параметром SHARED_POOL_RESERVED_SIZE). Запрос на размещение большого чунка выполняется - в незанятой части резервного пула, если нет места, то в резервном пуле, исли и там нет места, производится попытка освобождения памяти в нерезервированной области резервного пула и выделении там, если это невозможно, пытается освободить память и выделить её в резервированной части разделяемого пула (то есть в резерсном пуле)

По умолчанию выделяется 5% от SHARED_POOL_SIZE, то есть пул выделен. Основанием для увеличения является анализ V$SHARED_POOL_RESERVED, если памяти хватает, то поле REQUEST_MISSES должно быть равно нулю, если нет, то поле REQUEST_FAILURES также должно быть равно нулю или хотя бы не увеличиваться. Если это не так - повод увеличить SHARED_POOL_RESERVED_SIZE или размер резервного пула. Если REQUEST_MISSES равно 0 и FREE_MEMORY больше половины от SHARED_POOL_RESERVED_SIZE, можно смотреть в сторону уменьшения резервного пула. Если REQUEST_FAILURES больше нуля и растёт, нужно уменьшать SHARED_POOL_RESERVED_SIZE, а если резервный пул отключен - увеличивать размер всего разделяемого пула.

После загрузки в разделяемый пул объект не перемещается, но объекты выгружаются по алгоритму LRU,что может привести к фрагментации. Для оптимизации использования разделяемого пула можно воспользоваться пакетом DBMS_SHARED_POOL.KEEP для закрепления объектов, например часто используемых блоков PL/SQL - пакетов, часто выполняемых триггеров и последовательностей. Также для эмуляции использования связанных переменнных, когда запросы отличаются только литералами и велико время отклика по причине очень большого количества промахов библиотечного кеша, можно выставить CURSOR_SHARING = SIMILAR(без ухудшения плана)|FORCE(разделение SQL area с возможным ухудшением плана). При этом сначала будет производиться поиск текста запроса по полному совпадению, а потом - поиск похожих, с отличием только в значениях литералов. Оракл не рекомендует использовать режим FORCE для DSS архитектур, также не поддерживается star transformation для FORCE|SIMILAR, также могут не работать function-based индексы и не работают созданные для CURSOR_SHARING = EXACT каркасные планы исполнения

При использовании среднего уровня важно управлять соединениями вместо постоянного подключения и отключения от БД.

Буфер оперативных журналов выставляется параметном LOG_BUFFER и рекомендуется MAX(0.5M, (128K*селичество_CPU)). Показателем адекватности настройки является статистика SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME = 'redo buffer allocation retries' ; В случае, если показатель не равен нулю, нужно рассмотреть возможность увеличения буфера, а также оптимизации контрольных точек и отработки процессов архивирования

PGA содержит данные серверных процессов. Пример - выделяемая для каждого нового курсора обрабатывающие его серверным процессом "work area", большая часть которой для комплексных запросов может содержать данные основанных на сортировках операциях (строит сортировку в памяти для ORDER, GROUP, ROLLUP, оконных функций), соединений по hash (строит таблицу значений для левого вхождения), bitmap merge и create, буфера записи для массовых (пакетных) загрузок. В зависимости от размера рабочей области операции будут выполняться за одни проход (оптимально), с разовой разбивкой на чатсти (однопроходные операции) или же с множественными разбивками на части (многопроходные, самые низко производительные) операции. Автоматическое управление памятью PGA включено по умолчанию, и соответствует значению в 20% от SGA, однако этого может быть мало для DSS систем. Его можно отключить, выставив параметр PGA_AGGREGATE_TARGET = 0, и управлять параметрами SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE и CREATE_BITMAP_AREA_SIZE. В общем случае память рекомендуется выставлять для OLTP PGA_AGGREGATE_TARGET = (вся память * 80%) * 20%, а для DSS PGA_AGGREGATE_TARGET = (вся память * 80%) * 50%

Представлениями, отражающими утилизацию PGA, являются - V$PGASTAT, V$PROCESS, V$PROCESS_MEMORY, V$SQL_WORKAREA_HISTOGRAM, V$SQL_WORKAREA_ACTIVE, V$SQL_WORKAREA.

• aggregate PGA target parameter: текущее значение параметра PGA_AGGREGATE_TARGET (по умолчанию 20% от SGA), если этот параметр ноль 0, автоматическое управление PGA отключается
• aggregate PGA auto target: размер PGA, доступный рабочим областям а автоматическом режиме. Автоматически подстраивается от параметра PGA_AGGREGATE_TARGET и текущей нагрузки, если существенно меньше PGA_AGGREGATE_TARGET, часть PGA используется другими компонентами (например памятью PL/SQL или Java) и совсем мало остаётся для рабочих областей
• global memory bound: максимальный размер выделенной автоматом рабочей области постоянно меняется движком в соответствии с рабочей нагрузкой. Обычно уменьшается с увеличением количества рабочих областей, как правило не должно опускаться меьше 1 МБайта, иначе PGA_AGGREGATE_TARGET должно увеличить
• total PGA allocated: размер памяти PGA, выделенной экземпляром, меньше значения PGA_AGGREGATE_TARGET. Однако эта закономерность может нарушаться в короткие промежутки, когда рабочая нагрузка растёт очень быстро или PGA_AGGREGATE_TARGET имеет очень малое значение
• total freeable PGA memory: размер памяти, подлежащей освобождению
• total PGA used for auto workareas: размер автоматически распределённой рабочим областям в настоящий момент памяти, откуда можно вычислить потредление другими потребителями по формуле. PGA other = total PGA allocated - total PGA used for auto workareas
• over allocation count: статистика накапливается с момента старта экземпляра, если значение PGA_AGGREGATE_TARGET слишком мало для размещения прочих компонент PGA плюс минимальное количестов памяти для размещения рабочих областей, ибо в таком случае движок не смотрит на PGA_AGGREGATE_TARGET и выделяет больше памяти. Если этот параметр встречается, то PGA_AGGREGATE_TARGET нужно увеличить, основываясь на V$PGA_TARGET_ADVICE
• total bytes processed: обработанные байты отражают интенсивность работы с памятью со старта экземпляра. Например, на операциях сортировки
• extra bytes read/written: байт обработано в неоптимальных режимах, в идеале должно быть мало сравнительно с total bytes processed
• cache hit percentage: отражает процент оптимальных операций

SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM FROM V$PROCESS ;

Представление V$PROCESS_MEMORY отражает занятую по категориям память Java, PL/SQL, OLAP, SQL, ждущую освобождения без категорий и а также отданную в другие категории суммарно Представление V$SQL_WORKAREA_HISTOGRAM показывает оптимальные, однопроходные и многопроходные обработки для диапазонов размеров рабочих областей. Запросить сырые данные и вес каждого вида обработок можно запросами:

SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb, OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS FROM V$SQL_WORKAREA_HISTOGRAM WHERE TOTAL_EXECUTIONS != 0; SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc, multipass_count, round(multipass_count*100/total, 2) multipass_perc FROM (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total, sum(OPTIMAL_EXECUTIONS) optimal_count, sum(ONEPASS_EXECUTIONS) onepass_count, sum(MULTIPASSES_EXECUTIONS) multipass_count FROM v$sql_workarea_histogram WHERE low_optimal_size 64*1024) ;

Кроме того представление V$SQL_WORKAREA_ACTIVE отражает текущие исполняемые рабочие области, при этом небольшие сортировки до 64 Кб исключаются. В том числе отражаются сведения по использованию временных табличных пространств

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION, trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM,
       trunc(MAX_MEM_USED/1024) "MAX MEM", NUMBER_PASSES PASS,
       trunc(TEMPSEG_SIZE/1024) TSIZE
       FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2 ;

Кумулятивная статистика по всем рабочим областям для курсоров текущего SQL ID доступно в V$SQL_WORKAREA, вот примеры запросов

SELECT * FROM ( SELECT workarea_address, operation_type, policy,
       estimated_optimal_size FROM V$SQL_WORKAREA
       ORDER BY estimated_optimal_size ) WHERE ROWNUM <= 10 ;

col sql_text format A80 wrap 
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
       sum(MULTIPASSES_EXECUTIONS) mpass_cnt
       FROM V$SQL s, V$SQL_WORKAREA wa WHERE s.address = wa.address 
       GROUP BY sql_text
       HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS) > 0 ;

col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)",
       trunc(last_memory_used/1024) last_mem,
       trunc(estimated_optimal_size/1024) optimal_mem,
       trunc(estimated_onepass_size/1024) onepass_mem,
       decode(optimal_executions, null, null,
          optimal_executions||'/'||onepass_executions||'/'|| multipasses_executions)
       "O/1/M"
       FROM V$SQL_PLAN p, V$SQL_WORKAREA w 
       WHERE p.address=w.address(+) AND p.hash_value = w.hash_value(+) AND
             p.id = w.operation_id(+) AND p.address = '88BB460C' AND
             p.hash_value = 3738161960 ; 

SELECT address, hash_value FROM V$SQL WHERE sql_text LIKE '%my_pattern%';

Параметр PGA_AGGREGATE_TARGET проще тюнить, сверяясь с представлениями V$PGA_TARGET_ADVICE и V$PGA_TARGET_ADVICE_HISTOGRAMM, оба отражают ряд множителей и делителей от текущего значения (от 10 Мб до 256 Гб). Для заполнения представлений нужно включить автоматическое управление PGA и выставить STATISTICS_LEVEL = TYPICAL|ALL. Причём V$PGA_TARGET_ADVICE прогнозирует cache hit percentage и over allocation count из представления V$PGASTAT,

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE ;

Итак, при тюнинге PGA_AGGREGATE_TARGET вожно избегать over-allocation, выставив достаточное значение не меньше 375 Мб, после чего добиваются максимального cache hit percentage, если лимит памяти позволяет выставить оптимальное значение - выставить, иначе использовать что есть. После чего мониторить дельты estd_overalloc_count и estd_pga_cache_hit_percentage = дельта bytes_processed / дельта (bytes_processed + extra_bytes_rw)

Представление V$PGA_TARGET_ADVICE_HISTOGRAM позволяет оценить следствия изменения параметра PGA_AGGREGATE_TARGET, а представление V$PGA_TARGET_ADVICE_HISTOGRAM детализирует ожидания по оптимальным, однопроходным и многопроходным сортировкам

SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb, estd_optimal_executions estd_opt_cnt, estd_onepass_executions estd_onepass_cnt, estd_multipasses_executions estd_mpass_cnt FROM v$pga_target_advice_histogram WHERE pga_target_factor = 2 AND estd_total_executions != 0 ORDER BY 1 ; Статистики экземпляра и сессий показывают количество рабочих областей, выполненных с оптимальным размером памяти, однопроходным и многопроходным. SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%') ;

Параметр OLAP_PAGE_POOL_SIZE определяет максимальный размер страничного кэша для сессии OLAP. По соображениям производиетльности предпочтительно сконфигурировать небольшой страничный кеш OLAP и выставить большой размер кэша буферов параметром DB_CACHE_SIZE. Размер в 4МБайт довольно типичен, и 2 Мб для систем с ограничением памяти

Кеш результатов сервера и клиентов - область памяти в разделяемом пуле, сохраняющая результат запроса или блока запроса для повторного использования, содержит кеш результатов запросов (SQL result cache) и кеш функций PL/SQL. Выгода зависит от нюансов, показано коротким резульататм при большом количестве обрабатываемых строк. Например перезапись запросов можно использовать для построения материализованных представлений в кеше результата, а не в таблицах. При отработке запроса он ищется в кеше результата, и отдаётся оттуда вместо исполнения если есть, или исполняется и добавляется в кеш если не было. Содержимое кеша доступно в V$RESULT_CACHE_OBJECTS. Инициализационные параметры здесь RESULT_CACHE_MAX_SIZE (размер, если 0, то отключено), RESULT_CACHE_MAX_RESULT (сколько памяти в процентах можно отдать под один запрос, по умолчанию 5%, выставляется на уровне экземпляра и сессии), RESULT_CACHE_REMOTE_EXPIRATION (время устаревания с использованием объектов удалённой БД, по умолчанию 0 минут, т.е. не кешируется), RESULT_CACHE_MODE=MANUAL|FORCE (на уровне экземпляра или сессии)

Управлять памятью кеша результатов можно через установку инициализационных параметров, а также используя пакет DBMS_RESULT_CACHE. При старте в разделяемом пуле выделяется 0.25% от параметра MEMORY_TARGET, или при использовании - 0.50% от параметра SGA_TARGET, или при использовании 1% от SHARED_POOL_SIZE. Начальный размер растёт до максимального (но всегда не более 75% от разделяемого пула), причём для устаревания используется алгоритм LRU, но автоматического освобождения памяти не происходит - надо использовать процедуру DBMS_RESULT_CACHE.FLUSH (удаляет все сохранённые результаты и очищает кеш). Для RAC значения кеша выставляются отдельно для каждого узла. Пакет DBMS_RESULT_CACHE обеспечивает статистику, информацию и процедуры управления веделением памяти для кеша результатов

Oracle Call Interface result cache является частью памяти клиентского процесса и разделяется всеми сессиями процесса, Oracle рекомендует использовать его для запросов к только ситаемым или преимущественно читаемым таблицам. Клиентский кеш может быть активирован даже при выключенном серверном. OCI драйвера, например OCCI, JDBC OCI, ODP.NET поддерживают клиентский кеш. Преимущества его использования таковы - уменьшение времени ответа, более эффективное использование ресурсов БД, уменьшает стоимость памяти. Подзапросы и блоки запросов клиентским кешем результатов не кешируются. Параметры - CLIENT_RESULT_CACHE_SIZE (для включения нужно выставить в значение больше 32768, причём если он выключен, клиентский узел не может включить его, а если включен - может выключить), CLIENT_RESULT_CACHE_LAG (в миллисекундах, через сколько времени производить синхронизацию с базой), COMPATIBLE (11.0.0.0 для включения кеша вообще, и 11.2.0.0 для включения кеша представлений). Параметры клиентского конфигурационного файла замещают серверные параметры, на сервере можно выставить только LAG

Раздел 11. Оптимизатор

БД может обработать запрос массой способов, как full scan, index scan, nested loops или hash joins. оптимизатор учитывает массу факторов до выбора метода отработки запроса. Для этого он генерирует потнциальные планы с учётом путей доступа и подсказок и вычисляет стоимость на основе статистик о распределении данных и характеристик хранилищ. Стоимость является оценочным значением пропорциональным ожидаемому использованию ресурсов, и оптимизатор вычисляет стоимость вариантов доступа и порядка соединения, учитывая потребление ресурсов, в т.ч. I/O, CPU и памяти. После этого результаты сравниваются и выбирается вариант с наименьшейстоимостью

Фазы полного разбора - проверка выражений и условий, трансформация, выбор целей оптимизации, выбор вариантов доступа, выбор порядка соединения. Трансфоромация включает слияние представлений (старается исключить отдельную потимизацию представлений и действует на представления, содержащие только выборки, уточнения и соединения, и не содержит агрегации, группировки, UNION, CONNECT BY и т.п. операции, здесь важны привелегии MERGE ANY VIEW и MERGE VIEW для пользователя), вталкивание предикатов (при невозможности слить вьюху позволяет уменьшить вывод и задействовать оптимальные пути доступа типа индексов), развложение подзапросов (по возможности трансформирует запрос с вложеннными подзапросами в соединение, при условии получения той же выборки и отсутствия агрегатных функций. Если трансформация невозможна, подзапрос рассматривается как отдельный запрос, оптимизируется и исполняется отдельно, а результат отдаётся основному запросу), перезапись с материализованными представлениями

Модуль оценки определяет следующие виды характеристик - селективность (показатель отражает объём выборки относительно всего объёма данных и зависит от фильтров. Селективность отражает влияние выражений фильтрации и находится в диапазоне от 0.0 до 1.0. При отсутствии статистик от значения OPTIMIZER_DYNAMIC_SAMPLING используется либо оценочная выборка, либо внутреннее значение, если же статистика есть - используется она, в первую очередь величина уникальных значений, а при возможности - гистограммы), кардинальности (количество строк всего объёма данных - таблицы, вьюхи, соединения, группировки) и стоимость (показатель отражает юниты ресурсов - CPU, I/O и памяти, в частности вариант доступа - table scan, fast full index scan, index scan - определяет количество юнитов работы для получения данных из источника, а стоимость соединения отражает комбинацию стоимости доступа двух источников суммарно с операцией объединения). Облегчают задачу актуальные статистики

Модуль генератора плана отдаёт планы, определяемые разными комбинациями вариантов доступа, вариантами соединений и порядком соединений

... Внимание! Перевод этого раздела не закончен. Однако доступен мой перевод 9 версии руководства по тюнингу, разделённые на Перевод разделов "тюнинг Oracle 9i" - касательно оптимизатора, и Перевод разделов "тюнинг Oracle 9i" - касательно экземпляра

Раздел 15

Этот раздел описывает как управлять планами выполнения через SQL plan management (SPM), предотвращающий регрессию производительности при изменении плана и имеющий компоненты сбора, выбора и применения информации о плане. SPM есть механизм превернивной записи и оценки планов, по которым может строится SQL plan baseline (SPB) - набор принятых для SQL планов выполнения. Целью SPB является созхранение производительности соответствующих запросов при изменениях в БД (версии оптимизатора, статистики и параметров оптимизатора, изменений схем и описаний метаданных, системных установок и построения SQL профилей, но не может помочь при необратимых изменениях типа удаления индекса

Функционал СУБД позволяет строить профили для выбора оптимальных планов, однако это реактивный механизм, который можно использовать после идентификайции проблемы. Архитектура SPB - содержит один или несколько принятых планов - каждый с набором подсказок, хэшем плана и сопутствующей информацией. Plan history (история планов) является набором принятых и непринятых планов, генерируемых оптимизатором во времени. Поэтому baseline (SPB) есть подмножество history, а добавление плана в SPB называют plan evolution. Для использования план должен быть включен для оптимизатора. SQL management base (SMB) является частью словаря, содержит SPB и plan history, а также профили, хранится в SYSAUX

Управление SPB состоит из фаз сбора, отбора и развития baselines. На фазе сбора СУБД детектирует изменения плана и записывает новые планы, формируя историю планов, для повторяемых запросов, для чего ведётся журнал обрабатываемых оптимизатором запросов, и если sqlid при парсинге или выполнени встречается повторно - запрос повторяемый. Для каждого повторяемого запроса формируется история из всех сгенерированных потимизатором планов, а принятые составляют baseline. Если включить автоматический сбор, СУБД будет автоматически формировать историю, включая используемую оптимизатором для воссоздания плана информацию типа текста, outlines, связанных переменных и компиляционного окружения. Оптимизатор помечает начальный сгенерированный план как принятый к использованию и помещает его в SPB и историю. История же включает все последующие планы, и в процессе эволюции СУБД добавляет в baseline планы, проверенные как невызывающие деградации. Для активации нужно выставить параметр OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES в TRUE (по умолчанию в FALSE)

Можно вручную добавить в baseline планы, совместно или вместо автоматически собранных, при этом они не проверяются на производительность, но помечаются как принятые для текущих и новых SPB. Планы можно грузить из SQLSET, снапшотов AWR - через загрузку в SQLSET, или из памяти. Примеры

DECLARE
 my_plans PLS_INTEGER;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');
END;
/

DECLARE
 my_plans PLS_INTEGER;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');
END;
/

В фазе отбора СУБД опеределяет изменение плана, основываясь на plan history, и выбирает планы для избежания потенциальной деградации производительности. Каждый раз при компиляции запроса СУБД - строит best-cost план, ищет подходящий план в baseline, если найден - используется, иначе используется с самой маленькой стоимостью из baseline. В случае когда best-cost план не соответвтует планам в истории, этот план занисится в историю как непринятый и не используется системой до проверки на деградацию производительности, но когда система меняется и делает активные планы неприменимыми, пример - удаление индекса, этот план может использоваться. Baseline включены по умолчанию параметром OPTIMIZER_USE_SQL_PLAN_BASELINES

В фазе эволюции БД оценивает новые планы и интегрирует пладны с лучшей производительностью в SPB. Когда оптимизатор находит новый план, БД добавляет его в историю как непринятый, и может проверить его производительность по отношению к производительности выбранного плана baseline. Если производительность нового плана лучше - он добавляется в SPBaseline. Для ручной эволюции плана можно использовать функцию в примере ниже. Альтернативно можно не указывать функции аргументов - тогда проводится эволюция всех планов в истории, указать имя плана или список планов

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
          sql_handle => 'SYS_SQL_593bc74fca8e6738');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

Существует описанная в 15 главе (15.7) руководства по тюнингу процедура переноса SPB в другую систему. Также в разделе 15.8 описана процедура миграции stored outline в SQL Plan Baselines