How to maintain the good performance of Oracle database

oracle| Data | database | performance

Author: Brent

Oracle database is favored by more and more enterprises because of its high reliability, security and compatibility. How to keep Oracle databases in good performance is a concern for many database administrators and may be considered in the following ways, based on the author's experience.

One, zoning
According to practical experience, in a large database, the vast majority of data space is occupied by a small number of tables. Partitioning is generally used to simplify the management of large databases and improve the query performance of applications. Partitioning is the separation of records in dynamic tables into a number of different table spaces, allowing data to be physically partitioned for ease of 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. Create a 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 Valuse 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. Partitioned 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,
);

When there are many transactions in a partition and a global index is used to ensure the uniqueness of the data records in all partitions, 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 when you establish it. 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. Partition Management
Depending on your needs, you can also use ALTER TABLE commands to add, delete, Exchange, move, modify, rename, divide, and truncate the structure of an existing partition.

Second, the reconstruction index
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 is increasing, regardless of whether the number of records in the table increases, just because the 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 prevent the program from conflicting with other applications. If you can adhere to the index of this program planning, you can promptly reclaim those unused space, improve space utilization.

Iii. Defragmentation 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, but it 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 them in two ways:
1. Create a new table with the correct storage parameters, insert the old table data into the new table, delete the old table;
2. Use the Export/import tool.

such as: Exp System/manager file=exp.dmpcompress=y grants=y indexes=y
Tables= (T1,T2)
If the output succeeds, enter Oracle and delete the above 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. fragmentation of the free range
A free range in a tablespace is a collection of contiguous free (space) blocks in the 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 pctincrease setting is not 0 o'clock, the Smon background process periodically cooperates these adjacent free scopes. If the pctincrease is set to 0, then the adjacent free range is not automatically merged by the database. However, you can use the ALTER TABLE 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. In this way, the largest free range in the later part of the table space is used when the previous smaller free range in the tablespace is already in use. As a result, there is not enough space to use, resulting in the contradiction of table space requirements. As a result of such a situation, the spatial distribution of the database is more and more far 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 enough free space, minimize the circular call in the space, and improve the free space utilization rate.