Oracle Logical Structure

The logical structure of Oracle is a hierarchical structure. It consists of the following concepts: tablespace, segment, partition, and data block. The logical structure is user-oriented. The logical structure is used to develop applications using Oracle. The hierarchical structure of database storage and its components. Structural Objects also form different levels of granularity relationships from data blocks to tablespaces ,.

Figure Oracle 10 Gb database hierarchy

Relationship between the Image Segment, partition, and Data Block 1. Data Block Oracle Data Block is a group of Continuous Operating System blocks. The size of the allocated database block is set when the Oracle database is created, and the data block is the basic unit for Oracle read/write. The size of a data block is generally an integer multiple of the size of the operating system block, which can avoid unnecessary system I/O operations. From Oracle9i, the data block sizes of different tablespaces in the same database can be different. Data blocks are the most basic storage units of Oracle, while tablespaces, segments, and ranges are members of logical organizations. Each block in the database buffer is a data block. A data block cannot span multiple files. The data block structure mainly includes: www.2cto.com title: including general block information, such as block address and segment type. Table Directory: contains the row information about the table in the data block. Oracle architecture Chapter 1 row Directory: contains information about the row address in the data block. Row data: including table or index data. A row can span multiple data blocks. Free Space: allocate free space to insert new rows and update rows that require additional space. The space management parameter pctfree can be used to control the use of free space. Free space management can be automatic or manual. In data operations, there are two types of statements that can increase the free space of the database block: one is the Delete statement, and the other is the Update to Update the existing row. The released idle space can be used for the insert statement. If the insert statement is in the same transaction as the statement that generates the idle space, and then runs it, the insert statement can directly use the generated free space. If the insert statement is in a transaction that is separated from the statement that generates free space, the insert statement can be used after other transactions are committed and when it requires space, use the previously generated free space. The space released in the data block may be continuous or discontinuous. Oracle merges the free space of a data block only when the following conditions occur: When an insert or update statement uses a data block, the free space of the data block is sufficient to store a new row, in addition, the free space is fragmented, and the continuous space in the data block cannot be inserted into a row. In addition, Oracle also needs to compress the data block space when the system performance declines. There are two parameters that can affect the free space: pctfree and pctused. For manually managed tablespaces, you can use two space management parameters pctfree and pctused to control the use of idle space by insert and update. You can specify these two parameters when creating or modifying a table. You can specify the pctfree parameter when creating or modifying an index with its own index segment. Www.2cto.com
The pctfree parameter is the minimum percentage of free space reserved for updates to the block row. The default value is 10. For example, if pctfree is set to 20 in the Create table statement, 20% of each data block in the Data Segment of the table is used as available free space, used to update data rows that already exist in a data block. The remaining 80% is used to insert a new data row until it reaches 80%. Obviously, the smaller the pctfree value, the less space reserved for the existing row update. Therefore, if pctfree is set too high, it will increase I/O during full table scan, wasting disk space. If pctfree is set too low, it will cause row migration. The pctused parameter sets whether the data block is idle. When the used space of a data block is lower than the value of pctused, this data block indicates that it is idle and this free space is only used to insert new rows. If the data block has reached the upper boundary determined by pctfree, Oracle considers that the data block cannot be inserted into new rows. For example, if pctused is set to 40 in the Create table statement, the data block is available if it is less than or equal to 39. Therefore, you can fill in more data blocks to save space, but increase processing overhead, because the free space of data blocks is always occupied by updated rows, therefore, data blocks need to be reorganized frequently. Low pctused increases the free space of the database, but reduces the overhead of update operations. Therefore, if pctused is set too high, the disk utilization will be reduced, leading to row migration. If pctused is set too low, disk space will be wasted and I/O output during full table scan will be added. Pctused is a parameter relative to pctfree.
So, how do I select the pctfree and pctused values? There is a formula for reference. Obviously, the sum of pctfree and pctused cannot exceed 100. If the sum of the two is less than 100, the optimal balance between space utilization and system I/O is: The sum of pctfree and pctused is equal to 100% minus the size of a row as a percentage of the block space. For example, if the block size is 2048 bytes, it requires 100 bytes of overhead, and the row size is 390 bytes (20% of the available block ). To make full use of the space, the sum of pctfree and pctused should be 80%. How can we determine the size of data blocks? There are two factors to consider: first, the database environment type. For example, is it a DSS environment or an OLTP environment? In the data warehouse environment (OLAP or DSS), you need to perform many queries that have been running for a long time, so you should use big data blocks. In the OLTP system, users can process a large number of small transactions and use smaller data blocks to achieve better results. The second is the size of SGA. The size of the database buffer is determined by the data block size and the db_block_buffers parameter of the initialization file. It is best to set it to an integer multiple of the operating system I/O. 2. A partition (Extent), also known as a data partition, is a set of continuous data blocks. When a table, rollback segment, or temporary segment is created or requires additional space, the system always allocates a new data zone for it. A Data zone cannot span multiple files because it contains continuous data blocks. The purpose of the use area is to save data of a specific data type, which is also the basic unit of data growth in the table. In Oracle databases, the allocated space is in the Data zone. An Oracle object contains at least one data zone. Set the storage parameters of a table or index to include the size of its data zone.
3. A Segment is composed of multiple data zones. It is a specific database object (such as a table Segment, index Segment, rollback Segment, or temporary Segment) A series of data zones. The data areas contained in the segments can be discontinuous and span multiple files. The purpose of the use segment is to save a specific object. Www.2cto.com an Oracle database has four types of segments: data segments are also called table segments, which contain data and are related to tables and clusters. When a table is created, the system automatically creates a data segment named after the table. Index segment: contains the index used to improve system performance. Once an index is created, the system automatically creates an index segment named after the index. Rollback segment: Contains rollback information and is used during database recovery to provide the database with read consistency and rollback of uncommitted transactions, that is, the data space for rollback transactions. When a transaction starts to be processed, the system allocates a rollback segment for it. The rollback segment can be dynamically created and revoked. The system has a default rollback segment, which can be managed automatically or manually. Temporary segment: it is the segment created by Oracle during running. When an SQL statement requires a temporary workspace, Oracle creates a temporary segment. Once the statement is executed, the interval of the temporary segment is returned to the system.
4. The tablespace Oracle Database (tablespace) is composed of several tablespaces. Any database objects must be stored in a tablespace. The tablespace corresponds to several disk files, that is, the tablespace is composed of one or more disk files. A tablespace is equivalent to a folder in the operating system and a ing between the logical structure of the database and physical files. Each database has at least one tablespace, And the tablespace size is equal to the total size of all data files belonging to it. There are several special tablespaces in Oracle 10 GB: (1) system tablespaces (system tablespace) are required by each Oracle database. The function is to store information required for database management in the system tablespace, such as the tablespace name and data files contained in the tablespace. The name of the system tablespace cannot be changed. The system tablespace must be available at any time, which is also a necessary condition for database operation. Therefore, the system tablespace cannot be offline. System tablespaces include data dictionaries, stored procedures, triggers, and system rollback segments. To avoid storage fragmentation and competition for system resources in the system tablespace, an independent tablespace should be created to separately store user data. (2) The SYSAUX tablespace is created with the database creation. It acts as the auxiliary tablespace of the SYSTEM and stores objects other than the data dictionary. SYSAUX is also the default tablespace of many Oracle databases. It reduces the number of tablespaces managed by databases and DBAs, and reduces the load on SYSTEM tablespaces. (3) Temporary tablespace www.2cto.com is used to store temporary data generated during the running of the Oracle database. A database can create multiple temporary tablespaces. When the database is closed, all data in the temporary tablespace is cleared. All other tablespaces except temporary tablespaces are permanent tablespaces.
(4) The undo tablespace is used to save the Oracle database revocation information. That is, the tablespace that saves the user rollback segment is called the rollback tablespace (or RBS undo tablespace )). In Oracle8i, It is rollback tablespace, Which is changed from Oracle9i to undo tablespace. In Oracle 10 Gb, only six tablespace sysaux, system, temp, undotbs1, example, and users are initially created. Temp is a temporary tablespace, while undotbs1 is an undo tablespace. The figure shows the relationship between tablespaces and databases and data files.

Figure table space corresponds to databases and data files by Li Zhiqiang