Cell single block physical read oracle что это

Обновлено: 03.07.2024

Итак, я знаю, что это действительно основной вопрос, но я изо всех сил пытаюсь найти ответ на Google, Wiki или Oracle. (Каждый технический документ, который я вижу, предполагает, что эти темы уже поняты)

Я видел эти понятия раньше, но я только смутно понимаю, что это связано с тем, как движок Oracle обращается к данным, хранящимся на блоках.

Вопросы

    Может ли кто-нибудь объяснить мне разницу между этими двумя типами чтения и почему в определенных ситуациях может быть более выгодным другое?
    Какова важность сканирования полного индекса с использованием одноблочного чтения и быстрого сканирования полного индекса с использованием многоблочного чтения?

Multiblock I/O означает, сколько блоков базы данных считывается с помощью одного READ-вызова операционной системы. В текущих выпусках (11gR2 и 12c) значение по умолчанию соответствует максимальному размеру ввода-вывода, который может быть эффективно выполнен. Максимальное значение размера ввода-вывода зависит от платформы и составляет 1 МБ для большинства платформ. Многоблочные операции ввода-вывода контролируются параметром базы данных DB_FILE_MULTIBLOCK_READ_COUNT.

Другими словами: Обычно в эти дни блок базы данных составляет 8kb. 8kb может включать несколько строк таблицы и даже больше индексных ключей, если они используются для записи в индексном листе. Теперь, когда вы выполняете ввод/вывод Singleblock, вы читаете 1 из этих блоков 8kb - обычно это поиск идентификатора строки по значению ключа ключа (определяют блок, в котором находится индексный ключ, и делают один блок ввода-вывода для получения блока базы данных, содержащего фактический ряд). Однако, если вы читаете много данных, потому что вы, например, скопления на огромных таблицах, обычно в средах хранилища данных, было бы менее эффективно запрашивать блок размером 8 КБ после блока 8 КБ после блока 8 КБ, когда базовая система ввода-вывода может, например, читать 1 МБ в одном физическом считывании. Поэтому Oracle выдает многоблочный ввод-вывод и запрашивает блок размером 1 МБ (128 блоков 8 КБ) в одном вызове системы READ вместо 128 отдельных запросов и, следовательно, ускоряет работу с запросами ввода-вывода.

Обратите внимание на Справочник базы данных для параметра DB_FILE_MULTIBLOCK_READ_COUNT и запрошенный размер ввода-вывода в руководстве по настройке производительности базы данных для получения дополнительной информации об этом. Latter также является отличным ресурсом для понимания производительности Oracle Database в целом.

Этим небольшим постом хотелось бы развеять одно недоразумение, связанное с анализом AWR баз данных, работающих на Oracle Exadata. Почти 10 лет я постоянно сталкиваюсь с вопросом: каков вклад Exadata Software в производительность? Или с использованием новообразованных слов: насколько «экзадатится» работа той или иной базы данных?


Часто на этот правильный вопрос, на мой взгляд, дается неверный ответ со ссылкой на статистику AWR. В ней представлен метод системных ожиданий, трактующий время отклика как сумму времени работы процессоров (DB CPU) и времени ожиданий различных классов.

С появлением Exadata в статистике AWR появились специфичные системные ожидания, связанные с работой Exadata Software. Как правило названия таких ожиданий начинаются со слова “cell” (ячейкой называется Exadata Storage сервер), из них чаще всего встречаются ожидания с говорящими названиями “cell smart table scan”, “cell multiblock physical read” и “cell single block physical read”.

В большинстве случаев доля таких Exadata-ожиданий в общем времени отклика мала, и поэтому они даже не попадают в секцию Top10 Foreground Events by Total Wait Time (в этом случае их нужно искать в разделе Foreground Wait Events). Мы с большим трудом обнаружили у наших заказчиков пример суточного AWR, в котором Exadata-ожидания попали в секцию Top10 и в сумме составили около 5%:

Event

Waits

Total Wait Time (sec)

Avg Wait

% DB time

Wait Class

SQL*Net more data from dblink

cell single block physical read

Sync ASM rebalance

cell multiblock physical read

direct path read

SQL*Net message from dblink

cell smart table scan

direct path read temp

enq: TM - contention

Из подобной AWR статистики часто делают такие выводы:

1. Вклад магии Exadata в производительность базы данных не высок — не превышает 5 %, а база данных «экзадатится» плохо.

2. Если такую базу перенести с Exadata на классическую архитектуру «сервер + массив», то производительность изменится не сильно. Потому что даже если этот массив окажется втрое медленнее системы хранения Exadata (что едва ли возможно для современных All Flash массивов), то умножив 5% на три мы получим увеличение доли ожиданий ввода-вывода до 15% ­— такое база данных наверняка переживет!

Оба этих вывода неточные, более того они искажают понимание идеи, заложенной в Exadata Software. Exadata не просто обеспечивает быстрый ввод-вывод, она работает принципиально иначе по сравнению с классической архитектурой «сервер + массив». Если работа базы данных действительно «экзадатится» — то на систему хранения переносится SQL-логика. Storage серверы благодаря ряду специальных механизмов (в первую очередь Exadata Storage Indexes, но не только) сами находят нужные данные и пересылают DB северам. Они делают это достаточно эффективно, поэтому доля характерных Exadata-ожиданий в общем времени отклика мала.

Как изменится эта доля вне Exadata? Как это скажется на производительности базы данных в целом? Лучше всего на эти вопросы ответит тестирование. Например, ожидание “cell smart table scan” вне Exadata может превратиться в такой тяжелый Table Full Scan, что ввод-вывод займет все время отклика и производительность ухудшится драматически. Именно поэтому неправильно при анализе AWR считать суммарный процент ожиданий Exadata вкладом ее магии в производительность и тем более использовать этот процент для прогнозирования производительности вне Exadata. Чтобы понять насколько «экзадатится» работа базы нужно изучать статистики AWR секции “Instance Activity Stats” (там много статистик с говорящими названиями) и сравнивать их между собой.

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

Автор: Алексей Струченко, руководитель направления БД «Инфосистемы Джет»

Оптимизатор SQL запросов Oracle достиг небывалого уровня сложности. Количество встроенных решений для предопределённых "частных случаев" иногда делает планы выполнения непредсказуемыми. Задача анализа и оптимизации SQL усложняется широким распространением параллельного выполнения и кластеров, "переписыванием" запросов "на лету", динамическим сбором статистики. В этих условиях традиционные методы анализа производительности и выполнения SQL оказываются недостаточными.

Предлагаемая читателю заметка рассказывает о новом средстве проверки выполнения и оптимизации SQL запросов в реальном времени - Active SQL Monitor Report.

Прежде всего мы проанализируем два варианта простого SQL запроса и найдём причину чрезмерного количества чтений блоков данных. Затем, используя Real Time SQL Monitoring, мы проверим наши выводы и посмотрим как можно избежать излишнего выполнения "проблемных" запросов во время анализа.

Данные для запроса

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

Проверим, действительно ли значения нашего primary key монотонно возрастают:

Теперь посмотрим, как близко находятся ("упакованы") записи внутри блоков нашего индекса, отсортированного по столбцу SALEID:

Близость значений CLUSTERING_FACTOR и LEAF_BLOCKS говорит нам о хорошей "упаковке" записей в индексе. Это сделает индекс PK_SALES_ID более "привлекательным" для оптимизатора запросов, что теоретически должно гарантировать нам самый быстрый доступ к данным таблицы.

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

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

"Хороший" SQL запрос

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

В этот раз результат немного неожиданный - индекс не был использован вообще, поскольку полное чтение всей таблицы требует приблизительно такого же количества чтений блоков из буфера (19 буферов за 7 операций чтения), как и сканирование индекса (17 буферов за 5 операций.).

Также надо заметить что при пустом buffer cache платформа Exadata позволяет операции "TABLE ACCESS STORAGE FULL" использовать в одной сессии два разных способа чтения данных в buffer cache (а не PGA) - "cell single block physical read" для доступа к заголовку сегмента и "cell multiblock physical read" для прочтения "за один раз" всех оставшихся блоков. Если же buffer cache оказывается не пустым, как в этом примере "хорошего" запроса - сессия прочитает все необходимые блоки из памяти SGA (а не PGA), опять же несмотря на способ доступа к таблице "TABLE ACCESS STORAGE FULL".

Возможно ли что Оракл выбрал неоптимальный способ чтения данных и нам надо использовать индекс?

"Плохой" SQL запрос

Заставим Оракл использовать индекс и оценим результат.

В этом случае raw trace файл показывает, что "cr" равен "cost" для обеих строк плана "INDEX FULL SCAN" и "TABLE ACCESS BY INDEX ROWID" - наша сессия читает по одному блоку за раз. Просто для полного прочтения таблицы через индекс нам понадобится один блок для "корня" индекса, 12 блоков для "листьев" индекса и 17 прочтений блоков (смотрите CLUSTERING_FACTOR в начале заметки) для доступа к строкам самой таблицы - итого 30 consistent gets - буферов, прочитанных из buffer cache, равным по размеру 8192 байт каждый.

И чем выше значение CLUSTERING_FACTOR для используемого индекса - тем большее количество обращений к диску или buffer cache будет необходимо для прочтения всей таблицы. В нашем примере индекс PK_SALES_ID имеет почти идеальную структуру и очень низкий CLUSTERING_FACTOR, в реальной ситуации полное прочтение таблицы по индексу потребует значительно больше ресурсов чем простой full table scan.

Также заметьте, что при таком запросе не используется специфический для Exadata "INDEX STORAGE FULL SCAN".

Становится понятным, что показанный выше способ анализа производительности SQL запроса весьма трудоёмок и потребует многократного выполнения запроса, очищения buffer cache, трассировки сессий и прочих специфических приёмов, недопустимых на "живой" системе.

Active SQL Monitor Report

Основное назначение этого отчёта - периодически оценивать степень выплонения длительного SQL запроса. То есть этот отчёт может показывать нам сколько процентов работы уже выполнено параллельными сессиями, и сколько ещё осталось. В дополнение, Active SQL Monitor Report собирает для нас всю использованную выше статистику для уже выполненных SQL запросов длинной более 5 секунд (и для всех параллельных запросов). Это позволяет нам увидеть всю необходимую информацию в одном месте, без блуждания по raw trace файлам - и почти полностью исключает необходимость перезапусков "медленных" запросов. Генерировать этот отчёт надо как можно быстрее - желательно сразу же после завершения "плохого" запроса.

Итак, освежим нашу память - в чём проблема? Предположим, что один из разработчиков "по-старинке" уверен что индекс надо использовать всегда. Наш специалист берёт "хороший" запрос и делает из него "плохой" запрос (см. выше). После попадания этого кода в "живую" систему наши пользователи начинают жаловаться на резкое замедление работы приложения.

Как администраторы баз данных, мы должны найти причину деградации производительности как можно скорее. Мы быстренько генерируем AWR report за последние 15-20 минут и обнаруживаем наш "плохой" запрос на месте лидера по потреблению ресурсов системы. У нас нет времени на подробное "разжёвывание" запроса, как было сделано выше. Нам надо просто как можно быстрее понять, на что же "плохой" запрос тратит время и IO ресурсы. Выплоняем следующий шаг:

Главное, что нам надо записать - SQL_ID и SQL_EXEC_ID, эти два параметра однозначно определяют о каком SQL запросе мы говорим. Найдём соответствующие значения для "плохого" отчёта с индексом.

Итак, все необходимые данные имеются - сгенерируем Active SQL Monitor Report для обоих случаев. Необходимо правильно настроить параметры вывода SQL*Plus и потом вручную отредактировать HTML файлы. Не пропустите самую первую строку "set . " - без неё ваш SQL Monitor Report будет нечитаемым.

Отредактируем оба spool файла так, чтобы в них не было лишних строк внизу и вверху и загрузим их к себе на компьютер. Используйте веб броузер, подключённый к интернету и с полностью включенными Java Script и Plugins. Я всегда пользуюсь Opera.

Открыв файл "bad.sql", на странице "Details / Plan" мы сможем увидеть точное распределение 30-ти "consistent gets" между операциями доступа к таблице по индексу - 13 чтений буферов (в нашем случае и блоков) индекса и 17 чтений таблицы. "Откопать" эту информацию иным способом достаточно сложно и долго. Кроме того, время исполнения запроса указано с точностью до микросекунды. Сравните данные со вторым отчётом "good.sql".

Теперь поэкспериментируйте с другими запросами посложнее и обратите внимание на очень полезную страницу "Activity".

Я уверен что "Active SQL Monitor Report" послужит вам отличным подспорьем в работе.

Некоторые важные для анализа производительности систем Oracle события ожидания (wait events), статистики (statistics) и вычисляемые коэффициенты (ratio), используемые при анализе производительности и отчётов Statspack/AWR

События ожидания

Дисковый ввод-вывод (I/O wait events )

db file sequential read

Среднее время этого ожидания = фактическому среднему времени чтения одного блока БД (что на уровне ОС обычно называют выборочным или случайным чтением (random read)). Типичное нормальное значение

db file scattered read
read by other session
db file async i/o submit

недокументированное событие, появившееся начиная с Oracle 11.2, проявляется при установке комбинации параметров

с точки зрения настройки производительности ожидание db file async I/O submit должно рассматриваться как db file parallel write, или как другое ожидание выполнения операции дискового ввода-вывода (например, log file switch (checkpoint incomplete)). Точное название ожидания можно получить изменив значение параметра FILESYSTEMIO_OPTIONS на отличное от NONE

direct path read

Типичные ситуации возникновения ожидания:

по умолчанию, при старте инстанса параметр _small_table_threshold устанавливается

1.9-2 % от размера буферного кэша [__]db_cache_size / db_block_size

Для форсированного включения direct path read в документах поддержки упоминается установка параметра _serial_direct_read=TRUE (доступно на уровне сессии, значение по умолчанию FALSE до 11.2.0.1 включительно)

Для отключения Dion Cho Disabling direct path read for the serial full table scan– 11g нашёл и описал событие 10949:

Соответствующая ожиданию статистика physical reads direct

direct path write / direct path write temp

Соответствующая ожиданию статистика physical write direct

db file parallel read

В 11g используется, в основном, при индексном доступе к блокам таблицы, выполняемом параллельно PX slaves процессами

async disk IO | ksfd: async disk IO
control file sequential read
Disk file operations I/O
utl_file I/O

В соответствии с документацией ожидание фиксируется при использовании процедур пакета UTL_FILE

Обработка логов (Log waits)

log file sync

Пользовательский сессия (foreground process) ожидает совершения системным (background) процессом LGWR операции записи модифицированных данных из буфера в локальный или удалённый (при SYNC конфигурации standby сервиса) redo log файл во время выполнения пользовательской сессией операции завершения транзакции COMMIT / ROLLBACK

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

Типичные причины: неудачное размещение и [коственно] малый размер файлов redo, низкая производительность и/или неудачная конфигурация подсистемы ввода-вывода*), либо высокая (возможно, избыточная?) интенсивность операций COMMIT / ROLLBACK**. Нормальное время ожидания не должно превышать нескольких миллисекунд для OLTP систем

*) Пример: несколько копий лог файлов (для «надёжности») на одном небыстром разделе RAID5 либо RAID6 (для «экономии»). Время ожидания может легко превышать и 20, и 30, и 80 ms. Oracle категорически не рекомендует RAID5 для размещения online redo log файлов

**) При невозможности изменить приложение с целью уменьшить частоту транзакций, для буферизации и выбора асинхронного режима можно рассмотреть изменение параметров COMMIT_WRITE (начиная с Oracle 10g) или COMMIT_LOGGING, COMMIT_WAIT (начиная с 11g)

На пример отчёта AWR небольшого RAC 11.2.0.3 можно видеть, что время ожидания log file sync , в основном, тратится на запись логов процессом LGWR с ожиданием log file parallel write, которое кроме того включает время обработки запросов Global Cache Service от процессов LMS с соответсвующим ожиданием gcs log flush sync:

Для диагностики log file sync и связанных ожиданий может быть полезен скрипт lfsdiag.sql с сайта поддержки Oracle:

  • параметры, влияющие на log file sync
  • гистограммы ожиданий
  • вычисляет и выводит данные о периодах наихудших средних времён ожидания log file sync из репозиториев Active Session History / AWR

Простой пример влияния конфигурации передачи логов на удалённый standby (определяемой параметром log_archive_dest_n) на ожидания log file sync и log file switch completion, первоначальная конфигурация:

log buffer space
log file switch completion
  • недостаточный размер online лог файлов
  • недостаточное количество групп лог файлов
  • недостаточная скорость записи / передачи redo
  • блокировки синхронного доступа к controlfile
log file switch (checkpoint incomplete)
log file switch (private strand flush incomplete)
LGWR wait for redo copy
wait on ATTACH

ожидание ARC | LGWR соединения с процессами RFS (Remote File Server) для выполнения архивации на удалённый сервис

Например, при безуспешных попытках архивации логов на отсутствующие/неработающие LOG_ARCHIVE_DESTINATION SERVICE, в AWR можно наблюдать сетевой таймаум

при этом соответствующий V$ARCHIVE_DEST.STATUS будет периодически менять значение VALID / ERROR

wait on SENDREQ

открытие, запись полученных redo данных, закрытие удалённых лог-файлов

wait on DETACH

завершение RFS соединения

LGWR wait on LNS
LNS wait on LGWR
LGWR-LNS wait on channel

Пример: при передаче логов на standby в SYNC режиме:

AWR показывает значительную долю ожиданий log file sync в топе:

Пользовательские процессы (JDBC Thin Client) при выполнении commit ожидают завершения операции log file sync, выполняемой системным процессом LGWR, который в свою очередь ожидает (в основном) события LGWR-LNS wait on channel, вызванного синхронной передачей логов (на standby):

После отключения SYNC режима для удалённых LOG_ARCHIVE_DEST среднее и суммарное времена ожидания log file sync снижаются на порядок (примерно в той же пропорции ускоряя бизнес-процессы, активно пишущие/обновляющие данные):

Ожидание LGWR-LNS wait on channel ожидаемо исчезает из топа ожиданий, блокирующих пользовательские операции commit:

Ожидания при работе с разделяемой памятью Oracle Shared Pool

library cache load lock
library cache lock [object handle]
library cache pin [object heap]

События, контролирующие загрузку и совместный доступ к объектам Library Cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym)

  1. сессия 368 на время выполнения процедуры sleep(1000); удерживает Library Cache Lock (на дескриптор процедуры) в состянии Null и Pin (на объект пула) в моде Share
  2. следующая по времени запуска сессия 357 для рекомпиляции той же процедуры получила Lock в состянии Exclusive и ожидает Exclusive Pin на соответствующем событии library cache pin. Pin (объект процедуры в Library Cache) блокирован сессией 368 на время выполнения в Share моде во избежание изменений
  3. последняя по времени сессия 347 ожидает Lock в Exclusive моде, кот.удерживается сессией 357 на соответствующем событии library cache lock. Получение Pin (блокировки на объект) до получения Lock (нахождение и блокировка дескриптора) невозможно
cursor: pin S wait on X (library cache pin)

Ожидание, связанное с разбором SQL при использовании mutex механизма (начиная с Oracle 10.2, аналог события library cache pin традиционного library cache механизма) во время компиляции child cursor –> см. подробное обсуждение, системные обзоры:

CURSOR_SHARING – в частности, событие оказывало значительное влияние на производительность (загрузку процессоров) при значении параметра CURSOR_SHARING = SIMILAR в присутствии гистограмм (автоматический сбор статистики по столбцам METHOD_OPT = FOR ALL COLUMNS SIZE AUTO) – OLTP DB Web приложения без использования связанных переменных в версии 10.2.

Возможные способы уменьшения ожиданий:

Отключение (исключение) гистограм распределения значений в столбцах таблиц в статистике объектов БД. Например, можно установить на уровне системы параметр METHOD_OPT = FOR ALL COLUMNS SIZE 1, который отключит исключит расчёт гистограм из процедур автоматического сбора статистики

Использование значения параметра CURSOR_SHARING=FORCE

Использование связанных переменных в приложении и значения по умолчанию параметра CURSOR_SHARING=EXACT

Ещё одним источником кратковременных всплесков ожиданий cursor: pin S wait on X / library cache lock могут быть DDL операции при наличии проблем ввода-вывода, например, при выполнении TRUNCATE партицированной таблицы можно наблюдать:

Library cache: mutex X
Cursor: pin S
kksfbc child completion

ожидание завершения процесса формирования/построения дочернего курсора, относится к фазе hard parse, часто наблюдается при большом количестве дочерних курсоров, сигнализируя о проблеме high version count

Наблюдаемый в Oracle 11g таймаут 50 мс

SGA: allocation forcing component growth

Process waiting on an immediate mode memory transfer with auto-tune SGA after a 4031 for MMAN to get the memory and post it.
Wait Time: 10 msec

Для уменьшения ожидания логично выяснить и устранить причины конкуренции за память в области shared pool SGA (ORA-4031):

  • избыточную активность процесса автоматического перераспределения памяти ASMM
  • использование и фрагментацию shared pool
  • баги Oracle

Блокировки / Enqueue разного рода

Описание типов (V$LOCK.TYPE) и параметров блокировок (V$LOCK.ID1, V$LOCK.ID2) можно получить из обзора V$LOCK_TYPE

Удельный вес разных типов блокировок (с момента старта системы) можно получить из GV$ENQUEUE_STAT

Режимы, в которых блокировки удерживаются или ожидаются сессиями в Oracle (lock mode, V$LOCK.LMODE)

При описании глобальных блокировок (global deadlock detection) в разделе Global Wait-For-Graph(WFG) трейса LMD используются другие цифровые обозначения типов блокировок (GES enqueue lock mode)

10.3.6.1 Finding Locks and Lock Holders: как найти сессии, удерживающие/ожидающие блокировки

Типичные причины из документации

Также транзакционные блокировки на SYS.MLOG$ используются Oracle при конкурирующих операциях DML (dbms_mview.refresh) / DDL с Materialized View Log:

Дапм заголовка потенциально проблемного блока 4K:

Кроме того, эта блокировка используется для сериализации доступа к [заголовку] LOB-сегмента, например при интенсивных DML и недостаточной производительности ввода-вывода можно наблюдать:

Конкуренция происходит за LOB segment header:

В этом случае уменьшить ожидание можно как увеличив производительность ввода-вывода, так и изменив метод хранения LOB:

Инсерт 10000 копий файла в TEST таблицу в 10 сессиях
время выполнения с SECUREFILE

4minutes
время выполнения с BASIC

Sequence Cache enqueue, используется для доступа к последовательностям Oracle, ожидание может наблюдаться в кластерных конфигурациях (Oracle RAC), могут быть значительно уменьшены модификацией последовательностей с опциями CACHE NOORDER

Механизм возникновения, параметры, события, обзоры в Query result cache

p2 p3 argument
tablespace_number 1 Local enqueue, to notify SMON to request some action for tablespace identified by tablespace id
tablespace_number 2 Global enqueue, to release space for releasing space in tablespace identified by tablespace id

Latch contention
latch: library cache
latch: library cache pin

значение параметра P1RAW из V$SESSION_WAIT / V$SESSION:

Определение запрашиваемых объектов бд:

latch: library cache lock
latch: shared pool

Возможные причины из документации:

  • SQL предложения не используются повторно
  • Не используются связанные переменные
  • Недостаточный размер кэша курсоров приложения (application cursor cache) [см. параметр SESSION_CACHED_CURSORS]
  • Курсоры закрываются приложением [explicitly] после каждого выполнения
  • Частые пересоединения со стороны приложения [logins and logoffs]
  • Структура объектов, используемых курсорами модифицируется (например, командой truncate)
  • Недостаточный размер shared pool
latch: cache buffers chains

Запрос к V$LATCH_CHILDREN

Объединённый запрос для определения сегментов с hot blocks по значению V$LATCH_CHILDREN.ADDR

Однако на практике внезапный рост кол-ва ожиданий:

часто сопровождается появлением/частым одновременным выполнением конкретных запросов:

, что является проблемой плана выполнения, либо конструкции запроса, и может/должен быть исправлен:

latch: object queue header operation

Кроме общей конкуренции за буферный кэш (большое кол-во чтений Gets), может сигнализировать об интенсивных операциях над CLOB переменными в PL/SQL

Например, при замедлении выполнения неких процедурных отчётных заданий (типа concurrent) в 11.2.0.3:

Цепочка ожиданий по ASH выглядит так:

gc cr multi block request
gc current multi block request
gc cr request | global cache cr request | gc cr block request
gc current block request
gc current block busy

DML (update, delete) с использованием FULL SCAN

buffer busy waits

Один из возможных случаев отражает диаграмма ожиданий из окружающей действительности:

gc buffer busy
gc buffer busy release
gc buffer busy acquire

Индикаторы невозможности немедленного получения гранта на доступ (grant access) к блокам данных локального buffer cache

  • В случае, если блокирующий Global Cache запрос (GC request) открыт локальной сессией, ожидается gc buffer busy acquire
  • Если же блокирующий GC запрос открыт сессией другого инстанса (remote instance), будет отражено ожидание gc buffer busy release

Заметный уровень ожиданий сигнализирует об одновременных попытках доступа (локально или через cluster interconnect с помощью механизма cache fusion) с получением соответсвующего уровня доступа (grant access) большим количеством пользовательских процессов к определённому набору блоков buffer cache, например:

По значению параметра P3 можно определить, что сессии конкурирую за заголовок UNDO сегмента (undo header):

Красивый пример исследования события gc buffer busy с использованием ASH можно прочитать на сайте Jeremy Schneider’а GC Buffer Busy Waits in RAC: Finding Hot Blocks

gc cr block lost / gc current block lost
gcs log flush sync

Процессы LMS, обслуживающий запросы Global Cache Service, ожидает записи логов локальным процессом LGWR:

gc cr block flush time
gc current block flush time

отражаются в AWR:

, в то время как блокирующий процесс LGWR выполняется с обычным приоритетом

DFS lock handle

ожидание глобальной блокировки (lock handle), обслуживаемой процессом Distributed Lock Manager (DLM). Слово DFS (Distributed File System) унаследовано со времён Oracle Parallel Server

Сетевые ожидания (Wait class: Network)

virtual circuit status
shared server idle wait
virtual circuit wait

комплексное ожидание, параметры:

Ожидания, наблюдаемые при параллельном выполнении запросов

PX Deq: Parse Reply

Начиная с версии 10g из частей целого запроса процесс-координатор (QC) готовит отдельные SQL для выполнения процессами PX Slaves. На этом событии QC ожидает выполнения разбора (parse & binds variable) частичных SQL параллельными серверами

PX Deq: Test for msg

Прочие ожидания

buffer exterminate

достаточно экзотическое событие ожижания, возникающего при динамическом изменении размера DB CACHE BUFFER

events in waitclass Other
resmgr:cpu quantum

В последнем случае, как правило, событие является индикатором активного выполнения других ресурсоёмких задач / запросов

Wait for stopper event to be increased

Статистики Oracle (statistics)

redo size

Объём генерируемых данных redo

Генерация redo включает в себя redo entries for lost write detection в объёме, например:

redo synch time

суммарное время ожиданий log file sync, csec

redo synch long waits

Индикатор медленной записи, в трейсе LGWR:

, либо задержек в процессе SCN broadcast acknowledge при использовании RAC, в трейсе LGWR или LMD|LMS:

gc cr block receive time
gc cr blocks received
gc current block receive time

cуммарное время ожидания клиентскими процессами получения через interconnect блоков бд в состоянии CURRENT

gc current blocks received

общее количество полученных блоков

Запрос для оценки средних времён получения блоков по Oracle Cluster мс момента запуска инстансов)

Статистика Row CR attempts показывает количество попыток выполнения updates, удовлетворяющих этим условиям [про update здесь пишется применительно к Oracle 9i, начиная с Oracle 10g механизм Row CR используется также для операций SELECT с доступом по индексу].

class slave wait

Неправильно классифицированное ожидание в версии 10.1.0.3 (непропатченный Oracle 10) ошибочно относится к классу ожиданий Other, в отчёте ADDM выглядит так

index scans kdiixs1

Popular Statistics with Database In-Memory

Коэффициенты Statspack / AWR

% Non-Parse CPU = 100*(1-(:prscpu/:tcpu))

(1-parse time CPU / CPU used by session)*100

доля процессорного времени, затраченная пользовательскими процессами (сессиями) на продуктивную работу, в отличие от подготовки к выполнению/ разбора запросов (SQL parsing)

Parse CPU to Parse Elapsd % = 100*:prscpu/:prsela

parse time CPU / parse time elapsed

Estd Interconnect traffic (KB)

формула из sprepins.sql

SQL ordered by Reads
SQL ordered by Physical Reads (UnOptimized)

image

Привет! Меня зовут Александра, я работаю в команде тестирования производительности. В этой статье расскажу базовые сведения об OEM от Oracle. Статья будет полезна для тех, кто только знакомится с платформой, но и не только для них. Основная цель статьи — помочь провести быстрый анализ производительности БД и поиск отправных точек для более глубокого анализа.
OEM (Oracle Enterprise Manager) — платформа для управления БД. OEM предоставляет графический интерфейс для выполнения большого количества операций с базами данных: резервное копирование, просмотр аварийных журналов, графиков производительности.

Performance Home

На вкладке Performance Home можно увидеть основные графики утилизации БД.

Average Runnable Process



Этот график дает общее понимание использования CPU.

№ Показатель Описание
1 Instance Foreground CPU Отображает утилизацию CPU процессами текущего инстанса, напрямую запущенными клиентом, например выполнение запросов. Список событий ожидания текущего инстанса можно посмотреть в AWR-отчете
2 Instance Background CPU Отображает утилизацию CPU фоновыми процессами текущего инстанса, например LGWR. Список событий фонового процесса текущего инстанса можно посмотреть в AWR-отчете или в официальной документации Oracle
3 Non-database Host CPU Отображает утилизацию CPU процессами, не относящимися к текущему инстансу
4 Load Average Отображает среднюю длину очереди процессов, ожидающих выполнения
5 CPU Treads/CPU Cores Отображает лимит максимально возможного использования CPU

Average Active Sessions

  • Если зафиксирован рост активных сессий, то должна расти пропускная способность (график Throughput).
  • Если Active Sessions превышает CPU Cores/CPU Threads, это свидетельствует о проблемах производительности.
  • Если зафиксирован рост времени отклика операций, но при этом активные сессии не превышают CPU, это значит, что узкое место не в CPU и нужно более детально смотреть, по каким классам события ожидания фиксируется рост, после чего можно на графике нажать на соответствующий класс и провалиться глубже в детализацию (откроется отчет ASH — Active Session History).

Throughput


Раздел Throughput отображает пропускную способность. Пропускная способность базы данных измеряет объем работы, которую база данных выполняет за единицу времени.

Пики на графике Throughput должны соответствовать пикам на графике Average Active Sessions. Если заметен рост времени ожидания, необходимо убедиться, что увеличивается пропускная способность. Если пропускная способность низкая, а время ожидания растет — необходимо изменить настройки БД.


Latency показывает задержку чтения блоков. Это разница между временем выполнения чтения и временем обработки чтения БД. Показатель должен стремиться к нулю.
Оптимальным считается значение до 10 мс. Этот график — основной показатель производительности в этом блоке. Если зафиксирован рост времени задержки, нужно посмотреть, не растет ли количество I/O операций и их вес, также на рост Latency может влиять утилизация CPU.

Статистику по I/O можно смотреть в разрезе функций, в разрезе типов и в разрезе групп потребителей ресурсов (группы пользователей). Для этого на графике необходимо выбрать соответствующий Breakdown. Графики показывают количество I/O-операций в секунду и их вес в разрезе выбранного значения Breakdown. Для большей детализации можно провалиться глубже в статистику, выбрав соответствующее значение на графике или в легенде, и посмотреть статистику именно по выбранному значению.

I/O Function



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

Можно выделить следующие категории:
№ Категория Описание
1 Фоновые процессы Включают в себя ARCH, LGWR, DBWR (полный список фоновых процессов есть в документации)
2 Активность XML DB, Streams AQ, Data Pump, Recovery, RMAN
3 Тип I/O Включает прямую запись и чтение (в том числе чтение из кэша)
4 Другое Включает операции ввода/вывода управляющих файлов

I/O Type


Выводит статистику по тяжести операций ввода-вывода. Маленькими считаются операции, которые обрабатывают до 128 КБ. К большим операциям ввода-вывода относятся: сканирование таблиц и индексов, прямая загрузка данных, резервное копирование, восстановление и архивирование.

Consumer group

Дает представление об утилизации диска в разрезе групп пользователей: показывает, какая группа пользователей выполняет операции чтения и записи в определенный период. Включает в себя фоновые процессы.

Parallel Executions

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

Services


Службы на этом графике представляют собой группы приложений. Отображаются только сессии активных служб, находящиеся в ожидании в определенный момент времени. Например, служба SYS$USERS — это установка пользовательского сеанса.

ASH Report


ASH Report (Active Session History) дает более подробную информацию по потреблению ресурсов. Чтобы перейти к графику, в меню Performance нужно выбрать пункт Performance Hub/ASH Report. Также перейти к ASH Report можно при выборе класса события ожидания на графике Average Active Session.

  • События ожидания и группы событий ожидания.
  • Группы пользователей, пользователи, сервисы, инстансы.
  • SQL-запросы.

AWR (Automatic Workload Repository) дает подробную информацию о процессах, происходящих с БД в определенный период. Для построения AWR-отчета нужно выбрать пункт меню Performance/AWR/AWR Report. Также есть возможность сравнивать два временных промежутка. Для этого нужно выбрать пункт меню Performance/AWR/Compare Period Report.
Ниже будут описаны наиболее показательные разделы AWR-отчета, описание остальных разделов можно поискать в официальной документации.

Load Profile



Здесь отображается общая информация по тому, как была загружена БД за выбранный период.

№ Параметр Описание
1 DB Time(s) Сумма времени утилизации процессора и время ожидания (без простоя)
2 DB CPU(s) Нагрузка на процессор
3 Background CPU(s) Загрузка процессора фоновыми задачами
4 Redo size Объем чтения
5 Logical reads Среднее количество логических чтений блоков
6 Block changes Среднее значение измененных блоков
7 Physical reads Физическое чтение в блоках
8 Physical writes Количество записей в блоках
9 Read I/O requests Количество чтений
10 Write I/O requests Количество записей
11 Read I/O (MB) Объем чтения
12 Write I/O (MB) Объем записей
13 IM scan rows Количество строк в In-Memory Compression Units (IMCU), которые были доступны
14 Session Logical Read IM Чтения в In-Memory
15 User calls Пользовательские вызовы
16 Parses Разборы
17 Logons Количество входов
18 Excecutes Количество вызовов
19 Rollback Количество откатов данных
20 Transacions Количество транзакций

Instance Efficiency Percentages




№ Показатель Критерии
1 Buffer nowait Если показатель меньше 95%, значит, буферы data block buffer используются неправильно. Возможно, нужно увеличить data block buffer size
2 Buffer Hit Если показатель меньше 95%, значит, буферы data block buffer используются неправильно. Возможно, нужно увеличить data block buffer size
3 Library cache hit Если показатель меньше 95% — нужно расширять shared pool (либо причина в bind-переменных)
4 Redo NOWAIT Если показатель меньше 95%, это говорит о проблеме в redo log buffer или redo log
5 Parse CPU to Parse Elapsd Показатель должен быть больше или равен 90%, тогда большинство процессов не ожидает ресурсов, что говорит о правильной работе базы данных
6 Non-Parse CPU Показатель должен приближаться к 100%, это значит, что большинство ресурсов CP используется в различных операциях, кроме parsing, что говорит о правильной работе базы данных. Если Non-Parse CPU низкий, значит, база много времени тратит на разбор запроса вместо реальной работы
7 In-memory sort Значение меньше 100 говорит о том, что сортировка идет через диск, а также есть потенциальные проблемы с PGA_AGGREGATE_TARGET,SORT_AREA_SIZE,HASH_AREA_SIZE и bitmap setting
8 Soft Parse Чем он выше, тем меньше у нас Hard Parse
9 Latch Hit Чем он выше, тем меньше мы ждем Latches (если он низкий — у нас проблемы с CPU-Bound и Latches)

