|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Full table scan blocks in the buffer cache
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...
..., 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;
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