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.
However, waits for 'data block' class blocks are commonly due to insufficient process freelists. If multiple insert processes use the same process free list concurrently, then they will all attempt to modify the data block on the head of that free list. The associated buffer busy waits are recorded as 'data block' class waits because that is the class of block on which they are trying to establish a buffer lock.
Discrete transactions hold buffer locks for the duration of the transaction and can also cause data block buffer busy waits.
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.
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';
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.
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
25 October 1999
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';
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.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.
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
9 February 2000
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
18 February 2000
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? If you just change PCTUSED the situation will not get any worse. However, to fix the situation properly, you should rebuild the table as soon as possible (if not the whole database).The second, called sparse_tables.sql, reports the data density for sparse tables as a percentage of the number of rows that could fit below the high-water mark. A new PCTFREE of 1 is recommended, on the assumption that there is no risk of row migration for such tables. A new PCTUSED is suggested as before.
Warnings: Both these scripts use the statistics recorded in the data dictionary, so you need to have analyzed everything recently if the results are to be trusted. Also, they are based on the assumption of a pseudo-random pattern of insert/update/delete. There are some tables that need more generous values for PCTFREE and PCTUSED, because they have an unusual DML pattern. Nevertheless, you can certainly take this as a better starting point than the defaults of 10 and 40!
Assuming that a block has been filled up entirely and Oracle needs to expand a row in that block, will the row migrate to a new block (as it would definitely find a block in which to fit the entire row) or will it chain across to another block? I assume that Oracle would rather migrate than chain. If it migrates then why does it show that it has so many chained rows? Does it mean that migration is reflected as row-chaining?
Updating NUMBER columns to larger values can increase the storage needed. When Oracle reports "chained rows" it includes migrated rows in the count. In your case, they are all migrated rows, because the row length is less than the block size, and as you say, Oracle will migrate in preference to chaining.You can get the migrated rows using ANALYZE LIST CHAINED ROWS, but I would just rebuild the whole table with a suitable PCTFREE setting. You can use our row_migration.sql script to get a better value for PCTFREE.
The minimum row length is 11 bytes. To need 255 ITL entries, you would need 255 rows in the block. That makes 2805 bytes of data. Each ITL is 24 bytes. So 255 would need 6120 bytes. That will not fit in an 8192 byte block, without regard for headers even.
Or put another way, a 16K block with headers and 255 ITLs would have 10198 bytes for data. To get 255 rows in the block, the row length would have to be less than 40 bytes.
chained fetch ratio 0.3099 PCTFREE too low for a table
Is there any way I can find out which table has PCTFREE too low?
This could be chaining, rather than row migration. If it is row migration, and if your schema is analyzed, then our row_migration.sql script might help.
Block headers
10 April 2000
I'm starting to take some blockdumps, but it is hard to know what I'm looking at. Can you help me to understand what all those header fields are used for? They seem to take up quite a lot of space.
There are three sets of header information, corresponding to the three layers of the Oracle kernel that handle data blocks. I'll only explain the first two for now, because the third is too big a question to answer. Firstly, all datafile blocks are written and read by the cache layer (KCB) generally through the database block buffer cache. There is a 20-byte header and 4-byte tail that are read and maintained by the cache layer. The cache layer header is also called the common block header.
Blocks that contain user data are further interpreted and maintained by the transaction layer (KTB). These blocks have a transaction layer header immediately following the common block header. The transaction header is comprised of a 48-byte fixed header, including one 24-byte interested transaction slot, and a variable number of additional interested transaction list slots.
The remainder of each block, the data area, is used by the data layer (KD) to store user data. The data area contains a header, free space and row data. The internal structure of the data layer header and row data area is dependent on the block type. Cluster blocks, table blocks, index branch blocks and index leaf blocks each have different stuctures. As I said, I'll not attempt to explain the structure of this area in detail for now, but I will tell you how big the headers are. Here's how it looks in a diagram.
The Cache Header and Tail This is what the cache header and tail look like in a block dump.
buffer tsn: 0 rdba: 0x004000f8 (1/248) scn: 0x0000.0001ab63 seq: 0x01 flg: 0x00 tail: 0xab630601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
The cache layer header is comprised of the following fields.
databaseThe tail is comprised of the low-order two bytes of the SCN base followed by the block type and the sequence number. The consistency of the header and tail is checked whenever a block is read. This detects most block corruptions, in particular split blocks from hot backups.
The Transaction HeaderThe transaction header consists of 24 bytes of control information followed by an array of one or more transaction slots. This array is called the interested transaction list (ITL), and each slot may contain an interested transaction entry (ITE). The initial number of ITL slots is set by the INITRANS parameter for the segment, except that index branch blocks only have one slot. Cluster and index segments have a minimum INITRANS value of 2. If there is sufficient free space in the data area of the block, additional transaction slots can be dynamically allocated as required, up to a limit dictated by the database block size or the MAXTRANS parameter for the segment.
This is what the transaction header looks like in a block dump.
Block header dump: 0x00400035 Object id on Block? Y seg/obj: 0x2 csc: 0x00.21ab6 itc: 2 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0000.00f.00000005 uba: 0x0040000b.0000.12 C--- 0 scn 0x0000.000000ac 0x02 xid: 0x0002.003.00000027 uba: 0x00c00004.0019.01 --U- 1 fsc 0x0000.00021b7c
The control information in the transaction header contains the following fields.
object number 4 bytes The object number of the segment in OBJ$. cleanout SCN 6 bytes The SCN at which the last full cleanout was performed on the block. ITL count 1 byte Number of entries in the ITL. flag 2 bytes A 0 indicates that the block is on a freelist. Otherwise the flag is -. block type 1 byte 1 = dataEach interested transaction list entry contains the following fields.
transaction id 8 bytes This is comprised of the rollback segment number (2 bytes), the slot number in the transaction table of that rollback segment (2 bytes), and the number of times use of that transaction table has wrapped (4 bytes). undo blockThe body of each block contain the row data and free space. In general rows are inserted from the bottom of the block. However, block free space is not coalesced when rows are deleted or updated such that the overall row length is reduced. Block free space is only coalesced when more contiguous space is needed for an insert or update than that which is available.
本文地址:http://com.8s8s.com/it/it20526.htm