Oracle's easiest two processes to overlook Smon good arch____oracle

Article from: Also can not find the source address where, in short, thank the author of the sharing

two important processes that are most overlooked in Oracle

There are two processes in the Oracle database that are very small, but they are very powerful. Due to its relatively small and often overlooked by the database administrator. I remind you this time, do not ignore the role of these two processes. If the use of good, will reduce the system administrator a lot of work.

first, system monitoring process Smon

This process can be summed up in the Oracle database with one sentence, that is, the person is big. It is not responsible for a lot of content, but for data security and the performance of the database has a key role. As the data in the table space is constantly set up, deleted, updated, and so on, it is inevitable that fragmentation will occur in the tablespace. Due to the presence of these fragments, the performance of the database will gradually decrease. Now that the system is monitoring the smon of processes, these fragments can be resolved. The Smon process merges the free fragments of each table space together, making the database system easier to allocate. Thus improving the performance of the database. In addition, when the database is running, it will fail due to power outages or other reasons. This is because the dirty cache block in the data cache has not yet been written to the data file, resulting in the loss of data. When the database is started, the system monitoring process Smon automatically reads the redo log file and restores the database the next time the routine is started. That is, the commit is written to the data file (data that has been written to the log file without being written to the data file), and the uncommitted transaction action is rolled back. Visible, the Smon process is a small but very important role in the Oracle database.

In the management of this process, I think the main need to pay attention to two problems. One is the timing of its initiation. Normally, when a routine restarts, the system monitoring process is started. The process is then periodically awakened by the system during this routine run, and then it checks to see if there is work to be done. Most importantly, when needed, the database administrator can start the Smon system monitoring process with other processes to accomplish certain tasks.

Second, you need to pay attention to the impact of table space configuration on this process. In table space management, there is a parameter called Pctincrease. If you set this parameter to 0, the Smon system monitoring process will have a compromised effect on this tablespace. When set to 0, the Smon process does not defragment and merge idle fragments in this tablespace. In other words, it requires the database administrator to export the data to the import and other manual operations, can solve the fragmentation of the table space problem. This obviously increases the workload of the database administrator. To this end, I set up, unless there is a special need, do not set this parameter to 0. Let the Smon process automatically manage the fragmentation in the tablespace and automatically merge the free fragments in the table space. However, if the table space parameter is set to 0, it will not affect other uses of the system's monitoring process, such as the recovery of data when the routine shuts down gracefully. That is, even if this parameter is set to 0, it can still use the records in the Redo log file to recover the relevant data when necessary.

II. Archiving Process Arch

There are two modes of archiving and not archiving in redo log file management. When the log is switched, if the original log file is not archived and directly overwritten, it is called a non-archive mode. Instead, the target log file is archived before the next log file is written, which is called the archive mode. Archive Process Arch is responsible for copying the already filled redo log file to the archive log file after the redo log file is switched to prevent overwriting the redo log file when looping. So the arch process will only be started when the database is running in archive mode. In any mode of operation, redo log files are recycled. So when the LGWR process is in log switching and needs to use the next log file, the database is temporarily suspended and the target log file is archived. The database will not return to normal until the target redo log file is archived. Therefore, the operation of archived logs can sometimes affect the performance of the database, especially when it is necessary to make frequent mass data changes.

So what can you do to improve the efficiency of your archiving operations? The following recommendations are available to the database administrator for reference.

One is to increase the number of archive processes. By default, a routine will only start an archive process arch. When the arch process is archiving a redo log file, no other process can access the Redo log file. If you are in an Oracle database, you can start more than one archive process arch if you need to. In an Oracle database, the process of starting multiple archive processes is divided into manual and automatic two ways. To improve the speed of redo log file archiving, the LGWR process automatically starts multiple archive processes based on timing when the user process takes longer to wait. Up to 10 archive processes can be started in an Oracle database. Additionally, if the database administrator is deploying the database, estimating that the log archive job affects the performance of the database, you can manually start multiple archive processes. This is determined by the initialization parameter log_archive_max_processes. You can set this parameter to a value greater than 1 (note that no more than 9 archive processes are available). In this case, the database starts multiple archive processes when the routines are created. However, the author still prefers to let the database system to automate the process of management. The database administrator should not interfere. Also note the difference between the number of this arch archive process and the number of DBWR processes. By default, there is only one DBWR process. In order to improve the performance of the database, you can increase the number of this DBWR process according to the situation. However, the increase is limited by the number of CPUs, that is, a DBWR process needs to use a separate CPU. If you want to start three DBWR processes, you must use 3 CPU processors. For the arch archiving process, there is no such limit. Even if there is only one CPU processor, it can start up to three or more arch processes.

The second is to increase the redo log file to extend the time interval at which the archive log process starts. Typically, this arch archive log process is triggered only if the current redo log file is full and requires log switching. So if the redo file is larger, the time interval of the log switch will be extended. The Arch archive log process will have a longer startup time interval. So, by adjusting the size of the redo log file, you can extend the time interval at which the archive process starts. This reduces the negative impact on database performance as the archiving process starts.

Third, in the process of database initialization, you may need to import a large amount of data. At this time, the data in the database in a large number of inserts, deletes, updates and other operations, resulting in redo log file switching frequently. This can cause the database to frequently start the arch archive process. There is an invisible chain between a large number of database updates, redo log files (LGWR processes), and archive redo log files (ARCH) processes. Because of the "butterfly effect", thus reducing the performance of the database. In order to improve the performance of the database, it is necessary to cut off the chain. If you can in a large number of data import, update, delete, not log files inserted records, or temporarily increase the space for redo log files. In this way, you can avoid redo log switching or extend the time interval for redo log switching When you do these things. Thus the Arch archive log process can also avoid or prolong its time interval, thereby improving the performance of the database. Once the database initialization is complete, restore it. These temporary adjustments, while cumbersome, can improve the performance of the database. To this end, I think it is worthwhile.

This shows that the two processes in the Oracle database have a limited impact, but they are closely related to the performance of the database. In day-to-day operations, the flexibility to use the characteristics of this two process can increase the speed of some operations. This is a much simpler way to improve database performance than by optimizing SQL statements. To this end, I suggest that the database administrator, these two processes, although small, but its role can not be ignored. Database administrators need to focus on these two processes.