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

I. ORACLE tablespace management

ORACLE provides usage space for the database through tablespaces. Because extent is the smallest allocation unit when ORACLE creates objects, the management of tablespaces is actually aimed at interval management.

ORACLE tablespaces are managed in the following ways:

1.1 Dictionary Management method (DMT):

Use the data dictionary to manage the allocation of storage space. When the table space allocates new areas or reclaims the allocated regions, ORACLE will query and update the tables corresponding to the data dictionary, and use single thread, which is slow and produces fallback and redo information.

1.2 Local management method (LMT):

The management information of zone allocation and zone collection in the table space is stored in the data file in the table space, which has nothing to do with the data dictionary. The tablespace maintains a bitmap structure for each data file to record the extent allocation of the tablespace. When the tablespace allocates new extents or reclaims the allocated extents, ORACLE updates the bitmaps in the file, so no rollback and redo information is generated.

Advantages: a) improve the speed and concurrency of storage management. B) No disk fragments are generated. C) there is no recursive management. D) there is no system rollback segment.

II. ORACLE segment management

ORACLE allocates space to object segments in areas, while space is used and managed in segments in units of BLOCK.

The management of ORACLE section is divided into:

2.1 Manual Segment Space Management (Manual Segment Space Management):

Manually set FressLists, FreeList Groups, PctUsed, and other parameters to control how segment space is allocated, used, and reused. MSSM is supported in many versions.

2.2 automatic Segment Space Management (Automatic Segment Space Management)

Only the PctFree related to space usage needs to be controlled, and other parameters are ignored. ASSM was introduced only after the 9i version to reduce the management of too many parameters in MSSM.

Only Buffer_Pool, PctFree, IniTrans and MaxTrans are applied to the ASSM segment (this parameter will be ignored in the version after 10g). Other storage and physical attribute parameters are not applicable to the ASSM segment.

2.3 HWM High level

HWM increases with the growth of table data, and HWM decreases only when objects are rebuilt, truncated, and shrunk. HWM is important because all blocks under HWM are scanned when ORACLE performs a full table scan, even if the database table does not contain any data.

TRUNCATE resets the HWM of the table to 0 and truncates the relevant indexes on the table, while DELETE does not modify the HWM throughout the table, so if you plan to delete all rows of the table, you should try to use TRUNCATE.

In the MSSM tablespace, the segment has only one HWM. But in the ASSM tablespace, in addition to HWM, there is also a low HWM. This is because in ASSM, when HWM advances, ORACLE does not format all physical blocks immediately, but only when it is used for the first time for safe reading (there is some doubt here).

2.4 freelists

Freelists is when using MSSM tablespaces, when ORACLE uses blocks, you need to put the blocks in freelist or remove them from fresslist, and fresslists may have a great improvement or impact on performance.

In ASSM, there is no and no need to set the freelist parameter.

Create tablespace assm datafile size 10m autoextend on next 1m segment space management auto

Create table xxx tablespace assm

2.5 pctfree and pctused (also questionable)

PctFree tells ORACLE how much space should be reserved on the block to complete future updates. The default value is 10%. If the percentage of free space is higher than the value specified by PctFree, the block is called free.

PctUsed tells ORACLE what percentage of free space on a currently non-free block needs to be before it becomes free again, with a default value of 40%.

When using MSSM, these two parameter settings control when the block is placed in the freelist and when it is taken out of the freelist. If you use the default value, PctFree is 10, PctUsed is 40, then the block will remain on the freelist until it reaches 90% (there is more than 10% free space). Once it reaches 90%, it will be taken out of the freelist and will not return to the freelist until the free space on the block exceeds 60% of the block, which has not been on the freelist until then.

When using ASSM, PctFree still limits whether a new row can be inserted into a block, but it does not control whether a block is on freelist, because ASSM does not use freelist at all. In ASSM, the PCTUSED parameter is ignored.

If the PctFree setting is too small, updating rows will result in row migration.

III. Management of ORACLE area

The management of the ORACLE area is divided into:

3.1 automatic allocation

ORACLE allocates space according to the incremental algorithm. Can easily cause disk fragments.

3.2 uniform size

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.