Re: 2 Buffer Cache Questions

From: <RogBaker_at_gmail.com>
Date: 4 May 2005 06:48:53 -0700
Message-ID: <1115214533.085607.151250@z14g2000cwz.googlegroups.com>

I have a script, which ecludes SYSTEM and SYS objects. A Co-worker is running a tool from the Enterprise Manager performance pack or something that is showing graphs of what's in the Buffer Cache, and it was showing a lot of SYSTEM objects initally after the database is started. I suppose I could have edited the script I was using to include SYS and SYSTEM.

I analyzed the scripts, and the bases for them are dba_objects.blocks and the number of rows returned from v$bh. Just ignoring the scripts and looking at dba_objects and v$bh, I get 29 blocks total from v$bh, and 32 blocks from dba_objects. That gives me the same percentage of the scripts (91% for this example table). Anyway, the scripts are below for your review.

Whenever I use a script that I find on the internet, I try to verify it with something I found somewhere else.
Here is the script I used, it's from
http://www.pafumi.net/multi_buffers.htm

---

set pages 999
set lines 92

ttitle 'Contents of Data Buffers'

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;

column c0 heading "Owner"                              format a12
column c1 heading "Object|Name"                        format a20
column c2 heading "Object|Type"                        format a7

column c3 heading "Number of|Blocks in|Buffer|Cache" format 99,999,999

column c4 heading "% of |object|blocks |in Buffer"     format 999
column c5 heading "Buffer|Pool"                        format a7
column c6 heading "Block|Size"                         format 99,999

select t1.owner                                          c0,
       object_name                                       c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   (sum(num_blocks)/greatest(sum(blocks), .001))*100   c4,
   buffer_pool                                         c5,
   sum(bytes)/sum(blocks)                              c6

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;

drop table t1;

---

I found another version that had just about the same results from "everybody's favorite DBA:"
http://www.dba-oracle.com/art_builder_buffer.htm

---

set pages 999
set lines 80

spool blocks.lst

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select

   o.object_name object_name,
   o.object_type object_type,
   count(1) num_blocks
from

   dba_objects o,
   v$bh bh
where

   o.object_id = bh.objd
and

   o.owner not in ('SYS','SYSTEM')
group by

   o.object_name,
   o.object_type
order by

   count(1) desc
;

column c1 heading "Object|Name"                 format a30
column c2 heading "Object|Type"                 format a12
column c3 heading "Number of|Blocks"            format 999,999,999,999

column c4 heading "Percentage|of object|data blocks|in Buffer" format 999

select

   object_name       c1,
   object_type       c2,
   num_blocks        c3,

   (num_blocks/decode(sum(blocks), 0, .001, sum(blocks)))*100 c4 from

   t1,
   dba_segments s
where

   s.segment_name = t1.object_name
and

   num_blocks > 10
group by

   object_name,
   object_type,
   num_blocks
order by

   num_blocks desc
;

---

Received on Wed May 04 2005 - 08:48:53 CDT