Oracle mechanics

Igor Usoltsev's Oracle blog

_very_large_object_threshold

Posted by Игорь Усольцев on June 4, 2013

original in Russian

From Oracle 11.2 choice between serial direct path read and buffered read (through db buffer cache SGA) for INDEX FAST FULL SCAN (IFFS) operation depends on parameter:

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

The correct parameter meaning is: the percent of buffer cache size (more precisely % of _db_block_buffers value). If index size exceeds this threshold than this index is considered as “huge”, and for IFFS operation will be used direct path read. Otherwise IFFS will use standard buffered reads – exactly as Sayan Malakshinov described in Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats). In the same note Sayan described hint INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X), which can be often noticed for SQL Profiles usage

Simple tests show _very_large_object_threshold influence on direct path read usage for full table scan, together with event 10949

Sayan test schema:

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)
/

On Windows x86_64 platform:

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       -- default value
_db_block_buffers                          30442     -- on instance startup in blocks
db_cache_size                              0         -- ASMM
__db_cache_size                            260046848 -- current buffer cache size in bytes

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 -- our test index size is 20 MB, which is 8.4% of _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 -- query with IFFS uses buffer cache SGA
...
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 -- top non-idle wait event confirm buffer cache IO usage
SQL*Net message to client                                                   2            0            0           0

-- Now we artificially make index HUGE

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 -- query uses serial direct read
index fast full scans (full)                                                1

-- if we a bit increase parameter - index again becomes SMALL

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 -- and again IFFS uses buffer cache
consistent gets from cache (fastpath)                                    2496
consistent gets                                                          2496
session logical reads                                                    2496
consistent gets from cache                                               2496
...
index fast full scans (full)                                                1

In last tests there is not clear how to calculate _very_large_object_threshold value correctly, because this percent calculation based on __db_cache_size insignificantly differs from the same of _db_block_buffers value:

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

But dynamic (on fly) increase of current db_cache_size value (by ASMM usage!) can exactly shows that discussed threshold% is calculated by Oracle using _db_block_buffers value:

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 -- now index size is 6.94% of db_cache_size

SQL> @param_ _db_block_buffers

NAME                                       VALUE
------------------------------------------ ---------------------
_db_block_buffers                          30442                 -- not changed from startup, as expected

-- and now index can be considered as HUGE if Oracle calculation is based on _db_block_buffers value,
-- (more than 8%), while index is SMALL relatively db_cache_size - less than 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 -- now used direct read, which proves that threshold% is calculated from _db_block_buffers
physical reads direct                                                    2486
...
index fast full scans (full)                                                1
index fast full scans (direct read)                                         1

Setting event 10949 don’t affect on serial direct path read choice for INDEX FAST FULL SCAN – which is quite expected from short event description 🙂

$ oerr ora 10949
10949, 00000, "Disable autotune direct path read for full table scan"
// *Cause:
// *Action:  Disable autotune direct path read for serial full table scan.

And practical tests:

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
...

More interesting is fact that for full table scan operation event 10949 can be used for direct path read usage choice together with _very_large_object_threshold (as table segment size percent of _db_block_buffers value) for full table scan. But this dependence not so simple as for 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 -- segment size is about 30% of buffer cache

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 was not used
...

-- as we did for index, trying to make our table HUGE:

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 -- still reads over cache
...
table scans (long tables)                                                   1

-- and a little more:

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 -- direct path read
...

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 -- and appropriate wait event
...

– furthermore there is not (often noticed in different sources) accordance between _very_large_object_threshold and table size in Megabytes (72 MB), imho. Moreover this would be very strange to use parameter with unit which depends on SQL Plan operation

IFFS tests on Linux x86_64 platform with significantly increased segment size confirm previous parameter definition, and obviously demonstrate an absence of any accordance between _very_large_object_threshold and segment size (in Megabytes). And besides these tests show how direct read usage speed up simple query execution almost twice!:

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                -- table with increased segment size
  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 -- index size significantly exceeds _very_large_object_threshold value

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 over buffer cache twice slower

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
logical read bytes from cache                                      1715216384
physical read total bytes                                          1715159040
physical read bytes                                                1715159040
...
free buffer inspected                                                  212179 -- for cache blocks reading
...
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

-- with decreased threshold down to 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 more than twice quicker!

Session Statistics

NAME                                                                    DELTA
---------------------------------------------------------------- ------------
physical read total bytes                                          1715167232
...
consistent gets direct                                                 209371
physical reads direct                                                  209371 -- direct read
...
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 -- with one appropriate wait event
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 -- and another appropriate wait event
Advertisements

One Response to “_very_large_object_threshold”

  1. […] UPD: btw previously I wrote that _very_large_object_threshold is a percentage of _db_block_buffers and Igor Usoltsev have tested and confirmed my guess […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: