Nigel Noble's Oracle Blog

test_10205_directio_keep.sql

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: