Oracle mechanics

06.09.2011

ASMM: ORA-04031 и «cursor: pin S wait on X»

Oracle 11.1.0.7.2, Linux x86_64 с работающим Automatic Shared Memory Management (ASMM), некластерный инстанс с меняющимся профилем нагрузки

Периодические ошибки ORA-04031, с записями в alert.log:

Error: ORA-04031: невозможно выделить 32 байт разделяемой памяти ("shared pool","select sq_fl_task_result_id....","sql area","tmp")
...
AUTO SGA: Disabling background sga tuning due to err 604
AUTO SGA: Dumping call and error stack to file .../db_mmon_1209.trc

Задача автоматической настройки распределения SGA  также страдает в результате недостатка свободной памяти в shared pool — системный процесс MMON по-честному, через общедоступный SQL-интерфейс пытается выполнять задачи и получает ошибки (никаких backdoors ;) — из трейса процесса MMON:

AUTO SGA: Disabling background sga tuning due to err 604
AUTO SGA: Dumping stacks and disabling background sga tuning on error 604, simerr=0
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select java_pool_size_for_es...","sql area","tmp")
...
waited for 'SGA: allocation forcing component growth', seq_num: 56164
...
time_waited: >= 0 sec (still in wait)
...
time_waited: 0.051807 sec (sample interval: 0 sec)

По той же причине shutdown immediate также возвращает ошибку (!):

