oracle touch count测试

关于跟热块相关的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/