Tuning Memory Pools

In Oracle it is possible to define different memory pools and objects can be placed in the appropriate pool based on the amount of reads, writes and average row length. Objects that are small and accessed frequently should be kept in a seperate pool. Objects with large physical reads should be in a different pool. If tablespaces with different block sizes are created, then there are more tuning options available.

The challenge here is to determine the objects to be palced in which memory pools. There can be some rules defined like

Objects with low ratio for "physical Reads" / "Total Reads" should be placed in KEEP pool and those having heavy physical reads should go to RECYCLE pool
If the average row length is high then the tablespace used should be of larger block size.
Choose tablespace with smaller blocksize for objects that are having more inserts/deletes and no updates and less total reads
Objects with more random reads should be placed in tablespace with small blocksize

Choosing the best value for the memory pool is a difficult task. But some definite conclusions can be reached for segments that are having extreme values for hit ratios or writes or average row length.The following SQL's can be used to order segments by these parameters.

The following SQL statement can be used to determine segments statistics. Since this is used frequently it is better to create a view

create or replace view my_segstat_vw as
select object_name, reads,writes, physical_reads, buffer_busy, 
block_changes, round(100*(1-physical_reads/reads)) hits
FROM(
select object_name,
max(case when STATISTIC_NAME like '%reads%' 
	THEN value else null end) reads,
max(case when STATISTIC_NAME like '%writes%' 
	THEN value else null end) writes,
max(case when STATISTIC_NAME='physical reads' 
	THEN value else null end) physical_reads,
max(case when STATISTIC_NAME='buffer busy waits' 
	THEN value else null end) buffer_busy,
max(case when STATISTIC_NAME='db block changes' 
	THEN value else null end) block_changes
FROM v$segment_statistics 
GROUP BY object_name) where reads>0;

The following SQL gets segments that are having most writes and average row length is small Get total writes in the database select sum(writes) from my_segstat_vw;

select * from
(select a.object_name, a.writes, hits,
a.writes*100/total_writes pct_writes
from my_segstat_vw a 
where a.object_name not in(
select table_name from dba_tables where avg_row_len>200
)
order by a.writes DESC)
WHERE rownum<100;

The following SQL can used to obtains large segments with low hit ratio

select * from
(select a.object_name, a.physical_reads, a.hits
from my_segstat_vw a 
where a.object_name in(
select table_name from dba_tables where avg_row_len*num_rows>100000000
)
order by a.writes DESC)
WHERE rownum<100;