Tuning logical IO


There are two main types of IO, logical IO and physical IO. We prefer logical IO because instance reads data from memory called logical IO which is much more faster than physical IO which reads data from hard disk. To lower the physical IO, we use db_buffer_cache parameter which assigns some part of the memory for caching hard disk data. This section includes both logical IO and physical IO performance tuning topics.

-- db_cache_size
--
select name, value from v$parameter where name = 'db_cache_size';
alter system set db_cache_size=350M;
alter system set db_keep_cache_size=10M;
alter system set db_recycle_cache_size=10M;

NOTE

Tables which are more frequently used by sessions and has relatively small amount of update with long time intervals should be in keep_cache_size which is stored in memory and are not in LRU chain of default_cache_size. Recycle_cache_size is for tables which you dont want to be in memory for a long time. Default_cache_size is always there for any undefined buffer_pools.

 
--
-- static view of buffer pool, can inspect keep_cache_size and
recycle_cache_size
--

select * from v$buffer_pool;
-- buffer pool statistics for detailed information
--

select * from v$buffer_pool_statistics;
/* NAME: Name of the buffer pool PHYSICAL_READS: Number of physical reads
DB_BLOCK_GETS: Number of reads for INSERT, UPDATE and DELETE CONSISTENT_GETS: Number of reads for SELECT DB_BLOCK_GETS + CONSISTENT_GETS = Total Number of reads*/
 
--
-- as with the above information the following ratio should be very close
to zero

-- if not then we are reading data physical
--

select
name,
trunc(physical_reads/(decode(db_block_gets,0,1,1)+consistent_gets),3)
value
from
v$buffer_pool_statistics;
/* keep: %10 of default , recycle: %2 of default */
 
--
-- which objects are in memory cache
--

select
count(1) count,
t1.objd,
t2.name,
t3.OBJECT_NAME,
t3.OWNER
from
v$bh t1,
v$datafile t2,
dba_objects t3
where
t1.file# = t2.file# and
t1.objd = t3.OBJECT_ID and
t3.owner not in ('SYS', 'SYSTEM')
group by t3.object_name, t3.owner, t2.name, t1.objd
order by count desc, t3.object_name;
-- altering a table buffor_pool
--

alter table <tablename> storage (buffer_pool keep);
alter table <tablename> storage (buffer_pool recycle);
alter table <tablename> storage (buffer_pool default);
-- have a look at systems cache hit ratio
--
-- as you can expect the result should be as close as 1

select
1 - (phy.value - lob.value - dir.value) / ses.value cache_hit_ratio
from
v$sysstat ses,
v$sysstat lob,
v$sysstat dir,
v$sysstat phy
where
ses.name = 'session logical reads' and
dir.name = 'physical reads direct' and
lob.name = 'physical reads direct (lob)' and
phy.name = 'physical reads';
--
-- comparison of logical and physical reads in user level
--

select
t2.username,
t1.block_gets,
t1.consistent_gets,
t1.physical_reads
from
v$sess_io t1,
v$session t2
where
t1.sid=t2.sid and
t2.username is not null
order by username;
-- two important system wait events
--

select name,value from v$sysstat where name = 'free buffer inspected'
select
event,
total_waits
from
v$system_event
where
event in ('free buffer waits', 'buffer busy waits');
--
-- checks if there is any session currently waiting for buffer busy waits
(freelist contention)
--

select s.segment_name, s.segment_type, s.freelists, w.wait_time,
w.seconds_in_wait, w.state
from dba_segments s, v$session_wait w
where w.event='buffer busy waits'
and w.p1 = s.header_file
and w.p2 = s.header_block;
list of views for db_cache_size management


           v$db_cache_advice
           v$system_event
           v$session_event
           v$session_wait
           v$session
           v$sesstat
           v$sysstat
           v$bh
           v$sess_io
           v$buffer_pool_statistics
           v$buffer_pool
           dba_segments
parameters

           db_cache_advice       //[ON|OFF|READY]
           db_block_checsum     //[TRUE|FALSE]
           db_cache_size
           db_keep_cache_size
           db_recycle_cache_size
           db_2K_cache_size
           db_4K_cache_size
           db_8K_cache_size
           db_16K_cache_size
           db_32K_cache_size
Advertisements

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

%d bloggers like this: