Oracle touch count测试
Oracle touch count测试
关于跟热块相关的touch count 。
Oracle 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
评论暂时关闭