Oracle mechanics

29.10.2011

Oracle 11g: автоматическое управление памятью (ASMM) и serial direct read

Ничем (или недостаточно) ограниченный механизм Automatic Shared Memory Management (ASMM), динамически меняя размеры пулов SGA, может заставить Oracle  переключаться между режимами direct path read и кэшированного чтения (через buffer cache SGA), что, в свою очередь, может неожиданно, но вполне заметно влиять на время выполнения типовых запросов

И, хотя в документе High ‘direct path read’ waits in 11g [ID 793845.1]  описывается чудесное автоматическое переключение между этими режимами, в зависимости от динамически изменяющегося размера buffer cache:

When ASMM increases the buffer cache due to increased demand, 11g might not again do serial direct path read scans for these same large tables

— в действительности, это только пожелание или план работ на будущее — по крайней мере в версии 11.1.0.7.2 :)

Далее — небольшой практический пример

После очередной вынужденной (ora-4031 при включенном ASMM без ограничений нижних пределов размеров пулов параметрами db_cache_size, shared_pool_size,..) перезагрузки инстанса проявились проблемы с увеличившимся временем выполнения типовых быстровыполнявшихся запросов

Симптомы проблемы на примере одного запроса:

1) при неизменившемся плане выполнения (и неизменных данных) запрос стал выполняться заметно медленнее:

SQL> select snap_id,
  2         plan_hash_value,
  3         round(elapsed_time_delta / executions_delta / 1000) as ela_per_exec
  4    from dba_hist_sqlstat
  5   where sql_id = 'gnuq7ffxbs1mf'
  6     and executions_delta <> 0
  7   order by snap_id desc
  8  /

   SNAP_ID PLAN_HASH_VALUE ELA_PER_EXEC
---------- --------------- ------------
     41151      3697635338        10658
     41150      3697635338        27563
     41148      3697635338        13510
     41146      3697635338        11310
     41130      3697635338        12711
     41129      3697635338        12330
     41124      3697635338         8834
     41113      3697635338          476
     41112      3697635338          521
     41109      3697635338          500
     41106      3697635338          501
     41105      3697635338          395
     41104      3697635338          387
     41103      3697635338          607

2) статистика (autotrace on) показывает неуменьшающееся при повторных непараллельных выполнениях конкретного запроса высокое кол-во physical reads:

Elapsed: 00:00:14.61
...
Statistics
---------------------------------------------------
...
     101310  consistent gets
      75471  physical reads
...
          2  rows processed

3) в буферном кэше SGA кол-во блоков таблицы по которой выполняется FULL TABLE SCAN остаётся незначительным (независимо от частоты обращения к таблице), и все блоки — в статусе xcur — только транзакционно изменяемые блоки:

SQL> @v$bh_by_obj scott some_table

BL_STATUS  DB_BL_COUNT BH_BL_COUNT
---------- ----------- -----------
xcur               628         628

при общем кол-ве блоков таблицы:

SQL> select blocks from dba_segments where segment_name in ('SOME_TABLE');

    BLOCKS
----------
     77312

4) в ожиданиях сессии, выполняющей проблемный запрос в не параллельном режиме (реальный план выполнения, полученный с помощью dbms_xplan.display_cursor привожу просто для примера, чтобы показать, что запрос выполняется непараллельно):

