Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The Oracle documentation has a section in the 11.2 Performance Tuning Guide with the title Determining Which Segments Have Many Buffers in the Pool. The section lists the following query:

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

If we change this to a right join we get rows from v$bh regardless of whether there is a matching object_id in dba_objects.

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID(+) = bh.OBJD AND o.DATA_OBJECT_ID IS NULL
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

My question is what do these row represent? They have file#, block#, and objd columns, but the objd values do not exist in dba_objects. I dumped one of the blocks and it appeared to contain an index, but I couldn't tell anything else about it. There are plenty of rows in v$bh that do match indexes in dba_objects, so the unmatched v$bh rows can't be unmatched just because they are blocks of indexes. Something else must be going on.

There are more unmatched objects on one of my systems, but they all have unmatched objects including the 11.2.0.2.6 system.

Update:

For the system with the most unmatched objects, the majority can be matched when comparing the bh.objd with the dataobj# field from obj$. I'm not sure why these objects aren't reflected in dba_objects, but perhaps an examination of the view (or a separate question) will answer that.

For the remaining 391 unidentified entries, here is some other information of interest.

SELECT class#, count(class#) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY class#
  4  HAVING count(class#) > 1;

    CLASS# COUNT(CLASS#)
---------- -------------
        12            59
        13            36
        18            15
        20            20
        22            25
        30            12
        32             6
        36            28
        38             3
        42            16
        44           125

    CLASS# COUNT(CLASS#)
---------- -------------
        52             4

12 rows selected.

I eliminated the class values with only one entry. There were 33 of those.

SELECT status, count(status) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY status;

STATUS  COUNT(STATUS)
------- -------------
xcur              361

1 row selected.
share|improve this question
    
Do they have rows in obj$? –  Phil Jun 27 '12 at 20:56
1  
What's v$bh.class# for these? Could be undo... –  Phil Jun 27 '12 at 21:50
    
Be nice to see a v$bh.status,count(*) too. I suspect the majority will be free. –  Phil Jun 27 '12 at 22:00
    
Thanks for the questions Phil, I think I addressed all of in the updated question. –  Leigh Riffel Jun 28 '12 at 12:33
    
The majority are undo then. Relevant class# are: 7+(n*2) = undo segment n header block. 8+(n*2) = undo segment n block –  Phil Jun 28 '12 at 14:52
show 2 more comments

1 Answer 1

up vote 2 down vote accepted

The majority of the unmatched objects will be Undo.

You can join v$bh with DBA_ROLLBACK_SEGS to identify the Undo segments involved.

SELECT segment_name, owner, tablespace_name, class#, count(class#)
FROM v$bh bh, dba_rollback_segs drs
WHERE NOT EXISTS (SELECT 1 FROM sys.obj$ o2 WHERE o2.dataobj# = bh.objd)
AND drs.segment_id = class#
GROUP BY segment_name, owner, tablespace_name, class#
HAVING count(class#) > 1;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.