Oracle data buffer tuning scripts

Here, I'll discuss two scripts which I use often to check the oracle data buffer performance.



The first script will show when data buffer hit ratio is lower than a limit value. The following query will show the time when "data buffer hit ratio" is lower than 90%. Oracle instance needs to be further analysed during that time to know what the queries those are running that time.


select
   to_char(end_interval_time,'yyyy-mm-dd HH12')      mydate,
   ne.name                                           buffer_pool_name,
   (((ne.consistent_gets-ol.consistent_gets)+(ne.db_block_gets-ol.db_block_gets))-(ne.physical_reads-ol.physical_reads))/((ne.consistent_gets-ol.consistent_gets)+(ne.db_block_gets-ol.db_block_gets))    bhr
from
   dba_hist_buffer_pool_stat ol,
   dba_hist_buffer_pool_stat   ne,
   dba_hist_snapshot snap
where   (((ne.consistent_gets-ol.consistent_gets)+(ne.db_block_gets-ol.db_block_gets))-(ne.physical_reads-ol.physical_reads))/((ne.consistent_gets-ol.consistent_gets)+(ne.db_block_gets-ol.db_block_gets)) < .90
and   ne.name = ol.name
and   ne.snap_id = snap.snap_id
and   ol.snap_id = snap.snap_id-1

Here it is showing 31st August 2 am, the data base hit ratio has come down to 58%. Now, our analysis found that time a month end closer batch job runs that is decreasing hit ratio. This batch runs once in a month. So, we can allocate more buffer during that time before hand.

=================================================


The next query shows what are the objects those are currently residing inside data buffer. It also informs the percentage of that object that reside inside the buffer cache. This information during peak load time gives us idea whether we need to manually assign some tables in keep pool.



DROP TABLE t1;
CREATE TABLE t1 AS
SELECT o.owner owner,
  o.object_name object_name,
  o.subobject_name subobject_name,
  o.object_type object_type,
  COUNT(DISTINCT file#
  || block#) num_blocks
FROM dba_objects o,
  v$bh bh
WHERE o.data_object_id = bh.objd
AND o.owner NOT       IN ('SYS','SYSTEM')
AND bh.status         != 'free'
GROUP BY o.owner,
  o.object_name,
  o.subobject_name,
  o.object_type
ORDER BY COUNT(DISTINCT file#  || block#) DESC ;

SELECT t1.owner "Owner",
  object_name "Object|Name",
  CASE
    WHEN object_type = 'TABLE PARTITION'
    THEN 'TAB PART'
    WHEN object_type = 'INDEX PARTITION'
    THEN 'IDX PART'
    ELSE object_type
  end "Object Type",
  sum(num_blocks) "NumberofBlocks",
  (SUM(num_blocks)/greatest(SUM(blocks), .001))*100 "PercentageObjectblocks",
  buffer_pool "BufferPool",
  SUM(bytes)/SUM(blocks) "BlockSize"
FROM t1,
  dba_segments s
WHERE s.segment_name          = t1.object_name
AND s.owner                   = t1.owner
AND s.segment_type            = t1.object_type
AND NVL(s.partition_name,'-') = NVL(t1.subobject_name,'-')
GROUP BY t1.owner,
  object_name,
  object_type,
  buffer_pool
HAVING SUM(num_blocks) > 10
ORDER BY SUM(num_blocks) DESC ;

========================================