How to maintain excellent Oracle database performance

Summary of methods to maintain excellent performance of Oracle databases:

I. Partitioning

Based on actual experience, the vast majority of data space in a large database is occupied by a small number of tables. To simplify the management of large databases and improve the query performance of applications, you can use partitioning. The so-called partition is the separation of records in the dynamic table to several different tablespaces, so that the data is physically separated to facilitate maintenance, backup, recovery, transaction and query performance. When used, you can create a view that connects all partitions so that it still appears logically as a whole.

1. Create a partition table

Create table Employee (

EmpNo varchar2 (10) primary key,

Name varchar2 (30 ),

DeptNo Number (2)

)

Partition by range (DeptNo)

(Partition PART1 values less than (11)

Tablespace PART1_TS,

Partition PART2 values less than (21)

Tablespace PART2_TS,

Partition PART3 valuse less than (31)

Tablespace PART3_TS

Partition PART4 values less than (MAXVALUE)

Tablespace PART4_TS

);

Table Employee partitions Based on the DeptNo column.

2. Partition Index

Create index Employee_DeptNo on Employee (DeptNo) local (

Partition PART1 tablespace PART1_NDX_TS,

Partition PART2 tablespace PART2_NDX_TS,

Partition PART3 tablespace PART3_NDX_TS,

Partition PART4 tablespace PART4_NDX_TS,

);

Global indexes are used when many transactions occur in a partition and the uniqueness of data records in all partitions must be ensured. When a Global index is created, the Global clause allows you to specify the index range value, this range value can be different from the range value of the table partition. Only when a local index is created will the index partition have a one-to-one correspondence with the table shard. Therefore, you should use partial index Partitioning in most cases. If this index is used, partitions can easily associate index partitions with table partitions, making local indexes easier to manage than global indexes.

3. Partition Management

Based on actual needs, you can also use the Alter table command to add, delete, exchange, move, modify, rename, divide, and truncated an existing partition structure.

Ii. Re-Indexing

If records in a table are frequently deleted or inserted, the index space usage increases even though the total number of records in the table remains unchanged. Although the record is deleted from the index, the space used for this record index item cannot be reused. Therefore, if the table changes, the index space increases constantly, regardless of whether the number of records in the table increases, because the invalid index space increases.

The Alter index rebuild command is required to reclaim the space used by deleted records. You can create a regular batch processing program to rebuild the index of the most active table. This batch processing program can run in idle time to avoid conflicts between the program and other applications. If you can stick to the index planning, you can recycle unused space in time to improve the space utilization.

Iii. segment fragmentation

When a database object (a table or an index) is generated, the tablespace is specified by the user's default value or specified value. A segment generated in a tablespace is used to store object-related data. Before a segment is closed, shrunk, or truncated, the space allocated by the segment is not released.

A segment is composed of a range, And a range is composed of adjacent Oracle blocks. Once the existing range cannot store new data, this segment will obtain a new range, but these ranges are not required to be adjacent to each other. Such expansion will continue until the data files in the tablespace cannot provide more free space or the number of ranges has reached the limit.

Therefore, a data segment with too many fragments will not only affect the operation, but also cause space management problems in the tablespace. Therefore, it is very helpful that each data segment contains only one range. With the help of the monitoring system, you can check the DBA_SEGMENTS data dictionary view to find out which database objects contain 10 or more segments and determine their data segment fragments.

If a segment contains too many fragments, you can use either of the following methods:

1. Create a new table with the correct storage parameters, insert the data in the old table into the new table, and then delete the old table;

2. Use the Export/Import tool.

Example: exp system/manager file = exp. dmpcompress = Y grants = Y indexes = Y

Tables = (T1, T2)

If the output is successful, go to Oracle and delete the preceding table.

Note: compress = Y indicates that their storage parameters will be modified during the output process.

Imp system/manager file = exp. dmp commit = Y buffer = 64000 full = Y

Iv. Free-range fragmentation

A free range in a tablespace is a set of free (Space) blocks connected to the tablespace. When a segment is closed, its range is released and marked as free range. However, these free ranges cannot be merged with adjacent free ranges, and the boundaries between them always exist. However, when the default pctincrease value of the tablespace is not 0, the SMON background process regularly cooperates these adjacent free ranges. If pctincrease is set to 0, the adjacent free range is not automatically merged by the database. However, you can use the Alter table command "coalesce" option to force the merge of adjacent free ranges.

If range merging is not performed, the space allocation in the tablespace will be affected in future space requests. When a large enough range is required, the database does not merge adjacent Free Ranges unless there are no other options. In this way, when the first small free range in the tablespace has been used, the largest free range in the rest of the tablespace will be used. As a result, there is not enough space to use, which leads to a conflict of table space requirements. Due to this situation, the database space allocation is getting farther and farther away from the ideal. Free Space fragments often appear in database tables and indexes that are frequently disabled and regenerated.

In the ideal Oracle tablespace, each database object is stored in a separate range, and all valid free space is concentrated in a large and continuous range. In this way, when an object needs to be attached with a bucket, it can increase the possibility of obtaining sufficient free space while minimizing the loop calls in the space to improve the free space usage.