Oracle  Database 11g는 내부적으로 Table 크기에 대해 SMALL(Buffer Cache의 2%보다 작을 때), VERYLARGE( Buffer Cache의 5배 크기보다 클 때)로 구분하며  테이블에 대해 Full Table Sacn(FTS)을 할 때 Buffer Cache크기, Segment 크기, Buffer Cache에 Cache된 정도, 기타 통계 등을 고려해 Direct Path Read를 할 것인지 Buffer Cache에서 읽을지(Buffer Read)를 결정 합니다.

 

 

즉 SGA 의 메모리 값 수치를 키우면 기본적으로 DB 버퍼에서 읽고 있던 SQL 이 direct Read 로 변경 되거나 Direct Read로 읽고 있던 SQL이 버퍼캐시를 읽을려고 한다.

-> SQL플랜이 변경 된다.


 

< _very_large_object_threshold 히든 파라미터에 대한 테스트 >

 

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

 

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

One thought on “Oracle SGA 값을 증가 시킬 때 발생 장애 원인”
  1. Likewise numerous global brands, such as for example Nike and
    Reebok can charge premium costs for their products as a result of the devoted following17.

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다