{"id":1292,"date":"2016-01-29T01:47:30","date_gmt":"2016-01-29T01:47:30","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1292"},"modified":"2023-04-14T14:44:42","modified_gmt":"2023-04-14T05:44:42","slug":"oracle-sga-%ea%b0%92%ec%9d%84-%ec%a6%9d%ea%b0%80-%ec%8b%9c%ed%82%ac-%eb%95%8c-%eb%b0%9c%ec%83%9d-%ec%9e%a5%ec%95%a0-%ec%9b%90%ec%9d%b8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1292","title":{"rendered":"Oracle SGA \uac12\uc744 \uc99d\uac00 \uc2dc\ud0ac \ub54c \ubc1c\uc0dd \uc7a5\uc560 \uc6d0\uc778"},"content":{"rendered":"

 <\/p>\n

Oracle \u00a0Database 11g\ub294\u00a0\ub0b4\ubd80\uc801\uc73c\ub85c Table \ud06c\uae30\uc5d0 \ub300\ud574 SMALL(Buffer Cache\uc758 2%\ubcf4\ub2e4 \uc791\uc744 \ub54c), VERYLARGE( Buffer Cache\uc758 5\ubc30 \ud06c\uae30\ubcf4\ub2e4 \ud074 \ub54c)\ub85c \uad6c\ubd84\ud558\uba70 \u00a0<\/span>\ud14c\uc774\ube14\uc5d0 \ub300\ud574 Full Table Sacn(FTS)\uc744 \ud560 \ub54c Buffer Cache\ud06c\uae30, Segment \ud06c\uae30, Buffer Cache\uc5d0 Cache\ub41c \uc815\ub3c4, \uae30\ud0c0 \ud1b5\uacc4 \ub4f1\uc744 \uace0\ub824\ud574\u00a0<\/span>Direct Path Read\ub97c \ud560 \uac83\uc778\uc9c0 Buffer Cache\uc5d0\uc11c \uc77d\uc744\uc9c0(Buffer Read)\ub97c \uacb0\uc815 \ud569\ub2c8\ub2e4.<\/span><\/p>\n

 <\/p>\n

<\/p>\n

 <\/p>\n

\uc989 SGA \uc758 \uba54\ubaa8\ub9ac \uac12 \uc218\uce58\ub97c \ud0a4\uc6b0\uba74 \uae30\ubcf8\uc801\uc73c\ub85c DB \ubc84\ud37c\uc5d0\uc11c \uc77d\uace0 \uc788\ub358 SQL \uc774 direct Read \ub85c \ubcc0\uacbd \ub418\uac70\ub098 Direct Read\ub85c \uc77d\uace0 \uc788\ub358 SQL\uc774 \ubc84\ud37c\uce90\uc2dc\ub97c \uc77d\uc744\ub824\uace0 \ud55c\ub2e4.<\/span><\/b><\/p>\n

-> SQL\ud50c\ub79c\uc774 \ubcc0\uacbd \ub41c\ub2e4.<\/span><\/b><\/p>\n


\n<\/span><\/b><\/p>\n

 <\/p>\n

\n

< _very_large_object_threshold \ud788\ub4e0 \ud30c\ub77c\ubbf8\ud130\uc5d0 \ub300\ud55c \ud14c\uc2a4\ud2b8 ><\/b><\/span><\/p>\n

\u00a0<\/span><\/p>\n

Oracle 11.2 choice between\u00a0serial direct path read<\/strong>\u00a0and buffered read (through\u00a0db buffer cache SGA<\/strong>) for\u00a0INDEX FAST FULL SCAN<\/strong>\u00a0(IFFS) operation depends on parameter:<\/span><\/p>\n

\n

\n

\n\n\n<\/colgroup>\n\n\n
SQL> @param_ _very_large_object_threshold<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>VALUE IS_DEF\u00a0\u00a0 DSC<\/span>
\n<\/span><\/span><\/p>\n

—————————- —– ——– —————————————————–<\/span>
\n<\/span><\/span><\/p>\n

_very_large_object_threshold 500\u00a0\u00a0 TRUE<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>upper<\/span>
\n<\/span>threshold level<\/span>
\n<\/span>of<\/span>
\n<\/span>object size<\/span>
\n<\/span>for<\/span>
\n<\/span>direct reads<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

The correct parameter meaning is: the percent of buffer cache size (more precisely % of\u00a0_db_block_buffers<\/strong>\u00a0value). If index size exceeds this threshold than this index is considered as “huge”, and for IFFS operation will be used\u00a0direct path read<\/strong>. Otherwise IFFS will use standard buffered reads \u2013 exactly as Sayan Malakshinov described in\u00a0Example of controlling “direct path reads” decision through SQL profile hints (index_stats\/table_stats)<\/strong><\/span><\/a>. In the same note Sayan described hint\u00a0INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X)<\/strong>, which can be often noticed for\u00a0SQL Profiles<\/strong>\u00a0usage
\n<\/span><\/p>\n

Simple tests show\u00a0_very_large_object_threshold<\/strong>\u00a0influence on\u00a0direct path read<\/strong>\u00a0usage for\u00a0full table scan<\/strong>, together with\u00a0event 10949<\/strong>
\n<\/span><\/p>\n

Sayan test schema:
\n<\/span><\/p>\n

?<\/span><\/a>
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
drop<\/span>
\n<\/span>table<\/span>
\n<\/span>xt_iffs purge<\/span>
\n<\/span><\/span><\/p>\n

\/<\/span>
\n<\/span><\/span><\/p>\n

create<\/span>
\n<\/span>table<\/span>
\n<\/span>xt_iffs as<\/span>
\n<\/span><\/span><\/p>\n

with<\/span>
\n<\/span>gen as(<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0select<\/span>
\n<\/span>level<\/span>
\n<\/span>a,mod(level,10) b,lpad(1,50,1) c<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0from<\/span>
\n<\/span>dual<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0connect<\/span>
\n<\/span>by<\/span>
\n<\/span>level<=1e3 )<\/span>
\n<\/span><\/span><\/p>\n

select<\/span>
\n<\/span>gen.* from<\/span>
\n<\/span>gen,gen gen2<\/span>
\n<\/span><\/span><\/p>\n

\/<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

exec<\/span>
\n<\/span>dbms_stats.gather_table_stats(”,’XT_IFFS’)<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

create<\/span>
\n<\/span>index<\/span>
\n<\/span>ix_iffs on<\/span>
\n<\/span>xt_iffs(a,b)<\/span>
\n<\/span><\/span><\/p>\n

\/<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

On Windows x86_64 platform:
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> @inst<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

INST_ID INSTANCE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VERSION\u00a0\u00a0\u00a0 PLATFORM_NAME<\/span>
\n<\/span><\/span><\/p>\n

——- ——————– ———- —————————-<\/span>
\n<\/span><\/span><\/p>\n

1*\u00a0\u00a0\u00a0\u00a0\u00a0 orcl1123\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11.2.0.3.0 Microsoft Windows x86 64-bit<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> @param<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>VALUE<\/span>
\n<\/span><\/span><\/p>\n

—————————————— ———<\/span>
\n<\/span><\/span><\/p>\n

_very_large_object_threshold\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 500\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — default value<\/span>
\n<\/span><\/span><\/p>\n

_db_block_buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 30442\u00a0\u00a0\u00a0\u00a0 — on instance startup in blocks<\/span>
\n<\/span><\/span><\/p>\n

db_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — ASMM<\/span>
\n<\/span><\/span><\/p>\n

__db_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 260046848 — current buffer cache size in bytes<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> select<\/span>
\n<\/span>blocks, trunc(bytes\/1024\/1024) as<\/span>
\n<\/span>MB, trunc(blocks\/30442*100,2) as<\/span>
\n<\/span>“threshold%”<\/span>
\n<\/span>from<\/span>
\n<\/span>user_segments where<\/span>
\n<\/span>segment_name = ‘IX_IFFS’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

