Jonathan, I've tried your scripts in your post small tables in 11g https://jonathanlewis.wordpress.com/2011/03/24/small-tables/ My test shows that for the medium table(percent [2, 10] of db_cache_size), the first tablescan will be serial direct; for long talbe(percent [10, â??]), every tablescan scan is serial direct. http://sid.gd/small-tables/ What surpurise me is that the cache hint does not change the serial direct read for the medium/long table. For example, I've set the db_cache_size to 120M and t_1488 is the 10 percent table. All the 3 tablescan are all serial direct, so I wonder that the cache hint has no impact at least for the medium and long table. -- fill up the buffer cache sid@CS11GR2> select /*+ cache(t)*/ 2 max(small_vc) 3 from 4 t_1488 t; MAX(SMALL_VC) ---------------------------------------- 0000001488 Name Value ---- ----- physical reads 1,490 physical reads cache 2 physical reads direct 1,488 table scans (long tables) 1 table scans (direct read) 1 table scan rows gotten 1,488 table scan blocks gotten 1,488 sid@CS11GR2> exec dbms_lock.sleep(4); PL/SQL procedure successfully completed. sid@CS11GR2> select /*+ cache(t)*/ 2 max(small_vc) 3 from 4 t_1488 t; MAX(SMALL_VC) ---------------------------------------- 0000001488 Name Value ---- ----- physical reads 1,488 physical reads direct 1,488 table scans (long tables) 1 table scans (direct read) 1 table scan rows gotten 1,488 table scan blocks gotten 1,488 sid@CS11GR2> exec dbms_lock.sleep(4); PL/SQL procedure successfully completed. sid@CS11GR2> select /*+ cache(t)*/ 2 max(small_vc) 3 from 4 t_1488 t; MAX(SMALL_VC) ---------------------------------------- 0000001488 Name Value ---- ----- physical reads 1,488 physical reads direct 1,488 table scans (long tables) 1 table scans (direct read) 1 table scan rows gotten 1,488 table scan blocks gotten 1,488 sid@CS11GR2> select 2 obj, tch, count(*) 3 from x$_bh 4 where 5 obj between 87014 and 87020 6 group by 7 obj, tch 8 order by 9 count(*) 10 ; OBJ TCH COUNT(*) ---------- ---------- ---------- 87015 3 1 87016 3 1 <-- This is the segment header for t_1488, no data block cache for t_1488. 87020 1 32 87015 2 744 87019 1 14006 On Mon, Mar 12, 2012 at 8:40 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx > wrote: > > > Too many unknowns. > > One thing I can say, though, is that nothing gets loaded into the MRU end > of the LRU, everything goes to the mid point. > One thought about the cache hint - it's possible that it changes (again) in > 11.2 as a side effect of serial direct path reads. > > In 11.2 a table over 25% of the cache size (whatever that means exactly) > will be read using serial direct path reads (unless specified as > parallel) - perhaps if you specify the cache hint it will be read into the > cache instead. Conversely, if you specify nocache might this force a serial > direct read ? > > > > Regards > > Jonathan Lewis > http://jonathanlewis.wordpress.com > Oracle Core (Apress 2011) > http://www.apress.com/9781430239543 > > -- > Regards Sidney Chen -- //www.freelists.org/webpage/oracle-l