Shulou(Shulou.com)06/01 Report--

Extents in oracle

Properties:

1: an extent consists of multiple connected blocks, and multiple extents form a segment.

When 2:oracle allocates space for segment, it is in extent

So the benefits of extents:

1: the efficiency of space allocation and release will be improved

Oracle recommends that the number of extents in a segment should not exceed 1024. For a big table, it may contain tens of millions of block. If there is no extent, then the oracle needs to allocate tens of millions of block, while with extent, it only needs to allocate thousands of extents. Extent says that a large number of blocks is very efficient. Extent does not list all the block, but lists the first two block (because the extent is related, the initial block can locate an extent). The time that the oracle allocates space is proportional to the number of extents and has nothing to do with the number of blocks. Similarly, extent can also improve the efficiency of operations when freeing up space.

For the tablespace managed by the dictionary, the effect of extent is very obvious. If there is no extent, each block must be recorded in the data dictionary. If the big table occupies tens of millions of block, then the data dictionary will also be heavily occupied. It is assumed that the data dictionary will occupy millions of blocks. When allocating or releasing these big table, it will add a lot of extra work to insert and delete these millions of blocks.

2: full table scan

When scanning a full table, using the feature that extent contains contact blocks, multiple fields can be read at one time, which can greatly reduce the physical IO and greatly improve the efficiency of scanning.

The negative effects of extent:

Tablespace debris:

If no extent,oracle allocates space to segment in block units, and block is always the same size, typical 8k block 16k, then each block can be utilized. With extent,extent is the smallest allocation unit, and the size of extent is not always equal. Suppose the first three segment are very small, and the extent is not large, for example, 10 blocks. If the second segment is released, the 10block in the middle will be free, but if later segment requires the allocation of at least 20 block extent, then the free space of the 10blocks will not be utilized. In extreme cases, the table space will be free by 1000 10block. However, the extent for 20block cannot be assigned. Exp/imp can eliminate fragmentation, and locally managed tablespaces have the ability to automatically handle contiguous fragments, but not discontiguous fragments.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.