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 |
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 with select from connect select /
exec
create / |
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 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) ———- 500500000
1 row selected.
Session Statistics
NAME DELTA —————————————————————- ———— logical read … physical reads cache 2485 physical reads 2485 free … index …
Session Wait Events
NAME WAITS TIME_MS TIMEOUTS AVG_WAIT_MS —————————————————————- ———— ———— ———— ———– SQL*Net message from db file scattered read 53 132 0 2,5 — top non-idle wait event confirm buffer cache IO usage SQL*Net message to
— Now we artificially make index HUGE
SQL> alter
Session altered.
SQL> SELECT
Session Statistics
NAME DELTA —————————————————————- ———— … physical reads 2486 physical reads direct 2486 … index index
— if we a bit increase parameter – index again becomes SMALL
SQL> alter
Session altered.
SQL> SELECT
Session Statistics
NAME DELTA —————————————————————- ———— logical read consistent gets from consistent gets 2496 session logical reads 2496 consistent gets from … index |
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
NAME BYTES RESIZEABLE —————————————— ———- ———- Buffer Cache Size 301989888 Yes
SQL> select
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 altered.
SQL> alter
System altered
SQL> alter
System altered
SQL> SELECT
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 index |
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 |
And practical tests:
| SQL> @param_ _very_large_object_threshold
NAME VALUE —————————————— —– _very_large_object_threshold 500
SQL> alter
Session altered.
SQL> SELECT
Session Statistics
NAME DELTA —————————————————————- ———— logical read … consistent gets from consistent gets from … index
SQL> alter
Session altered.
SQL> SELECT
Session Statistics
NAME DELTA —————————————————————- ———— … index index …
Session Wait Events
NAME WAITS TIME_MS TIMEOUTS AVG_WAIT_MS —————————————————————- ———— ———— ———— ———– SQL*Net message from 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 altered.
SQL> SELECT
—————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————— | 0 | SELECT | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ——————————————————————————
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 altered.
SQL> SELECT
Session Statistics
NAME DELTA —————————————————————- ———— logical read … consistent gets from consistent gets from … table
— and a little more:
SQL> alter
Session altered.
SQL> SELECT
Session Statistics
NAME DELTA —————————————————————- ———— … consistent gets 8743 consistent gets direct 8740 … physical reads 8740 … physical reads direct 8740 … table … table …
Session Wait Events
NAME WAITS TIME_MS TIMEOUTS AVG_WAIT_MS —————————————————————- ———— ———— ———— ———– SQL*Net message from 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 2 with 3 select 4 from 5 connect 6 ) 7 select 8 from 9 /
Table
SQL> exec
PL/SQL procedure
SQL> create
Index
SQL> select
BLOCKS MB ———- ———- 213022 1664.23 — index size significantly exceeds _very_large_object_threshold value
SQL> select
threshold% ———- 5.63
SQL> alter
Session altered.
SQL> SELECT
Elapsed: 00:00:38.06 — IFFS over buffer cache twice slower
Session Statistics
NAME DELTA —————————————————————- ———— logical read physical read physical read … free … physical read physical read … index …
Session Wait Events
NAME WAITS TIME_MS TIMEOUTS AVG_WAIT_MS —————————————————————- ———— ———— ———— ———– SQL*Net message from db file scattered read 1643 23674 0 14,4 db file sequential read 2 14 0 7,1 SQL*Net message to
— with decreased threshold down to 5%:
SQL> alter
Session altered.
SQL> SELECT
SUM(A) ———- 5.0005E+11
Elapsed: 00:00:16.47 — direct read more than twice quicker!
Session Statistics
NAME DELTA —————————————————————- ———— physical read … consistent gets direct 209371 physical reads direct 209371 — direct read … index … index
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 events in enq: KO – fast object checkpoint 2 0 0 0,2 — and another appropriate wait event |
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.