Top 10 Foreground Events by Total Wait Time


В разделе находится топ-10 событий, которые ожидали ресурсов дольше остальных.

При анализе необходимо обратить внимание на класс события ожидания. Если wait class System I/O, User I/O или Other, это нормально для БД. Если класс события ожидания Concurrency, это может свидетельствовать о проблемах.
Классы события ожидания можно посмотреть в разделе Wait Classes by Total Wait Time. В разделе находится статистика по классам события ожидания с сортировкой по времени ожидания.

Описание некоторых событий ожидания:
№ Событие ожидания Описание
1 DB CPU Отображает процессорное время, затраченное на пользовательские операции над БД. Это событие должно находиться на первом месте списка
2 db file sequential read Метрика сигнализирует, что пользовательский процесс не находит нужный блок в buffer cache, загружает его с диска в SGA и ждет физического ввода/вывода
3 db file scattered read Указывает на проблему с фулл-сканами, возможно, нужны индексы
4 read by other session Может говорить о том, что размер блока слишком большой или задержка (latency) слишком большая
5 enq TX – row lock contention Событие возникает при ожидании блокировки строки для дальнейшей ее модификации DML-запросом. Если показатель больше 10%, необходимо разбираться в причинах. Более детальную информацию можно посмотреть в разделе Segments by Row Lock Waits, в котором есть сведения о том, какие таблицы были заблокированы и какими запросами
6 DB FILE SEQUENTIAL READ Если среднее значение параметра больше 100 мс, это может свидетельствовать о том, что диск работает медленно
7 LOG FILE SYNC Значение AVG WAIT более 20 мс может свидетельствовать о проблемах
8 DB FILE SCATTERED READ Если это событие выполняется — возможно, имеет смысл создать дополнительные индексы. Для более подробной информации нужно перейти к разделу Segments By Physical Read, в котором находится информация по таблицам и индексам, в которых происходит физическое чтение
9 direct path read temp ИЛИ
direct path write temp		 Эти события дают информацию по использованию временных файлов
10 Buffer Busy Wait Событие указывает на то, что несколько процессов пытаются обратиться к одному блоку памяти, то есть пока первый процесс работает с конкретным блоком памяти, остальные процессы находятся в статусе ожидания

Host CPU и Instance CPU

Здесь стоит обратить внимание на %Idle и %Total CPU. Если показатель %Idle низкий, а %Total CPU высокий, это может свидетельствовать о том, что процессор является узким местом.

Foreground Wait Class, Foreground Wait events и Background Wait Events

Показывают классы и события, которые провели в ожидании большего всего. Foreground Wait events дополняет информацию раздела Top 10 Foreground Events By Total Wait Time. Background Wait Events показывает детализацию по событиям ожидания фоновых процессов.


SQL statistics



Раздел содержит несколько таблиц со статистикой по SQL-запросам, отсортированным по определенному критерию.
Подробнее про оптимизацию запросов и примеры типичных проблем в запросах можно почитать в статье Проактивная оптимизация производительности БД Oracle.

№ Параметр Описание
1 SQL ordered by Elapsed Time Топ SQL-запросов по затраченному времени на их выполнение
2 SQL ordered by CPU Time Топ SQL-запросов по процессорному времени
3 SQL ordered by User I/O Wait Time Топ SQL-запросов по времени ожидания ввода/вывода для пользователя
4 SQL ordered by Gets Запросы к БД, упорядоченные по убыванию логических операций ввода/вывода. При анализе стоит учитывать, что для PL/SQL-процедур их количество прочитанных Buffer Gets будет состоять из суммы всех запросов в рамках этой процедуры
5 SQL ordered by Reads Этот раздел схож с предыдущим: в нем указываются все операции ввода/вывода, наиболее активно физически считывающие данные с жесткого диска. Именно на эти запросы и процессы надо обратить внимание, если система не справляется с объемом ввода/вывода
6 SQL ordered by Physical Reads (UnOptimized) В этом разделе выводятся неоптимизированные запросы. В Oracle неоптимизированными считаются все запросы, которые не обслуживаются DSFC или Exadata Cell Smart Flash Cache (ECSFC)
7 SQL ordered by Executions Наиболее часто выполняемые запросы
8 SQL ordered by Parse Calls Отображает количество попыток разбора SQL-запросов до его выполнения
9 SQL ordered by Sharable Memory Запросы, занимающие больший объем памяти общего пула SGA
10 SQL ordered by Version Count Здесь показано количество SQL-операторов экземпляров одного и того же оператора в разделяемом пуле
11 Complete List of SQL Text Показывает полный SQL-запрос, не только его хэш. В этой таблице можно найти неоптимальные запросы (например, запросы по всем столбцам таблицы «select * from. », запросы с большим количеством «like» и т. п.)

Active Session History (ASH) Report



В данной таблице находятся самые тяжелые SQL запросы, на которые приходится наибольший процент активности и наибольшее время ожидания.

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

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