SQL> shutdown immediate
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","insert into sys.mon_mods$ (o...","sql area","tmp")

Для понимания происходивших (в процессе автоматического управления — ASMM) движений памяти внутри SGA, можно посмотреть перераспределение памяти между buffer cache и shared pool (а также долю KGH: NO ACCESS в shared pool) за последние несколько часов:

SQL> select snap_id, name, round(value/1024/1024) as MB from dba_hist_sga
 2  where name in ('Database Buffers','Variable Size')
 3  and snap_id between 39776 and 39779
 4  order by snap_id
5  /

 SNAP_ID    NAME               MB
 ---------- ------------------ -----
 39776      Database Buffers    8448
 39776      Variable Size      11776
 39777      Database Buffers   12032
 39777      Variable Size       8192
 39778      Database Buffers    7168
 39778      Variable Size      13056
 39779      Database Buffers    7168
 39779      Variable Size      13056

buffer cache и shared pool фактически менялись размерами несколько раз, т.е. изменения профиля нагрузки были значительными и ASMM «работал очень активно»

Объём KGH: NO ACCESS (области внутри shared pool, выделяемой для блоков данных buffer cache при невозможности в процессе автоматического управления памятью увеличить буферный кэш бд за счёт уменьшения размера shared pool) — более 6ГБ, и продолжал расти:

SQL> select snap_id, name, pool, bytes from dba_hist_sgastat
 2  where name = 'KGH: NO ACCESS' and snap_id between 39776 and 39779
 3  order by snap_id
 4  /

 SNAP_ID    NAME            POOL         BYTES
 ---------- --------------- ------------ ----------
 39776      KGH: NO ACCESS  shared pool  6451253600
 39777      KGH: NO ACCESS  shared pool  6719570816
 39778      KGH: NO ACCESS  shared pool  7256400832
 39779      KGH: NO ACCESS  shared pool  7256449760

Операции ASMM по изменению размеров пулов часто заканчивались с ошибками:

SQL> select component, oper_type, status, count(*)
 2  from dba_hist_memory_resize_ops where snap_id = 39778
 3  group by component, oper_type, status
 4  /

 COMPONENT             OPER_TYPE     STATUS      COUNT(*)
 --------------------- ------------- --------- ----------
 DEFAULT buffer cache  SHRINK        ERROR              8
 shared pool           GROW          ERROR              8

В результате память SGA и shared pool, в частности, оказалась распределена следующим образом:

SQL> select name, round(bytes/1024/1024/1024) as GB, RESIZEABLE from v$sgainfo order by bytes desc;

 NAME                        GB RESIZEABLE
 ------------------ ---------- ----------
 Maximum SGA Size           20 No
 Shared Pool Size           12 Yes
 Buffer Cache Size           7 Yes

SQL> select * from v$sgastat where pool = 'shared pool' order by bytes desc
/
 1 shared pool    KGH: NO ACCESS          6,988,100,160
 2 shared pool    sql area                5,501,072,464
 3 shared pool    free memory             3,638,068,128
 4 shared pool    CCursor                 1,300,733,792
 5 shared pool    PCursor                 1,032,777,920
 6 shared pool    KGL handle                374,073,576
 7 shared pool    db_block_hash_buckets     186,650,624
 8 shared pool    Cursor Stats              180,479,248

Цифры в последнем запросе выглядят дико нескольно завышенными (см. Bug 4577426  V$SGASTAT may show misleading BYTES values larger than really allocated, исправлен в 11.2.0.2), но пригодны для оценки пропорций распределения компонентов

Как следствие в топе AWR:

                                                                          Avg
                                                                          wait   % DB
Event                                    Waits        Time(s)      (ms)   time  Wait Class
---------------------------------------- ------------ ----------- ------ ------ -----------
cursor: pin S wait on X                     2,796,471    30,946       11  26.9  Concurrency
db file sequential read                     2,120,688    23,506       11  20.4  User I/O
log file sync                                 149,200    23,371      157  20.3  Commit
library cache load lock                           235    13,524    57550  11.7  Concurrency
SGA: allocation forcing component growth      610,877     6,773       11   5.9  Other

— три ожидания из пяти имеют прямое отношение к конкуренции за место в shared pool.

Ожидание SGA: allocation forcing component growth по определению ждёт изменения размеров компонента SGA, проявление событий cursor: pin S wait on X & library cache lock при частых изменениях компонентов SGA и появлении KGH: NO ACCESS описано в High ‘cursor: pin S wait on X’ and/or ‘library cache lock’ Waits Generated by Frequent Shared Pool/Buffer Cache Resize Activity [ID 742599.1], где кроме отключения ASMM рекомендуется уменьшить частоту операций автоматического изменения компонентов разделяемой памяти параметром _memory_broker_stat_interval (по умолчанию — 30 секунд):

SQL> @param _memory_broker_stat_interval

Shared Pool Components        VALUE  IS_DEF  IS_MOD  IS_ADJ  DSC
----------------------------- ------ ------- ------- ------- --------------------------------------------------------
_memory_broker_stat_interval     30  TRUE    FALSE   FALSE   memory broker statistics gathering interval for auto sga

SQL> alter system set "_memory_broker_stat_interval" = 9999;

System altered.

Однако, изменение частоты операций управления памятью не поможет решить проблем с возникновением ora-4031, которые, очевидно, связаны с самим функционированием ASMM, и для блокирования возможности повторного возникновения напрашивается и рекомендуется простое и надёжное решение — отключить ASMM и распределять разделяемую память вручную (что уже совсем не радует). Какие ещё варианты предлагают специалисты Oracle?

В документе How To Prevent The Growth Of The Component ‘KGH: NO ACCESS’ In The Shared Pool When ASMM Is Enabled [ID 451960.1] кроме отключения ASMM (первое предлагаемое решение, что странно слышать от производителя фичи, имхо) предлагается и 2-й вполне рабочий вариант — при включенном ASMM установить размеры shared pool и buffer cache (точнее, нижние границы этих пулов) параметрами:

SQL> alter system set shared_pool_size = 4G scope=spfile;

System altered.

SQL> alter system set db_cache_size = 12G scope=spfile;
 alter system set db_cache_size = 12G scope=spfile
 *
 ERROR at line 1:
 ORA-32017: failure in updating SPFILE
 ORA-00384: Insufficient memory to grow cache

ошибка вызвана старым багом (Bug 9763829  ORA-384 while altering DB_CACHE_SIZE dynamically, согласно описанию исправлен в 11.2.0.2), изменение параметров может потребовать рестартовать бд. Практически интересный результат можно получить после фиксации нижней границы только shared pool параметром shared_pool_size. Результаты распределения компонентов SGA через 2+ недели после установки этого параметра:

SQL> @sga

ASMM_STATUS
-----------
ASMM:on

SGA Parameters

NAME                           MB DISPLAY_VALUE        DESCRIPTION
---------------------- ---------- -------------------- ------------------------------------------------------------
sga_max_size                20480 20G                  max total SGA size
sga_target                  20480 20G                  Target size of SGA
shared_pool_size             4096 4G                   size in bytes of shared pool
db_16k_cache_size               0 0                    Size of cache for 16K buffers
db_2k_cache_size                0 0                    Size of cache for 2K buffers
db_32k_cache_size               0 0                    Size of cache for 32K buffers
db_4k_cache_size                0 0                    Size of cache for 4K buffers
db_8k_cache_size                0 0                    Size of cache for 8K buffers
db_cache_size                   0 0                    Size of DEFAULT buffer pool for standard block size buffers
db_keep_cache_size              0 0                    Size of KEEP buffer pool for standard block size buffers
db_recycle_cache_size           0 0                    Size of RECYCLE buffer pool for standard block size buffers
java_pool_size                  0 0                    size in bytes of java pool
large_pool_size                 0 0                    size in bytes of large pool
olap_page_pool_size             0 0                    size of the olap page pool in bytes
streams_pool_size               0 0                    size in bytes of the streams pool

SGAINFO

NAME                                     MB RESIZEABLE
-------------------------------- ---------- ----------
Maximum SGA Size                      20389 No
Buffer Cache Size                     15616 Yes
Shared Pool Size                       4096 Yes
Java Pool Size                          256 Yes
Startup overhead in Shared Pool         256 No
Granule Size                            256 No
Large Pool Size                         256 Yes
Redo Buffers                            163 No
Fixed SGA Size                            2 No
Shared IO Pool Size                       0 Yes
Streams Pool Size                         0 Yes
Free SGA Memory Available                 0

Shared Pool Components - TOP5

NAME                               MB
-------------------------- ----------
free memory                      2165
sql area                          929
CCursor                           222
PCursor                           194
KGL handle                         66

Несмотря на значительную долю free memory (2165 из 4096 MB), область KGH: NO ACCESS в составе Shared Pool не появляется вовсе — предполагаю, этому препятствует установленный параметр shared_pool_size при функционирующем ASMM:

SQL> select * from x$ksmsp where ksmchcom = 'KGH: NO ACCESS';

no rows selected

4 комментария »

  1. P.S. Специалисты поддержки подсказали ещё один способ решения — применить патч 9267837, в ORA-04031 in 11g & 11gR2, Excess «KGH: NO ACCESS» Memory Allocation [ID 1127833.1] описаны похожие симптомы проблемы — значительные области KGH: NO ACCESS в одном из subpool’ов:

    An ORA-4031 error is reported by an 11g database, up to version 11.2.0.1.
    An examination of the 4031 diagnostics in the trace file shows that in one of the Subpools, a Memory Allocation called «KGH: NO ACCESS» is greater than 80~100Mb

    комментарий от Igor Usoltsev — 06.09.2011 @ 10:49 | Ответить

  2. ORA-00384 возникает только при попытке установить нижние пределы разделов SGA через параметры db_cache_size, shared_pool_size в значения, превышающие текущий размер этих пулов, например:

    SQL> @param_ _db_cache_size
    
    NAME                                       VALUE                                    IS_DEF   IS_MOD  IS_ADJ      DSC
    ------------------------------------------ ---------------------------------------- -------- ---------- -------- ------------------------------------------------------------------
    __db_cache_size                            20132659200                              FALSE    FALSE   FALSE       Actual size of DEFAULT buffer pool for standard block size buffers

    Пробую установить db_cache_size = 19G, что больше текущего размера (неважно, в памяти или в файле параметров) — получаю ошибку:

    SQL> alter system set db_cache_size = 19G;
    alter system set db_cache_size = 19G
    *
    ERROR at line 1:
    ORA-32017: failure in updating SPFILE
    ORA-00384: Insufficient memory to grow cache
    
    SQL> alter system set db_cache_size = 19G scope=spfile;
    alter system set db_cache_size = 19G scope=spfile
    *
    ERROR at line 1:
    ORA-32017: failure in updating SPFILE
    ORA-00384: Insufficient memory to grow cache

    И успешно устанвливаю в db_cache_size = 18G, что меньше (или равно) текущего размера:

    SQL> alter system set db_cache_size = 18G;
    
    System altered.

    Аналогично shared_pool_size:

    SQL> @param_ __shared_pool_size
    
    NAME                                       VALUE                                    IS_DEF   IS_MOD  IS_ADJ      DSC
    ------------------------------------------ ---------------------------------------- -------- ---------- -------- ------------------------------------------------------------------
    __shared_pool_size                         5368709120                               FALSE    FALSE   FALSE       Actual size in bytes of shared pool
    
    SQL> alter system set shared_pool_size = 4G;
    
    System altered.

    комментарий от Igor Usoltsev — 09.11.2011 @ 19:13 | Ответить

  3. UPD Дополнительная иллюстрация проблемы ORA-04031 при ASMM в 11.1
    Проявление ошибок:

    Tue Jun 25 17:39:29 2013
    ORA-04031: невозможно выделить 32 байт разделяемой памяти ("shared pool","select id, bean_name, params...","sql area","tmp")
    Tue Jun 25 17:39:29 2013
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select id, bean_name, params...","sql area","tmp")
    Tue Jun 25 17:39:29 2013
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select * from avatar_url whe...","sql area","tmp")

    сопровождается попытками изменить размеры пулов SGA с частотой 2-3 секунды:

    11.1.0.7@ SQL> select component,
      2         oper_type,
      3         oper_mode,
      4         to_char(start_time, 'hh24:mi:ss') AS start_time,
      5         status
      6    FROM V$SGA_RESIZE_OPS
      7   where start_time between to_date('&sdate', 'dd.mm.yyyy hh24:mi')
      8                        and to_date('&edate', 'dd.mm.yyyy hh24:mi')
      9   order by start_time
     10  /
     
    COMPONENT             OPER_TYPE     OPER_MODE START_TIME STATUS
    --------------------- ------------- --------- ---------- --------
    shared pool           GROW          IMMEDIATE 17:39:09   COMPLETE
    DEFAULT buffer cache  SHRINK        IMMEDIATE 17:39:09   COMPLETE
    shared pool           GROW          IMMEDIATE 17:39:12   COMPLETE
    DEFAULT buffer cache  SHRINK        IMMEDIATE 17:39:12   COMPLETE
    shared pool           GROW          IMMEDIATE 17:39:14   COMPLETE
    DEFAULT buffer cache  SHRINK        IMMEDIATE 17:39:14   COMPLETE
    DEFAULT buffer cache  SHRINK        IMMEDIATE 17:39:17   COMPLETE
    shared pool           GROW          IMMEDIATE 17:39:17   COMPLETE
    DEFAULT buffer cache  SHRINK        IMMEDIATE 17:39:19   COMPLETE
    shared pool           GROW          IMMEDIATE 17:39:19   COMPLETE

    , что превышает значение параметра _memory_broker_stat_interval (по умолчанию 30 сек)

    Кроме проблем реализации (багов), причиной проблем ASMM при наличии незанятых областей free memory в SGA может рассматриваться использование выделенных разделов Shared Pool SubPool Duration для областей KGH: NO ACCESS и sql area:

    SQL> select 'sga heap(' || KSMCHIDX || ',' || to_char(KSMCHDUR - 1) || ')' as "Heap(SubHeap,Duration)",
      2         ksmchcom,
      3         ksmchcls,
      4         sum(ksmchsiz),
      5         max(ksmchsiz)
      6    from X$KSMSP
      7   where ksmchcom in ('sql area', 'KGH: NO ACCESS')
      8      or ksmchcom = 'free memory' and KSMCHDUR - 1 = 3
      9   group by ksmchcom,
     10            'sga heap(' || KSMCHIDX || ',' || to_char(KSMCHDUR - 1) || ')',
     11            ksmchcls
     12   order by 2, 1, 3
     13  /
     
    Heap(SubHeap,Duration) KSMCHCOM         KSMCHCLS SUM(KSMCHSIZ) MAX(KSMCHSIZ)
    ---------------------- ---------------- -------- ------------- -------------
    sga heap(1,3)          KGH: NO ACCESS   no acce      532176704     252719136
    sga heap(2,3)          KGH: NO ACCESS   no acce      262820768     230707232
    sga heap(5,3)          KGH: NO ACCESS   no acce      262533088     210460704
    sga heap(6,3)          KGH: NO ACCESS   no acce      263098784     234422304
    sga heap(1,3)          free memory      free         308265760       1842408 -- в этот момент Duration=3 содержит
    sga heap(2,3)          free memory      R-free        18139864       2150168 -- свободное место во всех SubHeap
    sga heap(2,3)          free memory      free           4592744          4112 -- и ошибка не возникает
    ...
    sga heap(1,3)          sql area         freeabl      734246088         79640
    sga heap(1,3)          sql area         recr          30687272          4120
    sga heap(2,3)          sql area         R-freea        8695416         80120
    sga heap(2,3)          sql area         freeabl      455447328          8184
    ...

    , как вариант решения можно попробовать (с благословения поддержки) отключить деление на Duration параметром:

    SQL> alter system set "_enable_shared_pool_durations"=false scope= spfile;

    Severe Performance Problems Due to Sudden drop/resize/shrink of buffer cache [ID 1344228.1]:

    This will change the architecture of memory in the pools. When set to FALSE, subpools within the SGA will no longer have 4 durations. Instead, each subpool will have only a single duration. This mimics the behavior in 9i, and the shared pool will no longer be able to shrink.
    The advantage of this is that the performance issues documented in this note can be avoided. A duration will not encounter memory exhaustion while another duration has free memory.
    The disadvantage is that the shared pool (and streams pool) are not able to shrink, mostly negating the benefits of ASMM

    комментарий от Igor Usoltsev — 04.07.2013 @ 20:17 | Ответить

  4. Была такая же проблема. Версия субд 11.2.0.4. Для устранения этой проблемы нужно установить патч p14770516_112040

    комментарий от Аноним — 04.03.2015 @ 11:42 | Ответить


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 такие блоггеры, как: