Oracle увеличить количество процессов
Oracle Database 11g Настройка производительности
Причины неэффективного выполнения SQL выражений
SQL выражение может выполнятся неэффективно по следующим причинам:
- Устаревшая статистика оптимизатора: Планы выполнения SQL генерируются стоимостным оптимизатором. Для того чтобы оптимизатор генерировал и использовал наиболее эффективный план выполнения, он нуждается в точной информации о хранении и распределении данных в таблицах и индексах, которые участвуют в обработке SQL выражения.
- Отсутсвие путей доступа к данным (access path): Отсутствие структур доступа к данным, таких как индексы, материализованные представления или партиций - частая причина падения производительности SQL выражений. Создание соответствующих методов доступа к данным на порядок увеличит производительность SQL выражений.
- Выбор не оптимального плана выполнения: В отдельных случаях оптимизатор может выбрать не оптимальный план исполнения для SQL выражения. Причиной выбора не оптимального плана может быть неправильно оцененная стоимость, кардинальность или селективность для предиката выражения.
- Неправильно построенный SQL: Если SQL выражение спроектировано неправильно, оптимизатор не сможет построить оптимальный план выполнения. Отсутствующее условия соединения, ведущее к декартову произведению данных, или использование наиболее ресурсоёмких операций таких как UNION вместо менее затратной UNION ALL - некоторые примеры написания неэффективных SQL выражений.
- Дополнительно на производительность выполнения SQL могут влиять проблемы с аппаратным обеспечением, таким как память, ввод/вывод CPU и.т.д.
- Запрос определяет какие продукты имеют стоимость по прайс листу менее чем на 15% выше средней стоимости продукта. В данное выражение встроен подзапрос, который выполняется для каждой строки, найденой в результате основного запроса. Этот запрос можно переписать следующим образом: SELECT COUNT(*) FROM products p,(SELECT prod_id, AVG(unit_cost) ac FROM costsGROUP BY prod_id) cWHERE p.prod_id = c.prod_id ANDp.prod_list_price < 1.15 * c.ac
- В данном запросе применяется функция к операции объединения, ограничивая тем самым возможность использования индекса для объединения. Для нормального выполнения запроса понадобится создать индекс основанный на функции.
- Запрос включает в условии неявное преобразование типов данных.
- Запрос использует функцию преобразования типов для приведения в соответствие типов данных обрабатываемых в предикате. Проблема заключается в том, что функция будет вызываться для каждой строки в таблице employees. Более оптимальным будет конвертировать условие предиката а не весь столбец: SELECT * FROM employees WHERE salary = TO_NUMBER(:sal)
- В запросе используется оператор UNION вместо UNION ALL для того чтобы отбросить дублирующиеся строки в процессе выполнения запроса. Данная операция приведет к дополнительной сортировке для исключения не уникальных значений.
Решения Oracle Database для мониторинга производительности
Automatic Workload Repository (AWR): Собирает, обрабатывает и обслуживает статистику производительности для обнаружения проблем и задач самонастройки. Данные статистики хранятся в памяти и на диске в таблицах БД. собираемые данные могут быть просмотрены как при помощи представлений, так и в виде отчета за период.
Active Session History (ASH): Предоставляет выборочную статистику по активности сессий в экземпляре. Активные сессии выбирающиеся каждую секунду хранятся в цикличном буфере в SGA.
- Проактивный мониторинг
- Automatic Database Diagnostic Monitor (ADDM) автоматически выявляет узкие места в работе Oracle Database. Дополнительно, работая с другими компонентами управления, ADDM выдает рекомендации для устранения узких мест, используя доступные опции.
- Oracle Database 11g в дальнейшем автоматизирует процесс настройки SQL, идентифицирует проблемные SQL выражения, запускает для них SQL Tuning Advisor и применяет профиль, сгенерированный в результате работы SQL advisor к SQL выражению, повышая тем самым скорость его выполнения без вмешательства пользователя. Для этого, при помощи среды AUTOTASK создается пакетное задание для автоматического исследования производительности (Automatic SQL tuning task). Задание Automatic SQL Tuning task запускается по умолчанию каждую ночь.
- Реактивный мониторинг
- Предупреждения генерируемые сервером: База данных Oracle может автоматически определять проблемные ситуации. В качестве реакции на обнаруженную проблему, СУБД Oracle отправляет предупреждение о ней с возможными действиями для её устранения
- В качестве инструмента для реактивного мониторинга производительности БД также может использоваться Oracle Enterprise manager.
Инструменты мониторинга и настройки производительности Oracle Database 11g
Automatic Database Diagnostic Monitor: Постоянно анализирует данные производительности, собираемые экземпляром БД.
SQL Tuning Advisor: Анализирует SQL выражения, определенные как проблемные, и пытается из настроить. По умолчанию задание на анализ производительности включено. Вы можете также вручную запустить SQL Tuning advisor для анализа производительности определенного SQL, или группы SQL используя SQL Tuning set.
SQL Tuning Sets : Служит репозиторием для хранения набора SQL выражений. Может использоваться SQL Tuning advisor для анализа производительности группы SQL выражений или например для переноса SQL из одной БД в другую для выявления потенциальных проблем с производительностью, которые могут возникнуть после миграции.
SQL Access Advisor: Анализирует SQL выражение и выдает рекомендации по созданию материализованных представлений, индексов, логов материализованного представления или секционированию таблиц и индексов.
SQL Monitoring: Позволяет в режиме реального времени наблюдать за процессом исполнения SQL и просматривать план и статистику его выполнения.
SQL Plan Management (SPM): Может быть использован для контроля и стабилизации планов исполнения SQL выражений при помощи создания опорных линий. При использовании опорной линии SQL будет использовать план исполнения с наилучшим временем выполнения, остальные планы, сгенерированные для этого SQL будут помечены как исторические, будут хранится в опорной линии и постоянно анализироваться. В случае нахождения более оптимального плана выполнения, этот план будет применен к SQL выражению, а предыдущий будет помещен в историю.
- Определение высоко нагруженных SQL является одной из наиболее важных задач которые вы должны выполнять. ADDM - идеальный инструмент для выполнения данной задачи.
- По умолчанию СУБД Oracle собирает статистику оптимизатора автоматически. Для сбора статистики работает задание планировщика, которое запускается в maintenance window.
- Статистика операционной системы предоставляет информацию и производительности основных аппаратных компонентов также как и производительность системы в целом.
- Часто перестройка индексов оказывает положительное влияние на производительность. Например удаление не селективных индексов для увеличения скорости обработки DML или добавление новых столбцов в индекс для увеличения его селективности.
- Вы можете сохранить существующие планы выполнения SQL выражений используя сохранив статистику или создав опорную линию для этого SQL.
Наиболее часто на пользовательских системах встречаются следующие ошибки:
Неправильно настроенное подключение: Приложение подключается и отключается при каждой операции взаимодействия с БД. Эта проблема часто встречается в случае работы с stateless сервером приложений.
Некорректное использование курсоров и разделяемого пула: Не используются результаты хранимые в курсоре при повторном разборе. Если не используются BIND переменные, может выполнятся жесткий разбор при каждом выполнении одного SQL. В случае использования BIND переменных, открытые ранее курсоры могут использоваться повторно необходимое количество раз. Очень часто проблемы с версионностью и большим количеством жестких разборов выражения возникают в приложениях, генерирующих SQL динамически.
Некорректно спроектированный SQL: Плохой SQL - это SQL который использует больше ресурсов, чем уместно при его выполнении. Это может быть например DSS запрос, который выполняется более чем 24 часа или запрос к онлайн приложению, который выполняется более минуты. SQL который потребляет значительную часть системных ресурсов должен также быть обследован на предмет его потенциального улучшения. ADDM идентифицирует высоко нагруженные SQL и SQL Tuning advisor может быть использован в дальнейшем для увеличения его производительности.
Использование нестандартных параметров инициализации: Установка некорректных значений параметров может быть выполнена на основании неправильного совета или решения. Большинство систем показывает приемлемую производительность с использованием стандартных параметров экземпляра. В частности, недокументированные параметры оптимизатора могут вызвать серьезные проблемы, которые потом тяжело будет определить и устранить. Также, параметры оптимизатора установленные в файле инициализации могут поменять существующие планы выполнения запросов. Исходя из этого схемы, статистика схем и настройки оптимизатора должны управляться вместе для обеспечения целостности конфигурации и производительности.
Некорректная настройка ввода/вывода: Система хранения данных должна конфигурироваться из расчета доступности, а также пропускной способности, а не легкости хранения и максимизации размера разделов.
Проблемы с настройкой Redo логов: Часто создается недостаточное количество логов Redo. Маленький размер Redo лог файлов может привести к частым операциям checkpoint, что приведет в свою очередь к высокой нагрузке на буферный кэш и систему ввода/вывода. При избыточном количестве Redo логов не сможет выполняться архивирование и БД будет ждать выполнения процесса создания архивных логов.
Избыточная сериализация ресурсов: Сериализация блоков данных в буферном кэше из за нехватки undo сегментов является частой проблемой для приложений с большим количеством активных пользователей и маленьким количеством undo сегментов. Использование автоматического управления сегментами (ASSM) и автоматического управления UNDO чаще всего решает данную проблему.
Длительное полное сканирование таблиц: Большое количество продолжительных сканирований таблиц говорит о некорректном дизайне транзакций, возможном отсутствии индексов или не оптимизированном SQL. Операция full table scan вызывает большую продолжительную нагрузку на систему ввода/вывода.
Большое количество рекурсивного SQL: Большое количество рекурсивного SQL выполняемого SYS может обозначать активность, связанную с управлением пространством, например выделением экстентов. Для исключения рекурсивного SQL рекомендуется использовать табличные пространства с локальным управлением экстентами. В этом случае рекурсивный SQL выполняется от пользователей с разными ID и нагрузка распределяется более равномерно.
Ошибки внедрения и миграции: В большинстве случаев приложение использует слишком много ресурсов, поскольку схема в которой хранятся таблицы необходимые объекты не была должным образом смигрирована из среды разработки или из более поздней версии схемы. Типичными ошибками в процессе миграции являются отсутствие индексов или неактуальная статистика. Данные ошибки являются причиной формирования неоптимальных планов выполнение и как следствие, причиной падения производительности. Когда мигрируется какой либо из разработанных или сущевствующих компонентов, для обеспечения стабильности планов исполнения, необходимо вместе с объектами переносить статистику при помощи пакета DBMS_STATS.
Процент попаданий в буфер блока данных (кэш данных)
если процент ниже 95, то стоит увеличить размер DB_CACHE_SIZE
или оптимизировать запросы вызывающие много считываний с диска.
select 1- (sum(decode(name, 'physical reads',value,0))/
(sum(decode(name, 'db block gets',value,0)) +
(sum(decode(name, 'consistent gets',value,0)))))
"Read Hit Ratio"
from v$sysstat;
Покажет, как увеличение или уменьшение кэша скажется на процессе попаданий
(эффект от увеличения или уменьшения кэша данных)
select size_for_estimate, buffers_for_estimate,
estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name='DEFAULT'
and block_size=
(select value
from v$parameter
where name='db_block_size' )
and advice_status='ON';
Процент попадания в словарный кэш
Если меньше 95, то стоит задуматься над увеличением SHARED_POOL_SIZE
select sum(gets), sum(getmisses),
(1-(sum(getmisses)/(sum(gets)+ sum(getmisses))))* 100 HitRate
from v$rowcache;
Процент попаданий для кэша разделяемых SQL-запросов
и процедур на языке PL/SQL
процент попаданий при выполнении PinHitRatio должен быть не менее 95
процент попаданий при загрузке RelHitRatio должен быть не менее 99
select sum(pins) "Executions",
sum(pinhits) "Hits",
((sum(pinhits)/sum(pins))* 100) "PinHitRatio",
sum(reloads) "Misses",
((sum(pins)/(sum(pins) + sum(reloads)))* 100) "RelHitRatio"
from v$librarycache
Объекты PL/SQL, которые следует закрепить в памяти
(объекты, для хранения которых нужно больше 100 Кбайт)
select name, sharable_mem
from v$db_object_cache
where sharable_mem>100000
and type in ('PACKAGE','PACKAGE_BODY','FUNCTION','PROCEDURE')
and kept='NO';
Сессии, наиболее интенсивно использующие процессорное время
Сессии, наиболее интенсивно использующие ресурсы процессора,
можно определить несколькими способами:
- При помощи команды top операционной системы UNIX.
- С использованием динамической статистики Oracle,
выполнив следующий запрос:
Следует учитывать, что в данном запросе интересует не большое
значение статистики само по себе, а скорость ее роста в данный
момент времени. Связано это с тем, что сессия, функционирующая в
течение длительного периода времени, при незначительном приросте,
могла в сумме использовать большое количество процессорного времени.
Повторный разбор SQL-предложений
select name, value from v$sysstat
where name in (
'parse time cpu',
'parse time elapsed',
'parse count (hard)'
);
SQL-предложения, подвергающиеся наиболее частым разборам
select sql_text, parse_calls, executions
from v$sqlarea
order by parse_calls desc;
О том, что курсоры не разделяются между сессиями, свидетельствуют
большие и постоянно растущие значения поля VERSION_COUNT:
select sql_text, version_count
from v$sqlarea order by version_count desc;
SQL-предложения, наиболее интенсивно выполняющие обращения к блокам
данных:
select address, hash_value, buffer_gets, executions,
buffer_gets/executions "gets/exec", sql_text
from v$sqlarea
where executions > 0
order by 3 desc;
(следует обращать внимание на SQL-предложения с большим
отношением gets/exec или значением buffer_gets)
Интенсивные согласованные чтения
Система может тратить большое количество ресурсов на формирование
согласованного представления информации. Наиболее часто это происходит
в следующих ситуациях:
- В системе работают длительный запрос и множество маленьких транзакций,
выполняющих добавление данных и обращающихся к одной и той же таб-лице.
При этом запросу требуется откатывать большое количество измене-ний для
обеспечения согласованности чтения.
- Если мало число сегментов отката, то система может тратить много времени
на откат таблицы транзакций. Если запрос работает в течение длительного
времени, то поскольку число сегментов отката и таблиц транзакций слишком
мало, ваша система вынуждена часто повторно использовать слоты транзакций.
- Система сканирует слишком много буферов для того чтобы найти свободный.
Необходимо увеличить интенсивность скидывания буферов на диск процессом
DBWRn. Кроме этого можно увеличить размер кэша буферов для уменьшения
нагрузки для DBWR. Для нахождения среднего количества буферов, которое
необходимо просмотреть в списке LRU (Least Reasently Used) для нахождения
свободного буфера, необходимо использовать сле-дующий запрос:
select 1+sum(decode(name, 'free buffer inspected', value, 0)) /
sum(decode(name, 'free buffer requested', value, 0))
from v$sysstat
where name in (
'free buffer inspected',
'free buffer requested'
);
Результат должен быть равен в среднем 1-му или 2-м блокам.
Если количество блоков больше, то необходимо увеличить кэш буферов
или настроить процессы DBWRn.
Аналогично следует поступать, если велико среднее количество "грязных"
буферов в конце списка LRU:
select * from v$buffer_pool_statistics;
Для определения наиболее частых причин ожидания необходимо выполнить
следующий запрос:
select * from v$system_event
where event != 'Null event' and
event != 'rdbms ipc message' and
event != 'pipe get' and
event != 'virtual circuit status' and
event not like '%timer%' and
event not like 'SQL*Net % from %'
order by time_waited desc;
Обращать внимание следует на события с наибольшими временами ожидания.
Наиболее часто встречающиеся причины ожиданий:
- Buffer busy wait - данное событие обычно возникает, если несколько
сессий пытаются прочитать один и тот же блок, или одна или несколько
сессий ожидают окончания изменения одного блока. Конкуренция за блок
корректируется в зависимости от типа блока:
- Уменьшите количество строк в блоке путем изменения параметров
pctfree/pctused или уменьшением BD_BLOCK_SIZE.
- Проверьте на наличие right.hand.indexes (индексов, в которые добавляются
данные многими процессами в одну точку). Возможно, следует использовать
индексы с обратными ключами.
- Увеличьте количество freelists.
- Увеличьте размер экстентов для таблицы.
Заголовок сегмента отката:
- Добавьте больше сегментов отката для уменьшения количества транзакций
на сегмент.
- Уменьшите значение параметра TRANSACTION_PER_ROLLBACK_SEGMENT.
Блок сегмента отката:
- Увеличьте сегмент отката.
- Free buffer wait - обычно возникает, если процесс DBWR не справляется
с записью блоков на диск. Необходимо увеличить его пропускную способность.
- Latch free - конкуренция за доступ к защелкам. При помощи следующего
запроса можно определить защелки, которые ожидают активные сессии в
данный момент времени:
Конкуренция за доступ к защелкам
Одной из причин простоя процессов может быть конкуренция за доступ
к защелкам. Защелка - это внутренняя структура данных Oracle,
контролирующая доступ к объектам, находящимся в SGA (System Global Area).
О возникновении конкуренции за доступ к защелкам сигнализирует появление
сессий с ожиданием события "latch free" в динамическом представлении
V$SESSION_WAIT и соответственно рост статистики ожидания "latch free"
в V$SESSION_EVENT.
- Статистика по ожиданиям защелок в системе:
select * from v$system_event where event = 'latch free';
- Текущие ожидания защелок:
select * from v$session_wait where event = 'latch free';
- Защелки, доступ к которым ожидают процессы в текущий момент времени:
Выявить возникновение конкуренции за доступ к защелкам в системе поможет
скрипт response_time_breakdown.sql.
Наиболее часто встречающиеся причины ожиданий:
- Сache buffers chains - данная защелка запрашивается при поиске блока
данных, кэшированного в SGA. Поскольку буферный кэш представляет собой
последовательность блоков, каждая последовательность защищается защелкой,
которая является дочерней для данной защелки. Конкуренция за доступ к
данной защелке вызывается очень активным доступом к одному блоку, и обычно
требует для исправления переписывания приложения. Определить блоки данных
в кэше буферов, при обращении к которым возникают задержки, поможет
следующий запрос:
Cущественное уменьшение количества ожиданий защелки данного типа
можно выставлением скрытого параметра базы данных
Недостаточность ресурсов памяти
Разделяемый буфер (shared pool)
- Коэффициент попадания в библиотечный кэш, должен быть близок к 1:
select (sum(pins - reloads)) / sum(pins) "lib cache"
from v$librarycache;
- Коэффициент попадания в словарный кэш (содержащий библиотечные данные),
коэффициент должен быть близок к 1:
select (sum(gets - getmisses - usage - fixed)) / sum(gets) "row cache"
from v$rowcache;
- Свободное место в разделяемом кэше:
select * from v$sgastat where name = 'free memory';
- Коэффициент не попадания в библиотечный кэш:
select sum(pins) "executions",
sum(reloads) "cache misses while executing",
sum(reloads)/sum(pins) "miss rate"
from v$librarycache;
Кэш буферов (buffer cache)
- Коэффициент попаданий в буфер должен быть больше 0.9:
select name, 1-physical_reads/(db_block_gets+consistent_gets) "Hits"
from v$buffer_pool_statistics;
- Коэффициент ожидания LRU защелок при обращении к кэшу буферов:
Кэш журналов регистраций (redo log buffer)
Количество промахов при обращении к кэшу журналов регистрации:
select name, value
from v$sysstat
where name = 'redo buffer allocation retries';
Области сортировки (sort areas)
Количество сортировок на диске и в памяти:
select name, value
from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');
Конкуренция за доступ к ресурсам
Конкуренция за сегменты отката
Количество ожиданий доступа к сегментам отката не должно превышать 1%.
Если коэффициент ожиданий выше, то необходимо увеличить количество
сегментов отката:
select w.class, w.count/s.value "Rate"
from v$waitstat w,
( select sum(value) value from v$sysstat
where name in ('db block gets', 'consistent gets')) s
where w.class in (
'system undo header',
'system undo block',
'undo header',
'undo block');
Какие таблицы и индексы стали причиной высокого ожидания события buffer busy waits
select
OWNER ,
OBJECT_NAME ,
OBJECT_TYPE ,
TABLESPACE_NAME
from v$segment_statistics
where statistic_name = 'buffer busy waits'
order by value desc
Долго выполняющиеся транзакции:
select hash_value, executions,
round(elapsed_time/1000000,2) total_time,
round(cpu_time/1000000,2) cpu_seconds
from (select * from v$sql order by elapsed_time desc)
Далее по hash_value находим план
select * from v$sql_plan
where hash_value = 3287028449
Просмотр SQL - кода сеанса
Monitor long running operations using v$session_longops
Как посмотреть план запроса:
Запросы потребляющие ресурсы CPU:
select substr(sql_text,1,40) sql_text, hash_value, cpu_time
from v$sql
where cpu_time > 10000000
order by cpu_time;
Или любой другой запрос:
SELECT hash_value, address, child_number, sql_id, plan_hash_value
FROM v$sql
WHERE sql_text LIKE '%select sysdate from dual%'
AND sql_text NOT LIKE '%v_sql%';
По hash_value:
SELECT
lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name as "Query Block name"
FROM (select *
from v$sql_plan
where HASH_VALUE = 2343063137
and ADDRESS = '00007FF7C88C4128'
and CHILD_NUMBER = 0
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;
Или по sql_id:
SELECT
lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name as "Query Block name"
FROM (select *
from v$sql_plan
where sql_id = '7h35uxf5uhmm1'
and child_number = 0
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;
Из AWR по sql_id и plan_hash_value:
SELECT lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' ||
pt.object_name "Query Plan",
pt.cost,
pt.cardinality,
pt.bytes,
pt.cpu_cost,
pt.io_cost,
pt.temp_space,
pt.access_predicates,
pt.filter_predicates,
pt.qblock_name
FROM (select *
from dba_hist_sql_plan
where sql_id = '7h35uxf5uhmm1'
and plan_hash_value = 1388734953
) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0
В 11g из v$sql_plan_monitor :
SELECT lpad(' ', 2 * level) || pt.plan_operation || ' ' || pt.plan_options || ' ' ||
pt.plan_object_name "Query Plan",
pt.plan_cost,
pt.plan_cardinality,
pt.plan_bytes,
pt.plan_cpu_cost,
pt.plan_io_cost,
pt.plan_temp_space,
pt.starts,
pt.output_rows,
pt.workarea_mem,
pt.workarea_tempseg,
pt.physical_read_requests,
pt.physical_write_requests
FROM (select *
from v$sql_plan_monitor pt
where sql_id = '7h35uxf5uhmm1'
-- and sql_plan_hash_value = :sql_plan_hash_value
-- and sid = :sid
-- and sql_exec_id = :sql_exec_id
and status = 'EXECUTING') pt
CONNECT BY PRIOR pt.plan_line_id = pt.plan_parent_id
START WITH pt.plan_line_id = 0
Также можно получить отчет:
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
Производительность процесса LGWR является критической для скорости работы OLTP-систем и процедур загрузки данных которые не используют возможность работы в режиме «без восстановления» (unrecoverable). В средах, где не выполняются интенсивные изменения в базе данных с помощью стандартных операторов SQL, LGWR не испытывает значительной нагрузки. Деградация производительности и/или увеличение времени простоя будут наблюдаться главным образом в высокоинтенсивных OLTP-системах; именно поэтому задача конфигурирования журнальных файлов является темой этого раздела.
4.1 Баланс производительности и доступности
Борьба между производительностью и надежностью особенно ярко проявляется на примере журнальных файлов. Для оптимизации производительности Вам необходимо конфигурировать LGWR и DBWR так, чтобы операции записи выполнялись как можно реже. Однако для минимизации времени, необходимого для выполнения доката данных (roll-forward) при восстановлении инстанции, Вам необходимо конфигурировать LGWR и DBWR так, чтобы операции записи производились как можно чаще. Решение этой проблемы заключается в достаточном понимании технологий для того, чтобы выбрать значение параметра log_checkpoint_interval, которое было бы и достаточно большим и достаточно малым для удовлетворения обоих требований.Понимание технологий, относящихся к конфигурированию журнальных файлов, лежит в осознании двух ключевых событий: контрольной точки сервера Oracle и восстановление инстанции сервера Oracle.
4.2 Контрольные точки сервера Oracle
- Переключение журнального файла (log switch) — когда LGWR заполняет текущий журнальный файл и пытается переключиться на следующий в круговой очереди.
- Предопределенный интервал — LGWR будет порождать контрольную точку либо когда запишет число блоков операционной системы равное значению параметра log_checkpoint_interval, с момента последней контрольной точки, либо когда пройдет число секунд равное значению параметра log_checkpoint_timeout, с момента последней контрольной точки.
Размер журнального файла и параметры log_checkpoint_interval и log_checkpoint_timeout оказывают наиболее важное влияние на изменение производительности при обычной работе механизма контрольных точек. Многие администраторы БД отключают тайм-аут, устанавливая его в ноль и регулируют работу механизма контрольных точек только с помощью параметра log_checkpoint_interval. Некоторые администраторы деактивируют оба параметра и контрольные точки возникают лишь вследствие переключения журнальных файлов.
4.3 Восстановление инстанции сервера Oracle
- Отказ узла без кластера — отказ процессора, шины или памяти на незащищенном с помощью кластера узле.
- Отказ узла параллельного сервера Oracle — отказ одного или нескольких узлов в конфигурации с параллельным сервером Oracle будет причиной кратковременной недоступности всего кластера, которая оканчивается к моменту, когда «выжившие» узлы завершат восстановление нитей отказавших узлов.
Реализация отложенного, «по требованию», отката в Oracle 7.3 делает период недоступности кластера зависящим, в основном, от времени которое необходимо для реконструкции буферного кэша. Диаграмма, сравнивающая время восстановления после отказа в Oracle 7.3 и Oracle 7.2, приведена на рисунке 2.
Рис. 2. Временная диаграмма восстановления после отказа для параллельного сервера Oracle 7.2 и 7.3. OPS 7.3 откладывает откат транзакций для минимизации периода недоступности кластера при отказе узла, что дает улучшение производительности восстановления по сравнению с OPS 7.2 или ниже. В OPS 7.3 время завершения отката инстанции не связано с доступностью приложений, поскольку все откаты запускаются «по требованию», на уровне транзакций.
- Объем повторно-применяемой информации (redo-информации), которая была сгенерирована с момента последней контрольной точки (чем меньше, тем лучше), и
- Качество буферного кэша базы данных в течение выполнения восстановления (чем выше, тем лучше).
На первый фактор, в основном, влияет скорость генерации redo-информации и частота выполнения контрольных точек. Уменьшение объема генерируемой redo-информации является задачей разработчика приложения и связано с минимизацией «веса» транзакций, минимизацией числа транзакций, необходимых для достижения требований бизнеса и использованием там, где это допустимо, режима работы без восстановления. Уменьшение частоты выполнения контрольных точек достигается изменением параметра, определяющего интервал между контрольным точками или, возможно, увеличением размера журнального файла.
Улучшения, относящиеся ко второму фактору, Вы можете достичь с помощью увеличения размера буферного кэша базы данных и уменьшения, тем самым, вероятности промахов в буферном кэше в течение выполнения процедуры восстановления. Увеличение значение параметра db_block_buffers для работы процесса восстановления (ценой уменьшения значения параметра shared_pool_size, если необходимо) в общем, уменьшает число промахов в буферном кэше. В течение процедуры восстановления, при котором не требуется выполнения восстановления носителя, Вы не имеете других способов влиять на процент попаданий в буферный кэш, поскольку должна быть повторно применена вся информация, которая была сгенерирована с момента последней контрольной точки 11 .
Меняя параметр log_checkpoint_interval в диапазоне от нескольких килобайт до нескольких сотен мегабайт (в блоках операционной системы) можно достичь приемлемого компромисса с производительностью в OLTP-среде. Скорость доката при восстановлении варьируется почти также как кардинально, как и требования к скорости доката 12 . В средах с жестко заданными требованиями к производительности и надежности малое изменение log_checkpoint_interval может привести к значительному изменению времени восстановления инстанции. Вследствие этого поиск оптимального значения log_checkpoint_interval часто требует тестирования в среде эксплуатации.
4.4 Размещение журнальных файлов
В хорошей конфигурации VLDB высокоактивные, с точки зрения ввода/вывода, журнальные файлы должны быть изолированы от других файлов с высокой активностью ввода/вывода настолько, насколько это возможно. Физическая изоляция на выделенных дисках и контроллерах позволит Вам снизить до минимума шанс возникновения «узкого места» при выполнении операций ввода/вывода в журнальные файлы.Техника изолирования хорошо комбинируется с рассмотренным ранее желанием размещать журнальные файлы на дисковом массиве с малым размером сегмента чередования доступ к которому наиболее эффективен при работе однопоточного (с очень низким уровнем параллелизма) процесса.
В дополнение к отделению журнальных файлов от других файлов, фактически Вам необходимо физически отделить журнальные файлы друг от друга. В то время как LGWR-процесс пишет в журнальный файл с максимально возможной скоростью, процесс ARCH читает журнальный файл с максимально возможной скоростью (в любой момент времени он может читать один из журнальных файлов, кроме того, который открыт LGWR).
- Надежность лучше — ленточный накопи тель существенно менее надежен, чем диск. Оставлять лишь одну копию критически важного файла на ленточном носителе является не очень хорошим решением.
- Пропускная способность лучше — вы должны помнить, что ARCH-процесс не является чисто потоковым процессом копирования, но выполняется лучше на устройствах с возможностью произвольного доступа (random I/O). Архивирование на диск выполняется намного быстрее чем на ленточный накопитель, что в свою очередь снижает вероятность возникновения ненужного «узкого места».
- Управление ошибками лучшее — управление дисковыми устройствами, при переполнении, много надежней, чем ленточными устройствами. Управление пространством для дисков может быть автоматизировано с помощью программного обеспечения, в то время как для ленточных устройств требуется вмешательство человека.
- Время восстановления быстрее — многие администраторы БД хранят максимально возможное число архивных журнальных файлов на дисках, которые могут быть необходимы для восстановления с момента последнего «горячего» копирования. Такая техника уменьшает продолжительность процесса восстановления на время необходимое для нахождения требуемой ленты, ее монтирования и чтения с нее архивных журнальных файлов на диск.
4.5 Размер журнального файла
- Определите требования к времени восстановления после краха. Обозначим это время, заданное в секундах, как t.
- Рассчитайте скорость, с которой система применяет redo-информацию при восстановлении инстанции. Обозначим эту скорость, заданную в байтах в секунду, как r.
- Установите значение параметра log_checkpoint_interval в r × t/b, где b — размер блока ввода/вывода в байтах в Вашей операционной системе.
- Создайте журнальные файлы размером f = k × r × t, для некоторого целого k (т.е. f кратно r × t).
- Если новые контрольные точки накапливаются без завершения предыдущих, то Вы должны увеличить значение log_checkpoint_interval. Вы можете определить частоту возникновения таких ситуаций сравнив значения статистик background_checkpoints_started и background_checkpoints_completed из v$sysstat. Если значения отличаются более чем на 1, значит контрольные точки не завершались к тому моменту, когда начинались новые.
- Установите log_buffer в l = 3×n×s, где n — число дисков в массиве, хранящем журнальные файлы и s — размер сегмента чередования массива в байтах. Наибольший размер операции записи, генерируемый LGWR, будет примерно равен l/3 = n × s13 .
- снижение частоты переключения журнальных файлов;
- упрощение процедуры размещения журнальных файлов;
- снижение сложности при полном восстановлении за счет снижения числа обрабатываемых файлов;
- снижение частоты возникновения событий ожидания контрольных точек и занятости процесса архивирования.
- снижение стоимости отказа, связанной с потерей всех копий активного журнального файла 14 ;
- улучшение гибкости в предотвращении ситуации переполнения файловой системы, хранящей архивные журнальные файлы;
- снижение потерь данных в конфигурациях с резервной (standby) БД.
Общим правилом при выборе размера журнального файла можно принять пожелание о том, что переключение журнальных файлов не должно происходить чаще, чем два раза в час.
4.6 Число журнальных файлов
- Ожидания занятой контрольной точки (checkpoint busy wait) — ожидание занятой контрольной точки возникает, когда LGWR пытается переключиться на журнальный файл до того, как связанная с этим журнальным файлом предыдущая контрольная точка была завершена.
- Ожидание занятого процесса архивирования (archiver busy wait) — ожидание занятого процесса архивирования возникает, когда LGWR пытается переключиться на журнальный файл, который еще не было скопирован в архивный журнальный файл процессом ARCH.
- Проверьте событие log file switch (checkpoint incomplete) в v$session_wait. Если Вы установили параметр log_checkpoints_to_alert в значение true, то Вы можете определить возникновение этой ситуации с помощью текстового вхождения «cannot allocate» в файле alert.log.
- Снизить частоту возникновения события ожидания контрольной точки Вы можете:
- увеличивая число оперативных журнальных файлов для снижения вероятности того, что LGWR сможет заполнить все файлы до того, как будет завершена контрольная точка; или
- добавляя DBWR процессы для увеличения скорости выполнения контрольной точки (только если Вы используете синхронную запись); или
- увеличивая значение параметра db_block_checkpoint_batch для увеличения скорости выполнения контрольной точки; или
- уменьшая значение параметра db_block_buffers для снижения объема работы в контрольной точке; или
- снижая число и размеры сегментов отката в базе данных (описанных в нижеследующем разделе)
Событие ожидания занятого процесса архивирования обычно возникает при генерации большого числа транзакций во время пакетной обработке, когда скорость записи redo-информации LGWR превышают возможности копирования процесса ARCH. Оно также служит причиной возникновения ожидания контрольной точки — ARCH становится «узким местом» для завершения всех транзакций в системе.
Через несколько лет после внедрения автоматизированных систем организация может оказаться в ситуации, когда загрузка оборудования приблизилась к максимуму, производительность выполнения ежедневных штатных процедур упала, как следствие, встал вопрос о покупке более мощного железа. Можно ли максимально отсрочить этот момент? В большинстве случаев можно.
Каждый ИТ-специалист, занимающийся поддержкой инфраструктуры, знает, что через несколько лет эксплуатации система, работающая на базе данных (например, на БД Oracle), теряет производительность. Например, запрос к серверу, который изначально выполнялся буквально одну секунду, стал отрабатывать за 15 или даже 20 секунд. При этом довольно часто отчет о производительности (AWR-отчет) может свидетельствовать о нормальном функционировании ПО. Что же делать? Конечно, выявлять такие запросы и оптимизировать их.
Как вы знаете, АБС и системы на основе хранилищ данных (то есть OLTP- и OLAP-системы) по-разному используют базы данных, поэтому и методики оптимизации у них разные. На примере простейших графиков мы расскажем, в чем их отличие, и, опираясь на собственный опыт, посоветуем, как с помощью инструментов Oracle Enterprise Manager и SQL Monitoring найти ошибки в проектировании и коде, а за счет параллельного выполнения, секционирования и других приемов «выжать» из оборудования максимум его возможностей. Следуя этим рекомендациям, вам удастся продлить срок его службы.
Загрузка сервера в системе координат
Для начала представим себе, что делает сервер БД, когда обрабатывает запрос. Для этого построим график, где по оси X — время, а по оси Y — мощность (или загрузка) сервера (рис. 1). В реальности загрузка состоит из нескольких компонентов, включая загрузку процессора, памяти и системы хранения данных, но для простоты в нашем случае будем ориентироваться на некоторую «общую» загрузку.
Рис. 1. Одиночный запрос, выполненный на сервере
На рисунке 1 показан одиночный запрос. Его выполнение затратило какое-то время и некоторую мощность сервера, поэтому физический смысл нашего запроса — работа. Объем этой работы можно измерить в абстрактных «условных единицах». Именно в этих единицах оптимизатор Oracle измеряет стоимость запроса. То, что мы видим в разделе Cost в плане запроса, является оценочной стоимостью работы сервера по выполнению этого запроса.
Оптимизация OLTP-системы
Нагрузка в OLTP-системах
Теперь посмотрим, как выглядит нагрузка в OLTP-системе (рис. 2). Представьте, что перед вами многопроцессорный сервер, способный решать несколько задач одновременно. Вся нагрузка на схеме отображается как набор «кирпичиков» — запросов, каждый из которых выполняется некоторое время и занимает, например, один процессор.
По мере роста количества одновременно запущенных процессов каждый из них выполняется все медленнее. Причина в том, что сервер затрачивает дополнительные ресурсы на переключение между процессами, а кроме этого, обычно СХД является более «тонким» местом, чем процессоры. Если такая «кирпичная» стена достигнет в высоту 100%, то сервер «встанет» — новые процессы будут помещаться в очередь (если у системы предусмотрены ресурсы на это). Таким образом, глобальная задача оптимизации — как можно дальше отодвинуть момент полной загрузки сервера.
Для уменьшения нагрузки мы рекомендуем выбрать некоторое количество одинаковых процессов, которые можно оптимизировать. На нашем графике (рис. 3) они окрашены зеленым.
Рис. 3. Выбор одинаковых процессов с целью оптимизации
Давайте сократим эти блоки в 2-3 раза и посмотрим, что получится (рис. 4).
Как видите, сервер завершил их выполнение раньше, поэтому появилась возможность перераспределить процессы между процессорами. Таким образом, не только уменьшилась общая площадь фигуры (работа, произведенная сервером), но и максимальная загрузка снизилась на две условные линии, отодвинув, таким образом, критический момент полной загрузки сервера.
Упомянутая «общая площадь фигуры» примерно соответствует такому важному показателю AWR-отчета, как DB Time. По сути, это общее время работы сервера за период формирования AWR-отчета — The wall time, или Estimated. Например, если отчет выпускался за 1 час, количество процессоров в системе 16, а DB Time в отчете равен 8 часам, это означает, что средняя загрузка сервера составила 50%.
Показатель DB Time удобно использовать как меру произведенной оптимизации (конечно, с учетом отклонений нагрузки, потому что в промышленной среде редко бывают одинаковые по нагрузке дни). Но в целом, если сегодня это время в два раза меньше, чем вчера, день был примерно таким же, и перед снятием сегодняшнего отчета были установлены обновления по оптимизации, то можно предположить, что эти обновления сыграли свою роль.
Ищем «узкие» места
Вы спросите, как же найти эти самые «зеленые» квадратики? Один из методов — получить информацию от тех, кто непосредственно работает с системой. Используя ее каждый день, рядовые пользователи, как никто, «чувствуют» систему. Просто опросив нескольких человек, можно найти потенциальных «кандидатов», тем более что пользователи всегда готовы поделиться наболевшим.
Другой метод — тестовые базы. На более слабом, чем промышленное, оборудовании «внезапно» начинают медленно работать отдельные рутинные операции. В промышленной среде они выполняются еще «терпимо», например 2 секунды, а на тестовой — 10, а то и 20 секунд, что уже становится заметным. И выясняется, что какая-то маленькая незаметная функция уже давно работает с плохим планом запроса.
Более инструментальный подход — AWR-отчет. В специальной секции он показывает top queries — какие из запросов заняли наибольшее DB Time, а также количество выполнений.
Могу привести еще более действенный метод — использование Oracle Enterprise Manager (EM).
Внимание! Для работы со сколько-нибудь серьезными системами использование Enterprise Manager не только желательно, но и строго необходимо!
Решаем проблемы с производительностью
Исследование проблем с производительностью в Oracle без этого инструмента сродни изучению клеток без микроскопа. EM предоставляет возможность наблюдать нагрузку в реальном времени, в разрезе самых тяжелых запросов и пользовательских сессий. Кроме того, он умеет сам выделять top неоптимальных, по его мнению, запросов. Для них (а также для любых произвольных запросов по требованию) можно провести профилирование и получить советы по оптимизации — построить определенный индекс, изменить план запроса с возможностью его зафиксировать (baseline) и так далее.
Взглянем на одно из окон EM (рис. 5). Зеленая площадь — CPU, синяя — дисковый ввод-вывод. Хорошо, когда практически вся работа заключается в этих двух видах загрузки, как в нашем случае.
Рис. 5. Отражение разных видов нагрузки в Oracle Enterprise Manager
Одна из самых мощных возможностей EM — SQL Monitoring (рис. 6). С его помощью мы можем в режиме онлайн отслеживать запросы, которые при настройках по умолчанию отвечают одному из двух критериев: либо длятся более 5 секунд, либо осуществляются в параллели. В окне мониторинга видно, на какой стадии выполнения находится запрос, таким образом, можно прогнозировать время его завершения. Окно является активным Flash Plugin и позволяет изучать практически все аспекты обработки запроса — от общего количества считанных/записанных байтов до тонкостей параллельного выполнения. При желании SQL Monitoring запроса можно сохранить в файл для последующего изучения.
В соответствующих разделах панели SQL Monitoring отражаются диаграмма Ганта, количество операций ввода-вывода, потраченное время CPU и время ожидания. И эти параметры показаны для каждой операции из плана запроса. Проанализировав данную информацию, вы поймете, можно ли запрос оптимизировать и каким образом.
Вот как в SQL Monitoring выглядит пример «плохого» запроса (по отбору данных для работы в OLTP-системе), который постепенно стал работать более 20 секунд (рис. 7).
После изучения данный запрос путем несложного переписывания был оптимизирован и стал выполняться не более 1 секунды. Пользователи были счастливы!
Иногда бывают и совсем неожиданные результаты. Так, в одной большой промышленной среде замена в маленькой функции выражения «sUser:=user» на «select user into sUser from dual» дала снижение общей нагрузки на 10%. Правда, речь шла о 8 версии Oracle.
Оптимизация OLAP-системы
И снова графики
Рассмотрим типичный вид нагрузки на OLAP-систему (рис. 8).
Сразу замечу, что все сказанное выше про оптимизацию в OLTP сохраняет свою актуальность и для OLAP. Но есть и отличия: в OLAP меньше запросов, но они более сложные и требуют большего времени на выполнение. Кроме того, зачастую их обработка осуществляется в параллельном режиме.
Кстати, о времени ожидания: в OLAP может считаться нормальным запрос, который работает порядка часа, и пользователи готовы ждать его окончания. Процесс получения финальных данных обычно требует многочасовой работы сервера и включает этапы загрузки, перегрузки, валидации, расчета и т.д. Здесь ожидания те же — чем раньше получены данные, тем лучше, но оптимизации требует весь критический путь в целом. Проще говоря, пользователи будут счастливы, если ежедневно будут получать готовые данные не в 15:00, а, скажем, в 11:00, в идеале же — к моменту начала рабочего дня.
На нашем рисунке (см. рис. 8) выделен запрос SQL-3, который лежит на критическом пути и выполняется некоторое время. Преобразуем его из горизонтального «кирпичика» в вертикальный и получим совершенно другую картину (рис. 9).
Общее время выполнения всех четырех запросов значительно сократилось (сдвинулось влево). Это и есть главный результат оптимизации OLAP.
Интересно, что обычно после такого «переворачивания» общая площадь «кирпичиков» увеличивается, то есть сервер выполняет больше работы (накладные расходы на управление параллельными процессами). Но если ресурсы для такого запроса есть, и в это время нет нужды выполнять другой критически важный запрос, то объем работы не важен — важно меньшее время выполнения.
Конечно, действовать таким образом нужно обдуманно, учитывая наличие других возможных процессов в то же самое время: ввиду нехватки ресурсов они могут быть заблокированы.
Примеры модификаций
Рассмотрим пример подобной простой модификации (рис. 10). Как видите, изначально запрос выполнялся 3,6 минуты.
После простого распараллеливания на 8 потоков запрос начал выполняться 34 секунды. При этом его DB Time сохранился прежним и составляет 3,8 минуты. В целом соотношение 228 сек / 34 сек = 6,7 и близко к искомым 8 потокам, поэтому можно считать распараллеливание успешным (рис. 11). Интересно, что второй запрос потребовал меньше ресурсов для ввода-вывода данных, что не вполне типично.
Рис. 11. Результат оптимизации путем запараллеливания обработки
Обратите внимание на еще один пример (рис. 12). Идет вставка данных во временную таблицу. Здесь уже была предпринята попытка распараллеливания, но эффективна ли она? Мы видим «одиноких копателей», которые заняты, например, задачей Load table conventional. И общая эффективность 1,7 минут wall time против 3,6 минут DB-Time никак не дает коэффициент, хоть сколько-нибудь похожий на 8.
В данном случае пришлось удалить ненужный индекс, перевести insert на прямую работу с PGA (load as select). Результат не заставил себя ждать (рис. 13). Теперь в одиночку, как положено, работает только один начальник — PX Coordinator. Все подчиненные работают группами. Время выполнения — 7 секунд, общая эффективность 57/7 — даже чуть больше 8 (ошибка округления в wall time). Как видим, запрос работает уже достаточно эффективно, и дальнейшие попытки его оптимизации большого эффекта не дадут.
И в завершение приведу еще один пример, довольно забавный. Взгляните на SQL Monitoring запроса, на который пожаловались, что он «висит» (рис. 14). На самом деле он не висел, а очень активно работал на протяжении 20 часов. За это время бедный сервер «перелопатил» 57 Тбайт! И это при общем объеме базы всего лишь 80 Гб.
Читайте также: