Considerations for modifying Oracle Database initialization parameters

This initialization parameter needs to be modified only when the performance of large applications or databases is significantly reduced. From this perspective, the adjustment of initialization parameters is an advanced application in the Oracle database. However, this is what an Oracle database administrator must master. In order to better adjust the database initialization parameters, the administrator should first understand some common content of initialization parameter adjustment before learning the purpose of each initialization parameter and the adjustment rules. Specifically, there are the following aspects.

1. You need to know which parameters will take effect without restarting.

In Oracle databases, parameters can be roughly divided into two types based on the effective time. One is a parameter that can be modified or take effect only after being restarted, and the other is a parameter that takes effect after modification without being restarted. Generally, database administrators prefer the second type of parameter, that is, the parameter that takes effect immediately after modification and does not need to be restarted. Why? Because the database server cannot be restarted at will after it is put into production and use. If the modification takes effect only after a restart, or the parameter needs to be modified after a restart, the user's access needs to be interrupted during parameter adjustment, which will increase the downtime. This is a big blow to database deployment. The Database Administrator aims to minimize the downtime from the perspective of stability and availability. At this time, manual downtime increases, apparently in conflict with this goal. Therefore, the database administrator needs to know which parameters can be modified and take effect without restarting, and which parameters must be restarted. This is what the database administrator needs to know before adjusting the parameters. On the one hand, administrators can evaluate the loss of downtime and adjust the possible benefits of parameters based on this feature. If the benefit is greater than the loss, it is necessary to adjust the initialization parameters. The second is to determine the time for parameter adjustment. When no restart is required, that is, when the adjustment parameter does not cause downtime, the adjustment time of the parameter is relatively flexible and limited. However, if you need to adjust the parameters that can take effect or be modified after the restart, You need to select an appropriate time. If you need to select a time later than, there are usually not many database users in this time period. Of course, this time may vary from night to night. In short, you should select a database with as few users as possible. In this way, the loss caused by database downtime can be minimized.

Which parameters can be modified without restarting? In fact, the database administrator only needs to query the dynamic view to know which parameters can be changed without shutting down or restarting. For example, the value of the initialization parameter is stored in the dynamic v $ parameter. In general, you can add the condition issys_modifiable <> 'false' or isses_modifiable <> 'false' to the query statement (note that the relationship between them is "or" rather than "and ), the final queried parameter is the initialization parameter that can be set without shutting down or restarting the database. These parameters are also part of the initialization parameters that can be changed by using the alter system and alter session commands. In other words, you can use the preceding two commands to reset parameters that can be modified without shutting down or restarting the database. However, it does not mean that the initialization parameters can be changed without restarting or shutting down the database. Simply put, these two commands can be used to change the parameters. Some need to restart or shut down the database, while others do not.

2. determine the parameters to be modified according to the 20/80 principle.

There are hundreds of initialization parameters in the Oracle database. For whatever purpose, it is unrealistic to adjust these parameters one by one. An excellent database administrator only needs to know the modification of a few parameters. Most database maintenance tasks can be completed by mastering the modification methods and principles of these parameters and taking precautions. This is the role of the 20/80 rule. That is to say, you only need to master 20% of the parameters (in fact, it is far from this ratio), you can complete 80% of the tasks (in fact, far higher than this ratio ). That is to say, the 10/90 rule may be more suitable for this Oracle initialization parameter. In short, I want to say that you only need to know a few of the many initialization parameters. Even if you need to adjust initialization parameters to improve database performance or for other purposes, that is, in these few initialization parameters. For other parameters, the database administrator only needs to understand their purpose, rather than how to adjust these parameters. Because there is almost no chance.

So what are the important parameters? The main parameters are as follows. The most important parameter is DB_CACHE_SIZE. This is a parameter used to adjust the database cache size. When necessary, adjusting the size of this parameter can increase the data cache hit rate and significantly improve the database performance. The second parameter is DB_BLOCK_SIZE. This parameter is used to specify the default block size when the database is created. If the block settings are relatively small, it may be a line-chain phenomenon, thus reducing the database performance; it may also increase the database fragmentation, waste of tablespace storage space and reduce database query performance. Therefore, we sometimes need to adjust the block size as needed to improve the database performance. The third parameter is SHARED_POOL_SIZE. This parameter specifies the memory allocated in the SGA for the data dictionary cache and shared SQL statements. Simply put, you can reasonably configure this parameter to share the same SQL statement. Adjusting this parameter is also a common method to optimize database performance. The fourth parameter is the SGA_MAX_SIZE parameter, which specifies the maximum memory size that SGA can dynamically increase. The SQL Server database also has similar parameters. Generally, if multiple application services are deployed on the same database server, you must configure this parameter properly to avoid contention for memory among multiple application services. If this parameter is not set properly, the Service may be stopped for another application service due to insufficient memory. The fifth parameter is LOG_CHECKPOINT_INTERVAL. This parameter is mainly used to set the checkpoint frequency. At each checkpoint, the database system writes the execution data to write all the dirty blocks (the data has been modified and not saved to the hard disk) to the corresponding data file in the database. By default, if 1/4 of the data buffers in the database cache are dirty buffers, the database system automatically executes the check point. Another mandatory principle is that checkpoints are also executed during log switching. In some specific situations, such as creating a data warehouse, you need to adjust this parameter to meet the needs of specific scenarios.

These five parameters are the most frequent initialization parameters I have encountered over the past few years. Basically, database maintenance and performance tuning are based on these parameters. For this reason, I suggest that database administrators can start from these parameters when learning how to adjust database initialization parameters. In other words, these parameters must be mastered by the database administrator. It is not just a simple understanding, but a thorough understanding of it. Only in this way can you determine whether to make changes and handle problems that may occur during the change. For some other parameters, the author believes that the database administrator only needs to understand its basic purpose. I may have missed some points when summing up these parameters. You are welcome to add them. If you have the opportunity in the future, I will also give you a detailed description of the parameters to be adjusted and the problems that may be encountered during the adjustment process. If you are interested in this topic, you can follow the Post article of the author.

3. Use SPFILE to dynamically modify parameters.

In Versions later than Oracle10G, the database administrator is also allowed to use a SPFILE to store the dynamic modification of instance parameters. In versions earlier than 10 Gb, dynamic modification parameters will be lost after the database is restarted unless the parameters are manually added to the initial parameter file. This is similar to setting environment variables. However, in Versions later than 10 Gbit/s, this has been greatly improved. If the SPFILE mechanism is enabled, the system will enable a server parameter file. When some parameters are dynamically changed in the memory, these changes are recorded in the server parameter file. At the next restart, the server will read the values in the parameter files of the server to initialize the database system. That is to say, some Dynamically Modified parameters can also take effect permanently from now on. This is undoubtedly a good news for database administrators.

From the above analysis, we can see that the adjustment of this database parameter is still complicated. The author believes that the administrator needs to understand these commonalities before learning how to adjust this parameter. That is, the time limit for parameter modification, the parameters most likely to be adjusted, and how to make dynamic parameters take effect forever. These are the basis for parameter optimization.

1. Three criteria for creating an Oracle database index
2. Oracle retrieval data consistency and transaction recovery
3. Oracle performance optimization achieved through Partitioning technology
4. A simple view on Oracle concurrent processing mechanism
5. Three methods to simplify the management of Oracle table columns