Here’s a quick and dirty script I wrote some time ago when looking at a problem relating to “buffer busy waits”. We were seeing a large number of waits on a particular table (which we could identify easily from the “Segments by …” part of the AWR report , and I wanted to be able to see how much data was in the block while it was under stress. The point of being able to query the block (repeatedly) in near real time was that it always seemed to be empty if you waited a couple of minutes.
Update: I’ve realised that I’ve also used this query more recently to demonstrate that some blocks which were behaving “unreasonably” weren’t empty at the time. (Of course “empty” is slightly ambiguous term in this context, even when a block appears to be empty it may have rows in it that are invisible for reasons of read-consistency; in this case I just needed to know that there were rows that could be seen by other sessions.)
The query was cunningly constructed to access all rows that might appear (in read-consistent mode) in the given block; the /*+ rowid(t) */ hint ought to be redundant, but I like to play safe when I do this type of thing on a production system – even after testing it on a development system. The “magic number” 40189 was the data_object_id of table as recorded in the view dba_objects, and the two input parameters were the p1 and p2 values given in v$session_wait as the target buffer. The other “magic number” of 4,096 is there because that’s the maximum number of row directory entries you can have in a single block.
select /*+ rowid(t) */ &1, &2, count(*) from tableX t where rowid between ( select dbms_rowid.rowid_create( 1, 40189, &1, &2, 0 ) from dual ) and ( select dbms_rowid.rowid_create( 1, 40189, &1, &2, 4096 ) from dual ) ;
I’ve realised that there’s a possible complication to using this script. The file number you supply to dbms_rowid.rowid_create() is supposed to be the relative file number, but if you’ve been using the p1 parameter on “buffer busy waits” to get a file number then you may be looking at the absolute file number.
If this is the case (and it’s only a hypothesis at present, I haven’t confirmed it) then it isn’t actually going to matter in a large number of systems because Oracle has cunningly rigged things so that the absolute and relative file numbers are the same until you have more than (roughly) 1024 files in the database. If you do go above the limit then you ought to use the object_id to identify the tablespace and use the tablespace id to check what file number you should be using in the call to dbms_rowid.create_rowid().
Update on update:
If you are using bigfile tablespaces then the relative file number for the single file you’re allowed in the tablespace always seems to be 1024. You can always cross-check with a simple query:
select file#, rfile#, ts#, name from v$datafile;