The Oracle database is favored by its high reliability, security, compatibility and more and more companies. How to keep the Oracle database excellent performance, this is a question of many database administrators concerned. According to the author's experience, you may wish to consider the following aspects.
First, partition
According to the actual experience, most of the data space is occupied by a small amount of tables in a large database. In order to simplify the management of large databases, improve the application's query performance, can generally use this means of partition. The so-called partition is the record in a dynamic table to a number of different tablespaces, making the data physically split, facilitating maintenance, backup, recovery, transaction and query performance. A view that connects all partitions can be created when used, making it logically still appearing in one whole.
1. Establish the 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 Part 3_TSPartition Part4 Values Less Than (MaxValue) TableSpace Part4_ts);
Table Employee is partitioned in accordance with 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,);
When many transactions appear in the partition and to ensure the uniqueness of data record in all partitions, the global index is used, and when the global index is established, the Global clause allows the specified range value, which can be different from the range of the table partition. . Only establishing local indexing will enable the index partition to establish a correspondence between the table partition. Therefore, in most cases, a local index partition should be used. If this index is used, the partition can easily establish an index partition with the table partition, and the local index is easier to manage than the global index.
3. The partition management can also use the Alter Table command to increase, delete, exchange, move, modify, rename, and cut, truncated a structure that exists.
Second, rebuild indexes
If the recording is frequently deleted or inserted in the table, although the total record in the table remains unchanged, the amount of index space is increasing. Although the record is deleted from the index, the usage space of the record index cannot be reused. Therefore, if the table changes, the index space is increasing, regardless of whether the number of records increases in the table, only because the invalid space in the index increases. To recover spaces that have been used to be removed, you need to use the alter index rebuild command. You can make a regular running batch program to rebuild the index of the most active table. This batch program can be run when you are idle to avoid conflicts with other application. If you can adhere to the program plan of the index, you can recover those unused spaces and improve spatial utilization.
Third, the fragmentation of the paragraph
When generating a database object (a table or an index), the table space is specified by the user default or specified value. A segment generated in the table space for storing the information of the object. The space allocated in the segment is closed before the segment is closed, and the space allocated will not be released. A section is composed of a range, and the range is composed of adjacent Oracle blocks. Once there is no new data, this segment will be obtained, but these ranges are not required to be adjacent to each other. Such an extension will continue until the data files in the table space cannot provide more free space, or the range has reached the limit.
Therefore, there are too many data segments that will not only affect operation, but also spatial management issues in the table space. Therefore, each data segment contains only one scope is very beneficial. With the monitoring system, you can find out which database objects contain 10 or more segments by checking the DBA_SEGMENTS data dictionary view, determine their data segment fragmentation.
If the fragment of a segment is too much, two methods can be used: 1. Create a new table with the correct storage parameters, insert the old table data into the new table, delete the old table; 2. Using the Export / Import tool.
Such as: exp system / manager file = expression = y indexes = ytables = (t1, t2) If the output is successful, enter Oracle, delete the above table. Note: Compress = y indicates that they will modify their storage parameters during the output. IMP system / manager file = exp.dmp commit = y buffer = 64000 full = y
Fourth, free range fragmentation
A free range in the tablespace is a collection of free (space) blocks connected in the tablespace. When a segment is closed, its range will be released and labeled as free range. However, these free ranges cannot be combined with adjacent free range, and the boundaries between them always exist. However, when the default value of Table space is not 0, the SMON background process will regularly cooperate 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 Alter Table command "Coalesce" option to force the combination of adjacent free ranges.
The free range merges are not performed, in the future space request, the spatial allocation in the tablespace will be affected. When a large enough range is needed, the database does not merge adjacent free range unless there is no other option. Thus, when a smaller free range in the table space has been used, the maximum free range in the tablespace is used. As a result, it will cause the contradiction between table space requirements because there is not enough usage space. Due to this situation, the spatial assignment of the database is getting farther and farther away. Free space fragments often appear in database tables and indexes that are often closed and regenerated.
In an ideal Oracle table space, each database object is stored in a separate range, and all effective free space is concentrated in a huge and continuous range. Thus, when an object requires additional storage space, it is possible to increase the cyclic call in the space while increasing the possibility of the space, and improves the free space usage.
