Oracle space layer

最新推荐文章于 2023-03-25 15:20:54 发布

m_linda

最新推荐文章于 2023-03-25 15:20:54 发布

阅读量432

点赞数

本文链接：https://blog.csdn.net/m_linda/article/details/38925773

版权

Tablespace Management

1. Tablespace types

Permanent tablespaces --- These tablespaces store objects in segments that are permanent – that persist beyond the duration of a session or transaction.

Undo tablespaces --- These tablespaces store segments that may be retained beyond a transaction, but are basically used to:
- Provide read consistency for SELECT statements that access tables that have rows that are in the process of being modified.
- Provide the ability to rollback a transaction that fails to commit.
Temp tablespaces --- This tablespace stores segments that are transient and only exist for the duration of a session or a transaction. Mostly, a temporary tablespace stores rows for sort and join operations.

2. Tablespace management

Data-dictionary management (DMT) --- you will NOT be able to create any tablespaces of this type in 11g.
Local Mnagement (LMT)

The extents allocated to a locally managed tablespace are managed through the use of bitmaps.

Each bit corresponds to a block or group of blocks (an extent).
The bitmap value (on or off) corresponds to whether or not an extent is allocated or free for reuse.
Reduced contention on data dictionary tables
No undo generated when space allocation or deallocation occurs
No coalescing required
Local management is the default for the SYSTEM tablespace beginning with Oracle 10g.
When the SYSTEM tablespace is locally managed, the other tablespaces in the database must also be either locally managed or read-only.
With the LOCAL option, you cannot specify any DEFAULT STORAGE, MINIMUM EXTENT, or TEMPORARY clauses.

Advatntages: Basically all of these advantages lead to improved system performance in terms of response time, particularly the elimination of the need to coalesce free extents.

Local management avoids recursive space management operations. This can occur in dictionary managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in an undo segment or data dictionary table.
Because locally managed tablespaces do not record free space in data dictionary tables, they reduce contention on these tables.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
The sizes of extents that are managed locally can be determined automatically by the system.
Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

3. Tablespace extend

Adding a datafile to the Tablespace
Resize the datafile size
Change the increasing method of the datafile

Segment Management

1. Segment management

Manual management --- This setting uses free lists to manage free space within segments.
- Free lists are lists of data blocks that have space available for inserting rows.
- You must specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.
- MANUAL is usually NOT a good choice.
Automatic management --- This uses bitmaps to manage free space within segments.
- This is the default.
- A bitmap describes the status of each data block within a segment with regard to the data block's ability to have additional rows inserted.
- Specify automatic segment-space management only for permanent, locally managed tablespaces.
- Automatic generally delivers better space utilization than manual, and it is self-tuning.

2. Block migration & chaining

Block migration --- Migrated rows occur when an UPDATE DML causes the rows to expand onto another data block. This can be avoided by setting PCTFREE to a large enough value to accommodate row expansion, and existing migrated rows can be fixed by reorganizing the tables with the dbms_redefinition utility.

Block chaining --- A chained rows is a LOB row (usually a BLOB, CLOB, RAW or LONG RAW) where the row length is large than the data block size. Many shops will create a 32k blocksize to store large columns without row chaining.

Table Management

1. Important parameters

INITTRANS, MAXTRANS, PCTFREE, PCTUSED, INITIAL, NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENS

2. Table cross different datafiles

Alter table tablename allocate extent ( size **M datafile **);

3. Release the space of table

Alter table tablename deallocate unused;

Difference between delete and truncate:

HWM (High Water Mark) --- To manage space, Oracle Database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used.
Delete (DML operation) --- The DELETE command is used to remove rows from a table.After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. HWM is not changed.
Truncate (DDL operation) --- TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. And HWM is reset.'
'

4. Table Compression

Basic compression
Advance compression

5. Some update operation

Index management

When oracle operation try to delete indexes, the records are not really be deleted,database only mark it status as delete, but the space used by indexes are still focus, so it need to rebuild or coalesce.