Table space analysis results

Document directory

• Space management warning and warning: Test details

Space management warning and warning: Test details Perform one or more tests based on the evaluated tablespace to detect space management problems.

Out-of-band Scaling

Oracle allows a segment to have multiple zones. When extra space is required, Oracle server will automatically allocate these zones. Although the performance of DML operations is basically unrelated to the number of partitions in a segment, some DDL operations (such as deleting and truncation segments) on objects in a dictionary-managed tablespace are sensitive to the number of partitions. Performance Tests for such operations show that the Oracle server can efficiently process a partition smaller than 1024.

If possible, consider:

• Use a larger partition size to recreate this segment, that is, specify the storage parameter next = initial, and specify pctincrease = 0 to ensure that the segments have the same partition size.
• Move a segment to a locally managed tablespace (Oracle 8. I)

Close to maxextents

The storage parameters of the maxextents segment specify the maximum number of partitions that can be allocated to the segment. Once the number of zones in the segment reaches the maximum value, the insert operation cannot be performed and an error message is displayed.

Consider increasing the value of the maxextents storage parameter for this section. Alternatively, you can use a larger partition size to recreate this segment. You can specify the storage parameter as next = initial, and pctincrease = 0 to ensure that the size of each segment is the same.

Insufficient free space

Oracle server allocates space for segments by partition. If the existing zone of a segment is full, Oracle server will try to assign a partition to the segment. If the Oracle server cannot find a group of equal or larger continuous data blocks to meet the request for allocating new zones, an error message is returned.

To increase the size of a tablespace, enable automatic expansion of one of the existing data files, adjust the size of one of the existing data files, or add a new data file.

Or, if the tablespace has the free space fragmentation problem, consider reorganizing the entire tablespace.

If the free space in the tablespace can meet the request of the allocation area, but these spaces are scattered in several smaller idle "blocks, therefore, the table space cannot be used to meet the request (because the allocated space must come from a group of consecutive blocks.

Index fragmentation problems

As index values are updated or deleted, the number of unavailable spaces in the tree also increases. This low tree utilization will result in a low performance of performing index scanning on the index. Consider re-indexing to improve performance.

Too many line links

In either case, a single data block cannot be loaded because there may be too much data in a row in a table or table partition. Therefore, row fragments are generated.

In the first case, a data block cannot be loaded because the row is too large to insert this row for the first time. Oracle server uses a series of data blocks reserved for this segment to store row data. Chained rows are often used when a large row is used, for example, a row that contains a long data type. In this case, if you do not use a large database block size defined by the db_block_size initialization parameter, row continuation is inevitable.

However, in the second case, a row that can be loaded into a data block is updated at first, increasing the length of the entire row, and the free space of the data block is fully filled. In this case, the Oracle server migrates the entire row of data to a new data block (assuming that the entire row can be loaded into a new block ). Oracle Server retains the original row of the migrated row so that it references the new block containing the migrated row.

When a row is resumed or transplanted, the I/O performance associated with the row is reduced, because the Oracle server must scan more than one data block to retrieve information about the row.

There are two ways to solve the row migration problem. Rebuilding a table or table partition can avoid row fragmentation when the rows are tightly loaded into data blocks during the reconstruction process. However, if no other changes are made to the table or table partition (just to fix the migrated rows), problematic rows will be fixed in a centralized manner, rather than completely rebuilt.

Note: If the row size may increase during update, consider increasing the pctfree value of the segment to reduce the possibility of row fragmentation in the future.

The user object is in the system tablespace.

The system tablespace contains the database metadata. Although all database objects can be stored in this tablespace, Oracle strongly recommends not to do so. Storing user data in the system tablespace increases the possibility of space management problems. Because the only way to reconstruct the system tablespace is to recreate the database, we recommend that you reposition the user object to another tablespace.

Non-zero pctincrease

Pctincrease specifies the increasing percentage of the size of the third and subsequent districts compared with the previous one. Oracle recommends that you use the zero settings of pctincrease to make it easier to manage space tasks, because the use of non-zero pctincrease settings may cause the issue of table space free space fragmentation.

If the free space in the tablespace can meet the request of the allocation area, but these spaces are scattered in several smaller idle "blocks, therefore, the table space cannot be used to meet the request (because the allocated space must come from a group of consecutive blocks.

Fortunately, as long as the segment size in the tablespace should be exactly the same, the free space fragmentation of the tablespace can be easily avoided. This ensures that any idle zone in the tablespace can always be used for any segment in the tablespace. And there will be no idle areas that are too small to be selected.

To further ensure the same region size, oracle8 adds the mum extent clause to the reate tablespace command. Specify the minimum extent value to ensure that all allocated partitions in the tablespace are multiples of the value. If the allocated area is usually smaller, it is automatically rounded up to a multiple of minimum extent. At the same time, if a partition is trimmed (for example, because it is directly loaded in parallel), the remaining size and the truncated size are set to a multiple of minimum extent. Always specify the minimum extent value equal to the initial value to ensure that the partition size is consistent.