Data Blocks and Freelists (from ixora)[5]
[入库：2005年8月18日] [更新：2007年3月24日]
 |
number datatype storage |
|
i have been doing some testing on the internal representation/storage of the number datatype. what i have found so far puzzled me and would like an expert's analysis of the output shown below. dump_no col1 vsize(col1) length(col1)
======================================== ========== =========== ============
typ=2 len=3: 194,10,21 920 3 3
typ=2 len=2: 194,93 9200 2 4
the question is, why would 920 be stored using 3 bytes while 9200 used only 2 bytes? |
 |
the base for the exponent is 100 (stored in excess 64 notation with a high-order sign bit). thus each pair of significant digits is represented in one byte of the mantissa. 9200 only requires one byte in the mantissa, representing 92. whereas 920 requires two -- one representing 09, and one representing 20. |
|
buffer busy waits |
|
| we have some big tables that all users insert to and update. i noticed some waits on data block and segment header blocks. i could recreate the tables with more freelists, but i can't find a block with an itl count higher than 1 to get the highest number of concurrent transactions on the table. if i increase the number of freelists, does it help even if it is not as high as the number of concurrent transactions? is there a drawback in specifying many freelists? |
|
i assume you've been doing some block dumps to look at the itl count. well done. that means that you have understood a lot of the issues here already. increasing the number of freelists will not hurt, but in this case it will probably not help either. there is a second reason for data block waits that you should consider, namely that of reference data that is not being cached in a keep buffer pool being simultaneously required by two sessions. you can identify these from the p3 parameter value of the waits, which you can get with our trace_waits.sql script. the segment header contention is possibly caused by having pctfree and pctused too close together on some important tables. |
|
free space not reused |
|
| we have a strange problem on oracle 7.3.4. for a certain table we replace (delete and insert) 20% of the rows each day. but the free space from the deletes is not re-used by the inserts, so the table acquires a new extent each day. this table contains 1 long column, but there are no chained rows (analyze shows: chained_rows = 0 and avg_row_len = 650). the database block size is 2k - a little small, i know! also pctfree is 10 and pctused is 90 so pctfree + pctused = 100. what are the consequences of having pctfree + pctused = 100, and is this the cause of our problem? |
|
having pctfree + pctused = 100 does not cause free space to go unused, as in your case, but it does increase the risk of buffer busy waits against the segment header block if the table is modified by multiple concurrent sessions. however, that is a different problem.
assuming default initrans you have 1958 bytes available in the data area per block. each row takes an extra 2 bytes in the row directory, so 3 average rows would require 1956 bytes. it is easy to imagine that some rows being deleted are smaller than average. so let us say that 1% of the rows being returned to the freelist only have 600 bytes free. when searching for a block from the freelist, oracle will look at no more than the first 5 blocks (by default). any block that cannot fit the new row, but is still below pctused, is skipped over stays on the head of the freelist. in this case, the delete operation will return all the blocks from which a row has been deleted to the head of the freelist, and the inserts will begin to use those blocks. however, you will soon have 5 of the blocks with only 600 bytes of free space on the head of the freelist. this means that most inserts will scan these 5 blocks, skip them, and get a fresh block at the hwm.
this is part of why it is recommended that there be greater than 1 average row between pctused and pctfree. in your case, i would recommend firstly that you drop pctused to 50%. that will enable the unusable blocks on the head of the freelist to be unlinked, and you will soon be using all the presently unusable free space. long term, this will mean that two rows need to be deleted from any block before it goes on the freelist, but overall this will be a more space efficient solution. then, as soon as you have opportunity, this database should be rebuilt with an 8k or 16k block size. |
|
i don't understand very well the impact of pctused in this case. how does a smaller pctused resolve the problem of these 5 blocks? |
|
the impact of the pctused setting is that when the block on the head of the freelist cannot accommodate the new row, oracle can unlink it from the freelist if and only if it is fuller than pctused. if oracle were to unlink blocks with less free space than pctused, then that block would be most unlikely to ever end up on a freelist again, and once all the rows had been deleted the block would be "lost".
setting pctused enables blocks with relatively little free space to be unlinked from the freelist, so that blocks with more space further down the freelist can be reached. this is because oracle will only look at the first 5 blocks by default before formatting a new block above the high-water mark. the number of blocks is configurable via the _walk_insert_threshold parameter. there is also a _release_insert_threshold parameter that also defaults to 5. this limits the number blocks that will be unlinked, rather than just skipped (walked). so in the worst case, an insert may unlink 4 blocks and walk 5 or vice versa before going to the high-water mark. |
|
thanks. i'll change pctused. when i do, will it take effect immediately, or do i have to reorganize the table? |
本文关键：Data Blocks and Freelists (from ixora)
本站最佳浏览方式为 分辨率 1024x768 IE 6.0（或更高版本的 IE浏览器）