\u00a0\u00a0\u00a0\u00a0BLOCKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MB threshold%<\/span>
\n<\/span><\/span><\/p>\n

———- ———- ———-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02560\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8.4 — our test index size is 20 MB, which is 8.4% of _db_block_buffers<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

\u00a0\u00a0\u00a0\u00a0SUM(A)<\/span>
\n<\/span><\/span><\/p>\n

———-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0500500000<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

1 row selected.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

logical read<\/span>
\n<\/span>bytes from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20447232 — query with IFFS uses buffer cache SGA<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

physical reads cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2485<\/span>
\n<\/span><\/span><\/p>\n

physical reads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2485<\/span>
\n<\/span><\/span><\/p>\n

free<\/span>
\n<\/span>buffer requested\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2485<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Wait Events<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>WAITS\u00a0\u00a0\u00a0\u00a0\u00a0 TIME_MS\u00a0\u00a0\u00a0\u00a0 TIMEOUTS AVG_WAIT_MS<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ———— ———— ———— ———–<\/span>
\n<\/span><\/span><\/p>\n

SQL*Net message from<\/span>
\n<\/span>client\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 66604\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0 33301,8<\/span>
\n<\/span><\/span><\/p>\n

db file scattered read<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>53\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 132\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2,5 — top non-idle wait event confirm buffer cache IO usage<\/span>
\n<\/span><\/span><\/p>\n

SQL*Net message to<\/span>
\n<\/span>client\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

— Now we artificially make index HUGE<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=8;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

physical reads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2486<\/span>
\n<\/span><\/span><\/p>\n

physical reads direct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2486<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (direct read)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 — query uses serial direct read<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

— if we a bit increase parameter – index again becomes SMALL<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=9;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

logical read<\/span>
\n<\/span>bytes from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20447232 — and again IFFS uses buffer cache<\/span>
\n<\/span><\/span><\/p>\n

consistent gets from<\/span>
\n<\/span>cache (fastpath)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2496<\/span>
\n<\/span><\/span><\/p>\n

consistent gets\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2496<\/span>
\n<\/span><\/span><\/p>\n

session logical reads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2496<\/span>
\n<\/span><\/span><\/p>\n

consistent gets from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2496<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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:
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> select<\/span>
\n<\/span>blocks, trunc(bytes\/1024\/1024) as<\/span>
\n<\/span>MB, trunc(blocks\/(260046848\/8192)*100,2) as<\/span>
\n<\/span>“threshold%”<\/span>
\n<\/span>from<\/span>
\n<\/span>user_segments where<\/span>
\n<\/span>segment_name = ‘IX_IFFS’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

\u00a0\u00a0\u00a0\u00a0BLOCKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MB threshold%<\/span>
\n<\/span><\/span><\/p>\n

———- ———- ———-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02560\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8.06<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

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\u00a0_db_block_buffers<\/strong>\u00a0value:
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> alter<\/span>
\n<\/span>system set<\/span>
\n<\/span>db_cache_size=300000000;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

System altered<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> @param_ db_cache_size<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>VALUE<\/span>
\n<\/span><\/span><\/p>\n

—————————————— ———————<\/span>
\n<\/span><\/span><\/p>\n

db_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 301989888<\/span>
\n<\/span><\/span><\/p>\n

__db_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 301989888<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> select<\/span>
\n<\/span>* from<\/span>
\n<\/span>v$sgainfo where<\/span>
\n<\/span>name<\/span>
\n<\/span>= ‘Buffer Cache Size’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>BYTES RESIZEABLE<\/span>
\n<\/span><\/span><\/p>\n

—————————————— ———- ———-<\/span>
\n<\/span><\/span><\/p>\n

