Shulou(Shulou.com)05/31 Report--

This article mainly introduces the meaning of tablespaces, segments, areas and blocks in the database. It is very detailed and has a certain reference value. Interested friends must finish reading it!

1. Data blocks (Block)

Brief introduction

Block Block is the smallest unit in which Oracle stores data information. Note that this is the smallest unit in an Oracle environment. Oracle shields the differences in storage structures of different operating systems through data blocks. Whether it's their storage structure and way, or even the way the characters are arranged.

All Oracle operations and space allocation on data are actually operations on block Block. We search for a row from the data table, and in practice, Oracle reads the data block of the row from the memory buffer (or hard disk) and returns the specified data row on the data block. Oracle whether in the buffer or on the hard disk, although the small unit of data manipulation is the data block.

Parameters.

1 、 db_block_size

Data blocks are of size and are set by parameters when a database is established. The size of data blocks can support multiple data blocks in a database, but it generally does not make much sense and will bring some burden to management and debugging.

The size of the data block is specified by the number of kb bytes, and the default is 8KB. The related parameter is db_block_size.

The size of the block is set according to different types of systems. If the block setting is large, more rows are read at a time, which consumes more memory for SGA, and more swapping in and out may be triggered by a particular query. If the setting is too small, frequent IO logical physical reads can also cause performance problems.

2 、 db_file_multiblock_read_count

Represents the number of blocks read from physical storage at a time. For some data mining systems, you can consider adjusting this parameter a little larger.

II. District extent

Brief introduction

Area extent is a storage structure that is one level larger than data blocks and represents a series of consecutive sets of data blocks.

When storing data information, Oracle will allocate data blocks for storage, but there is no guarantee that all allocated data blocks are contiguous. Therefore, there is the concept of partition extent, which represents a series of contiguous sets of blocks.

View

View dba_extents (or all_extents, user_extents) is an important means for us to study partition structure and storage composition.

1. The partition is segment-specific. The data segment segment is the upper organizational unit of the partition, a database object corresponds to a segement, and the database object belongs to different schema (owner). Therefore, through different data segment names, different owner, and even different tablespace tablespace information, we can locate the information description of the data area extent.

2. The other part of the information is about the allocation of extent in this area, such as the file number, the starting block block number and the number of data blocks.

3. Data segment segment

Brief introduction

A data segment corresponds to a database object, and generally a database object corresponds to a data segment. Multiple extent correspond to a data segment, and each data segment is actually a representative of an object in the database.

View

From the dba_segments view, you can see the structure of the data segment more clearly.

1. From the comment information of the segment_type column, we can see that there are various types of data segments. Data tables, indexes, rollbacks, and aggregations are all forms of data segments. At the same time, the data segment has been created when the data object is created, and with the increase of the size of the object, multiple extents are constantly allocated for management.

2. Another part of the information that can be read from the dba_segments is the size of the space allocated by the data object and the number of data blocks and partitions. Using this view, you can easily get all the object sizes of the specified schema.

SELECT OWNER

SUM (BYTES) / 1024 / 1024 AS VOL

SUM (BLOCKS) AS TOTALBLOCKS

SUM (EXTENTS) AS TOTALEXTENTS

FROM DBA_SEGMENTS

GROUP BY OWNER

HAVING OWNER = 'SYS'

A data object corresponds to a segment. However, when partitioning a table, a partition corresponds to a segment object. In addition, segment objects can be specified to be stored in which tablespace, which is the basis for implementing storage partitioning. Different types of segment partitions are established in different tablespaces so that they can be stored in different files and finally distributed in different physical storage.

4. Tablespace tablespace

Brief introduction

TableSpace is the highest-level structure in the storage structure. When you create a tablespace, you need to specify the file to store. A tablespace can specify multiple data files, and multiple files can be on different physical storage. One thing, however, is that the storage of object data segments at the next level in the table space cannot be specified in that file. Therefore, if you want data objects to access IO load balancing, you need to specify that different data objects are in different table spaces. This is why data tables and indexes are built in different table spaces.

View

The tablespace is accessed through v$tablespace.

Data files are accessed through v$datafile

The above is all the contents of the article "what are table spaces, segments, blocks and blocks in the database?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.