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

In the process of Oracle learning, storage structure and table segment blocks may be the concepts involved by every beginner. Tablespaces, segments, partitions, and data blocks represent different levels and structures of data storage in Oracle. A clear understanding of these structures will not only help us to lay a solid foundation for learning, but also play an important role in understanding the working principle of Oracle.

This paper introduces four structures from small to large and gradually expands, and compares them with Schema and data files.

Data block (Block)

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 is the Windows environment or the Unix/Linux environment, their operating system storage structure and mode, and even the way characters are arranged are different. Oracle uses data blocks to shield these differences, and all data operations use operations on Oracle blocks, which is equivalent to a level of abstraction.

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.

Data blocks are of size and are set by parameters when a database is established. Note that among the Oracle database parameters, only block size parameters cannot be modified after the database is built. 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 relevant parameter is db_block_size. Here is the statement to check the size of the block.

SQL > show parameter db_block_size

NAME TYPE VALUE

-

Db_block_size integer 8192 / / 1024 × 8

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.

Another parameter related to blocks is db_file_multiblock_read_count, which 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.

Next, let's look at a unit higher than block, area extent.

Zone extent

Area extent is a storage structure that is one level larger than data blocks and represents a series of consecutive sets of data blocks. We know that physical storage is usually a random read and write process. Even in the same file, we cannot guarantee that the same information is stored in absolutely continuous physical storage space. The same is true for Oracle data stores.

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 dba_extents (or all_extents, user_extents) is an important means for us to study partition structure and storage composition.

SQL > desc dba_extents

Name Type Nullable Default Comments

OWNER VARCHAR2 (30) Y Owner of the segment associated with the extent

SEGMENT_NAME VARCHAR2 (81) Y Name of the segment associated with the extent

PARTITION_NAME VARCHAR2 (30) Y Partition/Subpartition Name, if any, of the segment

SEGMENT_TYPE VARCHAR2 (18) Y Type of the segment

TABLESPACE_NAME VARCHAR2 (30) Y Name of the tablespace containing the extent

EXTENT_ID NUMBER Y Extent number in the segment

FILE_ID NUMBER Y Name of the file containing the extent

BLOCK_ID NUMBER Y Starting block number of the extent

BYTES NUMBER Y Size of the extent in bytes

BLOCKS NUMBER Y Size of the extent in ORACLE blocks

RELATIVE_FNO NUMBER Y Relative number of the file containing the segment header

From the view, we can clearly see several characteristics of the partition.

First of all, 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.

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 blocks.

Segment segment

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. From the dba_segments view, you can see the structure of the data segment more clearly.

SQL > desc dba_segments

Name Type Nullable Default Comments

--

OWNER VARCHAR2 (30) Y Username of the segment owner

SEGMENT_NAME VARCHAR2 (81) Y Name, if any, of the segment

PARTITION_NAME VARCHAR2 (30) Y Partition/Subpartition Name, if any, of the segment

SEGMENT_TYPE VARCHAR2 (18) Y Type of segment: "TABLE", "CLUSTER", "INDEX", "ROLLBACK"

"DEFERRED ROLLBACK", "TEMPORARY", "SPACE HEADER", "TYPE2 UNDO" or "CACHE"

TABLESPACE_NAME VARCHAR2 (30) Y Name of the tablespace containing the segment

HEADER_FILE NUMBER Y ID of the file containing the segment header

HEADER_BLOCK NUMBER Y ID of the block containing the segment header

BYTES NUMBER Y Size, in bytes, of the segment

BLOCKS NUMBER Y Size, in Oracle blocks, of the segment

EXTENTS NUMBER Y Number of extents allocated to the segment

INITIAL_EXTENT NUMBER Y Size, in bytes, of the initial extent of the segment

NEXT_EXTENT NUMBER Y Size, in bytes, of the next extent to be allocated to the segment

MIN_EXTENTS NUMBER Y Minimum number of extents allowed in the segment

MAX_EXTENTS NUMBER Y Maximum number of extents allowed in the segment

PCT_INCREASE NUMBER Y Percent by which to increase the size of the next extent to be allocated

FREELISTS NUMBER Y Number of process freelists allocated in this segment

FREELIST_GROUPS NUMBER Y Number of freelist groups allocated in this segment

RELATIVE_FNO NUMBER Y Relative number of the file containing the segment header

BUFFER_POOL VARCHAR2 (7) Y The default buffer pool to be used for segments blocks

From the comment information in the segment_type column, you can see that there are various types of data segments. Any kind of database object is essentially a kind of data segment. 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.

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.

SQL > select owner,sum (bytes) / 1024 plus 1024 as vol, sum (blocks) as totalblocks,sum (extents) as totalextents from dba_segments group by owner having wner='SYS'

OWNER VOL TOTALBLOCKS TOTALEXTENTS

SYS 585.5 74944 3248

The above query shows that the schema of SYS occupies 585.5MB, including 74944 data blocks and 3248 partitions.

After an object is created, a partition extent and eight blocks block are allocated at the segment level.

One problem to note is that usually our data segments are related to data objects. 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.

Partitioning is actually the possibility of separate storage. Generally speaking, an object will not be stored across physical storage, and partition tables are corresponding to multiple segment. Therefore, it is possible for partitioned tables to store space separately.

Tablespace tablespace

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. That is, tablespaces can be stored across 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.

The tablespace is accessed through v$tablespace.

SQL > desc v$tablespace

Name Type Nullable Default Comments

TS# NUMBER Y

NAME VARCHAR2 (30) Y

INCLUDED_IN_DATABASE_BACKUP VARCHAR2 (3) Y

BIGFILE VARCHAR2 (3) Y

FLASHBACK_ON VARCHAR2 (3) Y

ENCRYPT_IN_BACKUP VARCHAR2 (3) Y

Compared to the previous structural view, the structure of the tablespace view is much simpler and is just some descriptive information. Two of these parameters need to be noted.

One is bigfile, which is a flag bit that marks whether the tablespace is called a large file tablespace. Large file tablespace is a new feature introduced in 10g. For performance considerations, you can set the tablespace to large file tablespace to store more than 100 T of data, but only one data file is required. The other is flashback_on, which indicates whether the flashback feature of the tablespace is turned on.

Note that the conceptual set of data table segment blocks can easily be confused with the structure of schema. Schema is an organizational concept, which comes from the category of classical database theory. In oracle, Schema is an organizational concept, and a user corresponds to a schema. Schema is a collection and organization of logical objects, which is not the same level as tablespaces.

In a schema, objects can be established in any data tablespace, with only one default tablespace concept default tablespace. Specifying the default tablespace is specified when the user is created.

By distinguishing several core concepts and their different relationships, we can better understand the various mechanisms of Oracle operation.

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.