freelist和buffer busy wait的关系

gdpr-dba

在网上看到有这种说法，增加freelist能缓解buffer busy wait，但是我不太认同，因为buffer busy wait是不同session对同一个block的争用，就算增加freelist也都是在segment header block的，访问不同的freelist照样会造成段头块的争用，不知道刘大怎么看？

Maclean Liu(刘相兵

Reducing Busy Buffer Waits
To reduce buffer busy waits on:
• Free list blocks:
- Add more free lists
- Use Automatic Segment Space Management
- In the case of Oracle Parallel Server, make sure that each instance has its own
free list group. An ALTER TABLE statement can change the number of free
lists in a free list group


Reducing Busy Buffer Waits (continued)
• Segment headers
- Increase the number of free lists or use Automatic Segment Space
Management.
- Use free list groups (This can make a difference even in a single instance
environment.)


Note: You cannot alter the FREELISTS or FREELIST GROUPS parameters for
segments in tablespaces using Automatic Segment Space Management.
Resolving Free List Contention
To increase the number of free lists for the object, do one of the following:
• Move the object to a tablespace using Automatic Segment Space Management.
• Use the ALTER TABLE command to change the number of FREELISTS.
• Use Oracle Enterprise Manager console, under the SCHEMA – TABLE option, to
change the number of FREELISTS.

Maclean Liu(刘相兵

To reduce buffer busy waits on:
• Free list blocks:
- Add more free lists


增加 freelist 只能减少对 Free list block的 busy buffer wait 争用 ， 对 普通的数据块 (trans data)而言起不到 改善的作用。

gdpr-dba

原帖由 maclean 于 2012-5-11 11:01 发表
To reduce buffer busy waits on:
• Free list blocks:
- Add more free lists


增加 freelist 只能减少对 Free list block的 busy buffer wait 争用 ， 对 普通的数据块 (trans data)而言起不到 改善的作用。 ...

但是我的疑问是增加freelist为什么可以降低Free list block的 busy buffer wait 争用，你增加再多的freelist也是使用一个Free list block，不同的session访问freelist也是会造成Free list block的争用吧？

Maclean Liu(刘相兵

"你增加再多的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
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  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。

gdpr-dba

谢谢刘大。还有几个问题：
1.segment header在整个搜索freelist的过程中是不是一直pin住的，还是process在segment header中发现freelist的header block之后就会释放segment header呢？

2.To reduce buffer busy waits on:
• Free list blocks:
- Add more free lists
这里提到的Free list blocks指的是segment header还是指在freelists上的block呢？

3.DATA SEGMENT HEADER WITH FREE LIST BLKS or  DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT

DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT 这个是什么block?

[ 本帖最后由 gdpr-dba 于 2012-5-12 15:58 编辑 ]

gdpr-dba

顶一下，刘大帮忙看看，谢谢。

Maclean Liu(刘相兵

请不要海量 发帖， 你的这些问题 延迟到不定期回答。