Buffer Cache Size<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>301989888 Yes<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> select<\/span>
\n<\/span>blocks, trunc(bytes\/1024\/1024) as<\/span>
\n<\/span>MB, trunc(blocks\/(301989888\/8192)*100,2) as<\/span>
\n<\/span>“threshold%”<\/span>
\n<\/span>from<\/span>
\n<\/span>user_segments where<\/span>
\n<\/span>segment_name = ‘IX_IFFS’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

\u00a0\u00a0\u00a0\u00a0BLOCKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MB threshold%<\/span>
\n<\/span><\/span><\/p>\n

———- ———- ———-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02560\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6.94 — now index size is 6.94% of db_cache_size<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> @param_ _db_block_buffers<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>VALUE<\/span>
\n<\/span><\/span><\/p>\n

—————————————— ———————<\/span>
\n<\/span><\/span><\/p>\n

_db_block_buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 30442\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — not changed from startup, as expected<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

— and now index can be considered as HUGE if Oracle calculation is based on _db_block_buffers value,<\/span>
\n<\/span><\/span><\/p>\n

— (more than 8%), while index is SMALL relatively db_cache_size – less than 7%:<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=8;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>system flush buffer_cache;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

System altered<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>system flush shared_pool;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

System altered<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

physical reads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2486<\/span>
\n<\/span><\/span><\/p>\n

consistent gets direct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2486 — now used direct read, which proves that threshold% is calculated from _db_block_buffers<\/span>
\n<\/span><\/span><\/p>\n

physical reads direct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2486<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (direct read)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Setting\u00a0event 10949<\/strong>\u00a0don’t affect on\u00a0serial direct path read<\/strong>\u00a0choice for INDEX FAST FULL SCAN \u2013 which is quite expected from short event description\u00a0\ud83d\ude42
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
$ oerr ora 10949<\/span>
\n<\/span><\/span><\/p>\n

10949, 00000, “Disable autotune direct path read for full table scan”<\/span>
\n<\/span><\/span><\/p>\n

\/\/ *Cause:<\/span>
\n<\/span><\/span><\/p>\n

\/\/ *Action:\u00a0 Disable autotune direct path read<\/span>
\n<\/span>for<\/span>
\n<\/span>serial full<\/span>
\n<\/span>table<\/span>
\n<\/span>scan.<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

And practical tests:
\n<\/span><\/p>\n

?<\/span><\/a>
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> @param_ _very_large_object_threshold<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>VALUE<\/span>
\n<\/span><\/span><\/p>\n

—————————————— —–<\/span>
\n<\/span><\/span><\/p>\n

_very_large_object_threshold\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 500<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>events ‘10949 trace name context forever, level 1’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

logical read<\/span>
\n<\/span>bytes from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20447232<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

consistent gets from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2496<\/span>
\n<\/span><\/span><\/p>\n

consistent gets from<\/span>
\n<\/span>cache (fastpath)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2496<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=8;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (direct read)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Wait Events<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>WAITS\u00a0\u00a0\u00a0\u00a0\u00a0 TIME_MS\u00a0\u00a0\u00a0\u00a0 TIMEOUTS AVG_WAIT_MS<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ———— ———— ———— ———–<\/span>
\n<\/span><\/span><\/p>\n

SQL*Net message from<\/span>
\n<\/span>client\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 27492\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0 9164,1<\/span>
\n<\/span><\/span><\/p>\n

direct path read<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>30\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 30\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

…<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

More interesting is fact that for\u00a0full table scan<\/strong>\u00a0operation\u00a0event 10949<\/strong>\u00a0can be used for\u00a0direct path read<\/strong>\u00a0usage choice together with _very_large_object_threshold (as table segment size percent of _db_block_buffers value) for\u00a0full table scan<\/strong>. But this dependence not so simple as for INDEX FAST FULL SCAN:
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> select<\/span>
\n<\/span>blocks, trunc(bytes\/1024\/1024) as<\/span>
\n<\/span>MB, trunc(blocks\/30442*100,2) as<\/span>
\n<\/span>“threshold%”<\/span>
\n<\/span>from<\/span>
\n<\/span>user_segments where<\/span>
\n<\/span>segment_name = ‘XT_IFFS’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

