Home > fundamental > Oracle的touch count算法

Oracle的touch count算法

November 14th, 2011 Leave a comment Go to comments

Touch count算法是用来确定lru chain上buffer header是否被age out,也可以看做是lru算法的细化吧。

LRU chain细分的话其实有LRU-MAIN跟LRU-AUX两个链,buffer header的初始化会在LRU-AUX上发生,然后再移到LRU-MAIN上,被age out的BH也会移到LRU-AUX上等待reuse;

LRU-MAIN类似于下图:

新进来的BH会插到head of cold end处(不一定就是50%的中点,具体位置由_db_percent_hot_default参数来指定,默认是50%的中点),然后被挤向cold end。

我们常说的LRU chain分热端跟冷端,可能更多的指的就是LRU-MAIN的概念,LRU-AUX是不是这样的HOT-COLD方式,这我还真不清楚。

假如有一个PIO进来,需要在buffer pool内申请一个buffer,那oracle会先在LRU-AUX这个链上找,如果没有可用的buffer header空间,才会在LRU-MAIN上cold end端找。那么怎么判断cold end的BH是否可用呢,不可用又该怎么处理呢,这就是由touch count算法来处理了。

Touch count可能涉及到的几个参数:

_db_percent_hot_default 用来控制hot部分的百分比,默认是50(我用10跟90测的时候数据对比不是很明显,不知道是不是理解的有出入。对应的也有_db_percent_hot_keep跟_db_percent_hot_recycle参数,再深入就不了解了^^)

  • _db_aging_hot_criteria: A threshold value that is used to decide victim selection. The default value is two.
  • _db_aging_stay_count: The touch count is set to this if low value is less than threshold during victim selection. The default value is 99(这是9I的,10GR1上好像是0).
  • _db_aging_touch_time: The touch count is not incremented if the buffer is  touched within _db_aging_touch_time seconds of the last touch.(即这个时间内多次hit都算作一个,默认3秒)

P-CODE伪代码:

IF ( touch count of scanned buffer > _db_aging_hot_criteria ) THEN

–这里是>还是>=,测的时候都出现过符合的情况,比较奇怪,Julian Dyke也说是>=,这个就没必要纠结了,除非oracle公开源码了。

–Give buffer another chance (do not select as a victim)

IF (_db_aging_stay_count >= _db_aging_hot_criteria) THEN

Halve the buffer’s touch count –然后移到HOT END

ELSE

Set the buffer’s touch count to _db_aging_stay_count –然后移到HOT END

END IF

ELSE

Select buffer as a victim

END IF

我设置的参数:

NAME                                 TYPE VALUE                          ISDEFAULT

—————————— ———- —————————— ———

_db_percent_hot_default                 3 90                             FALSE

_db_percent_hot_keep                    3 0                              TRUE

_db_percent_hot_recycle                 3 0                              TRUE

NAME                                 TYPE VALUE                          ISDEFAULT

—————————— ———- —————————— ———

_db_aging_hot_criteria                  3 2                              TRUE

_db_aging_stay_count                    3 0                              FALSE

_db_aging_touch_time                    3 3                              TRUE

测试如下:

SQL> select LRU_FLAG,TCH from x$bh where FILE#=6 and DBABLK=7164;

no rows selected

Elapsed: 00:00:00.01

SQL> select id from lyn.haha where rowid='AAACkBAAGAAABv8AAA';

ID

----------

2

Elapsed: 00:00:00.03

SQL> /

ID

----------

2

Elapsed: 00:00:00.00

SQL> /

ID

----------

2

Elapsed: 00:00:00.01

SQL> select LRU_FLAG,TCH from x$bh where FILE#=6 and DBABLK=7164;

LRU_FLAG        TCH

---------- ----------

0          3

--LRU_FLAG 8表示HOT,2表示COLD,4表示在LRU-AUX上,0表示没有设置(刚进来),但是测试的时候还出现过1跟5的情况。5有时候会在flush buffer_cache后出现。

Elapsed: 00:00:00.01

SQL> set autot trace

SQL> select /*+index(qif uni_qif)*/ * from qif where id<=40000;

40000 rows selected.

Elapsed: 00:00:03.06

Execution Plan

----------------------------------------------------------

Plan hash value: 433752319

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         | 41271 |    39M|  6857   (1)| 00:01:23 |

|   1 |  TABLE ACCESS BY INDEX ROWID| QIF     | 41271 |    39M|  6857   (1)| 00:01:23 |

|*  2 |   INDEX RANGE SCAN          | UNI_QIF | 41271 |       |   100   (0)| 00:00:02 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"<=40000)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

277  recursive calls

0  db block gets

10855  consistent gets

6157  physical reads

0  redo size

41056567  bytes sent via SQL*Net to client

29711  bytes received via SQL*Net from client

2668  SQL*Net roundtrips to/from client

4  sorts (memory)

0  sorts (disk)

40000  rows processed

SQL> set autot off

SQL> select LRU_FLAG,TCH from x$bh where FILE#=6 and DBABLK=7164;

LRU_FLAG        TCH

---------- ----------

0          3

Elapsed: 00:00:00.02

SQL> set autot trace

SQL> select /*+index(qif uni_qif)*/ * from qif where id between 40001 and 50000;

10000 rows selected.

Elapsed: 00:00:00.44

Execution Plan

----------------------------------------------------------

Plan hash value: 433752319

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |  3175 |  3147K|   529   (0)| 00:00:07 |

|   1 |  TABLE ACCESS BY INDEX ROWID| QIF     |  3175 |  3147K|   529   (0)| 00:00:07 |

|*  2 |   INDEX RANGE SCAN          | UNI_QIF |  3175 |       |     9   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID">=40001 AND "ID"<=50000)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

9  recursive calls

0  db block gets

2766  consistent gets

1500  physical reads

0  redo size

10266968  bytes sent via SQL*Net to client

7711  bytes received via SQL*Net from client

668  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

10000  rows processed

SQL> set autot off

SQL> select LRU_FLAG,TCH from x$bh where FILE#=6 and DBABLK=7164;

LRU_FLAG        TCH

---------- ----------

8          0 -- 被移到了HOT END

Elapsed: 00:00:00.01

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01

SQL> select LRU_FLAG,TCH from x$bh where FILE#=6 and DBABLK=7164;

LRU_FLAG        TCH

---------- ----------

4          0 – 被移到了LRU-AUX

Elapsed: 00:00:00.01
Categories: fundamental Tags:
  1. No comments yet.
  1. No trackbacks yet.