Latch free oracle что

Обновлено: 05.07.2024

Проведение настройки

Работы по настройке затрагивают три аспекта: планирование производительности, настройку экземпляра и настройку кода SQL.

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


Примечание: дополнительные сведения о настройке производительности см. в документе Oracle Database Performance Timing Guide.

Планирование производительности

При планировании производительности учитывается много аспектов. Так аппаратно-программная инфраструктура должна соответствовать вашим требованиям. Поэтому рассматриваются различные варианты инвестирования в системную архитектуру.

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

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

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

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

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

Настройка экземпляра

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

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

Дисковый ввод-вывод часто является узким местом базы данных. Поэтому он требует большого внимания в самом начале реализации любой базы данных.

Конфигурация операционной системы может также повлиять на производительность базы данных Oracle. Дополнительные сведения см. в документе Oracle Database Installation Guide для конкретной платформы.

Методология настройки производительности

Методология настройки производительности

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

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

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

Сбор статистик

Статистики оптимизатора используются сервером для выбора наиболее эффективного плана выполнения каждой команды SQL. В этих статистиках отражаются детальные сведения о базе данных и хранимых в ней объектах.

Системные статистики отражают необходимые оптимизатору показатели работы аппаратуры, например, производительность ввода-вывода и использование ЦП. При выборе плана выполнения оптимизатор оценивает ресурсы ввода-вывода и ЦП, требуемые каждому запросу. Системные статистики позволяют оптимизатору запросов более точно оценить стоимость операций ввода-вывода и использования ЦП и в результате выбрать лучший план выполнения. Системные статистики собираются с помощью процедуры DBMS_STATS . GATHER_SYSTEM_STATS. Когда сервер Oracle осуществляет сбор таких статистик, он анализирует системную активность в заданном периоде времени. Системные статистики не собираются автоматически. Корпорация Oracle настоятельно рекомендует использовать для этого пакет DBMS_STATS.

Рекомендуется разрешить серверу Oracle автоматически собирать статистики оптимизатора. Задание GATHER_STATS_JOB автоматически создается вместе с базой данных. Управляет этим заданием планировщик (Scheduler). Задание собирает данные обо всех объектах базы данных, статистики которых либо утеряны, либо устарели.

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

БД изменяются регулярно, также регулярно необходимо собирать статистики, чтобы быть уверенным в том, что они точно отражают характеристики объектов базы данных. Для ручного сбора статистик используется пакет DBMS STATS. Он также применяется для обновления, просмотра, экспорта, импорта и удаления статистик.

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

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

Параметр STATISTICS_LEVEL управляет сбором всех основных статистик и влияет на работу консультантов базы данных. В нем задается уровень сбора статистик, производимого в базе данных. Значения этого параметра - BASIC, TYPICAL или
ALL.

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

Параметр TIMED_OS_STATISTICS задает интервал времени (в секундах), в течение которого Oracle собирает статистики операционной системы, когда запрос на выполнение команды поступает от клиента или когда завершается его выполнение.

Временные системные статистики автоматически собираются базой данных, когда параметр инициализации STATISTICS_LEVEL установлен в TYPICAL или ALL. Если значение параметра STATISTICS_LEVEL равно BASIC, тогда необходимо установить TIMED_STATISTICS в TRUE, чтобы включить сбор временных статистик. Отметим, что установка STATISTICS_LEVEL в BASIC отключает многие автоматические возможности и поэтому не рекомендуется.

При явном задании параметра TIMED_STATISTICS или TIMED_OS_STATISTICS в файле параметров инициализации или с помощью команд ALTER SYSTEM и ALTER SESSION, переопределяется значение, получаемое на основе параметра STATISTICS_LEVEL.

Можно выполнить запрос к представлению V$STATISTICS_LEVEL, чтобы выяснить, на какие параметры оказывает воздействие значение, установленное в параметре STATISTICAL_LEVEL.

События ожиданий в Oracle

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

Следует помнить, что это только симптомы, но не реальные причины проблем. События ожиданий сгруппированы в классы: Administrative, Application, Cluster, Commit, Concurrency, Configuration, Idle, Network, Other, Scheduler, System I/O и User I/O.

В базе данных Oracle более 800 событий ожидания. В их число, например, входят следующие: free buffer wait, latch free, buffer busy waits, db file sequential read иdb file scattered read.

Чтобы просмотреть события ожиданий в ЕМ, перейдите на страницу Performance.

