关于跟热块相关的touch count 。
8i开始oracle提供了接触点(touch count)来作为block是冷热的标志，在一定条件满足的情况下block被进程访问一次touch count 增加一，到某个标准之后被移动到LRU热端。那在短时间内从某种意义上讲，touch count 大的block可能暗示着在当前某个周期内被访问次数比较多。
在一定条件满足的情况下block被进程访问一次touch count 增加一，这个条件是什么呢？
测试结果：
每次全表扫描，表块的tch都会+1;如果使用索引访问，并不是每次访问tch都会+1，而在多数时候是不会+1的。Touch count 待续..
SQL> create table test(id int,text char(1000));
Table created
SQL>
SQL> BEGIN
2 FOR i IN 1 .. 50 LOOP
3 INSERT INTO test VALUES (i,i || '');
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> BEGIN
2 FOR i IN 51 .. 100 LOOP
3 INSERT INTO test VALUES (i,i || '');
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL>
--获取文件号与block号
SQL> SELECT distinct dbms_rowid.rowid_relative_fno(ROWID) f,
2 dbms_rowid.rowid_block_number(ROWID) b
3 FROM test order by b;
F B
---------- ----------
1 60810
1 60811
1 60812
1 60813
1 60814
1 60815
1 60816
1 60817
1 60818
1 60819
1 60820
1 60821
1 60822
1 60823
1 60824
15 rows selected
SQL>
--找到对象test表的data_object_id与x$bh.obj进行关联，以查找块对应的tch值。
SQL> SELECT data_object_id
2 FROM dba_objects
3 WHERE owner = 'SYS'
4 AND object_name = 'TEST';
DATA_OBJECT_ID
--------------
51337
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 4
60823 3
SQL> select count(*) from test;
COUNT(*)
----------
100
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 5
60823 4
SQL> select count(*) from test;
COUNT(*)
----------
100
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 6
60823 5
SQL> select count(*) from test;
COUNT(*)
----------
100
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 7
60823 6
SQL> select count(*) from test where id=1;
COUNT(*)
----------
1
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 8
60823 7
SQL> select count(*) from test where id=1;
COUNT(*)
----------
1
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 9
60823 8
--创建索引，以索引访问的形式观察touch count,tch值并不会每次都+1,多
--数时候并没有+1。
SQL> create index idx_id on test(id);
Index created
SQL> select count(*) from test where id=1;
COUNT(*)
----------
1
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 11
60823 10
SQL> select count(*) from test where id=1;
COUNT(*)
----------
1
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 11
60823 10
SQL> select count(*) from test where id=1;
COUNT(*)
----------
1
SQL> SELECT dbablk, tch FROM x$bh WHERE obj = 51337 and dbablk in(60812,60823) ORDER BY dbablk;
DBABLK TCH
---------- ----------
60812 11
60823 10
“Touch Count” for Buffer Cache
http://avdeo.com/2008/06/26/touch-count-for-buffer-cache/