Data Blocks and Freelists (from ixora)[2]
[入库：2005年8月18日] [更新：2007年3月24日]
 |
the main cause of this is two processes trying to modify the block at the same time. however, it also occurs when one process is busy reading the block into the buffer cache, but it is not yet there, and another process needs to access the same block. you can tell the difference by looking at p3 in v$session_wait if you are lucky enough to catch some of the buffer busy waits there. a 0 or 1014 value is the read case; all other values are the modification contention case.
your next step should be to try to identify which database segments are involved. once again, if you can catch some waits in v$session_wait then you can use the p1 & p2 (file & block) values to lookup the segment name in dba_extents. if it is a table, then 9 times out of 10 all you need to do is to recreate the table with more process freelists. one way of working out how many freelists to create is to dump some blocks from the segment not very far below the high-water mark and take a look at the interested transaction list size. the peak number of interested transactions plus one is the minimum number of process freelists that you need. |
 |
transaction and process freelists |
|
| how can i determine how many transaction freelists are available and how much space they consume? does it depend on whether the freelists parameter is specified in the storage clause? |
|
other than the freelist headers in the segment header block (or freelist block if multiple freelist groups have been specified), freelists do not consume any space at all. blocks are linked into a freelist using a fixed pointer in the block header.
the number of freelists available depends on your database block size and oracle version. for a 2k block size under 8.0.5 there can be up to 47 process and transaction freelists in total. at least 25 must be reserved for transaction freelists. you can see this number with the following query:
select kviival from x$kvii where kviitag = 'ktsmtf'; |
|
row overhead |
|
| i would like to know if the avg_row_len column in user_tables that is populated by the analyze command includes the row directory overhead of 3 * ub1 for each row stored or not. |
|
the row directory overhead is 2 bytes per row, not 3. there is also a 3 byte overhead per row in the data section of the block: 1 for the total row length, 1 for flags, and 1 for the column count. avg_row_len does not include the row directory overhead, but does include the other three bytes of row data overhead. |
|
blocks on freelists |
|
| how can you calculate the number of blocks that are on a freelist, and how can you tell whether or not a block is on a freelist and which one it is on if there are more than one? |
|
you can use dbms_space.free_blocks to count the blocks on the freelists for a particular freelist group, but not down to the level of distinct freelists. it is not feasible to determine which freelist a particular block is on, because the freelists are singly linked. the code would have to start at the freelist header block (segment header) and follow all the freelists one block at a time to determine this, which makes it infeasible on performance grounds. |
|
freelist groups |
|
| you suggested 2 freelist groups. now every manual i know of relates this setting to multi-instance oracle and i believe that the group is just on the instance number. so why 2? |
|
you have buffer busy waits on the segment header blocks for your key tables. by having two freelist groups, you effectively move the freelists out of the segment header block into a separate freelist block. there will be two such freelist blocks, one of which will never be used. but the load that you presently have on the segment header block will be divided between the new segment header block and the first freelist block. |
|
initrans and maxtrans |
|
| i found an oracle book that said that the value of initrans and maxtrans can be low if the index entries are large. could you tell me why? |
|
when a transaction modifies a block, it needs exclusive use of a transaction slot, from that moment, until it commits (or rolls back). this is used to implement row level locking. if a table block only contains one row, then only one transaction slot can be needed. similarly, if an index block only has 3 keys, then at most 4 transaction slots can be needed (the extra one is for a recursive transaction for an index block split).
although the number of row/keys in a block sets the maximum for the number of transaction slots that might be needed, it is most unusual to require that many slots. indeed, given that oracle can dynamically allocate extra transaction slots from internal block free space as required, it is questionable whether you should use a non-default initrans setting at all, except in unusual circumstances (such as parallel dml). another exception is where rows/keys are updated in sequence by distinct transactions.
so your author is right about initrans in general. however it has more to do with oracle's ability to dynamically allocate transaction slots than the questionable assumption of random data access. i would also caution against tampering with maxtrans. |
本文关键：Data Blocks and Freelists (from ixora)
本站最佳浏览方式为 分辨率 1024x768 IE 6.0（或更高版本的 IE浏览器）
