事实上关于freelist和空间的分配和使用问题涉及到的因素太多,无法在这里详细阐述 freelist/freelist group/master freelist / transection free list/process free list 等等关系
偶在测试中的一个疑惑就是:
1:
当 freelist = 1 or 10 的2个表
同一个insert 语句插入100万的记录
为什么他们所使用的空间是一样的大小?
按照理想的状况当HWM移动的时候只有当所使用的 free list 才增加 block是合理的,但几乎所有书和资料上的观点似乎都是说HWM 移动的时候是移动 5*(freelists + 1),但同一个事务又只能使用同一个freelist上的block,于是跟测试结果似乎产生了矛盾,具体里面是怎么做的?算法?
2: 当delete 的时候所释放到freelist上的block,是怎样的放到freelist上的,
或者说是放到 transsection free list 上的这样就能移动到 master free list 上而被不同的transection 所使用?delete 释放的这些块应该是放在freelist的尾部?(insert 导致的HWM 移动是放在 头部)
就算设置了这个参数
_bump_highwater_mark_count
在大量insert 记录到freelist 为10 和1 的两个表中
结果两个表所使用的blocks也只有极少的差异,比例不到1%,如果不设置这个参数则没有差异
我不知道怎么解释这个现象
请参看下面论述:
Each segment has at least a master free list. This free list is implemented as a linked list. The segment header block contains a pointer to the first block on the free list. In the block header for that block is a pointer to the next free list block and so on. The free list pointer in the block header of the last block on a free list is null. The free list header record in the segment header block also contains a pointer to the last block in the free list.
If a segment is created with multiple free lists, then the segment header block also contains a free list header record for each of these process free lists in addition to the master free list.
When a process performs DML that causes a block to fall below the PCTUSED value for the table/cluster, or entirely frees an index leaf block (however, the index case is more complex, because blocks on a free list are not unlinked from the B*-tree structure until an attempt is made to reuse them), that block is put onto a transaction free list. Transaction free lists are dynamically created as necessary, and also have a header record in the segment header block. The transaction free list header also records the transaction id.
When a transaction needs to insert/migrate a row, it uses the first block on its transaction free list, if it has previously freed some blocks (except, for an index, because block splits always occur in a recursive transaction). Otherwise a process free list is used, based on the process number (PID) mod the number of process free lists. If the process free list is empty, or if there are no process free lists, then the master free list is used.
If the master free list is empty, and if there are some transaction free lists for transactions that have committed, then the transaction free list is marked as unused and the blocks are merged into the master free list. Otherwise, the high water mark is raised, initially by 1 block at a time for the first 5 data blocks in the segment, and thereafter by the greater of 5 blocks or _bump_highwater_mark_count blocks (which defaults to 0) times the number of process free lists plus 1 (for the master free list), up to the number of blocks remaining in the extent. These blocks are newed, which means that a free buffer is allocated in the cache and the block header is formatted to make the blocks part of the segment. Raising the high water mark may involve dynamic extension. Once the master free list is not empty, up to 5 blocks are moved to the target process free list if any, from where they can be used.
The number of free list headers that can fit into the segment header block is limited by the database block size. At least half of the free list slots must be available for transaction free lists. You can see the exact number with the query
select kviival from x$kvii where kviitag = 'ktsmtf';
connected as SYS (or internal). On busy segments, more transaction free lists than that may be dynamically created, slots permitting.
If a segment is created with multiple free list groups, one block after the segment header is used for the free list header records for each free list group. There is still a master free list in the segment header, as well as a master free list in each group. Free list group selection is based on the instance number mod the number of free list groups.
As mentioned previously, free list contention occurs when multiple processes using the same free list attempt to modify the data block on the head of the free list concurrently. It is shown in V$WAITSTAT against the data block class. V$WAITSTAT can also show contention for the segment header and free list blocks. This occurs where multiple transaction in the same free list group need to update their free list header records simultaneously. There are various ways of addressing these problems such as rebuilding the table with more free list groups, or increasing _bump_highwater_mark_count, or the novel idea of fixing the application.
To drill down on which segments are causing data block contention, I suggested using event 10046, level 8. This creates a trace file much like to one produced by the sql_trace facility, except that for each event wait a line is printed to the trace file. In particular, each buffer busy wait is recorded together with the P1 and P2 values which are the data file and block number of the wait. So to find which blocks a process has been waiting on, you just grep the trace file for buffer busy waits lines and produce a histogram of the file and block numbers most commonly waited for. Once you have suspect file and block numbers, you can relate them to a segment by querying DBA_EXTENTS. In the case of free list contention on a table it is common to have several hot blocks just below the high water mark for the segment.
There is no CPU cost to having multiple process free lists, and a trivial cost to having multiple free list groups. If you have too many process free lists the worst you get is more free space below the HWM that is not accessible to any particular process. You also get a lower limit on the number of possible transaction free lists you can have.
事实上根本结果来看
freelist的增加并不会导致 空间的巨大浪费。
如下
SQL> alter table t2 storage(freelists 10);
表已更改。
SQL> alter table t storage(freelists 1);
表已更改。
SQL> insert into t select a.* from all_objects a,all_objects b where rownum < 1000000;
已创建999999行。
SQL> commit;
提交完成。
SQL> insert into t2 select a.* from all_objects a,all_objects b where rownum < 1000000;
已创建999999行。
SQL> commit;
提交完成。
SQL> select sum(blocks), count(*) from dba_extents where segment_name = 'T' ;
SUM(BLOCKS) COUNT(*)
----------- ----------
13680 855
SQL> select sum(blocks), count(*) from dba_extents where segment_name = 'T2';
SUM(BLOCKS) COUNT(*)
----------- ----------
13680 855
SQL>
在修改 _bump_highwater_mark_count = 2 的测试例子中两者有细小的变化
但不足以说明所有freelist是同时增加
参考下面的文章,讲的特别好
THis document helps greatly,
http://support.oracle.co.uk/meta ... &p_id=1029850.6
因为HWM移动后的blocks挂在master freelist上并且所有process freelist 都可以去获取。我想这样的话,所谓freelist会浪费空间的可能性已经微乎其微了,不用考虑了!
5.2.6 (If 2.5 fails) Oracle tries to bump up the HWM. To reduce contention on segment header block, Oracle bumps up the HWM by m blocks at a time. The number m is: 1: if the block does not have to go on free list, 1: if we are on the initial extent (0) and the HWM is <= 4 (small table) min (5, unused blocks in the extent): before 7.2 min (5*(NFL+1), unused blocks in the extent): since 7.2. In the example, we assume HWM is at the end of the extent.
5.2.7 New extent allocation. The new blocks are moved to Master free list. Free block search continues from step 2.4
下面是和CP的探讨:
http://www.*****.org/bin/ut/topi ... p;bpg=1&age=100
http://c n o u g.org/bin/ut/topic_show.cgi?id=367&h=1&bpg=1&age=100