Detailed Oracle Local management table space __oracle

1 Terminology Interpretation and agreement

Tablespace (tablespace): Provides a logical structure for the use of space for a database, and its corresponding physical structure is a data file, and a table space can contain multiple data files.

Local Management table space (locally Managed tablespace LMT): A new table space management model that appears after 8i, using a local bitmap to manage the space usage of the tablespace.

Dictionary Management table Space (dictionary-managed tablespace, DMT): 8i previously included a table space management model that could be used in the future, managing the space use of tablespaces through a data dictionary.

Segment (Segment): A logical structure of a database, such as a table segment, an index segment, a rollback segment, etc., Gencun in a table space and corresponding to a certain amount of storage space.

Interval, can be referred to as area (Extent): The storage of segments can be divided into one or more intervals, each interval occupies a certain number of blocks (block), in the local management of the table space, the table space Extent on the corresponding segment of the Extent.

Block: The smallest storage unit of a database, in this article, the size of blocks is agreed to be 8192 bytes.

Bit: The space management unit that manages the table space locally, one bit may be equal to an interval, or multiple bits may form an interval.

2 Local Admin table space

2.1 Grammar

In the oracle8i version, Oracle has introduced a new way of tablespace management: a localized managed table space. Localized management means that Oracle no longer uses the data dictionary table to record the usage of the area inside the Oracle tablespace, but instead adds a bitmap area to the header of each table space's data file, where it records the usage of each area. Each time a zone is used, or released for reuse, Oracle updates the record in the header of the data file, reflecting the change.

The process of creating a localized managed table space:

