Oracle mechanics

14.12.2018

row cache lock: global deadlock

Filed under: Блокировки,Oracle,shared pool — Игорь Усольцев @ 00:33
Tags:

С Иваном Постниковым наблюдали/разбирали нечасто встречаемое ожидание library cache load lock, в течение заметного времени наблюдавшееся у ряда сессий 2-го инстанса:

12.1.0.2@inst#2 SQL> select * from v$session where state = 'WAITING' and event = 'library cache load lock';
 
  SID    SERIAL# USERNAME COMMAND STATUS   SERVER    SQL_ID        SQL_CHILD_NUMBER SQL_EXEC_START SQL_EXEC_ID MODULE                   ACTION ROW_WAIT_OBJ# LAST_CALL_ET  BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_SESSION_STATUS FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION EVENT                    P1TEXT          P1RAW            P2TEXT        P2RAW            P3TEXT             P3RAW            WAIT_CLASS  SECONDS_IN_WAIT STATE  
----- ---------- -------- ------- -------- --------- ------------- ---------------- -------------- ----------- ------------------------ ------ ------------- ------------ ------------------------ ----------------- ---------------- ----------------------------- ----------------------- ---------------------- ------------------------ --------------- ---------------- ------------- ---------------- ------------------ ---------------- ----------- --------------- -------
   13      54031 APPS           3 ACTIVE   DEDICATED 8r49n0b59js0a                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/       2707322        81853  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005CA8C05AD0 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING
  652      55148 APPS           3 ACTIVE   DEDICATED a742atptz32bs                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/            -1        81853  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005DA2ACFDF0 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING
 1632      58741 APPS           3 ACTIVE   DEDICATED gn7fg92jr1vkj                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/       2707322        81851  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005C037E1AC0 100*mask+namespace 0000000000010003 Concurrency           81758 WAITING
 1801      31526 APPS           3 ACTIVE   DEDICATED 99340wc8xpd52                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/       2707322        81852  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005BA6313E70 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING
 2495      38991 APPS           3 ACTIVE   DEDICATED 39wnr00vh9t1b                0                            e:SQLGL:cp:xla/XLADRPGLT SQLGL/            -1        81852  VALID                                   2              392 VALID                                               2                    392 library cache load lock  object address  0000005D14491B18 lock address  0000005DA151AF38 100*mask+namespace 0000000000010003 Concurrency           80854 WAITING

, содержимое ROW_WAIT_OBJ# не очень полезно:

SQL> select * from dba_objects where object_id  = 2707322;
 
OWNER OBJECT_NAME  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
----- ----------- ---------- -------------- -----------
SYS   USER$          2707322        2707320 TABLE

, судя по пустым SQL_EXEC_START / SQL_EXEC_ID запросы/курсоры находятся в процессе погрузки в Library Cache (на что указывает и название ожидания длительностью SECONDS_IN_WAIT 80000+ сек) и имеют сходные тексты, относящиеся к одной и той же временной по сути, но не по определению (TEMPORARY=N) таблице: (more…)

Реклама

14.07.2015

Deduplicate LOB

Filed under: Блокировки,Oracle — Игорь Усольцев @ 01:17
Tags: ,

Разочаровал способ реализации в Oracle killer-фичи версии 11g — Deduplicate LOB через обычный странный UNIQUE индекс, который значительно ограничивает возможности транзакционного использования, в частности, конкурентного заполнения таблицы дублирующими LOB-ами

SQL> CREATE TABLE deduplicate_clob (clob_data  CLOB)
  2  LOB(clob_data) STORE AS SECUREFILE dedup_lob( DEDUPLICATE ENABLE STORAGE IN ROW )
  3  tablespace USERS
  4  /
 
Table created

SQL> -- индекс вместе с LOB-сегментом создаются сразу по созданию таблицы:
SQL> select * from dba_indexes where table_name = 'DEDUPLICATE_CLOB';
 
OWNER  INDEX_NAME                INDEX_TYPE  TABLE_NAME        UNIQUENESS COMPRESSION   LOGGING STATUS  GENERATED VISIBILITY SEGMENT_CREATED INDEXING
------ ------------------------- ----------- ----------------- ---------- ------------- ------- ------- --------- ---------- --------------- --------
SCOTT  SYS_IL0000105054C00001$$  LOB         DEDUPLICATE_CLOB  UNIQUE     DISABLED      YES     VALID   Y         VISIBLE    YES             FULL

SQL> -- , однако, для случая ENABLE STORAGE IN ROW конкурентная вставка коротких LOB-дублей допускается:
SQL> insert into deduplicate_clob (clob_data) values ('test CLOB data'); -- в 1-й сессии без COMMIT-а
 
