Objects in v$bh but not dba_objects

oracleoracle-11g-r2

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.

Best Answer

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;
Related Question