Calculating hit ratios for performance tuning

Summary 

The old method (for my opinion) is calculating ratios and averages about system memmory allocation and I/O. Here is an example how to get this ratios. 

Connect as sys and create the table

create table tune ( 
  tune_date            date, 
  buffer_hit_ratio     number, 
  dict_hit_ratio       number, 
  libr_hit_ratio       number, 
  sort_hit_ratio       number, 
  shared_pool_free_mem number, 
  log_buff_wait        number, 
  nb_session           number, 
  nb_disk_read         number);

We will create a procedure called TUNING which calculates

* buffer hit ratio
* dictionary cache hit ratio
* library cache hit ratio
* sort_area_size hit ratio
* shared pool free size remaining
* number of process waits for space in the redo log buffer
* number of sessions
* number of heavy disk reads

CREATE OR REPLACE PROCEDURE TUNING IS 
  l_buff_hit            number; 
  l_dict_hit              number; 
  l_libr_hit              number; 
  l_memo_sort             number; 
  l_pool_free             number; 
  l_log_wait              number; 
  l_nb_sess               number; 
  l_nb_disk               number; 

  cursor sel_buffer_hit_ratio is 

    select (1 - (sum(decode(name, 'physical reads',value,0)) / 
           (sum(decode(name, 'db block gets',value,0)) + 
           sum(decode(name, 'consistent gets',value,0))))) * 100 
      from v$sysstat; 

  cursor sel_dict_cache_hit_ratio is 
    select (1-(sum(getmisses)/sum(gets))) * 100 
      from v$rowcache; 

  cursor sel_libr_cache_hit_ratio is 
    select sum(Pins) / (sum(Pins) + sum(Reloads)) * 100 
      from v$librarycache; 

  cursor sel_sort_in_memo is 
    select round(100 * b.value / 
                 decode(a.value + b.value,0,1,a.value + b.value),2) 
      from v$sysstat a, v$sysstat b 
     where a.name = 'sorts (disk)' 
       and b.name = 'sorts (memory)'; 

  cursor sel_shared_pool_free is 
    select (a.bytes / b.value)*100 
      from v$sgastat a, v$parameter b 
     where a.name = 'free memory' 
       and a.pool = 'shared pool' 
       and b.name = 'shared_pool_size'; 

  cursor sel_redo_buff_aloc is 
    select value 
      from v$sysstat 
     where name = 'redo buffer allocation retries'; 

  cursor sel_nb_sess is 
    select count(*) 
      from v$session; 

  cursor sel_nb_disk is 
    select count(disk_reads) 
      from v$sqlarea 
     where disk_reads > 10000; 

BEGIN 
--This query returns the percentage of the data selected by 
--users that have been hit in memory. 
  open sel_buffer_hit_ratio; 
    fetch sel_buffer_hit_ratio into l_buff_hit; 
  close sel_buffer_hit_ratio; 

--This query returns the percentage of memory reads for the 
--data dictionary and other objects. 
  open sel_dict_cache_hit_ratio; 
    fetch sel_dict_cache_hit_ratio into l_dict_hit; 
  close sel_dict_cache_hit_ratio; 

--This query returns the percentage of memory allocated 
--for user statements. 
  open sel_libr_cache_hit_ratio; 
    fetch sel_libr_cache_hit_ratio into l_libr_hit; 
  close sel_libr_cache_hit_ratio; 

--This query returns the percentage of memory allowed 
--to users for sorting. 
  open sel_sort_in_memo; 
    fetch sel_sort_in_memo into l_memo_sort; 
  close sel_sort_in_memo; 

--This query returns the percentage of free shared pool. 
  open sel_shared_pool_free; 
    fetch sel_shared_pool_free into l_pool_free; 
  close sel_shared_pool_free; 

--This query returns the number of times a user process waits 
--for space in the redo log buffer. 
  open sel_redo_buff_aloc; 
    fetch sel_redo_buff_aloc into l_log_wait; 
  close sel_redo_buff_aloc; 

--This query returns the number of sessions. 
  open sel_nb_sess; 
    fetch sel_nb_sess into l_nb_sess; 
  close sel_nb_sess; 

--This query returns the number of disk reads greater than 10000. 
  open sel_nb_disk; 
    fetch sel_nb_disk into l_nb_disk; 
  close sel_nb_disk; 

  insert into tune(TUNE_DATE,BUFFER_HIT_RATIO,DICT_HIT_RATIO,
                   LIBR_HIT_RATIO,SORT_HIT_RATIO, SHARED_POOL_FREE_MEM,
                   LOG_BUFF_WAIT,NB_SESSION,NB_DISK_READ) 
    values(sysdate,l_buff_hit,l_dict_hit,l_libr_hit,l_memo_sort,
           l_pool_free,l_log_wait,l_nb_sess,l_nb_disk); 
  commit; 
END; 
/

Once the above script has been run, all you have to do is:

execute tuning; 

select * from tune;