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 9.2.0.7.

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';

DATA_OBJECT_ID
--------------
  20505
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from t;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                        11970

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;

FILE#     DBABLK        TCH
---------- ---------- ----------
  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.