Use, monitoring, and maintenance of Oracle Data Space

Oracle provides a number of methods for the use, monitoring, and maintenance of data spaces. At the same time, features in various versions are gradually enhanced to simplify the complexity of this aspect, improve application running efficiency.

1. Related Concepts

The database space is logically divided into multiple tablespaces. Each tablespace is composed of one or more physical data files in the system. The basic unit of Oracle Data Storage is blocks, the size of the block is determined by the DB_BLOCK_SIZE parameter during database creation. One or more consecutive blocks constitute a partition (EXTENT), which is used as the basic unit of Data Object Storage. In Oracle, the space used by each basic data object is called segments. segments are stored in a unique tablespace. Each Segment is actually a series of segments (more accurately data blocks). Each simple data object corresponds to a segment. For partition objects such as partition tables and indexes, each (sub) Partition corresponds to a segment, and each (sub) Partition forms a complete data object. Therefore, you can think of a tablespace as a bucket with many segments in it. One segment can only be placed in one bucket, rather than crossing multiple buckets.

Ii. Use of tablespaces

Generation of tablespace fragments

Because multiple data segments are stored in the same tablespace, different data segments can have different partition sizes, and different segments can be stored across partitions; when the partitions in these segments are allocated (such as creating tables) and released (such as deleting tables), the original idle data blocks in the tablespace may become discontinuous, however, a partition must be composed of continuous data blocks. When a partition needs to be assigned a new partition, although the total number of free data blocks in the tablespace is greater than the size of the required partition, a series of continuous blocks cannot be found for distribution of partitions in the tablespace. This is called the ExtentFragmentation of a tablespace. We often encounter this situation. It is clear that there are still several hundred MB space in the calculation of the tablespace from DBA_FREE_SPACE, but one of the tables cannot expand the space of several MB.

Eliminate tablespace fragments

Oracle provides a great deal of flexibility in the partition allocation of segments. However, if you fail to properly use the optional parameters for creating tablespaces and data objects, in the end, we will inevitably face the issue of slice fragments. Oracle's BhaskarHimatsingka and JuanLoaiza proposed SAFE (SimpleAlgorithmforFragmentationElimination.) configuration rules. By following these rules, fragment-level fragments can be completely avoided. In fact, the new feature introduced by Oracle8i: LocalManagedTablespace is the built-in implementation of SAFE rules on OracleServer. The SAFE principles include:

1. use the same partition size for segments in each tablespace; Segment parameter INITIAL = NEXT, PCTINCREASE = 0; you can use the 'minimumextent' clause of CreateTablespace to ensure that the allocated partition is a multiple of this parameter;

2. Only INITIAL and NEXT parameters are specified at the tablespace level. do not specify these parameters when creating data segments;

3. the partition size is determined by the segment size. The principle is to balance the efficiency of sequential scanning and space utilization, and ensure that the number of segments is controlled below 1024. According to this principle, after the test, determine the partition selection rules;

This database can only use tablespaces with three partition sizes. Before creating an object, you need to evaluate its size and place it in the corresponding tablespace;

4. oracle815 introduces local tablespace management, which is superior to traditional dictionary tablespace management in terms of management and performance. It has integrated rules 1, 2, and 3. To use this feature, specify the EXTENTMANAGEMENTLOCAL clause in the CREATETABLESPACE statement;

5. the maximum number of segments must be less than 4096. There is no obvious performance difference between DML operations in the range of segments; however, the speed of some DDL operations has a large relationship with the number of partitions. Therefore, a reasonable number of partitions should be kept below 1024. For continuous expansion segments, the number of partitions should be monitored, move to other tablespaces if necessary;

6. for particularly large data segments, they should be controlled between 4G-128G (Oracle7 is 5G-160G) and stored in a separate tablespace, at the same time, we should consider using partition pull to improve the performance of these extra large segments;

7. the user's TEMPORARY tablespace should use the TEMPORARY type;

