determining which buffers are on the pool?

[kburrows]

Hi Everyone,

I am trying to find out which buffers are in the pool for a certain schema.

Getting object id:
select data_object_id, object_type, object_name
from dba_objects
where owner = 'SCHEMA_OWNER';

then
select count(*) as buffers
from v$bh
where objd = data_object_id (from above query);

I get
BUFFERS
----------
0
1 row selected.

This can't be right, user are using the schema objects all the time. What am I missing here. How can i find out which segments are on the buffer for a schema?

Thanks,

[sambavan]

   
              SELECT name,  
                          type,
                          sharable_mem,       -- Memory consumed in the shared pool
                          loads,                     -- # times the object had been loaded or invalidated
                          executions,
                          pines                      -- # of users currently pinning this object
               FROM V$DB_OBJECT_CACHE
                WHERE owner= 'schema_owner'
                    AND kept = 'Y';

In your query DBA_OBJECTS.DATA_OBJECT_ID would display the object number of the segment that contains the object. Where as the V$BH.OBJD is the database object number of the block buffer that object represents. This was the reason why you get the wrong answer.

Sam

[jmodic]

OBJD column does not exist in X$BH - I guess it is a typo, I suppose kburrows realy ment and used OBJ. And there is nothing wrong with his query, X$BH.OBJ column correctly represent the data_object_id of the segment object. I can only assume that the particular segment that he was querying for realy did not have any single block in the buffer cache at that time.

To display all the objects that currently have at least one block in the buffer cache, run the following query:

select o.object_name, count(*) buffers
from x$bh bh, dba_objects o
where bh.obj = o.data_object_id
group by o.object_name;

[sambavan]

Reference: V$BH
DBA_OBJECTS


Sam

[jmodic]

Ouch, I've misread that we are talking about V$BH, not X$BH. My bad.

OK, so replace any occurrence of OBJ with OBJD and any occurrence of X$BH with V$BH in my previous post, and you still have your answers . V$BH.OBJD represents the same thing as DBA_OBJECTS.DATA_OBJECT_ID, namely the segment id, not some block buffer id. The corrected query (if you want to use V$BH instead of X$BH) is:

select o.object_name, count(*) buffers
from v$bh bh, dba_objects o
where bh.objd = o.data_object_id
group by o.object_name;