Link to blog item
10.2.0.5, KEEP pool / Serial Direct Read
Script
Included the source of my test script outside the blog item just to reduce the size of the blog item. The script is just a template if you want to test things yourself. You would need to find your own suitably sized object to test with (>10% buffer cache and smaller than buffer cache/keep cache)
Warning: script should only run on 10.2.0.5 in it’s current form (uses 10.2.0.5 db events)
Warning: script contains a flush of the buffer cache! Test systems only!!!!
rem
rem script to show 10.2.0.5 direct IO feature (Back ported from 11g) and also show bug when using KEEP POOLS
rem Nigel Noble June 2010
rem
set echo on
spool test_&cache.lst
rem
rem Option to trace the test
rem alter session set events = '10046 trace name context forever, level 12';
rem ============================================================
rem confirm parameters
rem
show parameter db_cache_size
show parameter db_keep_cache_size
show parameter filesystemio_options
rem
rem Set the buffer pool of the table (DEFAULT or KEEP)
rem
alter table testuser.TABLE_DATA storage (buffer_pool &ENTER_BUFFER_POOL);
rem
rem show size of the test table and which buffer pool
rem table must be > 10% buffer cache and for purpose of the test, should fit within the whole buffer cache
rem
column segment_name format a30 truncate
column meg format 999999
select segment_name,
bytes / (1024 * 1024 ) Meg, blocks ,buffer_pool
from sys.dba_segments
where segment_name = 'TABLE_DATA'
/
rem
rem lets flush the buffer cache to start fresh (or bounce the instance)
rem
rem WARNING: Do NOT run this script on any production systems
rem
alter system flush buffer_cache;
rem
rem check which buffer pool the table is assigned too?
rem
select buffer_pool from dba_tables where table_name = 'TABLE_DATA';
rem
rem lets scan the table using the new serial direct path read option
rem going to switch it on at session level but can be set global
rem Will run it twice to show a consistant read time (ie data never made the buffer cache
rem
alter session set events = '10358 trace name context forever, level 4';
set timing on
select /*+FULL(c)*/ count(*) from testuser.TABLE_DATA c;
select /*+FULL(c)*/ count(*) from testuser.TABLE_DATA c;
set timing off
rem
rem Lets switch OFF the new 10.2.0.5 serial direct path read option
rem again, this is done at session level for purpose of this test
alter session set events = '10358 trace name context off';
rem scan it twice and see what happens
set timing on
select /*+FULL(c)*/ count(*) from testuser.TABLE_DATA c;
rem this second read is VERY MUCH FASTER because the data is already in the buffer cache
select /*+FULL(c)*/ count(*) from testuser.TABLE_DATA c;
set timing off
rem
rem One last test, lets now switch on the serial direct read event, BUT the data is already in the buffer cache.
rem
rem
alter session set events = '10358 trace name context forever, level 4';
set timing on
select /*+FULL(c)*/ count(*) from testuser.TABLE_DATA c;
set timing off
rem ============================================================
rem option to switch off trace
rem alter session set events = '10046 trace name context off';
set timing off
spool off
