Tips for keeping Oracle databases in good performance

Today, Oracle databases are favored by more and more enterprises because of their high reliability, security and compatibility. The reader may wish to consider some of the following aspects of how to keep the Oracle database permanently in good performance.

Partition
According to practical experience, in a large database, the vast majority of database space is occupied by a small number of tables. How to simplify the large database and management, how to improve the application of query performance, can generally use partitioning this method. Partitioning is the dynamic separation of records from a table into a number of different table spaces, making the data physically fragmented to facilitate maintenance, backup, recovery, transactional, and query performance. When used, you can create a view that connects all partitions so that they logically still appear as a whole.
1, the establishment of the partition table
Create Table Employee (
EmpNo varchar2 () 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 is partitioned according to the Deptno column.
2, the 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,
);
Use a global index when there are many transactions in the partition and to ensure the uniqueness of data records in all partitions, such as:
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 established, the global clause allows you to specify the range value of the index, which can be different from the range value of the table partition. Only a local index is established to establish a one by one correspondence between the index partition and the table partition. Therefore, in most cases, you should use a local index partition. With this index, partitioning makes it easy to associate index partitions with table partitions, and local indexes are easier to manage than global indexes.
3. Division Management
You can also use the ALTER TABLE command to add, discard, swap, move, modify, rename, divide, and truncate the structure of an existing partition, depending on your actual needs.

Rebuild Indexes
If the records in the table are frequently deleted or inserted, the amount of index space usage is increasing even though the total number of records in the table remains unchanged. Although the record was deleted from the index, the use space for the record index entry could not be reused. Therefore, if the table changes, the amount of index space increases, regardless of whether the number of records in the table increases-just because the amount of invalid space in the index increases.
To reclaim the space used by deleted records, you need to use the ALTER INDEX rebuild command. You can do a batch program that runs periodically to rebuild the index of the most active table. This batch program can be run at idle time to avoid conflicts between programs and users. If you can adhere to the index of this program planning, you can promptly reclaim those unused space, improve space utilization.

Fragmentation of segments
When a database object is generated (a table or an index), the table space is specified for it either by the user's default value or by the specified value. A segment generated in a tablespace that is used to store related data for an object. The space allocated by the segment will not be freed until the segment is closed, shrunk, and truncated.
A segment is made up of scopes, and the scope is made up of neighboring Oracle blocks. Once the scope of existence can no longer store new data, the segment will get a new range and does not require that these ranges be contiguous to each other. Such an extension will continue until the data file in the table space does not provide more free space, or the range reaches the limit.
Therefore, a piece of data that is too fragmented will not only affect the operation, but also raise the space management problem in the table space. Therefore, it is very useful for each data segment to contain only one range. With a monitoring system, you can check the Dba_segments data dictionary view to see which database objects contain 10 or more segments of the range, and determine their data fragment fragments.
If you have too many fragments in one segment, you can solve the problem in two ways:
1, with the correct storage parameters to create a new table, the old table to insert the data into the new table, and then delete the old table;
2, the use of export/import tools.
such as: Exp system/manager file=exp.dmp compress=y grants=y indexes=y
Tables= (T1,T2)
If the output succeeds, enter Oracle and delete the above table.
Note: Compress=y determines that their storage parameters will be modified during the output process.
Imp system/manager file=exp.dmp commit=y buffer=64000 full=y
Note: Reconfigure the new storage parameters when entering.

Free-scope defragmentation
A free range in a table space is a collection of contiguous free (space) blocks in a table space. When a segment is closed, its scope is released and marked as free range. However, these free ranges can no longer be merged with adjacent free ranges, and the boundaries between them always exist. However, when the default value of the tablespace is pctincrease set to 0 o'clock, Smon background will periodically merge these adjacent free ranges. If the pctincrease is set to 0, the adjacent free range is not automatically merged by the database. However, you can use the altertable command coalesce option to force a merge of adjacent free ranges.
Without free range merging, the space allocation in the table space will be affected in future space requests. When a large enough range is needed, the database does not merge adjacent free ranges unless there is no other choice. Thus, the largest free range in the later part of the table space is used when the previous smaller free range in the table space has been used. As a result, they do not have enough space to use, resulting in a speed paradox in the table space. As a result of such a process, the spatial distribution of the database is farther and further away from the ideal. Free space debris often occurs in database tables and indexes that are frequently closed and regenerated.
In an ideal Oracle tablespace, each database object is stored in a separate scope, and all valid free spaces are concentrated in a large and contiguous range. In this way, when an object needs additional storage space, it can increase the possibility of acquiring large enough free space while minimizing the circular call in the space, and increasing the free space usage rate.