События ожиданий отражаются на графике в разделе Sessions: Waiting and Working, как это показано на слайде. Щелкнув на ссылке событий определенного класса, а затем, используя интерфейс Top Sessions, можно перейти к просмотру конкретных событий, связанных с сеансами. В приводимом примере наибольшие ожидания были связаны с операциями чтения файлов.

Перечень наиболее общих событий ожиданий Oracle см. в документе Oracle Database Reference 10g.

Статистики системы

Для эффективной диагностики проблем производительности необходимы статистики.

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


Статистики событий ожиданий

Все возможные события ожиданий отражаются в представлении V$EVENT_NAME. Кумулятивные статистики для всех сеансов хранятся в представлении V$SYSTEM EVENT.
В нем отражаются итоговые данные об определенных ожиданиях, начиная с момента запуска экземпляра.
При поиске и устранении неисправности необходимо знать, ждал ли процесс какой-либо ресурс.


Основные системные статистики

Все основные системные статистики (systemwide statistics) отражаются в представлении V$STATNAME. В базе данных Oracle Database 10g их примерно 330.

Собранные сервером статистики можно просмотреть, используя представление V$SYSSTAT. Оно содержит кумулятивные итоговые данные, подсчитанные с момента запуска экземпляра.


Вывод основных системных статистик

Основные системные статистики классифицируются на основе областей настройки и целей отладки. Статистики объединяются в следующие классы: общие данные о работе экземпляра, общие данные о работе журнального буфера, блокировки, работа кэша буферов базы данных и т.д.

Все события ожидания определенного класса можно также просмотреть, запросив представление V$SYSTEM WAIT CLASS. Пример (результат отформатирован):


Общие статистики SGA


Сервер отражает все подсчитанные статистики использования оперативной памяти в представлении V$SGASTAT. Его можно использовать для просмотра кумулятивных итогов использования компонентов SGA с начала запуска экземпляра. Пример:


В примере показана только часть выходных результатов.

Вывод статистик, связанных с сеансом

Информация о текущих сеансах пользователей может быть получена с помощью представления V$SESSION. Например V$SESSION позволяет выяснить, это сеанс пользователя или же сеанс, созданный серверным процессом БД (BACKGROUND).

Для того, чтобы выявить ресурсы или события, которые ждет активный сеанс, можно запросить либо представление V$SESSION, либо V$SESSION_WAIT.

Сервер Oracle показывает статистики пользовательских сеансов в представлении V$SESSTAT. Сведения о событиях ожидания сеанса отражаются в представлении
V$SESSION_EVENT.

Кумулятивные значения статистик в основном доступны через динамические представления производительности, например, V$SESSTAT и V$SYSSTAT. Следует отметить, что кумулятивные данные в динамических представлениях сбрасываются во время остановки экземпляра базы данных.

В представлении V$MYSTAT выводятся статистики текущего сеанса.
Дополнительно можно запросить через представление V$SESSMETRIC значения метрик производительности всех активных сеансов. В этом представлении отражается использование ЦП, количество физических чтений, число полных разборов (hard parses) и коэффициент логических чтений (logical read ratio).

Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd. Перевод: zCarot

Процент попаданий в буфер блока данных (кэш данных)
если процент ниже 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