8. when the transaction scale of the system is balanced, the OPTIMAL parameter can be used for the rollback segment. Otherwise, the OPTIMAL parameter should be avoided, and the size of the rollback segment should be monitored regularly and rebuilt as necessary;

9. Do not store user data in the SYSTEM tablespace for temporary and rollback segments. It is designed for SYSTEM data objects that will never Drop or Truncate;

10. When creating a tablespace, specify the size of the data file = integer multiple of the partition space + 1 data block. For LocalManagedTablespace, it is an integer multiple of the partition space + 64 K;

11. when the tablespace uses a uniform partition size, do not organize it in space. The reorganization results will not only consume energy, but may also degrade the performance; tablespaces without uniform partition sizes should be reorganized through Export/Import;

12.8i provides AlterTable... Move [Tablespace…] Command can be used to quickly reorganize tables, AlterIndex... Rebuild... [Tablespace…] The command can be used to quickly reconstruct an index.

Misleading use of a single Partition

In many documents on fragment, we recommend that you use the Compress = Y option in Export to adjust all the data in the table to a single partition and expect good performance after Import. This allows many people to come up with a point of view that good performance can be achieved when all the data in the table is stored in a partition. In fact, a single segment is advantageous only when the following conditions are met.

Data is primarily accessed through (full-range) scanning;

The data blocks corresponding to the segments are continuously stored on the physical disk. Oracle can publish large sequential disk read operations;

Through the analysis, we can find that, on the one hand, most of the tables in the database are accessed through indexes, and on the other hand, the current database files generally use the RAID 5 or RAID 0 + 1 Technology physically, data is distributed to multiple physical disks in a strip manner. Logically, a single partition and multiple partitions are physically different. In addition, from the perspective of Oracle, it is very easy to manage the segments of hundreds of slices without performance degradation. It can be seen that it is no obvious benefit to put the entire segment in a single area, but this approach will lead to the generation of tablespace fragments.

Iii. Use of table data segments

Tablespace Organization

The Heap table space is composed of a series of partition links. Each data block, except the block header, can be used to store data. You can specify the following parameters when creating a table:

PCTFREE: the percentage of Space reserved in the block for the UPDATE operation. When the occupied space reaches the upper limit, new data cannot be inserted into the block;

PCTUSED: specifies the minimum percentage of data space used in a block. When a block reaches PCTFREE, it goes through some DELETE operations, after its space usage drops to PCTUSED, it can be re-used for INSERT data; this is the meaning of the PCTFREE/PCTUSED parameter;

The goal of adjusting the parameters of PCTFREE and PCTUSED is to improve the performance and space usage efficiency, so as to avoid a lot of unused space in the block, however, you cannot find a block that can be used to insert new data rows.

Use of PCTFREE

In Oracle, each row of data in a table is marked by a unique ROWID. Some data types supported by Oracle have variable lengths, such as VARCHAR. when the data is updated, if the available space in the block cannot accommodate the data row after UPDATE, Oracle will move the row to another data block and keep the ROWID of the Data row unchanged, create a pointer in the original block to point to the position after the row migration. In this case, two data blocks are required to read a row of data, resulting in performance degradation. The space reserved by PCTFREE is to ensure that the changed data rows can still be stored in the original data block to avoid row migration.

Therefore, if the PCTFREE setting is insufficient, row migration may occur. If the PCTFREE setting is too high, the space will be wasted. Therefore, correct PCTFREE settings require analysis of data usage in the table. If the data length does not change or is rarely updated, a smaller PCTFREE can be used. In most cases, a larger PCTFREE (the default value of PCTFREE is 10, if it is difficult to estimate the space to be reserved, you can use the range of 15-25). Do not use a small PCTFREE value to save space in the block.

1. How to make better use of Oracle full-text search
2. Analysis of Oracle Database management scripts
3. Explain how to create and manage users in Oracle