Data Blocks and Freelists (from ixora)[4]
[入库：2005年8月18日] [更新：2007年3月24日]
 |
freelists |
|
| will an insert statement lock the table being inserted into? if so, is there any way to avoid this? i have one table into which all transactions are inserting. i feel this table is acting as a bottleneck and slowing down all the users. what is the best way to approach this? |
 |
such a table should be recreated with multiple process freelists. to get the appropriate number of freelists, take a blockdump of some of the blocks in the table, and use the maximum number of interested transaction list entries that you see in any one block. |
|
minimum row length |
|
| i am unaware of a minimum row length. can you enlighten me some more here? |
|
the minimum row length requirement is that at least 11 bytes of data area are required for each row, to allow for the possibility of all the rows in the block needing to be migrated. the 11 bytes are 2 for a row directory entry, 3 for the row header and 6 bytes for the rowid of the migrated row piece. therefore no more than (data area size / 11) rows can be placed in any one block. |
|
freelists |
|
| is there any benefit in increasing the number of freelists on a single cpu machine? also, is there any point in doing so if there are no deletes, because all inserts will go to the high-water mark, rather than using freelists? |
|
yes, i have seen benefit in multiple freelists on a single cpu machine. while buffer lock duration is brief, it is still long enough relative to a time-slice to make a significant impact. the frequency of deletes is not a factor. all inserts use freelists, even when inserting at the high-water mark. |
|
freelists |
|
| i am trying to understand transaction and process freelists. what is the procedure when oracle needs to get a block from a freelist? |
|
a process will first check its own transaction freelist, for free space released earlier in the same transaction. failing that, it goes to the process freelist. if the process freelist is empty, it is populated with up to 5 blocks from the master free list. if the master freelist is empty, then if possible the freelist for a committed transaction will be merged with it - that is all the blocks freed by that transaction are moved to the master free list. otherwise the hwm is raised and the blocks are formatted and placed on the master freelist. the number of blocks formatted is normally 5, but if _bump_highwater_mark_count has been set, it is the number of process freelists plus 1 times the _bump_highwater_mark_count setting, or the number of unused blocks in the extent, whichever is less. once the master freelist is populated, a group of up to 5 blocks is moved to the process freelist, from where a block can be taken for use. |
|
freelist contention |
|
you suggested that my buffer busy waits were probably due to freelist contention. however, i have an article that explains how to check for freelist contention with the query select * from v$waitstat where class = 'free list';
and it appears that i don't have any freelist contention at all. what else could the problem be? |
|
that article is wrong. that query shows contention on freelist group blocks. by default tables don't have any freelist group blocks, so you are not likely to see contention for them. the freelist headers are stored in the segment header block. freelist group blocks only exist if you specify multiple freelist groups when creating the table. even then, contention on the freelist group block (or segment header) is only contention for modifying the freelist header, not the freelist data blocks themselves.
real freelist contention is when two or more processes look at the freelist header, without modifying it, and then attempt to insert into to the data block on the head of the freelist. the contention occurs on the data block itself and is shown as such in v$waitstat.
if that query were to show a significant number of waits, then that would probably indicate too small a gap between pctfree and pctused on some tables. |
|
initrans |
|
| in a database with a large concurrent user population and default initrans settings, can the cost of dynamically creating transaction slots be significant? |
|
the cost of dynamically creating a transaction slot is trivial. there is an extra change vector in the undo record and two more in the redo record, but no additional locking or latching. it is better to keep data density high, than to attempt to avoid this cost. |
本文关键：Data Blocks and Freelists (from ixora)
本站最佳浏览方式为 分辨率 1024x768 IE 6.0（或更高版本的 IE浏览器）
