Oracle mechanics

03.06.2013

Параметр _very_large_object_threshold

Filed under: Oracle,Oracle new features,parameters — Игорь Усольцев @ 23:20
Tags: ,

English version

Начиная с Oracle 11.2 на выбор способа чтения блоков индекса между serial direct path read и буферизованным чтением (через db buffer cache SGA) при операции INDEX FAST FULL SCAN (IFFS) влияет параметр:

SQL> @param_ _very_large_object_threshold

NAME                         VALUE IS_DEF   DSC
---------------------------- ----- -------- -----------------------------------------------------
_very_large_object_threshold 500   TRUE     upper threshold level of object size for direct reads

Правильное значение этого параметра: процент от размера буферного кэша (точнее, параметра _db_block_buffers) при превышении которого индекс считается «большим» и в процессе IFFS будет использоваться direct path read, если же размер индекса меньше — IFFS будет выполняться стандартными чтениями блоков через буферный кэш SGA — в точности как показал Саян Малакшинов в своём блоге Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats), где кроме этого параметра описывает хинт INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X), частовстречаемый / применяемый в SQL Profiles

В процессе тестирования на разных платформах, о котором мы договорились с Саяном, замечено также влияние значения параметра _very_large_object_threshold на управление direct path read с использованием event 10949 при полном сканировании таблиц

Тестовая схема из поста Саяна:

drop table xt_iffs purge
/

create table xt_iffs as
with gen as(
            select level a,mod(level,10) b,lpad(1,50,1) c
            from dual
            connect by level<=1e3 ) select gen.* from gen,gen gen2
/

exec dbms_stats.gather_table_stats('','XT_IFFS') create index ix_iffs on xt_iffs(a,b)
/

Windows платформа:

SQL> @inst

INST_ID INSTANCE_NAME        VERSION    PLATFORM_NAME
------- -------------------- ---------- ----------------------------
1*      orcl1123             11.2.0.3.0 Microsoft Windows x86 64-bit

SQL> @param

NAME                                       VALUE
------------------------------------------ ---------
_very_large_object_threshold               500       -- по умолчанию
_db_block_buffers                          30442     -- на момент старта системы, в блоках
db_cache_size                              0         -- ASMM
__db_cache_size                            260046848 -- текущее значение в байтах

SQL> select blocks, trunc(bytes/1024/1024) as MB, trunc(blocks/30442*100,2) as "threshold%" from user_segments where segment_name = 'IX_IFFS';

    BLOCKS         MB threshold%
---------- ---------- ----------
      2560         20        8.4 -- тестовый индекс 20 МБ, что составляет 8.4% от _db_block_buffers

SQL> SELECT SUM(A) FROM XT_IFFS T;

    SUM(A)
----------
 500500000

1 row selected.

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
logical read bytes from cache                                        20447232 -- запрос, использующий IFFS использует кэш,
...
physical reads cache                                                     2485
physical reads                                                           2485
free buffer requested                                                    2485
...
index fast full scans (full)                                                1
...

Session Wait Events

NAME                                                                    WAITS      TIME_MS     TIMEOUTS AVG_WAIT_MS
---------------------------------------------------------------- ------------ ------------ ------------ -----------
SQL*Net message from client                                                 2        66604            0     33301,8
db file scattered read                                                     53          132            0         2,5 -- что подтверждается ожиданиями
SQL*Net message to client                                                   2            0            0           0

-- сделаем так, чтобы индекс превысил значение параметра, стал БОЛЬШИМ ОБЪЕКТОМ

SQL> alter session set "_very_large_object_threshold"=8;

Session altered.

SQL> SELECT SUM(A) FROM XT_IFFS T;

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
...
physical reads                                                           2486
physical reads direct                                                    2486
...
index fast full scans (direct read)                                         1 -- запрос выполняется с использованием serial direct read
index fast full scans (full)                                                1

-- небольшое изменение параметра - и индекс вновь становится маленьким

SQL> alter session set "_very_large_object_threshold"=9;

Session altered.

SQL> SELECT SUM(A) FROM XT_IFFS T;

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
logical read bytes from cache                                        20447232 -- при IFFS используется буферный кэш
consistent gets from cache (fastpath)                                    2496
consistent gets                                                          2496
session logical reads                                                    2496
consistent gets from cache                                               2496
...
index fast full scans (full)                                                1

В последнем примере не совсем понятно, от какого параметра правильно рассчитывать _very_large_object_threshold, т.к. процент сегмента индекса IX_IFFS, рассчитанный относительно __db_cache_size мало отличается от расчёта с _db_block_buffers:

