Consider for example the table T1 illustrated below. It is comprised of 8 extents of 50 blocks each. The first block is the segment header, there are 389 data blocks in use, and there are 10 free blocks above the high water mark. With a multiblock read count of 16 blocks, and assuming none of the blocks are already in the cache, a full table scan on this table will require 4 data block reads per extent, except the last - a total of 31 multiblock reads.
If the table is rebuilt as T2 with an extent size that is an exact multiple of the multiblock read size, then the number of multiblock reads required to scan the table is minimized. This is illustrated in the figure below. The table is now comprised of 5 extents of 80 blocks each. So, a full table scan now requires 5 multiblock reads per extent, or a total of 25 multiblock reads.
Please note that it was not the reduction in the number of extents as such that made the difference. There would be no further saving in rebuilding the table with a single extent of 400 blocks. The number of multiblock reads required to scan the table would still be 25.
Used extents are recorded in the data dictionary in the UET$ table, which is clustered in the C_FILE#_BLOCK# cluster. This data dictionary cluster is sized on the assumption that segments will have an average of just 4 or 5 extents each. Unless your data dictionary was specifically customized prior to database creation to allow for more used extents per segment, then creating segments with thousands of extents will cause excessive cluster block chaining in this cluster. Excessive numbers of used and/or free extents can also distort the operation of the dictionary cache LRU mechanism. So extents should not be allowed to proliferate, but it is because of the performance impact on the data dictionary and dictionary cache, rather than any supposed impact of full table scan performance.
The performance impact of a proliferation of extents is most obvious when attempting to drop a segment with a large number of extents. There are anecdotes of such operations taking days, or taking so long that is was quicker to rebuild the database.
There is one case in which data dictionary updates are required for space management operations in locally managed tablespaces. Namely, if tablespace quotas are used. The total amount of space used against each quota is maintained in the TSQ$ table, which is clustered in the C_USER# cluster. However, updates to this table do not require the ST enqueue, and so there is no serialization risk at this point.
The performance of these space management transactions for dictionary managed tablespaces is critical, because they are single threaded on the ST (Space Transaction) enqueue. This is necessary to ensure that the changes made by space transactions to the data dictionary are atomic. So whenever a segment in a dictionary managed tablespace is created or extended or shrunk or freed, the ST enqueue must be held for the duration of that transaction, to the exclusion of all other such space management transactions. Contention for the ST enqueue can be a problem, particularly in parallel segment creation operations. To reduce the risk of contention for the ST enqueue, space management transactions should be both minimized and optimized, and adopting a fixed extent size policy is an important part of the strategy.
It is interesting to note that it is possible for a space transaction against a dictionary managed tablespace to require a recursive space transaction. For example, to coalesce free extents the free extent rows for the tablespace must be sorted. This sort may require the creation of a temporary segment, which involves a space transaction. That space transaction may in turn trigger its rollback segment to extend, which involves another space transaction. Recursive space transactions do not attempt to coalesce free space. They just fail if a big enough free extent is not immediately available.
Unless your extents are less than 5 blocks, Oracle rounds the extent size up to a multiple of 5 blocks. For example, if your multiblock read count is 16 blocks and you attempt to allocate extents of 48 blocks (3 multiblock reads), then you will actually get extents of 50 blocks as in the example at the beginning of this tip. Extents may be enlarged further if their allocation would otherwise leave a free space fragment of less than 5 blocks. Oracle will only fail to round an extent size up to a multiple of 5 blocks, if it cannot allocate an extent of the enlarged size. It is unfortunate that Oracle chose 5 as the multiple for rounding up extent sizes, rather than a binary power such as 4 or 8. As a result, multiblock reads will not align to extent boundaries unless all extents are a multiple of 5 blocks as well as being a multiple of the multiblock read size.
Another feature of Oracle that can undermine a fixed extent size policy is extent trimming. When Oracle7 creates a table or index in parallel, each of the final set of parallel query slaves writes to its own temporary segment. The temporary segments are then merged into a single data segment, and the unused part of the final extent is trimmed off and returned to the free extent table. Under a fixed extent size policy, the small free extents that are trimmed off are unusable. Extent trimming can be disabled by setting event 10901.
event = "10901 trace name context forever"If you have adopted a fixed extent size policy, and have no tablespaces with a non-zero default PCTINCREASE value, you can also save SMON the trouble of checking for free extents to coalesce every 5 minutes when it wakes up to perform its timeout actions. This further reduces the risk of contention for the ST enqueue. The event setting to disable free extent coalescing by SMON is 10269.
event = "10269 trace name context forever"Even if you have not adopted a fixed extent size policy, this last event should be set in the parameter file of all but one instance of a parallel server database to prevent contention between the instances for space management resources. Similarly, event 10061 should be set in all but one instance of a parallel server database, to ensure that only that instance will check for stray temporary segments to clean up. By default, SMON performs this check once every 2 hours and 5 minutes.
Please note that if multiple event settings are included in the parameter file, as contemplated here, then no other parameter settings must be placed between them. Otherwise, only the last set of consecutive event settings will be effective.Another event setting that is sometimes used in connection with a fixed extent size policy is event 10268. This event prevents forward coalescing when freeing extents. Forward coalescing means that whenever an extent is being freed, Oracle checks to see whether there is already a free extent immediately following the extent about to be freed. If so, it deletes that row of the free extent table and inserts a new free extent row for the combined free space. When freeing multiple extents, Oracle frees them in reverse order to increase the chances of being able to perform forward coalescing.
Forward coalescing is not necessary under a fixed extent size policy, because the extents being freed are exactly the size that will be required when they are later reused. However, forward coalescing also reduces the risk of cluster block chaining in C_TS# and should therefore not normally be disabled.
Our only reservation in recommending a fixed extent size policy is that we have often seen it implemented badly. Firstly, a fixed extent size policy is no excuse for a proliferation of extents. Secondly, the fact that two segments have the same extent size requirements, does not necessarily mean that they should be placed in the same tablespace and reside on the same disks. Segments that have incompatible I/O patterns should reside on separate disks. If that means having multiple tablespaces with the same fixed extent size, so be it.
| Copyright © Ixora Pty Ltd |
|