1 row inserted

SQL> -- и во 2-й сессии:
SQL> insert into deduplicate_clob (clob_data) values ('test CLOB data');
 
1 row inserted

SQL> -- при этом обе сессии вполне успешно получают две уникальные блокировки (неожиданно на разные ресурсы):
SQL> select * from v$lock where type = 'TX';
 
  SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
----- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  355 TX       327699       6328          6          0         46          0          0
  371 TX        65547       5571          6          0         52          0          0

SQL> -- - т.е. уникальность индекса не задействована, и, как следствие, в вышеописанном случае STORAGE IN ROW дедубликация в смысле экономии места практически не функционирует (как и продекларированная уникальность LOB-индекса)
SQL>
SQL> -- Если же при создании определить свойства LOB как DEDUPLICATE DISABLE STORAGE IN ROW, или же, не пересоздавая таблицу, попытаться одновременно в 2-х сессиях вставить в LOB длинные значения:
SQL> insert into deduplicate_clob (clob_data) values (rpad('test CLOB data',5000));
 
1 row inserted

SQL> -- получаем рудименты классического уникального индекса (проявляющиеся только при заполнении LOB-сегмента):
SQL> select * from v$lock where type = 'TX';
 
  SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
----- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  371 TX       458758       6029          6          0         16          1          0
  355 TX       458758       6029          0          4          5          0          0

SQL> -- с предсказуемым ожиданием на этом самом индексе:
SQL> @lock_tree
 
BLOCKING_TREE     USERNAME  EVENT                          REQ_OBJECT                           SECS_IN_WAIT BLOCK_SESSTAT SQL_TEXT                                                                       P1TEXT
----------------- --------- ------------------------------ ------------------------------------ ------------ ------------- ------------------------------------------------------------------------------ -------------------
INST#1 SID#371    SCOTT     SQL*Net message from client    LOB SCOTT.DEDUP_LOB                           604 NO HOLDER                                                                                    driver id driver id
  INST#1 SID#355  SCOTT     enq: TX - row lock contention  INDEX SCOTT.SYS_IL0000105054C00001$$          593 VALID         insert into deduplicate_clob (clob_data) values (rpad('test CLOB data',5000))  name|mode TX 4

SQL> -- - но в этом случае дедубликация действительно работает!

11.12.2013

Блокировки типа RC — Result Cache при использовании MTS

При использовании Multi-Threaded Server блокировки типа RC — Result Cache в AWR вполне ожидаемы при:

11.1.0.7.@ SQL> show parameter result_cache_mode

NAME                                 VALUE
------------------------------------ --------
result_cache_mode                    FORCE

Зависимости ожидания (wait tree) при этом представляют, в основном, логичную картину:

11.1.0.7.@ SQL> @ash_wait_tree "event = 'enq: RC - Result Cache: Contention'"

LVL BLOCKING_TREE       EVENT                              WAITS_COUNT SESS_COUNT AVG_WA
--- ------------------- ---------------------------------- ----------- ---------- ------
  1 FOREGROUND          enq: RC - Result Cache: Contention        3019         49    999 -- блокированные на кэше запросы ожидают:
  2   FOREGROUND        db file sequential read                   1243         13     50 -- ввод-вывод в процессе выполнения запроса/наполнения кэша
  2   FOREGROUND        read by other session                      844          1     69 -- --//--
  2   FOREGROUND        db file parallel read                      594          1     61 -- --//--
  2   FOREGROUND        db file scattered read                     230          9     59 -- --//--
  2   FOREGROUND        On CPU / runqueue                           37          8      0 -- ЦПУ при выполнении запроса/наполнения кэша
  2   FOREGROUND        virtual circuit wait                        14          2    146 -- ???
  2   FOREGROUND        log buffer space                             4          1     11 -- отложенная очистка блоков(delayed block cleanout)
  2   FOREGROUND        read by other session                        2          1     32
  3     FOREGROUND      db file sequential read                    834          4     69
  3     FOREGROUND      On CPU / runqueue                            5          2      0
  3     (LGWR)          control file parallel write                  4          1     24 -- и LGWR, её сдерживающий
  3     FOREGROUND      read by other session                        4          2     17
  3     FOREGROUND      read by other session                        1          1      9
  4       FOREGROUND    db file sequential read                      1          1      9 -- чтения нижнего уровня, завершения которых ждут на read by other session

— кроме ожидания virtual circuit wait, блокирующего доступ к кэшу на enq: RC — Result Cache: Contention (more…)

24.07.2013

