Forum Stats

  • 3,759,971 Users
  • 2,251,623 Discussions


v$bh view inconsistent with sql trace statistics

User194454 Member Posts: 10 Red Ribbon
edited Aug 25, 2021 12:29PM in Database 19c-21c

I'd like to check if a segment has really been uploaded in the buffer pool and the only way to check it, it seems to use this query under Oracle 19c:

select o.owner,o.object_name,count(blocks) bl

from sys.dba_objects o, sys.v_$bh b

where o.object_id = b.objid

and o.owner = 'TEST'

I executed the query:

set autotrace traceonly statistics;

select * from test.dummy;

I've got the following report: 79645 consistent gets - 12457 physical reads

I executed the query again and now I've got: 79101 consistent gets - 0 physical reads

Now I queried again the v_$bh view with the owner = 'TEST' but the segment 'dummy' is not present in the view. So it appears that blocks have been read from buffer pool but the v_$bh view has not been updated in agreement with sql trace statistics.

I did the same in Postgresql 13.x using pg_buffercache extension to query shared buffers and it works fine, is it possible to do the same under Oracle 19c ?