Data Blocks and Freelists (from ixora)[6]
[入库：2005年8月18日] [更新：2007年3月24日]
 |
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). |
 |
tuning table storage |
|
| i'm looking for a tuning utility to help me detecting block chaining and fragmentation, to suggest values for pctfree and pctused, and so on. |
|
there are two scripts on this web site that might help you. the first, called row_migration.sql, reports the degree of row migration as a percentage of the number of rows, for each table with migrated rows. it ignores tables with longs or possible row lengths greater than the block size, because it is not easy to distinguish row migration from chaining for such tables. a suggested pctfree is calculated as the free space left by one less than the number of average rows that can fit in a block. a new pctused is suggested to allow for a little more than one row between pctfree and pctused.
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! |
|
gap between pctfree and pctused |
|
| why does having too small or no gap between pctfree and pctused increase the risk of buffer busy waits? |
|
if you have a pctfree and pctused too close together, most inserts and most deletes will require the target block to be either taken off or added to a freelist. that involves a change to the segment header or freelist group block that stores the freelist headers. if there is high concurrency of inserts and deletes on the table, they will be single-threaded on the buffer lock for the buffer containing the current mode copy of that freelist header block. this will be shown as buffer busy waits in v$system_event. v$waitstat will classify these waits as against segment header class blocks, or free list class blocks, depending on whether the table has multiple freelist groups. |
|
row migration |
|
| is there a way to find out which rows are chained? i have a good number of chained rows in a table, and i need to find out which rows and why. the maximum size of the rows cannot exceed 150 bytes, and the database block size is 16k. updates occur only to number columns (the values increase) on a daily basis, and to char & date columns on a monthly basis. am i correct that updates on number columns do not lead to an increase in size?
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. |
|
maxtrans |
|
| what are the pro's and con's of setting the maxtrans to the maximum of 255? |
|
that's the default, but you actually cannot get that many, because there is a lower limit imposed by the block size, unless you have a 16k block size and very short rows. |
|
where did you find this info regarding maxtrans? i have checked several books and had no luck with an exact definition. did you find this info in a particular book, or did it come from a wealth of knowledge? |
|
the bit about the block size limit is in my book. i did not bother to explain how many itl entries can fit, because it is academic. however, if your curious ...
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. |
本文关键：Data Blocks and Freelists (from ixora)
本站最佳浏览方式为 分辨率 1024x768 IE 6.0（或更高版本的 IE浏览器）
