Некоторые важные для анализа производительности систем Oracle события ожидания (wait events), статистики (statistics) и вычисляемые коэффициенты (ratio), используемые при анализе производительности и часто упоминаемые в отчётах Statspack/AWR
События ожидания (wait events)
В документации есть таблица о типичных причинах частовстречающихся ожиданий - Wait Events and Potential Causes
Ожидания, связанные с дисковым вводом-выводом (I/O wait events)
Oracle: IO Waits by Kyle Hailey – отличное описание на английском (с картинками)
db file sequential read
Среднее время этого ожидания = фактическому среднему времени чтения одного блока БД (average time to read single block , то что на уровне ОС обычно называют выборочным или случайным чтением (random read)). Типичное нормальное значение ~10 ms (10 ms – это значение по умолчанию параметра IOSEEKTIM системной статистики из sys.aux_stats$), в реальных системах может быть значительно меньше при использовании RAID controller cache + OS cache. В зависимости от соотношения (очереди на выполнение операций ввода-вывода)/(производительность системы I/O) может многократно меняться. Полезно сравнить это время ожидания со значениями, используемыми Oracle CBO для расчётов – значением параметра SREADTIM, либо IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED при наличии или отсутствии WORKLOAD системной статистики (sys.aux_stats$)
db file scattered read
Фактическое среднее время многоблочного чтения файлов БД (на уровне ОС называется последовательным чтением – disk sequential read). CBO использует для расчётов значения MREADTIM либо IOSEEKTIM + DB_FILE_MULTIBLOCK_READ_COUNT*DB_BLOCK_SIZE / IOTFRSPEED при наличии или отсутствии WORKLOAD системной статистики (sys.aux_stats$)
read by other session
“сессия ожидает блок данных, который в настоящий момент считывается в буферный кэш (buffer cache) другой сессией. До версии 10g событие учитывалось в группе ожиданий buffer busy wait“
db file async i/o submit
недокументированное событие, появившееся начиная с Oracle 11.2, проявляется при установке комбинации параметров
disk_asynch_io TRUE filesystemio_options none
с точки зрения настройки производительности ожидание db file async I/O submit должно рассматриваться как db file parallel write, или как другое ожидание выполнения операции дискового ввода-вывода (например, log file switch (checkpoint incomplete)). Точное название ожидания можно получить изменив значение параметра FILESYSTEMIO_OPTIONS на отличное от NONE
См. на Металинке ‘db file async I/O submit’ when FILESYSTEMIO_OPTIONS=NONE [ID 1274737.1]
direct path read
Документация: “Возникает при чтении пользовательским процессом данных с диска напрямую в PGA (в отличие от чтения в buffer cache SGA)
В синхронном режиме ввода-вывода каждое ожидание соответствует запросу на физическое чтение. При использовании асинхронного ввода-вывода, [пользовательский] процесс Oracle способен совмещать генерацию запросов на чтение данных с обработкой блоков, уже находящихся в PGA. [И только] когда процесс пытается получить доступ к блоку, который ещё не был прочитан с диска в PGA, генерируется вызов (wait call) и обновляется статистика этого события. Таким образом, количество ожиданий не обязательно совпадает с кол-вом запросов на чтение (в отличие от ожиданий db file scattered read и db file sequential read)
Типичные ситуации возникновения ожидания:
- Сортировка большого (по сравнению с размером PGA или sort_area_size) объёма данных, после записи во временный сегмент, данные считываются с использованием операций direct path read temp
- Использование параллельные процессами (parallel slaves)
- Пользовательский серверный процесс обрабатывает данные в PGA быстрее, чем система ввода-вывода может считывать очередные блоки данных (buffers). Может быть индикатором недостаточной производительности ввода-вывода”
Scattered Read, Sequential Read, and Direct Path Read
Операция direct path read также применяется при непараллельном сканировании больших таблиц (serial direct path read) – см. High ‘direct path read’ waits in 11g [ID 793845.1]: “Начиная с Oracle 11g произошли изменения правил при выборе между операциями direct path reads и чтением через buffer cache (ожидание db file scattered read) при сканировании таблиц [full table scan].
В версии 10g, сканирование “больших” таблиц выполнялось через буферный кэш (по умолчанию). В 11g, решение о выполнении чтения данных с использованием direct path или через буферный кэш основывается на размере таблицы, размере буферного кэша и др.статистиках.
Direct path reads быстрее, чем scattered reads и меньше влияет на производительность др.процессов, т.к. исключает конкуренцию за latches“
Документ How does Oracle load data into the buffer cache for table scans ? [ID 787373.1] описывает правила выбора метода сканирования “больших” таблиц (или разделения таблиц на большие и маленькие - smallness logic):
- Если кол-во блоков меньше или равно параметру _small_table_threshold Oracle сканирует объект через buffer cache, считая это более эффективным, чем использование direct read
- Любой объект (таблица) размером менее 2% буферного кэша будет сканироваться через buffer cache без использования direct load
В блоге Alex’а Fatkulin 11G adaptive direct path reads — what is the cached/dirty blocks threshold? можно найти исследование некоторых параметров, которые берутся в расчёт Oracle при выборе использования direct path read:
- размер (в блоках) таблицы ~ _small_table_threshold умноженный на 5
- cached blocks threshold – кол-во блоков таблицы, уже находящихся в буферном кэше SGA
- dirty blocks threshold- кол-во изменённых блоков таблицы, находящихся в буферном кэше SGA, не сброшенных на диск процессом DBWR
по умолчанию, при старте инстанса параметр _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:
$ oerr 10949 "Disable autotune direct path read for full table scan" // *Cause: // *Action: Disable autotune direct path read for serial full table scan. //
SQL> alter session set events '10949 trace name context forever, level 1';
Соответствующая ожиданию статистика physical reads direct
direct path write / direct path write temp
Документация: “Ожидание выполнения операции записи (write call) возникает, когда пользовательский процесс пишет блоки данных данные напрямую (directly) из PGA (в отличие от DBWR, пишущего на диск из buffer cache). Direct path writes выполняется при след.операциях: дисковые сортировки (direct path write temp), параллельные DML, direct-path INSERTs, параллельные create table as select и некоторые операции с LOB
Аналогично direct path reads, количество ожиданий не обязательно совпадает с кол-вом запросов на запись в асинхронном режиме”
Соответствующая ожиданию статистика physical write direct
log file sync
Пользовательский сессия (foreground process) ожидает совершения системным процессом (background process) LGWR операции записи модифицированных данных из лог буфера в redo log файл во время выполнения пользовательской сессией операции завершения транзакции COMMIT / ROLLBACK. Ожидание может быть заметно в случае низкой скорости записи процессом LGWR или высокой конкуренции за системные ресурсы (диск, процессор). Типичные причины: неудачное размещение и [коственно] малый размер лог файлов (online redo logs), низкая производительность и/или неудачная конфигурация подсистемы ввода-вывода*), либо высокая (возможно, избыточная?) интенсивность операций COMMIT / ROLLBACK**. Нормальное время ожидания не должно превышать нескольких миллисекунд для OLTP систем
*) Пример: несколько копий лог файлов (для «надёжности») на одном небыстром разделе RAID5 либо RAID6 (для «экономии»). Время ожидания может легко превышать и 20, и 30, и 80 ms. Oracle категорически не рекомендует RAID5 для размещения online redo log файлов
**) При невозможности изменить приложение с целью уменьшить частоту транзакций, для буферизации и выбора асинхронного режима можно рассмотреть изменение параметров COMMIT_WRITE (начиная с Oracle 10g) или COMMIT_LOGGING, COMMIT_WAIT (начиная с 11g)
WAITEVENT: “log file sync” Reference Note [ID 34592.1]:
Ожидание log file sync может состоит из следующих компонент:
1. сессия пользователя посылает вызов системному процессу LGWR, который начинает обработку запроса, если не занят обработкой др.вызовов
2. LGWR собирает/готовит необходимые для записи redo данные и вызывает операцию ввода/вывода
3. операция записи log write
4. обработка результатов записи процессом LGWR
5. LGWR посылает сообщение пользовательской сессии сообщение о выполнении операции записи
6. сессия пользователя продолжает работу
Шаги 2 и 3 суммируются в статистике redo write time
Шаг 3 соответствует системному событию ожидания (Background Wait Events) log file parallel write
Шаги 5 и 6 могут занимать значительное время при увеличенной системной загрузке (load average) … после посылки сообщения пользовательскому процессу о завершении выполнения операции, ОС может потребоваться время до очередного запуска пользовательского процесса на выполнение…
Для подробной диагностики log file sync и связанных ожиданий может быть полезен скрипт lfsdiag.sql с сайта поддержки Oracle:
выводит инит-параметры, влияющие на log file sync:
INST_ID NAME VALUE ------- ---------------------------------------- ------------------------------------------------------------------------------------ ... 1 commit_wait 1 commit_write 1 log_archive_config dg_config=(...) 1 log_archive_dest_1 location="USE_DB_RECOVERY_FILE_DEST", ... 1 log_archive_dest_2 service="...", LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0... 1 log_archive_dest_3 location="/oracle/oradata/arch",... ... 1 log_archive_local_first TRUE 1 log_archive_max_processes 4 ...
строит гистограммы ожиданий:
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT ---------- ---------------------------------------- --------------- ---------- 1 log file sync 1 2103095 1 log file sync 2 378667 1 log file sync 4 129655 ...
вычисляет и выводит данные о периодах наихудших средних времён ожидания log file sync times из репозиториев Active Session History:
ASH WORST MINUTES FOR LOG FILE SYNC WAITS: APPROACH: These are the minutes where the avg log file sync time was the highest (in milliseconds). MINUTE INST_ID EVENT TOTAL_WAIT_TIME WAITS AVG_TIME_WAITED ---------- ---------- ------------------------------ ----------------- ---------- ----------------- Aug19_1620 1 log file sync 77045.483 134 574.966 ASH LFS BACKGROUND PROCESS WAITS DURING WORST MINUTES: APPROACH: What is LGWR doing when 'log file sync' waits are happening? LMS info may be relevent for broadcast on commit and LNS data may be relevant for dataguard. If more details are needed see the ASH DETAILS FOR WORST MINUTES section at the bottom of the report. MINUTE INST PROGRAM EVENT TOTAL_WAIT_TIME WAITS AVG_TIME_WAITED ---------- ---- ----------------------------------- ------------------------------ ----------------- ---------- ----------------- Aug19_1620 1 httpd@web (TNS V1-V3) log file sync 77045.483 134 574.966 Aug19_1620 1 oracle@db01 (LGWR) log file parallel write 12604.117 31 406.584 Aug19_1620 1 oracle@db01 (LGWR) control file parallel write 360.423 1 360.423 Aug19_1620 1 oracle@db01 (LGWR) LGWR-LNS wait on channel 66.504 3 22.168 ASH DETAILS FOR WORST MINUTES: APPROACH: If you cannot determine the problem from the data above, you may need to look at the details of what each session is doing during each 'bad' snap. Most likely you will want to note the times of the high log file sync waits, look at what LGWR is doing at those times, and go from there... SAMPLE_TIME INST SESSION_ID PROGRAM EVENT TIME_WAITED P1 P2 P3 ------------------------- ---- ---------- ------------------------- ------------------------------ ----------- ---------------------- -------------------- ------------ 19-AUG-10 04.20.00.287 PM 1 29 httpd@web01 (TNS V1-V3) log file sync 253.580 buffer#: 7883 sync scn: 49309834 : 0 19-AUG-10 04.20.00.287 PM 1 218 httpd@web01 (TNS V1-V3) log file sync 224.572 buffer#: 7905 sync scn: 49309840 : 0 19-AUG-10 04.20.00.287 PM 1 267 httpd@web01 (TNS V1-V3) .000 driver id: 1413697536 #bytes: 1 : 0 19-AUG-10 04.20.00.287 PM 1 389 oracle@db01 (DBW0) db file async I/O submit 90.900 requests: 1 interrupt: 0 timeout: 0 19-AUG-10 04.20.00.287 PM 1 401 httpd@web01 (TNS V1-V3) .000 address: 9956322944 number: 150 tries: 0 19-AUG-10 04.20.00.287 PM 1 452 httpd@web01 (TNS V1-V3) .000 driver id: 1413697536 #bytes: 1 : 0 19-AUG-10 04.20.00.287 PM 1 583 oracle@db01 (LGWR) log file parallel write 252.110 files: 2 blocks: 44 requests: 2
и из данных AWR:
AWR WORST AVG LOG FILE SYNC SNAPS: APPROACH: These are the AWR snaps where the average 'log file sync' times were the highest. SNAP_ID INST BEGIN END NAME TOTAL_WAIT_TIME TOTAL_WAITS AVG_TIME_WAITED ---------- ---- ------------ ------------ ------------- ----------------- ----------- ----------------- 820 1 Aug19_1700 Aug19_1800 log file sync 34213170.246 3155585 10.842 AWR REDO WRITE STATS VERSION SNAP_ID INST STAT_NAME VALUE MILLISECONDS ----------------- ---------- ---- ----------------- ---------- ------------- 11.2.0.1.0 820 1 redo writes 4033636 11.2.0.1.0 820 1 redo write time 1168710 11687100.000 AWR LFS AND OTHER RELATED WAITS FOR WORST LFS AWRs: APPROACH: These are the AWR snaps where the average 'log file sync' times were the highest. Look at related waits at those times. SNAP_ID INST NAME TOTAL_WAITS TOTAL_WAIT_TIME AVG_TIME_WAITED ---------- ---- ---------------------------------------- ----------- ----------------- ----------------- 820 1 log file switch completion 311 86998.089 279.737 820 1 LNS wait on ATTACH 1 37.473 37.473 820 1 log file sync 3155585 34213170.246 10.842 820 1 LGWR real time apply sync 2701284 27436464.579 10.157 820 1 LNS wait on SENDREQ 4408624 6836162.167 1.551 820 1 LGWR-LNS wait on channel 4467993 5375756.663 1.203 820 1 log file parallel write 8067272 6153707.621 .763
log file switch completion
ожидание завершения процесса переключения лог файлов (online redo logs), вероятная причина – неправильный (малый) размер online лог файлов (redo logs), недостаточное количество групп лог файлов (redo log groups)
log file switch (checkpoint incomplete)
ожидание переключения лог файлов по причине незаконченной операции checkpoint – записи процессом DBWR модифицированных блоков бд из кэша (buffer cache) на диск
Data Guard Wait Events [ID 233491.1] описывает следующие системные ожидания (background wait events) процессов LGWR | ARCH при выполнении сетевых операций в зависимости от атрибутов ARCH, LGWR, SYNC, ASYNC параметра LOG_ARCHIVE_DEST_n:
ARCH wait on ATTACH
LGWR wait on ATTACH
ожидание ARC | LGWR процессами RFS соединения для выполнения архивации на удалённый сервис
Например, при безуспешных попытках архивации логов на отсутствующие/неработающие log archive destination, в AWR можно наблюдать сетевой таймаум ~ 352 сек.:
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
ARCH wait on ATTACH 10 .0 3,525 352549 0.0
при этом соответствующий V$ARCHIVE_DEST.STATUS будет периодически менять значение VALID / ERROR
ARCH wait on SENDREQ
LGWR wait on SENDREQ
ARCH wait on DETACH
LGWR wait on DETACH
LNS wait on ATTACH
LNS wait on SENDREQ
LNS wait on DETACH
LGWR wait on full LNS buffer
LGWR wait on LNS
LNS wait on LGWR
LGWR-LNS wait on channel
отражает время ожидания сообщений по KSR (Control message Service Routine?) каналу процессами LoG WriteR (LGWR) или LGWR Network Server (LNS)
Пример. При передаче redo log данных на standby серверы в SYNC режиме:
SQL> select a.status, a.target, a.ARCHIVER, a.DESTINATION, a.PROCESS, a.TRANSMIT_MODE, a.AFFIRM, a.VALID_TYPE 2 from V$ARCHIVE_DEST a where dest_id in (1,2,3) 3 / STATUS TARGET ARCHIVER DESTINATION PROCESS TRANSMIT_MODE AFFIRM VALID_TYPE ------ ------- -------- ---------------- ---------- ------------- ------ --------------- VALID PRIMARY ARCH +DATA ARCH SYNCHRONOUS NO ALL_LOGFILES VALID STANDBY LGWR standby-db1.net LGWR PARALLELSYNC YES ONLINE_LOGFILE VALID STANDBY LGWR standby-db2.net LGWR PARALLELSYNC YES ONLINE_LOGFILE
AWR показывает значительную долю ожиданий log file sync в топе:
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 57,572 38.6
db file sequential read 3,390,943 25,810 8 17.3 User I/O
log file sync 47,988 22,466 468 15.0 Commit
При этом пользовательские процессы (JDBC Thin Client) при выполнении commit ожидают завершения операции log file sync, выполняемой системным процессом LGWR, который в свою очередь ожидает (в основном) события LGWR-LNS wait on channel, вызванного синхронной передачей логов на standby базы:
SQL> select * from (
2 select w1.SQL_ID, w1.EVENT WAIT_EVENT, en.wait_class, w1.PROGRAM waiting_process, h1.PROGRAM holding_process, h1.EVENT hold_event, count(*)
3 from v$active_session_history w1, v$active_session_history h1, dba_hist_event_name en
4 where w1.event = 'log file sync'
5 and w1.EVENT_ID = en.event_id
6 and w1.BLOCKING_SESSION = h1.SESSION_ID
7 and w1.BLOCKING_SESSION_SERIAL# = h1.SESSION_SERIAL#
8 and w1.SAMPLE_TIME = h1.SAMPLE_TIME
9 group by w1.SQL_ID, w1.EVENT, en.wait_class, w1.PROGRAM , h1.PROGRAM, h1.EVENT
10 order by count(*) desc
11 ) where rownum 12 /
SQL_ID WAIT_EVENT WAIT_CLASS WAITING_PROCESS HOLDING_PROCESS HOLD_EVENT COUNT(*)
------------- -------------- ----------- ----------------- ---------------------------- ------------------------- --------
log file sync Commit JDBC Thin Client oracle@primary-db.ru (LGWR) LGWR-LNS wait on channel 13440
log file sync Commit JDBC Thin Client oracle@primary-db.ru (LGWR) log file parallel write 2867
После отключения SYNC режима для удалённых LOG_ARCHIVE_DEST среднее и суммарное времена ожидания log file sync снижаются на порядок (примерно в той же пропорции ускоряя бизнес-процессы, активно пишущие/обновляющие данные):
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
...
log file sync 48,947 0 2,105 43 1.0 2.4
При этом LGWR-LNS wait on channel ожидаемо исчезает из топа ожиданий, блокирующих пользовательские операции commit:
SQL> select * from (
2 select w1.SQL_ID, w1.EVENT WAIT_EVENT, en.wait_class, w1.PROGRAM waiting_process, h1.PROGRAM holding_process, h1.EVENT hold_event, count(*)
3 from v$active_session_history w1, v$active_session_history h1, dba_hist_event_name en
4 where w1.event = 'log file sync'
5 and w1.EVENT_ID = en.event_id
6 and w1.BLOCKING_SESSION = h1.SESSION_ID
7 and w1.BLOCKING_SESSION_SERIAL# = h1.SESSION_SERIAL#
8 and w1.SAMPLE_TIME = h1.SAMPLE_TIME
9 group by w1.SQL_ID, w1.EVENT, en.wait_class, w1.PROGRAM , h1.PROGRAM, h1.EVENT
10 order by count(*) desc
11 ) where rownum 12 /
SQL_ID WAIT_EVENT WAIT_CLASS WAITING_PROCESS HOLDING_PROCESS HOLD_EVENT COUNT(*)
------------- -------------- ----------- ----------------- ---------------------------- ------------------------ ----------
log file sync Commit JDBC Thin Client oracle@primary-db.ru (LGWR) log file parallel write 2921
log file sync Commit JDBC Thin Client oracle@primary-db.ru (LGWR) enq: CF - contention 139
db file parallel read
“… кроме recovery, операция используется при buffer prefetching [опережающем чтении блоков данных] как оптимизация I/O (заменяя несколько одноблочных чтений (single-block reads))…”
В 11g используется при индексном доступе к блокам таблицы, выполняемом параллельно PX slaves процессами
async disk IO | ksfd: async disk IO
Ожидание выполнения асинхронной операции ввода-вывода, описание для 9.2 WAITEVENT: “async disk IO” Reference Note [ID 170357.1]
http://www.saptechies.com/oracle-wait-events/: “… может наблюдаться в следующих ситуациях: создание табличных пространств, создание и расширение файлов данных, операции RMAN backup, архивирование логов процессами ARCH, AUTOEXTEND расширений файлов (например, при операциях INSERT)”
control file sequential read
блокирующее чтение данных из контрольного файла, доступ регулируется блокировкой CF – contention:
SQL> select event, count(*) 2 from v$active_session_history 3 where (sample_time, session_id, session_serial#) in 4 (select sample_time, blocking_session, blocking_session_serial# 5 from v$active_session_history 6 where event = 'enq: CF - contention' 7 and blocking_session_status <> 'GLOBAL') 8 group by event 9 order by count(*) desc 10 / EVENT COUNT(*) ---------------------------------------------------------------- ---------- control file sequential read 900 control file parallel write 61 ...
см. также enq: CF – contention
Не учитывается в ожиданиях пользовательского ввода-вывода, например, в V$SQL.USER_IO_WAIT_TIME
Ожидания при работе с разделяемой памятью Oracle
cursor: pin S wait on X (library cache pin)
WAITEVENT: “cursor: pin S wait on X” Reference Note [ID 1298015.1]:
- P1 = idn = Mutex identifier = V$SQL.hash_value
- P2 = value = Mutex value (includes details of holder)
High order bits contain the session id of the session holding the mutex
Low order bits contain a reference count (which should be 0 if there is an X mode holder)- P3 = where = The high order bits of P3 give a number which represents a location in the Oracle code where the mutex was requested from …
… сессия ожидает получить shared mutex pin на курсор в то время как другая сессия удерживает exclusive mutex pin на тот же курсор. Т.о. сессия вынуждена ждать пока блокирующая в X mode сессия освободит ресурс (release the mutex)…
Mutexes – локальные структуры текущей системы (инстанса) в RAC
select h.sid SID_WAITING_MUTEX,
s.sql_id,
s.sql_text,
decode(trunc(h.p2 / 4294967296),
0,
trunc(h.p2 / 65536),
trunc(h.p2 / 4294967296)) SID_HOLDING_MUTEX,
m.MUTEX_TYPE,
m.LOCATION MUTEX_LOCATION
from v$session h, v$sqlarea s, x$mutex_sleep m
where h.event = 'cursor: pin S wait on X'
and h.p1 = s.HASH_VALUE
and decode(trunc(h.p3 / 4294967296),
0,
trunc(h.p3 / 65536),
trunc(h.p3 / 4294967296)) = m.location_id(+)
and nvl(m.mutex_type, 'Cursor Pin') like 'Cursor Pin%';
SID_WAITING_MUTEX SQL_ID SQL_TEXT SID_HOLDING_MUTEX MUTEX_TYPE MUTEX_LOCATION
----------------- ------------- -------------- ----------------- ---------- --------------
1017 6vbz13st2s8p0 INSERT INTO " 968
Ожидание, связанное с разбором SQL при использовании mutex механизма (начиная с Oracle 10.2, аналог события library cache pin традиционного library cache механизма) во время компиляции child cursor –> см. подробное обсуждение, системные обзоры:
V$MUTEX_SLEEP – статистика
V$MUTEX_SLEEP_HISTORY – история
Связанные параметры:
_KKS_USE_MUTEX_PIN = FALSE – отключает использование mutex механизма, см. 564719.1 Catupgrd.sql Hangs waiting for Cursor: Pin S Wait On X
CURSOR_SHARING – в частности, событие оказывало значительное влияние на производительность (загрузку процессоров) при значении параметра CURSOR_SHARING = SIMILAR в присутствии гистограмм (автоматический сбор статистики по столбцам METHOD_OPT = FOR ALL COLUMNS SIZE AUTO) – OLTP DB Web приложения без использования связанных переменных в версии 10.2.
Связанные проблемы:
high version count – см. 261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE
unsafe binds – см. 731468.1 QUERIES HAVING HUGE VERSION COUNTS WHEN CURSOR_SHARING=SIMILAR
Возможные способы уменьшения ожиданий:
Отключение (исключение) гистограм распределения значений в столбцах таблиц в статистике объектов БД. Например, можно установить на уровне системы параметр METHOD_OPT = FOR ALL COLUMNS SIZE 1, который отключит исключит расчёт гистограм из процедур автоматического сбора статистики
SQL> exec dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE 1');
Использование значения параметра CURSOR_SHARING=FORCE
Использование связанных переменных в приложении и значения по умолчанию параметра CURSOR_SHARING=EXACT
Ожидание может быть заметно при проблемах свободного места в shared pool – см. ASMM: ORA-04031 и «cursor: pin S wait on X»
Library cache: mutex X
WAITEVENT: “library cache: mutex X” [ID 727400.1]
Cursor: pin S
Последние 2 события связаны с высокой конкуренцией за частоиспользуемые объекты library cache, которая, начиная с Oracle 11.2.0.2 может быть уменьшена “клонированием” (созданием нескольких копий объекта в library cache) - подробное описание технологии -> Divide and conquer the “true” mutex contention
SGA: allocation forcing component growth
Doc:
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
The waits for “SGA: allocation forcing component growth” indicates that SGA is inadequate. It looks like there is no enough memory available in SGA for the growth of components.
Для уменьшения ожидания логично выяснить и устранить причины конкуренции за память в области shared pool SGA (ORA-4031):
- избыточную активность процесса автоматического перераспределения памяти ASMM
- использование и фрагментацию shared pool
- баги Oracle
ASMM: ORA-04031 и «cursor: pin S wait on X»
ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql
Блокировки / Enqueue разного рода
102925.1 Tracing sessions: waiting on an enqueue:
“Зачастую слово “блокировка” (“lock”) используется в качестве синонима к слову enqueue – очередь, ожидание очереди. Термин enqueue используется [Oracle] для обозначения механизма блокировки (locking mechanism), детали которого отражены в системном обзоре V$LOCK. Термин блокирование (“locking”) будет использоваться для следующих событий: запрос на постановку в очередь (requesting an enqueue) [для доступа к разделяемому ресурсу], ожидания доступа (waiting on an enqueue) и блокирование доступа со стороны других процессов во время использования (blocking others while holding the enqueue).
Блокировка (lock) – это ресурс, который требуется удерживать для получения доступа к ресурсу. У Oracle есть два типа блокировок (locks): enqueues (очереди) и latches (защёлки) …” [с различными механизмами доступа]
Описание типов (V$LOCK.TYPE) и параметров блокировок (V$LOCK.ID1, V$LOCK.ID2) можно получить из обзора V$LOCK_TYPE
Удельный вес разных типов блокировок (с момента старта системы) можно получить из GV$ENQUEUE_STAT
SQL> select * from GV$ENQUEUE_STAT order by CUM_WAIT_TIME desc;
INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
---------- -- ---------- ----------- ---------- ----------- -------------
2 TX 1135032 2175 1132833 2192 4137530
1 TX 363081 719 362528 549 1913420
2 TM 1488558 3589 1488072 483 43320
1 TM 474879 774 474788 89 21800
2 KO 3032 809 3032 0 11780
2 WF 190 100 190 0 8260
2 TS 18 13 17 1 4610
Режимы, в которых блокировки удерживаются или ожидаются сессиями в Oracle (lock mode, V$LOCK.LMODE)
- 1 – null (NULL)
- 2 – row-S (SS) – Sub[set] Share
- 3 – row-X (SX) – Sub[set] eXclusive
- 4 – share (S)
- 5 – S/Row-X (SSX)
- 6 – exclusive (X)
При описании глобальных блокировок (global deadlock detection) в разделе Global Wait-For-Graph(WFG) трейса LMD используются другие цифровые обозначения типов блокировок (GES enqueue lock mode)
#define KJUSERNL 0 /* no permissions */ (Null) #define KJUSERCR 1 /* concurrent read */ (Row-S (SS)) #define KJUSERCW 2 /* concurrent write */ (Row-X (SX)) #define KJUSERPR 3 /* protected read */ (Share) #define KJUSERPW 4 /* protected write */ (S/Row-X (SSX)) #define KJUSEREX 5 /* exclusive access */ (Exclusive)
10.3.6.1 Finding Locks and Lock Holders: как найти сессии, удерживающие/ожидающие блокировки
SELECT DECODE(request,0,'Holder: ',' Waiter: ') || sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0) ORDER BY id1, request;
enq: TX – row lock contention
Типичные причины из документации
“… ожидание блокировки в mode 6 [V$LOCK.LMODE=6 - exclusive (X)]: наблюдается когда сессия ожидает освобождения блокировки уровня строки (row level lock), удерживаемой другой сессией”
“… ожидание блокировки в mode 4 [V$LOCK.LMODE=4 - share (S)] может возникать если сессия ожидает [завершения транзакции, начатой другой сессией] для избежания возможных дубликатов уникального индекса”
“… ожидание блокировки в mode 4 [V$LOCK.LMODE=4 - share (S)] … если 2 сессии планируют обновить строки, контролируемые тем же значением bitmap-индекса (bitmap index fragment)”, пример:
SQL> select blocking_session, event, dba_objects.owner||'.'||dba_objects.object_name req_object, sid as blocked_sid, sql_text 2 from v$session, dba_objects, v$sql 3 where v$session.ROW_WAIT_OBJ# = dba_objects.object_id 4 and v$session.event = 'enq: TX - row lock contention' 5 and v$session.sql_id = v$sql.sql_id 6 / BLOCKING_SESSION EVENT REQ_OBJECT BLOCKED_SID SQL_TEXT ---------------- ------------------------------ ---------------------- ------------ ----------------------------------------------------------------- 682 enq: TX - row lock contention SCOTT.SOME_BITMAP_IDX 458 update some_table set used = 1 where id in (5392963) and used = 0 SQL> select index_type from dba_indexes where index_name = 'SOME_BITMAP_IDX'; INDEX_TYPE --------------------------- BITMAP SQL> select sid, type, id1, id2, lmode, request, block from v$lock where type = 'TX' and sid in (458,682); SID TYPE ID1 ID2 LMODE REQUEST BLOCK ---------- ---- ---------- ---------- ---------- ---------- ---------- 458 TX 2097192 60978 0 4 0 458 TX 786493 96568 6 0 0 682 TX 2097192 60978 6 0 1
Также по теме:
62354.1 TX Transaction locks – Example wait scenarios
15476.1 FAQ about Detecting and Resolving Locking Conflicts
Про анализ взаимых блокировок (deadlocks, сопровождаются сообщениями в alert.log: ORA-04020: deadlock detected while…, ORA-00060: Deadlock detected. More info in file название трейс-файла) с примерами различных типов Deadlock graph из трейс-файлов – читать:
Mark Bobak Understanding and Interpreting Deadlocks. Presented at Hotsos 2006
Также по теме deadlock:
62365.1 What to do with “ORA-60 Deadlock Detected” Errors
131885.1 ORA-04020 : Analyzing the Deadlock Graph
Для анализа нечасто возникающей проблемы с блокировками может быть использована историческая информация из AWR обзора DBA_HIST_ACTIVE_SESS_HISTORY (event, sql_id, blocking_session, current_obj#,..)
Также в Oracle 11.1 в ожидании enq: TX – row lock contention может наблюдаться сессия, пытающаяся выполнить ALTER INDEX REBUILD [ONLINE]для индекса таблицы, подлежащей под перестраиваимой в этот момент матвьюшкой:
SQL> SELECT DECODE(request,0,'Holder: ',' Waiter: ') || 2 sid sess, id1, id2, lmode, request, type 3 FROM V$LOCK 4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0) 5 ORDER BY id1, request; SESS ID1 ID2 LMODE REQUEST TYPE ------------ ---------- ---------- ---------- ---------- ---- Holder: 713 3604494 77585 6 0 TX --exec dbms_refresh.refresh - [complete, atomic] Waiter: 510 3604494 77585 0 4 TX --ALTER INDEX REBUILD
enq: TX – allocate ITL entry
“… может наблюдаться когда сессии нужно заблокировать строку в блоке бд, в то время как одна или несколько др.сессий уже блокируют строки в том же блоке, занимая все выделенные ITL-слоты. Обычно в этом случае Oracle динамически добавляет следующий ITL-слот [не более MAXTRANS, или 48% размера блока - INITRANS Relationship with DB_BLOCK_SIZE. [ID 151473.1]], что может быть невозможно при отсутствии свободного места в блоке…”
Стандартная рекомендация – пересоздание (например, MOVE) сегмента с увеличением значения INITRANS, либо уменьшение времени выполнения транзакций
Дапм заголовка потенциально проблемного блока 4K:
Block header dump: 0x054c339b Object id on Block? Y seg/obj: 0x1753 csc: 0x78c.1b2a3560 itc: 2 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0028.008.0022698e 0x10c69323.bd71.05 --U- 4 fsc 0x0000.1b2a3752 0x02 0x002e.005.00198290 0x0f06390b.7823.13 C--- 0 scn 0x078c.1b29d9be # 2 ITL слота bdba: 0x054c339b data_block_dump,data header at 0x7fc44665ce5c =============== tsiz: 0xfa0 # TotalSIZe = 4000 bytes hsiz: 0xae pbl: 0x7fc44665ce5c 76543210 flag=-------- ntab=1 # N of TABles = 1 nrow=78 # N of ROWs = 78 frre=-1 fsbo=0xae fseo=0xb8 avsp=0xa # AVailable SPace = 10 bytes tosp=0xa
- недостаточно места для 3-го ITL-слота размером 24 байта
enq: TX – index contention
“… транзакция во время добавления значения в индекс (inserting a row in an index) вынуждена ожидать завершения операции разделения индексного блока (index block split), производимой другой транзакцией”
enq: TM – contention
“Наиболее вероятной причиной ожиданий блокировок типа TM (TM locks) является использование ограничений по внешнему ключу (foreign key constraints) для неиндексированных столбцов (constrained columns). Во избежание проблемы нужно проиндексировать столбцы с ограничениями по внешнему ключу (foreign key columns)” Скрипт Т.Кайта для нахождения таблиц с неиндексированными FK столбцами (с учётом порядка следования столбцов в FK и в индексе) Ожидание также может быть заметным при выполнении конкурентных операций direct path insert – см. direct-path INSERT и ожидания enq: TM – contention
enq: SQ – contention
Sequence Cache enqueue, используется для доступа к последовательностям Oracle, ожидание может наблюдаться в кластерных конфигурациях (Oracle RAC), могут быть значительно уменьшены модификацией последовательностей с опциями CACHE NOORDER
SQL> alter sequence my_rac_seq cache 10000 noorder;
, которые официально рекомендуются для Oracle RAC конфигураций, если не противоречат логике приложения
enq: RC – Result Cache: Contention
Механизм возникновения, параметры, события, обзоры в Query result cache
В 11.1.0.7 параметр _result_cache_timeout (maximum time (sec) a session waits for a result) определяем макс.время ожидания, отображаемое в поле SECONDS_IN_WAIT (v$session_wait | v$session_wait). При этом ожидать получения result cache следующие сессии могут долго – сотни и тысячи секунд – пока первая не получит результатов запроса
enq: KO – fast object checkpoint
Ожидание вызывается необходимостью checkpoint‘а на уровне сегмента / записи всех изменённых блоков сегмента из буферного кеша SGA на диск/ для последующей операции [serial] direct path reads – см. отличное объяснение Tanel Poder’а на форуме OTN
Latch contention
Со стороны настройки производительности, является индикатором других проблем (приложения, конфигурации системы) и требуют анализа причин. Или, по словам Anjo Kolk “…latch contention – это симптом, а не проблема”
22908.1 What are Latches and What Causes Latch Contention
latch: library cache
latch: library cache pin
WAITEVENT: “library cache pin” Reference Note [ID 34579.1]:
“Запрос для определения сессий, удерживающих (holding) и/или запрашивающих (requesting) pins on the object (специальная форма блокировки объекта бд в Library Cache), указанный в параметре P1 события ожидания:”
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req" FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='&P1RAW';
значение параметра P1RAW из V$SESSION_WAIT / V$SESSION:
select p1raw from v$session_wait where event like '%library cache pin%';
Определение запрашиваемых объектов бд:
SELECT kglnaown "Owner", kglnaobj "Object_to_pin" FROM x$kglob WHERE kglhdadr in (select p1raw from v$session_wait where event like '%library cache pin%') ;
How to analyze ORA-04021 or ORA-4020 errors? [ID 169139.1]
How to Find the Blocker of the ‘library cache pin’ in a RAC environment? [ID 780514.1] – через последовательные запросы dba_kgllock (объединение x$kgllk и x$kglpn), x$kglob (Kernel Generic Library cache objects – список объектов), v$session на разных инстансах
How to Find which Session is Holding a Particular Library Cache Lock [ID 122793.1] с использованием:
- oradebug dump systemstate 266 / alter session set events ‘immediate trace name systemstate level 10′
- X$KGLLK
Mark Bobak’s script для определения блокера и блокируемого объекта при library cache pin
Причины:
- выполнение DDL (Grant/Revoke/Drop/Create or replace) с выполняемой в настоящий момент PL/SQL процедурой ORA-4021 ‘LIBRARY CACHE PIN’ and PL/SQL blocks [ID 264476.1]
latch: library cache lock
latch: shared pool
Ожидания типов latch: library cache [pin|lock] и latch: shared pool указывают на проблемы с частым разбором или совместным использованием SQL курсоров (SQL parsing or sharing). “Рекомендуется проанализировать обзоры V$SQLAREA / V$SQLSTATS в поисках запросов с относительно большим количеством разборов (parse calls) либо с большим количеством дочерних курсоров (child cursors) – столбец V$SQLAREA.VERSION_COUNT…”
Возможные причины из документации:
- SQL предложения не используются повторно
- Не используются связанные переменные
- Недостаточный размер кэша курсоров приложения (application cursor cache) [см. параметр SESSION_CACHED_CURSORS]
- Курсоры закрываются приложением [explicitly] после каждого выполнения
- Частые пересоединения со стороны приложения [logins and logoffs]
- Структура объектов, используемых курсорами модифицируется (например, командой truncate)
- Недостаточный размер shared pool
latch: cache buffers chains
“… используется для разделения/регулирования доступа к списку буферов буферного кэша (to protect a buffer list in the buffer cache). … используются во время поиска, добавления или удаления буферных блоков (buffer) из кэша. Соперничество на этом latch обычно означает, что есть активно используемый запросами блок или группа блоков (также известные как hot blocks).
Для определения активно используемой цепочки буферов (buffer chain) … рекомендуется проанализировать статистику для latches типа cache buffers chains с помощью системного обзора V$LATCH_CHILDREN. Если будет обнаружен child latch типа cache buffers chains со сравнительно большими (относительно других child latch того же типа) значениями GETS, MISSES, и SLEEPS, значит имеет место соперничество за этот child latch.
Этот latch характеризуется адресом (memory address), определённом в столбце ADDR. Используйте значение столбца ADDR в соединении с таблицей X$BH для определения списка блоков, доступ к которым регулируется с помощью этого latch. Например, получив адрес (V$LATCH_CHILDREN.ADDR) активно используемого latch типа cache buffers chains, с помощью запроса можно определить data_object_id, файл данных (FILE#) и номер блока (DBABLK):
SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH as "Touch count" FROM X$BH WHERE HLADDR = 'address of latch' ORDER BY TCH;
X$BH.TCH – количество обращений к буферу (touch count). Высокое значение X$BH.TCH указывает на активно используемый запросами блок (hot block).
Каждый latch отвечает за доступ к нескольким блокам буферного кэша… Любой блок с высоким значением TCH может быть hot block. [статистика буферного кэша X$BH может быстро меняться и поэтому] выполните запрос несколько раз для определения блока, постоянно появляющегося в первых строках запроса. После определения hot block, зная номера файла и блока, сделайте запрос к DBA_EXTENTS для определения сегмента данных
SELECT OBJECT_NAME, SUBOBJECT_NAME FROM DBA_OBJECTS WHERE DATA_OBJECT_ID = &obj;
В этом запросе переменной &obj нужно присвоить значение столбца OBJ предыдущего запроса из X$BH”
163424.1 How To Identify a Hot Block Within The Database Buffer Cache
Запрос к V$LATCH_CHILDREN
select CHILD# "cCHILD" , ADDR "sADDR" , GETS "sGETS" , MISSES "sMISSES" , SLEEPS "sSLEEPS" from v$latch_children where name = 'cache buffers chains' order by SLEEPS desc;
Объединённый запрос для определения сегментов с hot blocks по значению V$LATCH_CHILDREN.ADDR
select /*+ RULE */ x.hladdr, e.owner ||'.'|| e.segment_name segment_name, e.extent_id extent#, x.dbablk - e.block_id + 1 block#, x.tch, l.child#, decode(x.state,0,'FREE',1,'XCUR',2,'SCUR',3,'CR',4,'READ', 5,'MREC',6,'IREC',7,'WRITE',8,'PI',9,'MEMORY',10,'MWRITE', 11,'DONATED',x.state) state, decode(x.state,3,cr_scn_bas,NULL) scn_bas from sys.v_$latch_children l, sys.x$bh x, sys.dba_extents e where x.hladdr in (&ADDR) and e.file_id = x.file# and x.hladdr = l.addr and x.dbablk between e.block_id and e.block_id + e.blocks -1 order by x.tch desc, 2, 3;
Кластерные ожидания – Wait class “Cluster”
gc cr multi block request
gc cr request | global cache cr request | gc cr block request
WAITEVENT: ”global cache cr request” Reference Note [ID 69048.1]
gc current block request
ожидания блоков в статусе consistent read (cr) (для операций чтения) или current – для операций DML или чтения (SELECT). Зависимости запрашиваемых статусов блоков от операций
gc current block busy
DML (update, delete) с использованием FULL SCAN
gc buffer busy
“… ожидание события buffer busy на соседней системе (remote instance)”. Красивый пример исследования события с использованием ASH можно прочитать на сайте Jeremy Schneider’а GC Buffer Busy Waits in RAC: Finding Hot Blocks
P1 = file#
P2 = block#
P3 = id (Reason Code)/Block Class# in 10g, – см. Dion Cho:Parameter3 of gc buffer busy/gc current request wait event:
SQL> select rownum, class from v$waitstat; ROWNUM CLASS ------ ------------------ 1 data block 2 sort block 3 save undo block 4 segment header 5 save undo header 6 free list 7 extent map 8 1st level bmb 9 2nd level bmb 10 3rd level bmb 11 bitmap block 12 bitmap index block 13 file header block 14 unused 15 system undo header 16 system undo block 17 undo header --> to_number(substr(to_char(:p3, 'xxxxx'), length(to_char(:p3, 'xxxxx'))-1, 2), 'xxxxx') = 2 * usn + 15 18 undo block --> to_number(substr(to_char(:p3, 'xxxxx'), length(to_char(:p3, 'xxxxx'))-1, 2), 'xxxxx') = 2 * usn + 16
gc buffer busy release | gc buffer busy acquire | buffer busy waits
Заметный уровень ожиданий сигнализирует об одновременных попытках доступа (локально или через cluster interconnect с помощью механизма cache fusion) с получением соответсвующего уровня доступа (grant access) большим количеством пользовательских процессов к определённому набору блоков buffer cache, например:
SQL> select event, sql_text, plan_operation, object_type, object_name, p1text, p1, p2text, p2, p3text, p3, waits from (
2 select event, s.sql_text, ash.sql_plan_operation as plan_operation, o.object_type, o.object_name, ash.p1text, ash.p1, ash.p2text, ash.p2, ash.p3text, ash.p3,
3 count(*) as waits,
4 RANK() OVER (PARTITION BY event order by count(*) desc) AS COUNT_RANK
5 from dba_hist_active_sess_history ash, dba_objects o, v$sqlarea s
6 where blocking_session is not null
7 and snap_id between 253975 and 253976
8 and ash.current_obj# = o.data_object_id
9 and event in ('gc buffer busy release',
10 'buffer busy waits',
11 'gc buffer busy acquire')
12 and ash.sql_id = s.sql_id
13 group by event, s.sql_text, ash.sql_plan_operation, o.object_type, o.object_name, ash.p1text, ash.p1, ash.p2text, ash.p2, ash.p3text, ash.p3
14 ) where COUNT_RANK <= 1
15 order by waits desc
16 /
EVENT SQL_TEXT PLAN_OPERATION OBJECT_TYPE OBJECT_NAME P1TEXT P1 P2TEXT P2 P3TEXT P3 WAITS
----------------------- --------------------------------------------------------- -------------- ----------- ----------- ------ -- ------ ------ ------ -- -----
gc buffer busy release SELECT ... FROM T_EXPORT WHERE ... FOR UPDATE SKIP LOCKED FOR UPDATE TABLE T_EXPORT file# 10 block# 195066 class# 1 415
gc buffer busy acquire SELECT ... FROM T_EXPORT WHERE ... FOR UPDATE SKIP LOCKED FOR UPDATE TABLE T_EXPORT file# 10 block# 194960 class# 1 224
buffer busy waits SELECT ... FROM T_EXPORT WHERE ... FOR UPDATE SKIP LOCKED FOR UPDATE TABLE T_EXPORT file# 11 block# 287016 class# 1 76
gc cr block lost / gc current block lost
Статистика потерянных блоков глобального кэша (gc – Global Cache), появление этих событий в Top 5 Events указывает на проблему или неэффективную обработку пакетов interconnect интерфейса на уровне конфигурации оборудования / ОС – рекомендации по диагностике gc lost blocks diagnostics [ID 563566.1]
DFS lock handle
Troubleshooting ‘DFS lock handle’ waits
Сетевые ожидания (Wait class: Network)
virtual circuit status
shared server idle wait
virtual circuit wait
комплексное ожидание, параметры:
- p1 circuit# номер ожидаемого circuit
- p2 type тип операции: p2=2 – ожидание ответа клиента – “If the shared servers are waiting on operation 2 then it indicates that it is waiting on the client for some input”
SQL> select STATE, P1TEXT, P1, P2TEXT, P2, SECONDS_IN_WAIT, WAIT_TIME_MICRO from v$session_wait where event = 'virtual circuit wait'; STATE P1TEXT P1 P2TEXT P2 SECONDS_IN_WAIT WAIT_TIME_MICRO ------------------- --------- ---------- ------- ---------- --------------- --------------- WAITING circuit# 96 type 2 15 14697304 WAITING circuit# 80 type 2 23 22766118
Прочие ожидания
buffer exterminate
достаточно экзотическое событие ожижания, возникающего при динамическом изменении размера DB CACHE BUFFER
259137.1 When Does “Buffer Exterminate” Wait Event Occur ?
events in waitclass Other
События отражаются (группируются в event_class Other) в V$SESSION_EVENT, в V$SESSION_WAIT и SQL_TRACE отображаются без группировки – см. пример исследования Another use case for WaitProf – diagnosing “events in waitclass Other”
resmgr:cpu quantum
“Сессия ожидает выделения кванта ЦПУ (quantum of cpu). Событие возникает при использовании resource manager, ограничивающего выделение ресурса процессора [для сессий]. Для уменьшения ожидания рекомендуется увеличение процессорных квот (CPU allocation) текущей группе (consumer group) ожидающих сессий”
В версиях 11.1.* может наблюдаться при отключенном resource manager – High “Resmgr:Cpu Quantum” Wait Events In 11g Even When Resource Manager Is Disabled [ID 949033.1]
Статистики Oracle (statistics)
redo size
Для исторического анализа не хватает данных в AWR обзоре DBA_HIST_SESSMETRIC_HISTORY, который, как и DBA_HIST_FILEMETRIC_HISTORY (соответствующие таблицы WRH$_FILEMETRIC_HISTORY, WRH$_SESSMETRIC_HISTORY), не заполняется ни в Oracle 10g, ни в Oracle 11g при значении параметра CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING на платформах Linux x86_64, Windows x86 – см. замечания и предложения аргентинских товарищей и описания багов на Metalink
gc cr block receive time
“cуммарное время ожидания клиентскими процессами получения через interconnect блоков бд в состоянии Consistent Read (CR)“
gc cr blocks received
“общее количество полученных блоков”
gc current block receive time
cуммарное время ожидания клиентскими процессами получения через interconnect блоков бд в состоянии CURRENT
gc current blocks received
общее количество полученных блоков
Запрос для оценки средних времён получения блоков по Oracle Cluster мс момента запуска инстансов)
select
'GC CR BLOCKS',
b1.inst_id,
b2.value "BLOCKS RECEIVED",
b1.value "RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1,
gv$sysstat b2
where b1.name in ('gc cr block receive time', 'global cache cr block receive time')
and b2.name in ('gc cr blocks received', 'global cache cr blocks received')
and b1.inst_id = b2.inst_id
union all
select
'GC CURRRENT BLOCKS',
b1.inst_id,
b2.value "BLOCKS RECEIVED",
b1.value "RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1,
gv$sysstat b2
where b1.name in ('global cache current block receive time', 'gc current block receive time')
and b2.name in ('global cache current blocks received', 'gc current blocks received')
and b1.inst_id = b2.inst_id;
более подробные и полезные данные о производительности interconnect можно найти в разделе AWR Global Cache and Enqueue Services – Workload Characteristics
Avg global cache cr block receive time (ms) Avg global cache current block receive time (ms)
RowCR – row contention
RowCR attempts
RowCR hits
Статистики row CR (Consistent Read на уровне строки, не блока) – механизма “оптимизации с целью уменьшения consistent-read rollbacks при выполнении запросов”
“CR feature уменьшает количество CR rollbacks , избегая, таким образом, необходимости выполнения дорогостоящих (дисковых) операций block cleanout/rollback в RAC окружении. Вместо выполнения [стандартной операции] block cleanout, Row CR пытается сгенерировать консистентную версию определённой строки. [В Oracle 9i] Row CR выполнялся только для операций UPDATE с использованием Unique Index Scan или Fetch by Row ID…
Статистика Row CR attempts показывает количество попыток выполнения updates, удовлетворяющих этим условиям [про update здесь пишется применительно к Oracle 9i, начиная с Oracle 10g механизм Row CR используется также для операций SELECT с доступом по индексу].
Статистика Row CR hits отображает количество удачных использований Row CR. Начиная с Oracle10g применение этого механизма должно быть расширено для index range scans для дальнейшей оптимизации операций cleanout…”
“RowCR разрешён (значение параметра по умолчанию _row_cr = TRUE) и поддерживается с версии 10.2″
Применение RowCR может сопровождаться различными ошибками ORA-600 типа Bug 8771916 OERI [kdsgrp1] during CR read, одним из методов решения которых может быть “отключение rowCR … установкой параметра “_row_cr”=FALSE … Однако, это может привести к ухудшению времени выполнения запросов – соотношение статистик RowCR hits / RowCR attempts поможет увидеть, насколько это критично для производительности…”
class slave wait
Неправильно классифицированное ожидание в версии 10.1.0.3 (непропатченный Oracle 10) ошибочно относится к классу ожиданий Other, в отчёте ADDM выглядит так
FINDING 5: 13% impact (626 seconds) ----------------------------------- Wait class "Other" was consuming significant database time. NO RECOMMENDATIONS AVAILABLE ADDITIONAL INFORMATION: Database latches in the "Other" wait class were not consuming significant database time.
в AWR
~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) DB Time Wait Class ------------------------------ ------------ ----------- --------- -------------- ... class slave wait 122 625 12.52 Other
исправлено в 10.1.0.4, 10.2, см. Bug 3876475 “class slave wait” has the wrong wait classification
index scans kdiixs1
Кол-во операций индексного сканирования (index scans)
Статистика обращения к корневым блокам индекса
Коэффициенты Statspack / AWR
Execute to Parse % = 100 * (1 – Parses/Executions)
execute to parse ratio – обратное соотношение количества разборов SQL (включая hard parses и soft parses) к количеству выполнений запросов (executions). Может рассматриваться как коэффициент эффективности работы приложения с бд: чем больше выполнений запросов на один разбор, тем выше коэффициент (меньше ресурсов / времени тратится на разбор = выше эффективность работы бд). Если количество выполнений равно числу разборов SQL – этот коэффициент = 0 (эффективность работы бд низкая). Если количество разборов больше числа выполнений запросов, например, если приложение многократно делает разбор запроса parse и не выполняет execute – коэффициент execute to parse может быть отрицательным
Это соотношение не может быть изменено с помощью параметра cursor_sharing, поскольку этот параметр не влияет на суммарное число разборов (может изменить только соотношение между hard parses и soft parses) – см. Ask Tom “execute to parse ratio” : для уменьшения эффективно уменьшать количество разборов кэшируя открытые курсоры для дальнейшего повторного использования с использованием jdbc statement caching, Oracle .NET statement cache или “… перемещая все [возможные] SQL внутрь PL/SQL (как в машину для кэширования запросов), который автоматически кэширует запросы (сохраняет открытыми куксоры) для нас”
% 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
Время, затраченное на разбор запросов (parse time elapsed) делится на “продуктивную работу” процессора – parse time CPU и время ожиданий: низкое значение Parse CPU to Parse Elapsd означает затрату значительного времени при разборе на непродуктивные ожидания , наиболее вероятно – ожидания событий latch free, рекомендуется проверить статистику ожиданий. Пример с картинками в блоге Jeremy Schneider
Estd Interconnect traffic (KB)
Средний (за период между измерениями – snapshot‘ами суммарный трафик через cluster interconnect
(gc cr blocks received/sec + gc current blocks received/sec + gc cr blocks served/sec + gc current blocks served/sec) * db_block_size + ( messags sent directly/sec + messages send indirectly/sec + messages received/sec ) * 200 bytes
формула из sprepins.sql
round((((:gccrrv+:gccurv +:gccrsv+:gccusv) * :bs) + ((:dpms+:dnpms+:pmrv+:npmrv)* 200))/&&btokb/:ela,2)
gccrrv = global cache consistent read blocks received gccurv = global cache current blocks received gccrsv = global cache consistent read blocks served (обработано, отправлено) gccusv = global cache current blocks served (обработано, отправлено) bs = block size (размер блока бд) dpms = global cache service messages sent dnpms = global enqueue service messages sent pmrv = global cache service msgs received npmrv = global enqueue service msgs received 200 = средний размер ges/gcs сообщения, применяемый для оценки btokb = 1024 ela = elapsed, период между измерениями(snapshot), сек.
TBD
Спасибо! Очень хорошая статья ! Весь блог весьма полезен.
Respect!
комментарий от Александр Беляков — 16.09.2011 @ 12:56 |
Спасибо, Александр, за добрый слова
комментарий от Igor Usoltsev — 18.09.2011 @ 21:07 |