How do I maintain the excellent performance of the Oracle database
- 2020-05-27 07:25:35
- OfStack
Oracle database is favored by more and more enterprises because of its high reliability, security and compatibility. How to keep the Oracle database in good performance is a concern for many database administrators. Based on the author's experience, it is advisable to consider the following aspects.
1. Partition
As a rule of thumb, in a large database, the vast majority of the data space is occupied by a small number of tables. In order to simplify the management of large databases and improve the query performance of applications, you can generally use partitioning. Partitioning is the separation of records from a dynamic table into several different table Spaces, making the data physically separated for easy 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 is partitioned according to column DeptNo.
2. Partitioned indexes
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 data records in all partitions are guaranteed to be unique. When creating a global index, the Global clause allows you to specify a range value of the index that is different from the range value of the table partition. Only by creating a local index can we establish an 11 correspondence between the index partition and the table partition. Therefore, in most cases, you should use a local index partition. With this index, the partition can easily associate the index partition with the table partition, and a local index is easier to manage than a global index.
3. Partition management
Depending on your needs, you can also use the Alter table command to add, delete, swap, move, modify, rename, partition, and truncate the structure of an existing partition.
2. Rebuild the index
If the records in the table are deleted or inserted frequently, the usage of index space will continue to increase even though the total number of records in the table remains the same. Although the record is deleted from the index, the space used for the index entry of the record cannot be reused. Therefore, if the table is variable, the amount of index space will increase, whether or not the number of records in the table will increase, just because the invalid space in the index will increase.
To reclaim space that has been used by deleted records, use the Alter index rebuild command. You can do a batch program that runs periodically to rebuild the index of the most active table. The batch program can be run at idle to avoid conflicts with other applications. If you can adhere to the 1 program planning of the index, you can recover the unused space in time, improve space utilization.
3. Paragraph defragmentation
When a database object (a table or an index) is generated, specify a table space for it by user default or by specified value. A segment generated in a table space to store data about an object. Until the segment is closed, contracted, or truncated, the space allocated by the segment will not be freed.
A segment is made up of ranges, which are made up of adjacent Oracle blocks. Once the existing range can no longer store new data, the segment will acquire the new range, but it does not require that the ranges be adjacent to each other. Such extensions continue until the data files in the table space do not provide more free space, or the number of ranges has reached its limit.
Therefore, a data segment with too much fragmentation will not only affect the operation, but also lead to space management problems in the publishing space. Therefore, it is beneficial to have only 1 range per data segment. With the help of the monitoring system, you can check the DBA_SEGMENTS data dictionary view to see which database objects have a range of 10 or more segments and determine their data segment fragmentation.
If there are too many fragments in one segment, two solutions can be used:
1. Set up a new table with the correct storage parameters, insert the data of the old table into the new table, and delete the old table;
2. Use the Export/Import tools.
exp system/manager file=exp.dmpcompress=Y grants=Y indexes=Y tables=(T1,T2)
If the output is successful, enter Oracle and delete the above table.
Note: compress=Y indicates that their storage parameters will be modified during the output.
imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y
4. Free range defragmenter
A free range in a table space is a collection of contiguous free blocks in a table space. When a segment is closed, its range is freed and marked as free range. However, these areas of freedom can no longer merge with adjacent areas of freedom; the boundaries between them will always exist. But when the default pctincrease setting for a tablespace is not 0, the SMON background process periodically collaborates with these adjacent free scopes. If pctincrease is set to 0, then adjacent free ranges will not be automatically merged by the database. But you can use the Alter table command " coalesce & quot; Option to force the merge of adjacent free ranges.
Without free-scope merging, space allocation in a table space will be affected in future space requests. When a sufficiently large range is required, the database will not merge adjacent free ranges unless there is no other option. Thus, when the smaller front free range in the table space has been used, the largest one in the back part of the table space will be used. As a result, there is not enough space to use, resulting in conflicting requirements for table Spaces. As a result of this situation, the space allocation of the database is further and further away from the ideal. Free space debris often occurs in database tables and indexes that are often closed and rebuilt.
In an ideal Oracle table space, each database object is stored in a single scope, and all available free space is concentrated in a large, contiguous scope. In this way, when an object needs additional storage space, it can increase the possibility of obtaining a large enough free space while minimizing the loop call in the space to improve the utilization of free space.
1. Partition
As a rule of thumb, in a large database, the vast majority of the data space is occupied by a small number of tables. In order to simplify the management of large databases and improve the query performance of applications, you can generally use partitioning. Partitioning is the separation of records from a dynamic table into several different table Spaces, making the data physically separated for easy 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 is partitioned according to column DeptNo.
2. Partitioned indexes
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 data records in all partitions are guaranteed to be unique. When creating a global index, the Global clause allows you to specify a range value of the index that is different from the range value of the table partition. Only by creating a local index can we establish an 11 correspondence between the index partition and the table partition. Therefore, in most cases, you should use a local index partition. With this index, the partition can easily associate the index partition with the table partition, and a local index is easier to manage than a global index.
3. Partition management
Depending on your needs, you can also use the Alter table command to add, delete, swap, move, modify, rename, partition, and truncate the structure of an existing partition.
2. Rebuild the index
If the records in the table are deleted or inserted frequently, the usage of index space will continue to increase even though the total number of records in the table remains the same. Although the record is deleted from the index, the space used for the index entry of the record cannot be reused. Therefore, if the table is variable, the amount of index space will increase, whether or not the number of records in the table will increase, just because the invalid space in the index will increase.
To reclaim space that has been used by deleted records, use the Alter index rebuild command. You can do a batch program that runs periodically to rebuild the index of the most active table. The batch program can be run at idle to avoid conflicts with other applications. If you can adhere to the 1 program planning of the index, you can recover the unused space in time, improve space utilization.
3. Paragraph defragmentation
When a database object (a table or an index) is generated, specify a table space for it by user default or by specified value. A segment generated in a table space to store data about an object. Until the segment is closed, contracted, or truncated, the space allocated by the segment will not be freed.
A segment is made up of ranges, which are made up of adjacent Oracle blocks. Once the existing range can no longer store new data, the segment will acquire the new range, but it does not require that the ranges be adjacent to each other. Such extensions continue until the data files in the table space do not provide more free space, or the number of ranges has reached its limit.
Therefore, a data segment with too much fragmentation will not only affect the operation, but also lead to space management problems in the publishing space. Therefore, it is beneficial to have only 1 range per data segment. With the help of the monitoring system, you can check the DBA_SEGMENTS data dictionary view to see which database objects have a range of 10 or more segments and determine their data segment fragmentation.
If there are too many fragments in one segment, two solutions can be used:
1. Set up a new table with the correct storage parameters, insert the data of the old table into the new table, and delete the old table;
2. Use the Export/Import tools.
exp system/manager file=exp.dmpcompress=Y grants=Y indexes=Y tables=(T1,T2)
If the output is successful, enter Oracle and delete the above table.
Note: compress=Y indicates that their storage parameters will be modified during the output.
imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y
4. Free range defragmenter
A free range in a table space is a collection of contiguous free blocks in a table space. When a segment is closed, its range is freed and marked as free range. However, these areas of freedom can no longer merge with adjacent areas of freedom; the boundaries between them will always exist. But when the default pctincrease setting for a tablespace is not 0, the SMON background process periodically collaborates with these adjacent free scopes. If pctincrease is set to 0, then adjacent free ranges will not be automatically merged by the database. But you can use the Alter table command " coalesce & quot; Option to force the merge of adjacent free ranges.
Without free-scope merging, space allocation in a table space will be affected in future space requests. When a sufficiently large range is required, the database will not merge adjacent free ranges unless there is no other option. Thus, when the smaller front free range in the table space has been used, the largest one in the back part of the table space will be used. As a result, there is not enough space to use, resulting in conflicting requirements for table Spaces. As a result of this situation, the space allocation of the database is further and further away from the ideal. Free space debris often occurs in database tables and indexes that are often closed and rebuilt.
In an ideal Oracle table space, each database object is stored in a single scope, and all available free space is concentrated in a large, contiguous scope. In this way, when an object needs additional storage space, it can increase the possibility of obtaining a large enough free space while minimizing the loop call in the space to improve the utilization of free space.