\u00a0\u00a0\u00a0\u00a0BLOCKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MB threshold%<\/span>
\n<\/span><\/span><\/p>\n

———- ———- ———-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a09216\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72\u00a0\u00a0\u00a0\u00a0\u00a0 30.27 — segment size is about 30% of buffer cache<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>events ‘10949 trace name context forever, level 1’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>\/*+ FULL(T)*\/<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

——————————————————————————<\/span>
\n<\/span><\/span><\/p>\n

| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name<\/span>\u00a0\u00a0\u00a0 <\/span>| Rows<\/span>\u00a0 <\/span>| Bytes | Cost (%CPU)| Time<\/span>\u00a0\u00a0\u00a0\u00a0 <\/span>|<\/span>
\n<\/span><\/span><\/p>\n

——————————————————————————<\/span>
\n<\/span><\/span><\/p>\n

|\u00a0\u00a0 0 | SELECT<\/span>
\n<\/span>STATEMENT\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0 4 |\u00a0 2416\u00a0\u00a0 (1)| 00:00:29 |<\/span>
\n<\/span><\/span><\/p>\n

|\u00a0\u00a0 1 |\u00a0 SORT AGGREGATE\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0 4 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>
\n<\/span><\/span><\/p>\n

|\u00a0\u00a0 2 |\u00a0\u00a0 TABLE<\/span>
\n<\/span>ACCESS FULL| XT_IFFS |\u00a0 1000K|\u00a0 3906K|\u00a0 2416\u00a0\u00a0 (1)| 00:00:29 |<\/span>
\n<\/span><\/span><\/p>\n

——————————————————————————<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Statistics<\/span>
\n<\/span><\/span><\/p>\n

———————————————————-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0 recursive calls<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0 db block gets<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a08749\u00a0 consistent gets<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0 physical reads\u00a0 — direct read was not used<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

— as we did for index, trying to make our table HUGE:<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=30;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>\/*+ FULL(T)*\/<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

logical read<\/span>
\n<\/span>bytes from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72310784<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

consistent gets from<\/span>
\n<\/span>cache (fastpath)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8809<\/span>
\n<\/span><\/span><\/p>\n

consistent gets from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8809 — still reads over cache<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

table<\/span>
\n<\/span>scans (long tables)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

— and a little more:<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=26;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>\/*+ FULL(T)*\/<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

consistent gets\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8743<\/span>
\n<\/span><\/span><\/p>\n

consistent gets direct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8740<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

physical reads\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8740<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

physical reads direct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8740<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

table<\/span>
\n<\/span>scans (long tables)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

table<\/span>
\n<\/span>scans (direct read)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 — direct path read<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Wait Events<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>WAITS\u00a0\u00a0\u00a0\u00a0\u00a0 TIME_MS\u00a0\u00a0\u00a0\u00a0 TIMEOUTS AVG_WAIT_MS<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ———— ———— ———— ———–<\/span>
\n<\/span><\/span><\/p>\n

SQL*Net message from<\/span>
\n<\/span>client\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15199\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0 7599,3<\/span>
\n<\/span><\/span><\/p>\n

direct path read<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>144\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 331\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2,3 — and appropriate wait event<\/span>
\n<\/span><\/span><\/p>\n

…<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\u2013 furthermore there is not (often noticed in different sources) accordance between\u00a0_very_large_object_threshold<\/strong>\u00a0and table size in Megabytes (72 MB), imho. Moreover this would be very strange to use parameter with unit which depends on SQL Plan operation
\n<\/span><\/p>\n

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<\/strong>\u00a0and segment size (in Megabytes). And besides these tests show how direct read usage speed up simple query execution almost twice!:
\n<\/span><\/p>\n

?<\/span><\/a>
\n<\/span><\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> @inst<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

INST_ID INSTANCE_NAME\u00a0 HOST_NAME\u00a0\u00a0\u00a0\u00a0\u00a0 VERSION\u00a0\u00a0\u00a0 PLATFORM_NAME<\/span>
\n<\/span><\/span><\/p>\n

——- ————– ————– ———- —————-<\/span>
\n<\/span><\/span><\/p>\n

1*\u00a0\u00a0\u00a0\u00a0\u00a0 INST1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db1.domain.net 11.2.0.3.0 Linux x86 64-bit<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> @param<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>VALUE<\/span>
\n<\/span><\/span><\/p>\n

—————————————— ———–<\/span>
\n<\/span><\/span><\/p>\n

_db_block_buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3780500<\/span>
\n<\/span><\/span><\/p>\n

db_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — ASMM<\/span>
\n<\/span><\/span><\/p>\n

__db_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 34762391552<\/span>
\n<\/span><\/span><\/p>\n

db_block_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8192<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> create<\/span>
\n<\/span>table<\/span>
\n<\/span>xt_iffs as<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a02\u00a0 with<\/span>
\n<\/span>gen as(<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select<\/span>
\n<\/span>level<\/span>
\n<\/span>a, lpad(1,50,1) c<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a04\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from<\/span>
\n<\/span>dual<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a05\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connect<\/span>
\n<\/span>by<\/span>
\n<\/span>level<=1e4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — table with increased segment size<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a06\u00a0 )<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a07\u00a0 select<\/span>
\n<\/span>gen.*<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a08\u00a0 from<\/span>
\n<\/span>gen,gen gen2<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a09\u00a0 \/<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Table<\/span>
\n<\/span>created.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> exec<\/span>
\n<\/span>dbms_stats.gather_table_stats(”,’XT_IFFS’)<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

PL\/SQL procedure<\/span>
\n<\/span>successfully completed.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> create<\/span>
\n<\/span>index<\/span>
\n<\/span>ix_iffs on<\/span>
\n<\/span>xt_iffs(a);<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Index<\/span>
\n<\/span>created.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> select<\/span>
\n<\/span>blocks, bytes\/1024\/1024 as<\/span>
\n<\/span>MB from<\/span>
\n<\/span>user_segments where<\/span>
\n<\/span>segment_name = ‘IX_IFFS’;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

\u00a0\u00a0\u00a0\u00a0BLOCKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MB<\/span>
\n<\/span><\/span><\/p>\n

———- ———-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0213022\u00a0\u00a0\u00a0 1664.23 — index size significantly exceeds _very_large_object_threshold value<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> select<\/span>
\n<\/span>round((213022\/3780500)*100,2) as<\/span>
\n<\/span>“threshold%”<\/span>
\n<\/span>from<\/span>
\n<\/span>dual;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

threshold%<\/span>
\n<\/span><\/span><\/p>\n

———-<\/span>
\n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05.63<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=6;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Elapsed: 00:00:38.06\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — IFFS over buffer cache twice slower<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

logical read<\/span>
\n<\/span>bytes from<\/span>
\n<\/span>cache\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1715216384<\/span>
\n<\/span><\/span><\/p>\n

physical read<\/span>
\n<\/span>total bytes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1715159040<\/span>
\n<\/span><\/span><\/p>\n

physical read<\/span>
\n<\/span>bytes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1715159040<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

free<\/span>
\n<\/span>buffer inspected\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 212179 — for cache blocks reading<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

physical read<\/span>
\n<\/span>IO requests\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1645<\/span>
\n<\/span><\/span><\/p>\n

physical read<\/span>
\n<\/span>total multi block requests\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1639<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Wait Events<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>WAITS\u00a0\u00a0\u00a0\u00a0\u00a0 TIME_MS\u00a0\u00a0\u00a0\u00a0 TIMEOUTS AVG_WAIT_MS<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ———— ———— ———— ———–<\/span>
\n<\/span><\/span><\/p>\n

SQL*Net message from<\/span>
\n<\/span>client\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 73827\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0 36913,7<\/span>
\n<\/span><\/span><\/p>\n

db file scattered read<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>1643\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 23674\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 14,4<\/span>
\n<\/span><\/span><\/p>\n

db file sequential read<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 14\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7,1<\/span>
\n<\/span><\/span><\/p>\n

SQL*Net message to<\/span>
\n<\/span>client\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

— with decreased threshold down to 5%:<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> alter<\/span>
\n<\/span>session set<\/span>
\n<\/span>“_very_large_object_threshold”=5;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session altered.<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

SQL> SELECT<\/span>
\n<\/span>SUM(A) FROM<\/span>
\n<\/span>XT_IFFS T;<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

\u00a0\u00a0\u00a0\u00a0SUM(A)<\/span>
\n<\/span><\/span><\/p>\n

———-<\/span>
\n<\/span><\/span><\/p>\n

5.0005E+11<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Elapsed: 00:00:16.47\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — direct read more than twice quicker!<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Statistics<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DELTA<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ————<\/span>
\n<\/span><\/span><\/p>\n

physical read<\/span>
\n<\/span>total bytes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1715167232<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

consistent gets direct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 209371<\/span>
\n<\/span><\/span><\/p>\n

physical reads direct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 209371 — direct read<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (direct read)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

…<\/span>
\n<\/span><\/span><\/p>\n

index<\/span>
\n<\/span>fast full<\/span>
\n<\/span>scans (full)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

Session Wait Events<\/span>
\n<\/span><\/span><\/p>\n

 <\/p>\n

NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>WAITS\u00a0\u00a0\u00a0\u00a0\u00a0 TIME_MS\u00a0\u00a0\u00a0\u00a0 TIMEOUTS AVG_WAIT_MS<\/span>
\n<\/span><\/span><\/p>\n

—————————————————————- ———— ———— ———— ———–<\/span>
\n<\/span><\/span><\/p>\n

direct path read<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>795\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8218\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10,3 — with one appropriate wait event<\/span>
\n<\/span><\/span><\/p>\n

SQL*Net message from<\/span>
\n<\/span>client\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6492\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0 3245,9<\/span>
\n<\/span><\/span><\/p>\n

events in<\/span>
\n<\/span>waitclass Other\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1,5<\/span>
\n<\/span><\/span><\/p>\n

enq: KO – fast object checkpoint<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0,2 — and another appropriate wait event<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

  Oracle \u00a0Database 11g\ub294\u00a0\ub0b4\ubd80\uc801\uc73c\ub85c Table \ud06c\uae30\uc5d0 \ub300\ud574 SMALL(Buffer Cache\uc758 2%\ubcf4\ub2e4 \uc791\uc744 \ub54c), VERYLARGE( Buffer Cache\uc758 5\ubc30 \ud06c\uae30\ubcf4\ub2e4 \ud074 \ub54c)\ub85c \uad6c\ubd84\ud558\uba70 \u00a0\ud14c\uc774\ube14\uc5d0 \ub300\ud574 Full Table Sacn(FTS)\uc744 \ud560 \ub54c Buffer Cache\ud06c\uae30, Segment \ud06c\uae30, Buffer Cache\uc5d0 Cache\ub41c \uc815\ub3c4, \uae30\ud0c0 \ud1b5\uacc4 \ub4f1\uc744 \uace0\ub824\ud574\u00a0Direct Path Read\ub97c \ud560 \uac83\uc778\uc9c0 Buffer Cache\uc5d0\uc11c \uc77d\uc744\uc9c0(Buffer Read)\ub97c \uacb0\uc815 \ud569\ub2c8\ub2e4.     \uc989 SGA \uc758 \uba54\ubaa8\ub9ac […]<\/p>\n","protected":false},"author":1,"featured_media":4770,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[14],"tags":[697,300,698,699,314,700,701,702],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2021\/11\/cropped-troubleshoot.png?fit=680%2C182","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1292"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1292"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1292\/revisions"}],"predecessor-version":[{"id":4814,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1292\/revisions\/4814"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/4770"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1292"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}