Экземпляр Oracle состоит из ряда процессов, обращающихся к общим сегментам памяти (SGA и другие общедоступные ресурсы). Поэтому они могут испортить информацию друг друга. Следовательно, во многих случаях требуется обеспечить механизм, который при использовании одним процессом общедоступных ресурсов (например, участки памяти) запрещает другим процессам изменять эти данные. Таким механизмом в СУБД Oracle являются блокировки, то есть специальные переменные, показывающие, занят или свободен некоторый ресурс.

  • механизм управления одновременным доступом к таблицам и строкам данных (DML locks, DDL locks);
  • внутренние блокировки сервера (enqueues, internal locks, latchs (защелки)), то есть механизм защиты сервером своих внутренних структур данных и блокирования общедоступных ресурсов.

    • В данной статье под блокировками будем понимать внутренние блокировки сервера и защелки.

    В СУБД Oracle блокировки делятся на два непересекающихся класса: защелки (latch) и очереди (enqueues).

    Защелки - это двоичные переменные, фактически переключатели-триггеры, которые применяются на короткое время и защищают структуры памяти. Защелка имеет только два состояния – занята или свободна. Наиболее известные из защелок – shared pool latch, library cache pin, library cache lock, cache buffer chains, redo allocation latch, redo copy.

    Защелки в СУБД Oracle могут запрашиваться в двух режимах: “willing-to-wait” и “no-wait” (= immediate). Если процесс имеет возможность продолжать работу, не получив запрашиваемую защелку, то это запрос no-wait (например, redo copy latch). Если процесс не может продолжать работу, не получив запрашиваемую блокировку, то это режим willing-to-wait.

  • Если защелка свободна, то запрос на нее удовлетворяется. Конец.
  • Если защелка занята, то процесс циклично повторяет запросы на защелку _spin_count раз. Если запрос удовлетворен, то Конец.
  • Если запрос на защелку не удовлетворен, то процесс “засыпает” на 1/100 секунды, после чего переходит к п.2. Если запрос опять не удовлетворен, то в каждом следующем цикле длительность интервала удваивается, после чего следует переход к п.2.

    • В отличие от защелок, очереди запросов (enqueue) действительно образуют упорядоченную очередь FIFO. Каждый запрос в очереди, кроме порядкового номера, отражает еще и режим запроса (share, exclusive). Например, запросы на чтение могут выполняться одновременно, не блокируя друг друга. Если запрос на блокировку enqueue не может быть удовлетворен, то он ставится в очередь. Порядковые номера в очереди запрашиваются через системные вызовы ОС (семафоры).

    С блокировками типов enqueues и latches всегда связана процедура, которая возвращает блокировку к предыдущему состоянию, если процесс, удерживающий блокировку, зависнет или аварийно завершится. В СУБД Oracle эту функцию выполняет процесс PMON.

    Проблема

    В общем случае блокировка - это некая булевская переменная, которая показывает, что ресурс свободен или занят. Если значение переменной 0 (false), то это означает, что блокировка свободна и любой процесс может изменить ее значение на 1 (true), а затем обращаться к защищаемому ресурсу. Если значение блокировки true, то процессу следует подождать, поскольку кто-то еще пользуется этим ресурсом.

    Вопрос: можно ли программным путем гарантированно заблокировать ресурс?

    Ответ: нет, невозможно! Например, два процесса могут одновременно опросить одну и ту же переменную и, убедившись, что ее значение равно 0, установят ее значение в 1. Такой сценарий не редкость в многопроцессорных ЭВМ.

    Возможен и другой сценарий. Допустим, что один процесс считывает значение переменной блокировки и обнаруживает, что она равна 0. Но прежде, чем первый процесс успевает изменить ее на 1 (отвлекся на обработку прерывания или был снят с процессора по истечении отведенного ему кванта времени), управление получает второй процесс, который тоже считывает значение переменной блокировки и изменяет ее на 1. Когда первый процесс снова получит управление, он тоже заменит переменную блокировки на 1, и оба процесса будут считать себя исключительными владельцами ресурса.

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

    Механизмы блокирования

    Искомое решение требует участия аппаратного обеспечения. Процессоры многопроцессорных ЭВМ имеют специальную команду, которая в разных источниках называется TSL (Test and Set Lock), CAS (Compare and Swap) или LL/SC (Load Link /Store Conditions).

    Процессор, выполняющий эту команду, блокирует шину памяти так, чтобы остальные процессоры не могли обратиться к оперативной памяти, и затем выполняет команду ‘test’, читая соответствующую ячейку памяти. Если возвращаемое значение равно нулю (false), то это значит, что переменная свободна, и процессор выполняет команду ‘set’, которая записывает в эту переменную значение 1 (true), и шина памяти разблокируется. Освобождение блокировки выполняется путем записи 0 (false) в переменную блокировки.

    Если другой процессор позже попытается запросить блокировку, то команда ‘test’ возвратит ему значение 1 (true), означающее, что блокировка уже установлена. В этом случае второму процессу придется подождать некоторое время, а затем снова запросить блокировку. При выполнении каждой TSL-команды происходит блокирование шины ЭВМ.

    Таким образом, команда типа TSL аппаратно обеспечивает неделимость обращения к переменной блокировки, ибо процесс может быть снят с выполнения либо до начала команды, либо после ее окончания. В результате чего блокировки СУБД ORACLE спускаются на уровень аппаратного обеспечения и блокируют шину ЭВМ. Блокирование шины сервера означает, что во время выполнения команды TSL все остальные процессоры и процессы не могут получить доступ к оперативной памяти и вынуждены ждать завершения операции (однако они могут обращаться к данным в своем локальном кеше).

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

    Механизм разблокировки

  • непрерывно выполнять команду TSL в цикле с тем, чтобы непрерывно опрашивать значение переменной;
  • с помощью системного вызова sleep(time) процесс может на некоторое время “заснуть” и тем самым освободить процессор, отдав управление в ОС, чтобы она “разбудила” этот процесс по истечении периода time. Sleep() – это системный вызов, в результате которого вызывающий процесс снимается с процессора и переводится в неактивное состояние на время указанное в параметре этой команды. Wakeup(pid) – обратная к sleep команда, которой на вход передается один параметр – pid процесса, который следует запустить на ЦПУ.

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

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

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

    Влияние на производительность

    О необходимости борьбы с hard & soft parse и использования связываемых переменных написаны сотни статей. Мотивировка: в ожидании блокировки сессии выстраиваются в очередь и простаивают до ее освобождения. А также: использование литералов приводит к конкуренции пользовательских сессий за shared pool latch и library cache latch. Однако негативные последствия процесса hard & soft parse на этом не заканчиваются.

    Механизм блокирования системной шины фактически замораживает функционирование сервера на короткий период времени. А это означает, что если теоретически в одном часе имеется 3600 секунд, то в результате блокирования шины сервер фактически функционирует не 3600 секунд в час, а 3599, 3598, … и, возможно, менее. То есть, слишком часто блокируемый сервер работает не все отведенное для работы время. Причем частота блокирования растет пропорционально количеству процессоров и процессов. В результате чего добавление очередного процессора может не приводить к увеличению производительности всего сервера в целом.

    • для управления буферным кешем. Блокировки вызываются при вставке/удалении/перемещении блока в кеше. Если учесть, что кешей может быть пять штук (2k,4k,8k,16k,32k), в каждом по три типа (Default, Keep,Recycle), поэтому для всех 15 областей памяти потребуется до 30 блокировок, по две блокировки на кеш;
    • для управления журнальным буфером: минимум по две блокировки на каждый log_buffer (2*log_parallelism);
    • для управления Library Cache & Shared Pool: 16 блокировок на library cache lock + 26 блокировок на library cache pin. (В одном отчете Statspack мне пришлось увидеть такую картину “Hard parses: 12.48/секунду” - очевидно, что высокой производительности от такой системы ждать не приходится). Особенно стоит отметить блокировки на library cache pin. Эта блокировка вызывается при каждом выполнении PL/SQL;
    • 26 блокировок для выполнения операций над Row Cache;
    • блокировка на SCN;
    • блокировка на SMON;
    • блокировкина обращение к файлам БД (по одной блокировке на файл данных);
    • блокировка на транзакцию над контрольным файлом;
    • блокировка, управляющая job (работами);
    • блокировкана выделение/удаление сегментов в табличных пространствах TEMP и UNDO;
    • блокировкана выполнение действий над файлом паролей и файлом инициализации (ALTER SYSTEM SET …).

    Для полноты картины попробуем численно оценить влияние блокировок на производительность сервера, для чего рассмотрим типичный отчет Statspack, секцию “Latch Activity for DB”. Понятно, что этот расчет довольно приблизительный, но, на мой взгляд, довольно показательный.

    У меня в наличии есть подходящий отчет для 16-процессорного сервера, частота каждого процессора которого составляет 1200МГц. Из отчета Statspack для этого сервера следует, что СУБД Oracle выполняет более 650 тысяч блокировок в секунду (точное значение 651801,9). По справочникам можно уточнить, что команды типа TSL для процессора UltraSparcIII - CASA и CASXA – требуют для своего выполнения 32 цикла. Тогда доля времени, в течение которого системная шина заблокирована, составит 651802*32/1200МГц = 0,0174, то есть 1,74% всего рабочего времени, другими словами 62,64 секунды в час.

    Управление поведением

    Поведением процесса, запрашивающего блокировку, управляет параметр _spin_count. Если запрашиваемая блокировка занята, то процесс повторяет запросы на защелку в цикле _spin_count раз, после чего засыпает на 1/100 секунды, после чего опять опрашивает и опять засыпает и т.д., причем в каждом следующем периоде длительность интервала удваивается.

    Документация по СУБД Oracle не дает информации о вычислении значения _spin_count. Для версий 8, 9 и 10 это значение равно 2000. Интересно, как получено это значение?

    Методики анализа блокировок могут быть статическими (анализ кода), либо динамическими (анализ работы программы).

    С точки зрения исследования исполняемого кода, если разработчики СУБД Oracle знают, что процесс, работающий под защитой блокировки, в среднем выполняет не более Х команд, то и параметр _spin_count можно было бы установить в значение, покрывающее этот промежуток. Однако реально сложно однозначно сказать, сколько времени займет выполнение 2000 команд. Команды процессора различаются по длительности, иногда довольно сильно, от нескольких тактов, например, инкремент – 1 такт, до нескольких десятков – деление требует 42 такта.

    Удовлетворительный результат можно получить только в том случае, если только код, выполняющийся под блокировкой, специально написан так, чтобы он выполнялся за время, не большее, чем _spin_count. А еще и прерывания оказывают свою роль. В общем, статический механизм дает очень приблизительное значение для _spin_count.

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

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

    • высокая загрузка сервера. Если процесс, завладевший защелкой, выполняется на загруженном процессоре и снимается с процессора, не освободив ее, то конкуренция за такую защелку будет весьма высокой.
    • продолжительный захват защелки. Чаще всего это происходит из-за того, что структура памяти (связный список), защищенный конкретной защелкой, стал слишком длинным. Пример – список свободных участков памяти внутри buffer cache, shared pool.

    Способы оптимизации

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

    Возможно, в будущем мы можем стать свидетелями появления нового процесса в Oracle типа “прогнозировщик блокировок”, а также нового узла в ЦПУ, целью которого является динамически идентифицировать начало и конец критической секции кода, выполняемой под блокировкой. Идея этого метода в том, продолжать вычисления, не обращая внимания на блокировку. Ключевым моментом здесь является то, что значительная часть исполнимого кода может не зависеть от значения этой переменной блокировки. И после каждой выполненной команды (или после каждой сотни команд) можно будет опрашивать переменную блокировки. Таким образом, запрашивающий процесс будет выполнять не холостой цикл, а полезную работу. В результате влияние блокировок на производительность будет минимальным. Главная задача при этом состоит в определении в исполнимом коде процесса максимально длинной последовательности команд, которые можно безопасно выполнить независимо от значения блокировки.

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

    Таким образом, узел ЦПУ - “прогнозировщик блокировок” будет динамически идентифицировать начало и конец критической секции с целью оптимизации блокирования.

    Вторым по порядку и простейшим “тупым” методом повышения быстродействия, на мой взгляд, является создание отдельной быстрой шины и выделенной только под переменные блокировки памяти. Тогда блокировки можно будет быстро ставить и быстро снимать, а все остальные процессоры будут обращаться к общей памяти по неблокируемой шине.

    В ОС Solaris, начиная с 7 версии, появилось понятие adaptive mutex. В этом случае процесс, прежде чем запросить блокировку, проверяет, удерживает ли ее какой-нибудь другой процесс, и если да, то первый процесс проверяет, находится ли удерживающий процесс на процессоре или нет. Если процесс, удерживающий блокировку, выполняется на процессоре, то запрашивающий процесс переходит к стандартному алгоритму – в цикле выполняет TSL. Если удерживающий процесс не находится на процессоре, то запрашивающий процесс освобождает свой процессор и переходит в состояние ожидания.

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

    Справедливым возражением, относительно единого и универсального параметра _spin_count будет сказать, что не все блокировки одинаково длительны. Для того чтобы правильно настроить ожидание за защелку, следует знать особенности каждой конкретной защелки, то есть разные защелки могут иметь существенно разное среднее время удержания. В связи с этим вызывает сомнение, что один единственный параметр будет достаточным для всех защелок в СУБД. На месте разработчиков Oracle я бы, вероятно, для каждой защелки (или для каждого класса защелок с похожим поведением) определил свой параметр _spin_count.

    Заключение

    В процессе работы над этой статьей мне попало в руки исследование Лаборатории Компьютерных Архитектур университета Карнеги Мелон [8], в котором сравниваются СУБД Oracle и DB2 по активности блокировок, которая возникает в процессе работы. И в этом состязании СУБД Oracle показывает относительно неплохой результат: при одинаковой нагрузке в БД дополнительная активность, создаваемая блокировками в СУБД DB2, составляет 40% от системной (system) нагрузки и 18% от пользовательской (user), а в СУБД Oracle системное время выполнения только 20% и пользовательское время 12%.

    Автор выражает благодарность сотруднику компании“Открытые технологии” Александру Иванову за внимание и полезные советы при подготовке данной статьи.

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