Data Blocks and Freelists (from ixora)[3]
[入库：2005年8月18日] [更新：2007年3月24日]
 |
first insert slow |
|
| the main account table is processed during batch by an array fetch and update. the first insert to the account table does 55000 disk reads and takes 15 minutes. subsequent inserts work normally. |
 |
this sounds like a freelist issue. if your overnight process first drops block space usage below pctused, and then raises it close to pctfree again, you will thereby create a very long transaction freelist. the first insert has to migrate that transaction free list to the master freelist, and unlink the leading blocks on the freelist that do not have enough free space for the row to be inserted. you can control the maximum number of blocks that will be unlinked from the freelist by the first insert with the _release_insert_threshold parameter. i think this is supposed to default to 5 blocks, but if my hunch is correct, it seems to be much higher in your case. alternately, you can reduce pctused to avoid the problem entirely. |
|
strange enqueue wait |
|
why would oracle incur an enqueue wait on update statements issued by two database sessions that touch two different records if both records are on the same block, but otherwise succeed if the statements update two records from different blocks? session_id type id1 id2 mode_held mode_requested
---------- ---- ---------- ---------- -------------- --------------
19 tx 262182 71967 none share
18 tx 262182 71967 exclusive none
|
|
you can tell from the fact that the waiting session wants a share mode lock that it is waiting for a free transaction slot in the block. the problem is that there is not enough free space in the block to dynamically allocate another transaction slot. |
|
impact of multiple freelists |
|
| i have found out that freelists on one table was set up to 10. generally you set freelists to greater than 1 (maybe 2) for heavy inserting tables. my question is, what kind negative impact do high freelists have on a table aside from more storage requirements? |
|
multiple process freelist have a minimal impact on table storage requirements. on average each process freelist will have either 2 or 3 blocks. that is, the difference between 2 freelists and 10 freelists in terms of storage space is likely to be just 20 more blocks below the highwater mark.
the freelist headers themselves have no storage requirements, as they go into the freelist group block or segment header block. having a high number of process freelists does limit the space in that block for the dynamic creation of transaction freelists to which blocks that fall below pctused may be returned. however, there is a database block size based minimum to prevent the creation of too many process freelists. that minimum can be seen with
select kviidsc, kviival from x$kvii where kviitag = 'ktsmtf'; |
|
block header size |
|
| i'd like to know the space actually reserved for row data in a block. i suppose it's (total block size - block header size). in this case the problem becomes knowing the block header size. |
|
the common block header takes 20 bytes, and the tail takes 4 bytes. the transaction control header takes 24 bytes, and there are 24 bytes for each transaction slot. then there is a 14-byte common header for the data layer. cluster blocks have a table directory, followed by a row directory. each slot in the table directory takes 4 bytes, and each slot in the row directory takes 2 bytes. table blocks are the same as cluster block, except that there can only be one table in the table directory. index blocks have a 16-byte common index block header following the common data block header, and then either a 24-byte branch block header, or a 32-byte leaf block header, depending on the block type. these index headers are followed by a row directory, as for clusters and tables. there is also a 3-byte row header on each row in the data area. so there you have it ... it depends. |
|
minimum row length |
|
in a recent article in oracle professional magazine, it says
pctfree will always default to at least 10 percent, even if its set lower than 10 percent
and to confirm this the author suggests
create a new tablespace; create a single table with pctfree set to 1 or so, and load short rows of just a few characters. it should be easy to find the block and see. use od on unix. anyway, the data appears from the back and moves forward in the block. i would say if there is a significant blank spot ('00') at the beginning of the data, then it is indeed leaving behind some space".
can you throw any light on this? |
|
the author is mistaken. this phenomenon that he has observed is due to the minimum row length requirement. if he were to use rows longer than 6 bytes of real data, he would be able to fill up a block completely. it is not necessary to resort to od to see the available space, it is much preferable to use an oracle blockdump because oracle only logically deletes rows in blocks by changing a flag until such time as the space is needed. |
本文关键：Data Blocks and Freelists (from ixora)
本站最佳浏览方式为 分辨率 1024x768 IE 6.0（或更高版本的 IE浏览器）
