Oracle programming Art Study Notes (23)-Section

 

Segments are the database objects that occupy the storage space. Each object that occupies the storage space is stored in a segment, such as tables, indexes, and rollback segments.

Segment type

1) Table segment

2) table partition or subpartition)

This type of segment is used for partitioning, which is similar to the table segment. A partitioned table consists of one or more partition segments. A combined partition table consists of one or more table partition segments (tablesubpartition segment.

3) cluster)

"Clustering" means that this segment physically aggregates relevant information. There are two types of clustering: B * tree clustering and hash clustering.

4) index segment)

5) index partition)

Similar to table partitions, this segment type contains an index. A partitioned index consists of one or more index partitions.

6) lob segment (lobsegment), lob index segment (lobindex), Lob partition segment (lob partition), and lob subpartition segment (lob subpartition)

Used to save large objects (large objects or LOB ).

7) nested table segment (nested table)

Specifies the segment type for the nested table.

8) rollback and Type2 undo segments

Undo data is stored here. Rollback segments are manually created by the DBA. The Type2 undo segment is automatically created and managed by Oracle.

 

When creating a table, a table segment is created. When creating a partition table, a partition is created for each partition. When an index is created, an index segment is created, and so on.

For example, a simple statement like create table t (x int primary key, y clob) creates four segments:

One is the TABLE segment of table t;

One is the index segment (this index is created to support the primary key );

There are also two CLOB segments (one is the LOB index and the other is the LOB data itself ).

 

After 11gRelease2, by default, segments are created after the first row of data is inserted. You can also use the following statement to create segments when creating a table.

Create table t (x int primary key, y clob) segment creation immediate;

 

Segment space management

Segment space management is an attribute inherited from the tablespace of a segment (and the segment never cross the tablespace. When using the create tablespace statement to CREATE a locally managed TABLESPACE, you can use the segment space management clause to set how the available/used SPACE in the SEGMENT is managed.

Tablespaces-tablespace http://www.bkjia.com/database/201110/109560.html

1) Manual Segment Space Management (MSSM)

Oracle uses the free list to manage the available space in the segment. The available block list records all data blocks that can be used to insert new data.

 

2) Automatic Segment Space Management (ASSM)

Oracle uses bitmap to manage the available space in a segment. [Note that the bitmap here is different from the bitmap used by the local tablespace] The bitmap here is used to describe whether each data block in the segment has enough available space to insert new data. As the available space in a data block changes, her status is also promptly reflected in the diagram. Oracle uses bitmap to manage the available space in a segment more automatically.

 

The storage parameters used for ASSM are as follows:

· BUFFER _ POOL

· PCTFREE

· INITRANS and MAXTRANS (-> http://www.bkjia.com/database/201110/108943.html)

You can also accept other parameters when creating a segment, but these parameters are ignored.

 

High Water Level line

There is a high-water mark (HWM) in the segment ). If you think of a segment as a series of blocks from bottom to top, HWM represents the top block that contains the data.

HWM is located in the first block of the newly created table. After data is inserted in this table, HWM increases. If we delete some (or even all) rows in the table, the HWM will not decrease. In this way, there may be many blocks that no longer contain data, but they are still under the HWM until the segment is rebuilt, truncated, or shrunk.

(Segment shrinkage is a new feature of Oracle 10 Gb. This feature is only supported when the segment is in an ASSM tablespace. The following statement is used:

Alter table t enable row movement; alter table t shrink space;

Segment shrinkage can be performed on a table)

 

Because Oracle scans all the blocks under the HWM during full scan, even if it does not contain any data, this will affect the performance of Full scan, especially when most of the blocks under the HWM are empty. Therefore, TRUNCATE can be used to delete all rows in the table.

 

In the ASSM tablespace, in addition to an HWM, there is also a low HWM. In MSSM, when HWM is pushed (for example, inserting rows. Segments are allocated in extent units. A segment contains many physical consecutive blocks. All blocks are formatted and valid immediately, oracle can securely read these blocks. However, for ASSM, when HWM is pushed, Oracle does not immediately format all blocks, and the formatting is completed only when records are inserted into these blocks for the first time, so as to safely read the data. For ASSM, data may be inserted into any block between high water level and low water level, so many blocks in this area will not be formatted. During full scanning, you need to know whether the read block is not formatted. To avoid checking each block in the segment, Oracle will directly read all blocks below the low level, for blocks between low water level and high water level, you need to refer to the block bitmap information of ASSM to view which blocks should be read and which ones should be ignored.

 

FREELIST

When using MSSM, Oracle maintains the following HWM in FREELIST for data insertion. If multiple concurrent users are expected to execute a large number of INSERT or UPDATE activities on an object, multiple FREELIST can be configured, which is good for performance improvement.

 

PCTFREE and PCTUSED

 

The PCTFREE parameter is used to tell Oracle how much space should be reserved on the block to complete future updates. This value is 10% by default. If the percentage of unused space is higher than the value specified in PCTFREE, this block is regarded as "available" and can be used to insert data.

PCTUSED informs Oracle of the amount of unused space on a block that has become "unavailable" (space is released due to deletion or update) to make it "available" again. The default value is 40%.

 

When MSSM is used, these two parameters control when the block is placed in FREELIST and when it is retrieved from FREELIST. If you use the default value: PCTFREE is 10 and PCTUSED is 40, the block will remain in FREELIST until the block reaches 90% (more than 10% free space ).

. Once it reaches 90% full, it will be taken from FREELIST, and will not return to FREELIST until the free space on the block exceeds 60% of the block.

When ASSM is used, PCTFREE still limits whether a new row can be inserted into a block, but PCTUSED will be ignored.

 

If the PCTFREE setting is too large, it will waste space. If the setting is too small, updating the row will cause row migration.

Row migration means that a row is too large to fit in the block. Oracle migrates this row of data (migrate) to the new data block, save a pointer to the new data block in the original location of the migrated data row. The rowid of the migrated data row remains unchanged. Row migration affects performance. You must execute more I/O operations to obtain row data.