-------------------------------------------------------------------------------
| Id  | Operation                             |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |00:00:13.51 |     101K|  75471 |
|   1 |  SORT ORDER BY                        |00:00:13.51 |     101K|  75471 |
|*  2 |   HASH JOIN                           |00:00:13.51 |     101K|  75471 |
|*  3 |    FILTER                             |00:00:13.39 |   92158 |  75471 |
|*  4 |     HASH JOIN RIGHT OUTER             |00:00:13.39 |   92158 |  75471 |
|   5 |      VIEW                             |00:00:17.44 |   88510 |  75471 |
|*  6 |       HASH JOIN RIGHT ANTI            |00:00:17.44 |   88510 |  75471 |
|   7 |        VIEW                           |00:00:00.08 |    3810 |      0 |
|   8 |         HASH UNIQUE                   |00:00:00.08 |    3810 |      0 |
|*  9 |          INDEX FAST FULL SCAN         |00:00:00.01 |    3810 |      0 |
|* 10 |        HASH JOIN RIGHT OUTER          |00:23:42.11 |   84700 |  75471 | <-- здесь статистика оптимизатора считает время неверно :(
|* 11 |         TABLE ACCESS FULL             |00:00:00.01 |      46 |      0 |
|* 12 |         HASH JOIN                     |00:23:42.09 |   84654 |  75471 |
|* 13 |          HASH JOIN                    |00:00:00.01 |    9166 |      0 |
|  14 |           NESTED LOOPS                |00:00:00.01 |      14 |      0 |
|  15 |            NESTED LOOPS               |00:00:00.01 |       4 |      0 |
|  16 |             INDEX FULL SCAN           |00:00:00.01 |       1 |      0 |
|* 17 |             INDEX RANGE SCAN          |00:00:00.01 |       3 |      0 |
|* 18 |            TABLE ACCESS BY INDEX ROWID|00:00:00.01 |      10 |      0 |
|  19 |           TABLE ACCESS FULL           |00:00:00.01 |    9152 |      0 |
|* 20 |          TABLE ACCESS FULL            |00:00:05.61 |   75488 |  75471 |
|  21 |      VIEW                             |00:00:00.32 |    3648 |      0 |
|  22 |       UNION-ALL                       |00:00:00.32 |    3648 |      0 |
|* 23 |        CONNECT BY WITH FILTERING      |00:00:00.32 |    3602 |      0 |
|* 24 |         FILTER                        |00:00:00.18 |    1801 |      0 |
|  25 |          COUNT                        |00:00:00.01 |    1801 |      0 |
|* 26 |           HASH JOIN RIGHT OUTER       |00:00:00.01 |    1801 |      0 |
|* 27 |            TABLE ACCESS FULL          |00:00:00.01 |      46 |      0 |
|  28 |            TABLE ACCESS FULL          |00:00:00.01 |    1755 |      0 |
|* 29 |         HASH JOIN                     |00:00:00.12 |    1801 |      0 |
|  30 |          CONNECT BY PUMP              |00:00:00.01 |       0 |      0 |
|  31 |          COUNT                        |00:00:00.01 |    1801 |      0 |
|* 32 |           HASH JOIN RIGHT OUTER       |00:00:00.01 |    1801 |      0 |
|* 33 |            TABLE ACCESS FULL          |00:00:00.01 |      46 |      0 |
|  34 |            TABLE ACCESS FULL          |00:00:00.01 |    1755 |      0 |
|* 35 |        COUNT STOPKEY                  |00:00:00.01 |      46 |      0 |
|* 36 |         TABLE ACCESS FULL             |00:00:00.01 |      46 |      0 |
|  37 |        FAST DUAL                      |00:00:00.01 |       0 |      0 |
|  38 |    TABLE ACCESS FULL                  |00:00:00.01 |    9152 |      0 |
-------------------------------------------------------------------------------

— основные non-Idle ожидания — direct path read и enq: KO — fast object checkpoint, характерные для чтения в режиме direct path:

NAME                           WAITS      TIME_MS      TIMEOUTS   AVG_WAIT_MS
-------------------------------- ------ ------------- ------------- -----------
SQL*Net message from client          2         28815          0        14,407
direct path read                   626          5752          0         9
enq: KO - fast object checkpoint     1           343          0       343
SQL*Net message to client            2             0          0         0

При этом параметр _serial_direct_read (судя по названию, имеющий отношение к проблеме) установлен в значение по умолчанию:

SQL> @param_ _serial_direct_read

NAME                 VALUE  IS_DEF   DESC
-------------------- ------ -------- ----------------------------
_serial_direct_read  FALSE  TRUE     enable direct read in serial

Причинами проблем явилось, как отмечено вначале, неконтролируемое поведение ASMM в момент рестарта инстанса и, как следствие, переключение режима чтения при FULL TABLE SCAN —  Oracle 11g: direct path read при непараллельном сканировании таблиц:

SQL> select dba_hist_snapshot.startup_time,begin_interval_time, dba_hist_sga.name, value
2  from dba_hist_sga, dba_hist_snapshot
3  where dba_hist_snapshot.snap_id = dba_hist_sga.snap_id
4  and name = 'Database Buffers'
5  and dba_hist_snapshot.snap_id between 41121 and 41141
6  order by dba_hist_snapshot.snap_id desc
7  /

STARTUP_TIME           BEGIN_INTERVAL_TIME    NAME                   VALUE
---------------------- ---------------------- ----------------- ----------
27.09.11 16:42:44,000  28.09.11 04:00:23,144  Database Buffers  1181116006
...
27.09.11 16:42:44,000  27.09.11 17:00:40,908  Database Buffers  4294967296
27.09.11 16:42:44,000  27.09.11 16:42:44,000  Database Buffers  2415919104
09.09.11 16:19:26,000  27.09.11 14:00:47,311  Database Buffers  2415919104

В момент (внепланового) рестарта инстанса размер буферного кэша был экстремально низким (2 из 20 GB SGA)

SQL> select
  2         begin_interval_time,
  3         dba_hist_parameter.parameter_name,
  4         dba_hist_parameter.value
  5    from dba_hist_parameter, dba_hist_snapshot
  6   where dba_hist_snapshot.snap_id = dba_hist_parameter.snap_id
  7     and dba_hist_parameter.parameter_name in
  8         ('__shared_pool_size', '__db_cache_size')
  9     and dba_hist_snapshot.snap_id between 41127 and 41129
 10   order by 3, 2
 11  /

BEGIN_INTERVAL_TIME            PARAMETER_NAME                 VALUE
------------------------------ ------------------------------ -----------
27-SEP-11 14.00.47.311         __db_cache_size                2415919104
27-SEP-11 16.42.44.000         __db_cache_size                2415919104
27-SEP-11 14.00.47.311         __shared_pool_size             18253611008
27-SEP-11 16.42.44.000         __shared_pool_size             18253611008

В соответственно маленькие значения были установлены внутренние параметры _db_block_buffers и _small_table_threshold:

SQL> @param_ small_table

NAME                    VALUE  IS_DEF  IS_MOD  IS_ADJ  DSC
----------------------- ------ ------- ------- ------- ----------------------------------------------
_small_table_threshold  11782  TRUE    FALSE   FALSE   threshold level of table size for direct reads

— параметр непосредственно влияющий на выбор доступа к блокам таблицы при операции FULL TABLE SCAN между чтением через буферный пул SGA (блоки кэшируются) и serial direct path read (блоки таблицы не кэшируются и перечитываются в PGA при каждом последующем выполнении запроса) — при превышении размера таблицы (в блоках бд) величины _small_table_threshold*5 таблица считается большой и кэш SGA не используется.
В этом случае после рестарта таблица в 77312 блоков стала больше 11782*5=58910, следовательно начал использовался serial direct path read
После второго рестарта с уже контролируемым поведением ASMM (был установлен нижний предел размера buffer cache параметром db_cache_size):

SQL> @param db_cache_size

NAME           VALUE        IS_DEF   IS_MOD  DSC
-------------- ------------ -------- ---------- --------------------------------------------------------
db_cache_size  10737418240  FALSE    FALSE   Size of DEFAULT buffer pool for standard block size buffers

соответственно увеличились значения _db_block_buffers и _small_table_threshold:

NAME                    VALUE    IS_DEF   IS_MOD  IS_ADJ   DSC
----------------------- -------- -------- ------- -------- ---------------------------------------------
_db_block_buffers       2435840  TRUE     FALSE   FALSE    Number of database blocks cached in memory: hidden parameter
_small_table_threshold  48716    TRUE     FALSE   FALSE    threshold level of table size for direct read

таблица «стала маленькой» 77312 < 48716*5=243580, и «как маленькая» — стала кэшироваться в Buffer Cache SGA:

SQL> @v$bh_by_obj SCOTT SOME_TABLE

BL_STATUS  DB_BL_COUNT BH_BL_COUNT
---------- ----------- -----------
xcur             75563       75563

Время выполнения и physical reads значительно уменьшаются:)

Elapsed: 00:00:00.80
...
Statistics
---------------------------------------------------
...
     101470  consistent gets
          0  physical reads
...
          2  rows processed

, уменьшается время выполнения проблемных запросов, ситуация возвращается к нормальному состоянию

Таким образом, при необходимости обеспечения стабильности времён (не планов) выполнения запросов при использовании ASMM можно:

  • фиксировать нижнюю границу Buffer Cache SGA параметром db_cache_size, таким образом фиксируя размер small table и регулируя включение режима serial direct path read
  • либо то же самое можно сделать напрямую через параметр _small_table_threshold:
SQL> alter system set "_small_table_threshold"=999999 scope=spfile;

System altered.

SQL> startup force

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

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

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