3

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.
7
  • Do they have rows in obj$?
    – Philᵀᴹ
    Commented Jun 27, 2012 at 20:56
  • 1
    What's v$bh.class# for these? Could be undo...
    – Philᵀᴹ
    Commented Jun 27, 2012 at 21:50
  • Be nice to see a v$bh.status,count(*) too. I suspect the majority will be free.
    – Philᵀᴹ
    Commented Jun 27, 2012 at 22:00
  • Thanks for the questions Phil, I think I addressed all of in the updated question. Commented Jun 28, 2012 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ᵀᴹ
    Commented Jun 28, 2012 at 14:52

1 Answer 1

3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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