Thursday, December 22, 2005

Touch count bug

I came upon this comment from Jonathan Lewis. He talks about a bug related to the touch count. If you are full scanning some tables and you are using versions before 10g, be aware that they are not kept in the buffer cache no matter how frequently you scan them.

Here is a simple test case in

create table t as select object_id from user_objects where rownum=1;
SQL> select data_object_id from user_objects2  where object_name='T';
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from t;


SQL> begin
2 for t in (select * from t) loop
3 null;
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select file#,dbablk,tch from x$bh where obj=20505;

---------- ---------- ----------
1 11969 3
1 11970 0

After scanning the small table 10 times we see that the touch count for the block is 0.

So, how can we keep the small parameter tables which are scanned frequently? One thing to consider is the use of the file system cache as Jonathan Lewis mentiones in the same link. If you are using a file system cache than it is likely that these tables are in that cache and can be retrieved into the database buffer cache when requested.

Another option is to use a keep pool that is sized according to the sizes of the tables we want to keep and alter the tables to use that cache. This way we can keep the tables cached although scanning them does not increment the touch count.


  1. shouldn't it be:

    SQL> select file#,dbablk,tch from x$bh where obj=20505;

    instead of

    SQL> select file#,dbablk,tch from x$bh where obj=20509;

  2. Corrected. I was working on several tables and i pasted the wrong line.

  3. Could you please also show the plan that was generated as a result of this query