Построение индекса в режиме ONLINE и ожидание enq: MD — contention

Filed under: commonplace,Блокировки,Oracle,wait events — Игорь Усольцев @ 09:27
Tags: ,

В 11.2.0.3 поспешно несвоевременно попытался построить индекс на таблице с materialized view log:

SID#283 SQL> @sid

INST_ID SID  SERIAL#  SPID
------- ---- ------- -----
1       283      295 32156

SID#283 SQL> create index T_CLIENT_CLASS_ID_UIDX on T_CLIENT(CLASS_ID, ID) tablespace ITS online parallel;

Однако, заметив затянувшееся ожидание enq: MD — contention:

SQL> select * from v$lock_type where type = 'MD';

TYPE  NAME                       ID1_TAG          ID2_TAG  IS_USER DESCRIPTION
----- -------------------------- ---------------- -------- ------- -----------------------------------------------------
MD    Materialized View Log DDL  master object #  0        NO      Lock held during materialized view log DDL statements

, попытался терминировать сессию средствами Oracle:

OTHER_SID SQL> Alter system kill session '283,295';

Alter system kill session '283,295'

ORA-00031: session marked for kill

Через некоторое время на этой ( конечно же, тестовой ;) системе были замечены ряд сессий в длительном ожидании library cache lock: (more…)

30.05.2013

Блокировки direct path DML

Filed under: Active Session History,commonplace,direct-path DML,Блокировки,Oracle — Игорь Усольцев @ 01:02
Tags: ,

Периодически alert.log версии 11.1.0.7 демонстрирует пакетные сообщения об ошибках ORA-00060:

Sun May 19 20:15:25 2013
ORA-00060: Deadlock detected. More info in file /opt/oracle/admin/diag/rdbms/orcl11107/orcl11107/trace/orcl11107_s000_1386.trc.
Sun May 19 20:15:26 2013
ORA-00060: Deadlock detected. More info in file /opt/oracle/admin/diag/rdbms/orcl11107/orcl11107/trace/orcl11107_s009_6543.trc.
Sun May 19 20:15:27 2013
ORA-00060: Deadlock detected. More info in file /opt/oracle/admin/diag/rdbms/orcl11107/orcl11107/trace/orcl11107_s001_3077.trc.

Причина блокировок известна — попытка одновременного выполнения нескольких транзакций на одной таблице в режиме parallel dml (alter session enable parallel dml => V$SESSION.PDML_ENABLED=’YES’, далее составляющие транзакцию 2 последовательные непараллельные операции DELETE, INSERT /*+ APPEND*/ на непартиционированной таблице TAB1) — и относится к вопросам разработки и архитектуры приложения. Интересны детали отображения и обработки Oracle блокировок типа TM Lock Requesting Mode X (6) (SX X SX X)

Кроме необходимости преобразования / эскалации блокировки TM SX -> X при переходе от DELETE к direct path insert, первый трейс не показывает ничего неожиданного:
(more…)

10.11.2012

ORA-600 [sorput_1] bitmap index deadlock в версиях Oracle 10.2 — 11.1

Filed under: commonplace,Блокировки,error,Oracle — Игорь Усольцев @ 15:11
Tags: , ,

Хорошо известны официальные рекомендации не использовать bitmap-индексы в OLTP приложениях по архитектурным соображениям. Для разрешения возникающих при выполнении конкурирующих DML конфликтов Oracle использует своеобразный механизм deadlock, который на практике может сопровождаться ORA-600:

ORA-00060: Deadlock detected. More info in file /opt/oracle/admin/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_20536.trc.
Errors in file /opt/oracle/admin/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_20536.trc (incident=475770):
ORA-00600: internal error code, arguments: [sorput_1], [], [], [], [], [], [], [], [], [], [], []
ORA-00060: deadlock detected while waiting for resource
Incident details in: /opt/oracle/admin/diag/rdbms/ORCL/ORCL/incident/incdir_475770/ORCL_ora_20536_i475770.trc

Как оказалось, проблема, с которой мы столкнулись, — штатная, в точности описана в документе поддержки Ora-600 [sorput_1] and Ora-60 Loading Data [ID 826084.1] — не является багом и в качестве единственного варианта решения предлагается избавиться от bitmap-индексов, на время OLTP активности ( или навсегда :)

Детали описаниния deadlock graph достаточно стандартны для блокировок битового индекса: (more…)

30.01.2012

ORA-14452 при попытке DDL на временной таблице

Filed under: commonplace,Блокировки,Oracle — Игорь Усольцев @ 15:45
Tags: ,
SQL> drop table SOME_GTT_TABLE;

