oracle的freelist,freelist和buffer busy wait的关系

最新推荐文章于 2024-05-26 06:21:26 发布

rubben peng

最新推荐文章于 2024-05-26 06:21:26 发布

阅读量200

点赞数

文章标签： oracle的freelist

"你增加再多的freelist也是使用一个Free list block"

as maclean said:

To handle such contention situations, we can specify many free lists at the segment level, sometimes called process free lists. The freelist storage parameter indicates the number of free list sets. Each user process is assigned to one process free list. When the user process needs a block with free space, it scans its assigned process free lists. Existence of multiple process free lists improves performance for concurrent OLTP activities on the same segment and avoids segment header contention.

Free List 分成：

FREELISTS (KTSFS)

Master free list - committed/newly allocated blocks.

Process free lists - spread insertion hot spot

Transaction free lists -

创建 segment 时可以指定 Process free lists，如

SQL> create table maclean_freelist (t1 int) storage (freelists 10);

Table created.

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='MACLEAN_FREELIST';

HEADER_FILE HEADER_BLOCK

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

1 67617

nfl = 10, nfb = 1 typ = 1 nxf = 0 ccnt = 0

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

更少的 process free list 意味着 process 需要对 Freelist block更多的 search 搜索操作，意味着更长时间的 freelist block被pin，意味着更多进程需要等待 freelist block的pin 被释放，意味着更多的 busy buffer wait 。

而更多的process free list则相反，这就是为什么增加freelists 有助于减少 =DATA SEGMENT HEADER WITH FREE LIST BLKS or DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT 的 busy buffer wait。