Tips on maintaining excellent Oracle database performance

Nowadays, Oracle databases are favored by more and more enterprises for their high reliability, security, and compatibility. Readers may wish to consider the following aspects to ensure the permanent performance of Oracle databases.

Partition
Based on actual experience, in a large database, the vast majority of database space is occupied by a small number of tables. Partition is generally used to simplify database management and improve query performance of applications. The so-called partition is to dynamically separate records from several different tablespaces so that 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 values 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 there are many transactions in a partition and the uniqueness of data records in all partitions must be ensured. For example:
Create index Employee_DeptNo on Employee (DeptNo)
Global partition by range (DeptNo)
(
Partition PART1 values less than (11)
Tablespace PART1_NDX_TS,
Partition PART2 values less than (21)
Tablespace PART2_NDX_TS,
Partition PART3 values less than (31)
Tablespace PART3_NDX_TS
Partition PART4 values less than (MAXVALUE)
Tablespace PART4_NDX_TS
);
When a global index is created, the global clause promises to specify the range value of the index. The range value can be different from the range value of the table partition. Only when a local index is set up will the index partition have a one-to-one correspondence with the table shard. Therefore, you should use partial index Partitioning in most cases. With this index, partitions can easily associate index partitions with table partitions, making local indexes easier to manage than global indexes.
3. Partition governance
You can also use the alter table command to add, discard, exchange, move, modify, rename, divide, and truncated an existing partition structure as needed.

Rebuild Indexes
If records in the table are frequently deleted or inserted, the index space will increase 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-only because of the increase in the amount of invalid index space.
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 the user. If you can stick to the index planning, you can recycle unused space in time to improve the space utilization.

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, and it is not required that these ranges are 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 governance 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 solve the problem in two ways:
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. dmp compress = Y grants = Y indexes = Y
Tables = (T1, T2)
If the output is successful, go to Oracle and delete the preceding table.
Note: compress = Y decides to modify their storage parameters during the output process.
Imp system/manager file = exp. dmp commit = Y buffer = 64000 full = Y
Note: The new storage parameters are reconfigured when input.

Free-range Fragment
A free range in a tablespace is a set of connected free (Space) blocks in 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 a tablespace is not set to 0, the SMON backend periodically merges these adjacent free ranges. If pctincrease is set to 0, the adjacent free range will not be automatically merged by the database. However, you can use the altertable 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, they do not have enough space to use, resulting in a conflict of speed in the tablespace. The emergence of such a process makes the database space allocation 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.