After 11g, serial table scan is introduced, and the whole table scan is at least 5 times the value of _small_table_threshold. The database will use direct path read instead of db file scattered read.
db file scattered read: To read multiple blocks from disk to discontinuous buffers in SGA, scattered read must read multiple blocks from disk to buffer cache, and buffers are discontinuous. Blocks must be scattered (scattered) distributed in memory. The upper limit for a full scan is DB_FILE_MULTIBLOCK_READ_COUNT, with a default value of 128
direct path read: Read one or more blocks from disk directly into PGA without SGA
Trigger direct path read:
Find if the _serial_direct_read feature is turned on
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 2 from x$ksppi a,x$ksppcv b 3 where a.indx = b.indx 4 and a.ksppinm='_serial_direct_read' 5 SQL> / NAME VALUE DESCRIPTION --------- _serial_direct_read auto enable direct read in serial
Finding threshold for small table
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
2 from x$ksppi a,x$ksppcv b
3 where a.indx = b.indx
4 and a.ksppinm like '%small_table%' ;
NAME VALUE DESCRIPTION
---------------------- ---------- ----------------------------------------
_small_table_threshold 7679 lower threshold level of table size for
direct reads
_ small_table_threshold is 7679, which means that the number of tables we create must be greater than 7679. Because of the relationship between block head and water level line, we roughly calculate that a block occupies 8k, so our table should be larger than 7679*8/1024= 59.9921875mb. If we build a table with a record occupying 1k, we need at least 60k rows.
Create a large table with 3300 W + data.
SQL> create table tbig(a char(1024 byte));
Table created.
SQL> insert into tbig values('a');
1 row created.
SQL> insert into tbig select * from tbig;
1 row created.
SQL> /
65536 rows created.
SQL> alter system flush buffer_cache
2
SQL> /
System altered.
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event
2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');
EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
----------------------------------- ----------- -----------------
db file sequential read 272 133 1328785
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event
2 where EVENT in ('db file sequential read','db file scattered read','direct path read');
EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
----------------------------------- ----------- -----------------
db file sequential read 359234531 100436020 1.0044E+12
db file scattered read 63025426 5089881 5.0899E+10
direct path read 416564 341760 3417600930
SQL> select * from (select * from tbig order by 1)where rownum<=10;
...
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event
2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');
EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------------------ ----------- -----------------
db file sequential read 309 133 1334823
db file scattered read 28 0 797
direct path read 148 12 122384
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event
2 where EVENT in ('db file sequential read','db file scattered read','direct path read');
EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
----------------------------------- ----------- -----------------
db file sequential read 359251382 100439191 1.0044E+12
db file scattered read 63025485 5089885 5.0899E+10
direct path read 416712 341772 3417723314Direct path reads at session level increased 148 times from scratch, and direct path read s at system level increased 416712-416564 = 148 times.
Finally, thanks to Enmo, thanks to Geithner's training
Reference article: http://www.eygle.com/archives/2012/05/oracle_11g_direct_path_read.html