Oracle Database initialization parameter changes to note

Jun 26 posted in Oracle Database (RSS), 134 views.

Generally only in large-scale application or performance data, according to the Treasury decreased only need to change the initialization parameters. So from this perspective, the adjustment of parameters to initialize Oracle database is a high-level applications. But this is also an Oracle database administrators need to master the content. In order to better adjust the database initialization parameters, I believe that administrators in understanding the use of all initialization parameters and adjust the rules, need to be aware of some initialization parameters to adjust the content of a number of similarities. Specifically, in the following areas.
First, do not need to know what parameters to restart to take effect.

In the Oracle database, according to the time the entry into force of different parameters can be roughly divided into two categories. One is to be able to restart after the entry into force of amendments to, or parameters; another type of modification is not required to re-start after the entry into force of that parameter. Generally speaking, database administrators prefer the second type of parameters, that is, do not need to restart the revised parameters take effect immediately. Why? Because the use of the database server into production after the restart can not be arbitrary. If modifications need to restart in order to take effect, or need to modify the parameters before we can start, then adjust the parameters in the course of a clear need to interrupt the user's visit will increase downtime. This database deployment, the combat will be very large. Because, from the stability and availability, the database administrator is to minimize downtime for the goal. At this point the increase in man-made and downtime, it is clear there is a conflict with this goal. Therefore, the database administrator will need to know which parameter is not required to re-start can be modified with the entry into force; and what parameters must be before they can restart. This is a database administrator for further adjustment of parameters need to know before the first content. On the one hand, according to the characteristics of administrators to assess the loss of downtime and adjustment parameters of the potential benefits. If losses exceed gains, then there is still need to adjust the initialization parameters. The second is to determine the parameters of the adjustment time. Which do not need to restart, that is, adjust the parameters would not result in the case of downtime, then the adjustment of parameters is relatively flexible, few restrictions. However, if the need to adjust the entry into force will be able to restart or modify the parameters, then need to select an appropriate time to. If you need to choose after the 0:00 time, this time using the database is often not a lot of users. Of course, all this time may be different for each Qiye . The use of a word is to select a database when the user as little as possible. If so will it be possible to the maximum extent possible to reduce the losses caused by database downtime.

What are the parameters then restart not required for the purposes can be amended? In fact, database administrators only need to query the dynamic view, that is, to know which parameters can not shut down or restart you can change. Such as dynamic as in the v $ parameter, the stored value of the initialization parameters. In general, you can add query conditions issys_modifiable <> 'FALSE' or isses_modifiable <> 'FALSE' (note that they are or the relationship between, rather than and relations), and finally out the parameters of inquiry is not to close or re - can start the database initialization parameter settings. At the same time, these parameters also can be used with the ALTER SYSTEM command ALTER SESSION to change some initialization parameters. In other words, do not shut down or restart the database on the parameters can modify the above two commands can be used to re-set. But not to say that the use of these two orders can not restart or shut down the database initialization parameters to change the situation. Simply put, is that we can use these two commands to change the parameters, and some need to restart or shut down the database, while others do not.

Second, under the 20/80 principle to determine the need to change the parameters.

Oracle database initialization parameters are more than a hundred. No matter what the purpose of adjustment to each of these parameters are often unrealistic. A good database administrators only need a few parameters which can be modified. Often have several parameters modification method, in principle, to pay attention to matters to be completed on most of the database maintenance tasks. This is the law of 20/80 in his role. In other words, only 20% of the required parameters (in fact, is far less than the ratio), you can complete 80% of the task (in fact much higher than the ratio). In other words, the 10/90 rule of the initialization parameters for Oracle, may be more applicable. In short I want to say is that only need to master the large number of initialization parameters can be few. If required, by adjusting the initialization parameter to increase database performance, or other purposes, that is, a small number of these initialization parameters. For other parameters, database administrators only need to be aware of its use, without the need to understand how to adjust these parameters. Because the probability of experiencing little can be said.

These important parameters which in the end it? Mainly in the following parameters. One of the most important parameter is the DB_CACHE_SIZE. This is a database used to adjust the parameters of the cache size. When necessary, adjust the size of this parameter, you can improve data cache hit rate, thus substantially improved the performance of the database. The second parameter is DB_BLOCK_SIZE. This parameter is used to specify the database by default when in the establishment of the block size. If the block set is relatively small, there may occur the phenomenon of chain line, thereby reducing the performance of the database; also may increase the database of debris, waste table space, storage space and reduce the performance of database queries and so on. Sometimes there needs to be adjusted in accordance with the block size to improve the performance of the database. The third parameter is SHARED_POOL_SIZE parameters. This parameter is mainly for those data dictionary cache and shared SQL statement specified in the SGA allocated in memory. Simple, the rational distribution of the parameters, can share the same SQL statement. Adjust the parameters to optimize database performance is also a common means. SGA_MAX_SIZE parameters for the fourth parameter, this parameter specifies the dynamic growth of the SGA may be the biggest memory. SQLServer database in a similar parameters. Generally speaking, in the same database server is deployed in a number of applications will often need to configure the parameters of a reasonable number of applications to avoid contention between the memory. If the inappropriate parameter settings, then sometimes will lead to another kind of applications do not have enough memory as a result of the cessation of services. The fifth parameter is LOG_CHECKPOINT_INTERVAL. This parameter is mainly used to set the frequency of checkpoints. Each checkpoint, the database system for the implementation of data to write all dirty block (has been modified not saved to your hard disk of the data) to the database to write the corresponding data file. By default, if the database cache in the 1 / 4 of the data buffer is a dirty buffer, then the database system will automatically check points. In addition the principle of a mandatory switch is in the log, it will also implement the checkpoint. In some specific circumstances, such as the creation of data warehouse, the parameters need to be adjusted to meet the needs of specific occasions. Parameters of the five writers in recent years over the work of the most frequently encountered initialization parameter. Basically, the database maintenance and performance tuning is surrounded by a few parameters to the. To this end I suggested that a database administrator that the database initialization parameters in the learning skills of the adjustment, you can start from a number of parameters. In other words, these parameters are the database administrator must be available. Understanding of not only simple, but the need to thoroughly understand it. Only then, faced with the situation, we can then judge whether there is a need to make changes, as well as respond to changes in the course of the problems that may arise. For other parameters, I believe that a database administrator needs only to understand their basic use. In conclusion, perhaps the author of these parameters, some miss the place, welcome you to supplement. After a chance, I would like all details of the circumstances under which the adjustment of any parameter; as well as in the adjustment process may be trouble. If you interested in this topic may be concerned about the author behind the article.

Third, the use of SPFILE to dynamically modify the parameters.

In a future version Oracle10G, also allows the database administrator to store the SPFILE using the example of a dynamic parameter modification. In previous versions of 10G, unless the parameters manually added to the initial parameters of the document, or dynamic changes to parameters in the database will be lost after restart. This environment variable is somewhat similar to the set. However, in subsequent versions of 10G, in this respect a great improvement. SPFILE mechanisms such as open, then the system will enable a server parameter file, the dynamic changes in memory some of the parameters at the same time these changes will be recorded in the server parameter file. Wait until the next restart, the server will read like a server parameter file value, used to initialize the database system. In other words, from now on a number of dynamic parameters can be modified a permanent effect. This is a database administrator is no doubt a good news.

From the above analysis we can see that the database parameters of the adjustment is a relatively complex task. I believe that administrators in learning how to adjust the parameters until the need to understand the general content of the above. That is, modify the parameters of the time constraints, most likely need to adjust the parameters and how to make the entry into force of the dynamic parameters and so on forever. These are good parameters to optimize the basis of the work.