Oracle Database 11g performance optimization strategy-optimizing Table Performance

Oracle Database 11g performance optimization strategy-optimizing Table Performance
Introduction 1-1 Create a database with optimal performance problem description www.2cto.com you realize that when you first create a database, there are some attributes (if enabled) it will have a long-term impact on the performance and availability of tables in the database. Specifically, when creating a database, you need to do the following: force each tablespace created in the database to be managed locally. Local tablespace Management provides better performance than the dictionary management technology that has been discarded. Make sure that the database automatically assigns a default permanent tablespace to each user. This ensures that a default tablespace is automatically allocated when a user is created, instead of the SYSTEM tablespace. You cannot allow users to always create objects in the SYSTEM tablespace, because this will have a negative impact on performance and availability. Make sure that the database automatically assigns a default temporary tablespace to each user. This ensures that a temporary tablespace is automatically allocated when a user is created, instead of a SYSTEM tablespace. You cannot always use the SYSTEM tablespace as the temporary tablespace for sorting operations, because this will have a negative impact on performance and availability. The solution uses a script similar to the following to create a database that complies with reasonable standards, which lays a good foundation for the high performance of the database:

The above create database script provides the following functions to lay a good foundation for DATABASE performance. Use the extent management local clause to define the SYSTEM tablespace as the local management tablespace. This ensures that all tablespaces created in the database are managed locally. In Oracle Database11gR2 or later versions, the extent management dictionary clause has been discarded. To create a user who does not explicitly define the default tablespace, define a default tablespace USERS. This prevents users from allocating SYSTEM tablespace as the default tablespace. Creating users who use SYSTEM as the default tablespace has a negative impact on performance. Define a default temporary tablespace named TEMP for all users. This prevents users from allocating SYSTEM tablespace as the default temporary tablespace. Creating a user who uses SYSTEM as the default temporary tablespace has a negative impact on the performance, because this will cause resource competition in the SYSTEM tablespace. Good performance begins with correct database configuration. The configurations recommended above help create a reliable basic structure for table data.

Properly configuring and creating databases helps ensure the database's excellent performance. Although the function can be modified after the DATABASE is created, the general situation is that a poorly written create database script will have a permanent impact on the DATABASE performance. In the production environment, it is sometimes difficult for the database to have a chance to shut down and reconfigure the configuration that is not properly configured. If possible, it is best to start from creating a database and carefully consider performance issues at every step of the environment creation. When creating a database, you also need to consider the functionality that affects maintainability. Databases that are easy to maintain can run normally for a longer time, which is also an important part of the overall performance. The create database statement in the "solution" section also takes into account the following maintainability features. Create an automatic UNDO tablespace (automatic UNDO management is enabled by setting UNDO_MANAGEMENT and UNDO_TABLESPACE initialization parameters ). This enables Oracle to automatically manage rollback segments, so you do not have to perform regular monitoring and fine-tuning. Put the data file in the corresponding folder according to certain criteria in the environment. This facilitates maintenance and management, so that the database can have better long-term availability and thus achieve better performance. Set the password of the database administrator (DBA) to a non-default value. This can further ensure the security of the database, and will also affect the performance in the long run (if an attacker intrude into the database and delete data, the performance will also be compromised ). Create three groups of online redo log files, two members in each group, and the size is suitable for transaction loading. The size of the redo log file directly affects the switching frequency. If the redo log is switched too frequently, the performance will decrease. You should take some time to confirm that every database is created according to widely recognized standards, which helps to ensure a solid performance Foundation for the database. If you take over a database and want to check the default permanent tablespace settings, you can use a query similar to the following: If you need to modify the default permanent tablespace, follow these steps: to check the settings of the default temporary tablespace, use this query: you can modify the settings of the temporary tablespace as follows: You can use the following query to check the settings of the UNDO tablespace: if you need to modify the undo tablespace, first create a new redo tablespace, and then use the alter system set UNDO_TABLESPACE statement.