Oracle mechanics

Типичные проблемы

Unshared cursors / High version_counts

Симптомы

Wait events

  • cursor: pin S wait on X
  • library cache pin
  • latch: shared pool

Низкий процент Execute to Parse %, высокий процент Recursive Call % в отчётах Statspack/AWR

Документ поддержки 296377.1 Handling and resolving unshared cursors/large version_counts:

Optimizer mismatch()…The number is brackets gives the reason why

1 = Degree used is not the default DOP
2 = In (RAC) cases where instance count is not the same, or session CPU count is not the same, or thread count is not the same
3 = _parallel_syspls_obey_force is FALSE
4 = The PQ mode does not match.
5 = The degree does not match.
6 = The parallel degree policy does not match.
7 = The session limit is not the same as the cursor limit but the cursor limit is the same as the degree used.
8 = The cursor limit is greater than the degree used and the session limit is less than the cursor limit
9 = The cursor limit is less than the degree used and the session limit is not the same as the cursor limit
10 = Optimizer mode difference
11 = Materialized View mismatch
12 = Optimizer environment mismatch (ie an optimizer parameter is different)
13 = Cardinality Feedback is use

Обзор V$SQL_SHARED_CURSOR предназначен для идентификации причин, а начиная с 11g содержит информационное поле REASON, из XML-содержимого которого, кроме Optimizer mismatch, можно найти след.причины:

Different Call Duration(0) — соотв. V$SQL_SHARED_CURSOR.DIFF_CALL_DURN=Y — Bug 14040433 Cursors not shared due to DIFF_CALL_DURN even though they should share:

Note that a DIFF_CALL_DURN=Y mismatch can also have legitimate reasons
in some scenarios where the same SQL statement is executed in two different
ways (for example, via explicit and implicit PL/SQL cursors)

Bind mismatch — BIND_EQ_FAILURE=Y — соотв.множеству возможных причин:

Bind mismatch(25) — Adaptive Cursor Sharing

Bind mismatch(19) — несовпадение, связанное с параллельным выполнением (?)

Некоторые другие причины

ROLL_INVALID_MISMATCH = Y

«Marked for rolling invalidation and invalidation window exceeded» — инвалидация курсора при изменении статистики объектов. Зависит, в частности, от значения параметра NO_INVALIDATE пакета DBMS_STATS — 557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10g Version: 10.1.0.0 to 11.1.

PQ_SLAVE_MISMATCH = Y

Дополнительные дочерние курсоры генерируются при параллельном выполнении. При этом головной серверный процесс (Query Coordinator) выполняет SQL с CHILD_NUMBER=0, а остальные дочерние курсоры используются параллельными процессами (PQ Slaves):

SQL> select CHILD_NUMBER, PQ_SLAVE_MISMATCH from v$sql_shared_cursor where sql_id = '3u23x3r81893m';

CHILD_NUMBER PQ_SLAVE_MISMATCH
------------ -----------------
           0 N
           1 Y
           2 Y
           3 Y

Случай, если вышеуказанный запрос ничего не говорит о причинах «high version counts» (возвращает NULL в поле REASON), вероятнее всего относится к проблеме 377847.1 Unsafe Peeked Bind Variables and Histograms

ROW_LEVEL_SEC_MISMATCH = Y
В версии 11.1 в действительности означает BIND_EQUIV_FAILUREBug 6964441 V$SQL_SHARED_CURSOR.ROW_LEVEL_SEC_MISMATCH = Y actually means a bind equivalence failure in 11g
Пример:

11.1.0.7@ SQL> select is_bind_aware, is_bind_sensitive, count(*)
  2    from v$sql
  3   where sql_id = '071hjm3y853gw'
  4   group by is_bind_aware, is_bind_sensitive
  5  /
 
IS_BIND_AWARE IS_BIND_SENSITIVE   COUNT(*)
------------- ----------------- ----------
Y             Y                        203
 
SQL> select row_level_sec_mismatch, roll_invalid_mismatch, count(*)
  2    from v$sql_shared_cursor
  3   where sql_id = '071hjm3y853gw'
  4   group by row_level_sec_mismatch, roll_invalid_mismatch
  5  /
 
ROW_LEVEL_SEC_MISMATCH ROLL_INVALID_MISMATCH   COUNT(*)
---------------------- --------------------- ----------
Y                      N                              3
Y                      Y                            200

SQL> -- или скриптиком
SQL> @shared_cu111 071hjm3y853gw
 
SQL_ID         VERSION_COUNT  SHARED_CU_CNT     EXECS REASON                                         SQL_TEXT
------------- -------------- -------------- --------- ---------------------------------------------- --------
071hjm3y853gw            203            203      5336  ROW_LEVEL_SEC_MISMATCH ROLL_INVALID_MISMATCH  select  

High Number of Child Cursors for Queries using SPM due to HASH_MATCH_FAILED (Doc ID 2210515.1)12.1.0.2+ ожидаемое поведение для CURSOR_SHARING=FORCE AND TIMESTAMP COLUMN, независмо от SPM

ORA-00060 DEADLOCK DETECTED

62354.1 TX Transaction locks — Example wait scenarios
62365.1 What to do with «ORA-60 Deadlock Detected» Errors

Подробный анализ различных сценариев возникновения ошибки с примерами разбора DEADLOCK-графа из трейс файла и рекомендациями: Mark Bobak Understanding and Interpreting Deadlocks. Presented at Hotsos 2006:

Scenario #1 – TM enqueue. Причиной являются, как правило, неиндескированые столбцы с ограничением Foreign key, найти которые можно с помощью запроса от T.Kyte
Scenario #2 – TX Enqueue, ‘X’ mode held, ‘X’ mode waiting. Блокировка на уровне строк (row-level lock). Чаще всего является «особенностью» дизайна приложения.

Это те 2 классических сценария deadlock, о которых в трейс-файлах Oracle написано: «The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL.«

Scenario #3 – TX Enqueue, ‘X’ mode held, ‘S’ mode waiting
1) ITL slot contention.  (Not possible if statement is an INSERT and table is not an IOT). Причиной проблемы является конкуренция за место для записи о транзакции (transacion entry) в Interested Transaction List (ITL) заголовка блока данных таблицы или индекса Oracle. Указанная конкуренция, согласно утверждению автора, невозможна для операций вставки в не-IOT таблицы.  Проблема решается измением параметров хранения объектов (INITRANS, PCTFREE). Начиная с Oracle 10g идентифицируется отдельным событием ожидания «enq: TX — allocate ITL entry» и отдельной статистикой сегментов (STATISTIC_NAME=»ITL Waits» в обзоре V$SEGMENT_STATISTICS).
2) Bitmap index fragment overlap.  (Not possible if no bitmap exists on the table). Проблема использования bitmap индексов в OLTP системах.
3) Primary key, unique key, or IOT row values overlapping on insert. Случай взаимоожидания проверки ограничений целостности при вставке.

Обсуждение частных случаев, включая сценарий #3 и self-deadlock при использовании pragma autonomous_transaction можно найти на сайте AskTom INITRANS Cause of deadlock.

В кластерном окружении Oracle RAC диагностика deadlock не так отработана, вместо ORA-60 можно получить ошибки вида Global Enqueue Services Deadlock detected. More info in file /u01/app/oracle/admin/dbname/bdump/inst1_lmd0.trc с трейсом в файле bdump/inst1_lmd0.trc

Подробное описание диагностики глобальных блокировок от Oracle 262226.1 Deadlock Error Not in Alert.log and No Trace File Generated on OPS or RAC (для версии 10.2)

Global Wait-For-Graph(WFG) at ddTS[0.1] :
 BLOCKED 6A084460 5 [0xd0004][0x30e],[TX] [1245185,21] 0
 BLOCKER 6A092C40 5 [0xd0004][0x30e],[TX] [1245186,24] 1
 BLOCKED 6A0932C0 5 [0x10003][0x5ef],[TX] [1245186,24] 1
 BLOCKER 6A057EF0 5 [0x10003][0x5ef],[TX] [1245185,21] 0
<BLOCKED|BLOCKER> <lockp> <cvt|held mode> <res name> <pid|did|txn_id> <node>

типы блокировок (mode) отличаются от локальных блокировок, в частности, 5 — exclusive (How to analyze global deadlocks ?)

GLOBAL ENQUEUE SERVICES DEADLOCK DETECTED

http://jonathanlewis.wordpress.com/2010/06/21/locks/

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: