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;
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