René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Full table scan blocks in the buffer cache

Each database block that is cached in the buffer cache is represented by a row in the x$bh table.
This x$bh table has a column flag whose 0x80000 bit is set if the block was read as part of a full table scan. By selecting rows from x$bh that have this bit set, it is possible to show which database blocks were read during a full table scan.
This is demonstrated in the following.
First, I connect as myself... 
connect rene/rene
..., create a table and fill it with some random values: 
create table fts_test_1 (
  a number,
  b varchar2(10)
);

begin
  for i in 1 .. 1000 loop
    insert into fts_test_1 values (i, dbms_random.string('x', 10));
  end loop;
end;
/
Then, a second table is created that contains the same structure and data as the first table: 
create table  fts_test_2 as 
select * from fts_test_1;
However, the first table gets an index on one of its column: 
create unique index ix_fts_test on fts_test_1 (a);
The buffer cache is flushed to make sure that all blocks somehow belonging to fts_test_1 on fts_test_2 are not in the buffer cache anymore. 
alter system flush buffer_cache;
Two select statements. The first one will use the index (and doesn't full table scan the table) while the second statement will full scan the table: 
select b from fts_test_1 where a = 500;
select b from fts_test_2 where a = 500;
Connecting as sysdba in order to be able to query x$bh: 
connect / as sysdba
And doing the query where only non-sys, full table scaned blocks are selected. Remember, these blocks have the 524288 (0x80000) bit set. 
select 
  substr(obj.object_name, 1,30)  object,
  obj.owner                      owner
from 
  dba_objects obj,
  x$bh        xbh
where 
  xbh.obj         = obj.data_object_id and 
  obj.object_type ='TABLE'             and 
  bitand(xbh.flag,524288)>0            and 
  obj.owner<>'SYS';
And indeed, as expected, only FTS_TEST_2 was full table scaned. 
OBJECT                         OWNER
------------------------------ ------------------------------
FTS_TEST_2                     RENE
FTS_TEST_2                     RENE
FTS_TEST_2                     RENE