Syntax: CREATE tablespace table space name datafile ' data file Details ' [EXTENT MANAGEMENT {local {autoallocate | Uniform [SIZE inteter [k| M]]}}

Keyword Extent MANAGEMENT local Specifies that this is a localized managed table space. For system tablespace, you can specify extent Mangement local only when you create the database because it is the first tablespace that was created when the database was built.

In 8i, dictionary management is still the default way of managing, and when the local keyword is selected, it indicates that it is a locally managed table space. Of course, you can continue to choose a more detailed management style: is Autoallocate or uniform. In the case of Autoallocate, Oracle is used to determine the use of the block, and if uniform is selected, the size of each chunk can be specified in detail, and 1M size is used for each area if unspecified.

2.2 Local Management benefits

1. Localized management of tablespaces avoids recursive space management operations. This situation in the data dictionary management of the table space is frequent, when the use of the area in the table space changes, the data dictionary table information changed, and also used in the system table space in the rollback segment.

2. Localized management of the table space to avoid the corresponding table in the data dictionary to write free space, the use of space information, thereby reducing the competition in the Data dictionary table, improve the concurrency of space management

3. The localization management of the zone automatically tracks the free blocks in the table space, reducing the need for manual merging of free space.

4. The size of the area in the tablespace can be determined by the Oracle system, or by the database administrator specifying a uniform size, avoiding the fragmentation problem that has been a headache for the dictionary table space.

5. Manage the idle block from the data dictionary to manage the idle block by the header record of the data file, so as not to generate rollback information and no longer use the rollback segment in the system tablespace. Because it is managed by a data dictionary, it will record the relevant information in the data dictionary, resulting in rollback information.

Because of the above characteristics of this tablespace, it supports more concurrent operations within a table space and reduces reliance on data dictionaries.

3 Local Management table space management mechanism

A table space is a logical structure that provides space for segments (tables, indexes, etc.), so when you add to a table space and delete segments, the database must track the use of those spaces.

As shown in the following example, suppose a newly created table space contains five tables

Table I. ... Table Two ... Table Three ... Table four ... Table Five ... Unused space

When we delete table four, we have the following results

Table I. ... Table Two ... Table Three ... Free Space segment ... Table Five ... Unused space

Obviously, Oracle needs to have a mechanism to manage these allocated or unallocated space for each data file in the tablespace, in order to keep track of the available space (both unallocated and reusable), we must know for each space:

1, this free space is located in what data file

2, the size of this space is how big

3, if it is in use, which segment occupies this space

Until 8i, all tablespaces were in dictionary management mode, and in order to ensure that the above information was saved, Oracle used two data dictionary tables: uet$ (used intervals) or fet$ (free space):

Sql> desc uet$

Name Type Nullable Default Comments

--------- ------ -------- ------- --------

segfile# number

segblock# number

ext# number

ts# number

file# number

block# number

LENGTH number

Sql> desc fet$

Name Type Nullable Default Comments

------ ------ -------- ------- --------

ts# number

file# number

block# number

LENGTH number

Query the table to see that each use space or free space (not necessarily a extent, can be multiple extent) corresponds to a row in the table. The way it works is when a segment is deleted, Oracle moves the corresponding line in the uet$ to fet$, the process is continuous, and can happen to wait. When concurrency is high, the contention for the data dictionary comes. Another problem is that when the table space is very discontinuous or the table space has a large number of fragments causing the two tables to increase, it will also cause the database performance degradation.

The Local management table space is to solve this problem, in the space management of Tablespace, Oracle saves the information in the bitmap of the head of the table space, instead of saving it in the data dictionary. In this way, when allocating reclaimed space, the table space can be independently completed and not associated with other objects.

Let's go inside the local admin table space and see how Oracle does it.

4 Local Management table space in uniform mode

4.1 When uniform size value is too small

sql> Create tablespace demo32 datafile '/oradata/ltest/demo32.dbf ' size 10m extent management local uniform size 32k;

Ora-03249:uniform size for auto segment spaces managed tablespace should have at least 5 blocks

Note: My experimental environment block is 8k, so uniform size is at least 40k.

4.2 When the initial in the storage parameter is empty

sql> Create tablespace demo64 datafile '/oradata/ltest/demo64.dbf ' size 10m extent management local uniform size 64k;

Tablespace created

Sql> Select a.initial_extent/1024 "init_extent (K)",

2 a.next_extent/1024 "next_extent (K)"

3 from User_tablespaces A

4 Where a.tablespace_name = ' DEMO64 ';

Init_extent (k) next_extent (k)

-------------- --------------

64 64

Sql> CREATE table demotab64 (x number) tablespace demo64;

Table created

Sql> Select A.table_name,

2 a.initial_extent/1024 "init_extent (K)",

3 a.next_extent/1024 "next_extent (K)"

4 from User_tables A

5 Where a.table_name = ' DEMOTAB64 ';

table_name Init_extent (k) next_extent (k)

-------------------------- -------------- --------------

DEMOTAB64 64 64

Note: When there is no storage parameter initial when you build the table, the initialization area and the next area are 64k in size, the same size as uniform.

Sql> Select a.bytes/1024 "init_extent (K)" from user_extents a where a.segment_name = ' DEMOTAB64 ' and a.extent_id = 0;

Init_extent (K)

--------------

64

Sql> Select COUNT (*) from user_extents where segment_name = ' DEMOTAB64 ';

COUNT (*)

----------

1

Note: In this section, a zone is generated.

4.3 When initial < uniform size

Sql> CREATE table Demotab64_1 (x number) tablespace demo64 storage (initial 1K next 5k);

Table created

Sql> Select A.table_name,

2 a.initial_extent/1024 "init_extent (K)",

3 a.next_extent/1024 "next_extent (K)"

4 from User_tables A

5 Where a.table_name = ' demotab64_1 ';

table_name Init_extent (k) next_extent (k)

--------------------------- -------------- --------------

Demotab64_1 16 64

Note: At this point the init_extent is 16, not 1 of the initial parameter.

Sql> Select a.bytes/1024 "init_extent (K)" from user_extents a where a.segment_name = ' demotab64_1 ' and a.extent_id = 0;

Init_extent (K)

--------------

64

Sql> Select COUNT (*) from user_extents where segment_name = ' demotab64_1 ';

COUNT (*)

----------

1

4.4 When initial > Uniform size

Sql> CREATE table demotab64_200 (x number) tablespace demo64 storage (initial 200K next 20k);

Table created

Sql> Select A.table_name,

2 a.initial_extent/1024 "init_extent (K)",

3 a.next_extent/1024 "next_extent (K)"

4 from User_tables A

5 Where a.table_name = ' demotab64_200 ';

table_name Init_extent (k) next_extent (k)

-------------------------- -------------- --------------

DEMOTAB64_200 200 64

Note: When initial > Uniform size, the size of the initialization area initial.

Sql> Select a.bytes/1024 "init_extent (K)" from user_extents a where a.segment_name = ' demotab64_200 ' and a.extent_id = 0;

Init_extent (K)

--------------

64

Sql> Select COUNT (*) from user_extents where segment_name = ' demotab64_200 ';

COUNT (*)

----------

4

Note: The allocated area is not one at this time, it is 4. In this case initial has a role to play. Number of allocation areas is: rounding (initial/uniform size) + 1

Conclusion: When uniform size, the size of each region of the segment is uniform size, regardless of the number of initial.

5 Local Management table space in Autoallocate mode

5.1 When the initial in the storage parameter is empty

sql> Create tablespace Demoa datafile '/oradata/ltest/demoa.dbf ' size 10m extent management local autoallocate;

Tablespace created

Sql> Select a.initial_extent/1024 "init_extent (K)",

2 a.next_extent/1024 "next_extent (K)"

3 from User_tablespaces A

4 Where a.tablespace_name = ' Demoa ';

Init_extent (k) next_extent (k)

-------------- --------------

64

Sql> CREATE table Demoatab (x number) tablespace Demoa;

Table created

Sql> Select A.table_name,

2 a.initial_extent/1024 "init_extent (K)",

3 a.next_extent/1024 "next_extent (K)"

4 from User_tables A

5 Where a.table_name = ' demoatab ';

table_name Init_extent (k) next_extent (k)

------------------------- -------------- --------------

Demoatab 64

Sql> Select COUNT (*) from user_extents where segment_name = ' demoatab ';

COUNT (*)

----------

1

Since then, increasing data can be found:

Sql> Select A.segment_name, A.bytes, a.blocks from user_extents a where a.segment_name = ' demoatab ';

Segment_na BYTES BLOCKS

---------- ---------- ----------

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 65536 8

Demoatab 1048576 128

Demoatab 1048576 128

Demoatab 1048576 128

Demoatab 1048576 128

Demoatab 1048576 128

Demoatab 1048576 128

Demoatab 1048576 128

Selected rows

When automatically allocated, it is found that the first section allocates 8 blocks (64K), starts with 17, and allocates 128 blocks per zone (size 1M). I've done experiments. When the initial is large enough, the size of the first area is not necessarily 64K, it can be 1M, 8M, 64M, or even 256M.

5.2 When initial < uniform size

Sql> CREATE table Demoatab_1 (x number) tablespace Demoa storage (initial 1K next 5k);

Table created

Sql> Select A.table_name,

2 a.initial_extent/1024 "init_extent (K)",

3 a.next_extent/1024 "next_extent (K)"

4 from User_tables A

5 Where a.table_name = ' demoatab_1 ';

table_name Init_extent (k) next_extent (k)

-------------------------- -------------- --------------

Demoatab_1 16

Sql> Select a.bytes/1024 "init_extent (K)" from user_extents a where a.segment_name = ' demoatab_1 ' and a.extent_id = 0 ;

Init_extent (K)

--------------

64

Sql> Select COUNT (*) from user_extents where segment_name = ' demoatab_1 ';

COUNT (*)

----------

1

5.3 When initial > Uniform size

Sql> CREATE table demoatab_200 (x number) tablespace Demoa storage (initial 200K next 5k);

Table created

Sql> Select A.table_name,

2 a.initial_extent/1024 "init_extent (K)",

3 a.next_extent/1024 "next_extent (K)"

4 from User_tables A

5 Where a.table_name = ' demoatab_200 ';

table_name Init_extent (k) next_extent (k)

--------------------------- -------------- --------------

DEMOATAB_200 200

Sql> Select a.bytes/1024 "init_extent (K)" from user_extents a where a.segment_name = ' demoatab_1 ' and a.extent_id = 0 ;

Init_extent (K)

--------------

64

Sql> Select COUNT (*) from user_extents where segment_name = ' demoatab_200 ';

COUNT (*)

----------

4

Conclusion: 1. Oracle makes all extent in the local management table space the same size through mandatory means, although different storage parameters may be customized. 2. In an automatically allocated locally managed tablespace, the interval size may consist of 64K, 1M, 8M, 64M, or even 256M, in the following dimensions. But no matter how big, there is a general size of 64k, so 64K is the bit size of the table space.

6 Check uet$ and fet$ whether there is data

Sql> Select file#, name from V$datafile;

file# NAME----------------------------------------

1/oradata/ltest/datafile/o1_mf_system_79q5214w_.dbf

2/oradata/ltest/datafile/o1_mf_undotbs1_79q521ct_.dbf

3/oradata/ltest/datafile/o1_mf_sysaux_79q52169_.dbf

4/oradata/ltest/datafile/o1_mf_users_79q521do_.dbf

5/oradata/ltest/datafile/o1_mf_example_79q55jt4_.dbf

6/oradata/ltest/datafile/o1_mf_bigtbs_7ct5vw4x_.dbf

12/oradata/ltest/demo64.dbf

13/oradata/ltest/demo1024.dbf

14/oradata/ltest/demoa.dbf

9 Rows selected

You can check uet$ and fet$.

Sql> Select COUNT (*) from uet$ where file# = 12;

COUNT (*)

----------

0

Sql> Select COUNT (*) from fet$ where file# = 12;

COUNT (*)

----------

0

A locally managed table space with no data in either view. The following is the information of the dump block to further analyze the characteristics of the local management table space.

Bitmap information in 7 Dump data file (3rd block to 8th block)

7.1 Dump Third block