drop table SOME_GTT_TABLE
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL> with locked_obj as
2   (select o.object_id
3      from dba_objects o
4     where o.owner = sys_context('userenv','CURRENT_SCHEMA')
5       AND o.object_name = 'SOME_GTT_TABLE')
6  select i.host_name,
7         case
8           when s.inst_id = sys_context('userenv', 'instance') and
9                s.sid = sys_context('userenv', 'sid')
10           then 'My own session'
11           else 'Alter system kill session ''' || s.SID || ',' || s.SERIAL# ||
12            ''';'
13         end as KILL_SESSION,
14         l.type
15    from gv$lock l, locked_obj, gv$session s, gv$instance i
16   WHERE l.id1 = locked_obj.object_id
17     AND s.sid = l.sid
18     AND s.inst_id = l.inst_id
19     AND s.inst_id = i.inst_id
20  /

HOST_NAME       KILL_SESSION                           TYPE
--------------- -------------------------------------- ----
host1f.prod.ru  My own session                         TO
host1f.prod.ru  Alter system kill session '453,1309';  TO
host2f.prod.ru  Alter system kill session '458,2337';  TO

SQL> select * from v$lock_type where type = 'TO';

TYPE  NAME         ID1_TAG   ID2_TAG  IS_USER DESCRIPTION
----- ------------ --------- -------- ------- ----------------------------------------------------
TO    Temp Object  object #  1        NO      Synchronizes DDL and DML operations on a temp object

18.08.2011

enq: CF — contention

Нечастое событие enq: CF — contention в топе ожиданий AWR:

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                          9,620          51.2
db file sequential read             977,369       4,530      5   24.1   User I/O
db file scattered read              761,460       1,914      3   10.2   User I/O
enq: CF - contention                  2,133       1,002    470    5.3      Other
log file sync                        22,429         666     30    3.5     Commit

Документация не даёт никакой информации, кроме общего описания транзакционной блокировки/синхронизации доступа к controlfile:

SQL> select NAME, DESCRIPTION from v$lock_type where type = 'CF';

NAME                      DESCRIPTION
------------------------- ----------------------------------------
Controlfile Transaction   Synchronizes accesses to the controlfile

Однако на сайте поддержки можно найти документ (отлично заполняющий пробелы в документации) Performance Degradation as a result of ‘enq: CF — contention’ [ID 1072417.1]:
(more…)

06.03.2011

direct-path INSERT и ожидания enq: TM — contention

Filed under: Active Session History,Блокировки,Oracle,wait events — Игорь Усольцев @ 13:14

Текстовый отчёт AWR (Oracle 10.2.0.4)

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time  Wait Class
------------------------------ ------------ ----------- ------ ------ -----------
CPU time                                         27,751          47.3
enq: TM - contention                 31,252      15,179    486   25.9 Application ...

показывает, что на ожидание, связанное с неприятной блокировкой уровня таблиц (TM или DML locks), тратится более 25% общего времени выполнения запросов на уроне инстанса (DB time)

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

Для нахождения типичные запросов, ожидающих блокировки можно попробовать использовать Active Session History (ASH), используя фиксированную «оперативную» таблицу v$active_session_history для активных или выполнявшихся в течение последнего часа сессий, либо исторические таблицы, хранящиеся в бд dba_hist_active_sess_history / wrh$_active_session_history (more…)

26.06.2009

Блокировки при использовании AUTO_INCREMENT столбцов в MySQL Innodb

Filed under: Блокировки,MySQL — Игорь Усольцев @ 11:20
Tags: ,

Новость о том, что разработчики MySQL серьёзно переработали блокировки этого типа и соответственно продвинули свою СУБД по пути масштабируемости, давно уже новостью не является  (см. InnoDB auto-inc scalability fixed, сентябрь 2007). Однако многие стабильные дистрибутивы Linux, например, Ubuntu 8.04LTS включают в себя пакет MySQL server версии 5.0, для которой проблема блокировок AUTO-INC вполне актуальна. Практический пример типичной проблемы и решения.

LAMP+CMS окружение, активный веб-магазин, десятки тысяч хитов в сутки. MySQL 5.032, обычная нагрузка на сервер ~ 300 запросов в секунду. Проблема возникала при выполнении пакетной загрузки данных о товарах PHP-скриптом: 100-200 тысяч записей, во время выполнения нагрузка на сервер ~ 1000 запросов в секунду, в логе выполнения — ошибка:

Deadlock found when trying to get lock; try restarting transaction

(more…)

Следующая страница →

Создайте бесплатный сайт или блог на WordPress.com.