SQL> select blocks, trunc(bytes/1024/1024) as MB, trunc(blocks/(260046848/8192)*100,2) as "threshold%" from user_segments where segment_name = 'IX_IFFS';

    BLOCKS         MB threshold%
---------- ---------- ----------
      2560         20       8.06

Если динамически увеличить текущий размер буферного кэша db_cache_size (благо ASMM) можно убедиться, что threshold% рассчитывается Oracle именно относительно _db_block_buffers:

SQL> alter system set db_cache_size=300000000;

System altered

SQL> @param_ db_cache_size

NAME                                       VALUE
------------------------------------------ ---------------------
db_cache_size                              301989888
__db_cache_size                            301989888

SQL> select * from v$sgainfo where name = 'Buffer Cache Size';

NAME                                            BYTES RESIZEABLE
------------------------------------------ ---------- ----------
Buffer Cache Size                           301989888 Yes

SQL> select blocks, trunc(bytes/1024/1024) as MB, trunc(blocks/(301989888/8192)*100,2) as "threshold%" from user_segments where segment_name = 'IX_IFFS';

    BLOCKS         MB threshold%
---------- ---------- ----------
      2560         20       6.94 -- индекс занимает 6.94% от db_cache_size

SQL> @param_ _db_block_buffers

NAME                                       VALUE
------------------------------------------ ---------------------
_db_block_buffers                          30442                 -- ожидаемо не изменился

-- при таком значении индекс является уже БОЛЬШИМ при расчёте относительно _db_block_buffers, т.к.
-- занимает более 8%, но всё ещё маленьким относительно при расчёте с db_cache_size - меньше 7%:

SQL> alter session set "_very_large_object_threshold"=8;

Session altered.

SQL> alter system flush buffer_cache;

System altered

SQL> alter system flush shared_pool;

System altered

SQL> SELECT SUM(A) FROM XT_IFFS T;

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
...
physical reads                                                           2486
consistent gets direct                                                   2486 -- используется direct read, т.е. расчёт от _db_block_buffers
physical reads direct                                                    2486
...
index fast full scans (full)                                                1
index fast full scans (direct read)                                         1

Выставление event 10949 никак не влияет на выбор serial direct path read при INDEX FAST FULL SCAN — что, в общем-то и не обещано в кратком описании события :)

SQL> @param_ _very_large_object_threshold

NAME                                       VALUE
------------------------------------------ -----
_very_large_object_threshold               500

SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

SQL> SELECT SUM(A) FROM XT_IFFS T;

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
logical read bytes from cache                                        20447232
...
consistent gets from cache                                               2496
consistent gets from cache (fastpath)                                    2496
...
index fast full scans (full)                                                1

SQL> alter session set "_very_large_object_threshold"=8;

Session altered.

SQL> SELECT SUM(A) FROM XT_IFFS T;

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
...
index fast full scans (direct read)                                         1
index fast full scans (full)                                                1
...

Session Wait Events

NAME                                                                    WAITS      TIME_MS     TIMEOUTS AVG_WAIT_MS
---------------------------------------------------------------- ------------ ------------ ------------ -----------
SQL*Net message from client                                                 3        27492            0      9164,1
direct path read                                                           30           30            0           1
...

Интереснее, что при полном сканировании таблицы event 10949 также отключает direct path read в зависимости от значения _very_large_object_threshold — % размера табличного сегмента к _db_block_buffers — хотя эта зависимость не такая простая как в случае с INDEX FAST FULL SCAN:

SQL> select blocks, trunc(bytes/1024/1024) as MB, trunc(blocks/30442*100,2) as "threshold%" from user_segments where segment_name = 'XT_IFFS';

    BLOCKS         MB threshold%
---------- ---------- ----------
      9216         72      30.27 -- таблица занимает 30% кэша

SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

SQL> SELECT /*+ FULL(T)*/ SUM(A) FROM XT_IFFS T;

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     4 |  2416   (1)| 00:00:29 |
|   1 |  SORT AGGREGATE    |         |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| XT_IFFS |  1000K|  3906K|  2416   (1)| 00:00:29 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8749  consistent gets
          0  physical reads  -- direct read не используется
...

-- по аналогии с индексом попробуем сделать таблицу БОЛЬШИМ ОБЪЕКТОМ:

SQL> alter session set "_very_large_object_threshold"=30;

Session altered.

SQL> SELECT /*+ FULL(T)*/ SUM(A) FROM XT_IFFS T;

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
logical read bytes from cache                                        72310784
...
consistent gets from cache (fastpath)                                    8809
consistent gets from cache                                               8809 -- по прежнему читается из кэша
...
table scans (long tables)                                                   1

-- ещё немного снизим порог:

SQL> alter session set "_very_large_object_threshold"=26;

Session altered.

SQL> SELECT /*+ FULL(T)*/ SUM(A) FROM XT_IFFS T;

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
...
consistent gets                                                          8743
consistent gets direct                                                   8740
...
physical reads                                                           8740
...
physical reads direct                                                    8740
...
table scans (long tables)                                                   1
...
table scans (direct read)                                                   1 -- прямое чтение
...

Session Wait Events

NAME                                                                    WAITS      TIME_MS     TIMEOUTS AVG_WAIT_MS
---------------------------------------------------------------- ------------ ------------ ------------ -----------
SQL*Net message from client                                                 2        15199            0      7599,3
direct path read                                                          144          331            0         2,3 -- соответствующее ожидание
...

— связь между _very_large_object_threshold и размером таблицы в МБ (72 МБ), о которой периодически пишут, на мой взгляд явно не просматривается. Да и странно представить параметр, единица измерения которого меняется в зависимости от типа операции

Модифицированное тестирования IFFS на платформе Linux x86_64 со значительно увеличенным размером тестовых сегментов и индексом по одному полю подтверждает предыдущее определение параметра, более явно показывает отсутствие связи между _very_large_object_threshold и размером сегмента в МБ и, кстати, демонстрирует почти двухкратное преимущество direct read по времени выполнения простого запроса:

SQL> @inst

INST_ID INSTANCE_NAME  HOST_NAME      VERSION    PLATFORM_NAME
------- -------------- -------------- ---------- ----------------
1*      INST1          db1.domain.net 11.2.0.3.0 Linux x86 64-bit

SQL> @param

NAME                                       VALUE
------------------------------------------ -----------
_db_block_buffers                          3780500
db_cache_size                              0          -- ASMM
__db_cache_size                            34762391552
db_block_size                              8192

SQL> create table xt_iffs as
  2  with gen as(
  3              select level a, lpad(1,50,1) c
  4              from dual
  5              connect by level<=1e4                -- увеличенный размер таблицы
  6  )
  7 select gen.*
  8  from gen,gen gen2
  9  /

Table created.

SQL> exec dbms_stats.gather_table_stats('','XT_IFFS')

PL/SQL procedure successfully completed.

SQL> create index ix_iffs on xt_iffs(a);              -- индекс по одному полю

Index created.

SQL> select blocks, bytes/1024/1024 as MB from user_segments where segment_name = 'IX_IFFS';

    BLOCKS         MB
---------- ----------
    213022    1664.23 --размер индекса в МБ внушает и несравнимо больше значения _very_large_object_threshold

SQL> select round((213022/3780500)*100,2) as "threshold%" from dual;

threshold%
----------
      5.63

SQL> alter session set "_very_large_object_threshold"=6;

Session altered.

SQL> SELECT SUM(A) FROM XT_IFFS T;

Elapsed: 00:00:38.06                                    -- IFFS через кэш в 2 раза медленнее

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
logical read bytes from cache                                      1715216384
physical read total bytes                                          1715159040
physical read bytes                                                1715159040
...
free buffer inspected                                                  212179 -- читается в кэш
...
physical read IO requests                                                1645
physical read total multi block requests                                 1639
...
index fast full scans (full)                                                1
...

Session Wait Events

NAME                                                                    WAITS      TIME_MS     TIMEOUTS AVG_WAIT_MS
---------------------------------------------------------------- ------------ ------------ ------------ -----------
SQL*Net message from client                                                 2        73827            0     36913,7
db file scattered read                                                   1643        23674            0        14,4
db file sequential read                                                     2           14            0         7,1
SQL*Net message to client                                                   2            0            0           0

-- при расчётном уменьшении порога до 5%:

SQL> alter session set "_very_large_object_threshold"=5;

Session altered.

SQL> SELECT SUM(A) FROM XT_IFFS T;

    SUM(A)
----------
5.0005E+11

Elapsed: 00:00:16.47                                    -- direct read более чем в 2 раза быстрее!

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
physical read total bytes                                          1715167232
...
consistent gets direct                                                 209371
physical reads direct                                                  209371 -- получаем прямое чтение
...
index fast full scans (direct read)                                         1
...
index fast full scans (full)                                                1

Session Wait Events

NAME                                                                    WAITS      TIME_MS     TIMEOUTS AVG_WAIT_MS
---------------------------------------------------------------- ------------ ------------ ------------ -----------
direct path read                                                          795         8218            0        10,3 -- ***
SQL*Net message from client                                                 2         6492            0      3245,9
events in waitclass Other                                                   2            3            0         1,5
enq: KO - fast object checkpoint                                            2            0            0         0,2 -- ***

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

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

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