Two important processes most easily overlooked in Oracle

There are two very small processes in the Oracle database, but their role is very huge. Because it is relatively small, it is often ignored by the database administrator. I will give it this time

There are two very small processes in the Oracle database, but their role is very huge. Because it is relatively small, it is often ignored by the database administrator. I will give it this time

There are two very small processes in the Oracle database, but their role is very huge. Because it is relatively small, it is often ignored by the database administrator. This time I will remind you not to ignore the role of these two processes. If it is well utilized, it will reduce the workload of the system administrator.

1. system monitoring process SMON

This process can be summarized in one sentence for the Oracle database, that is, it is a huge crowd. It is not responsible for a lot of content, but it plays a key role in data security and database performance. For example, as data in a tablespace is constantly created, deleted, and updated, fragments are generated in the tablespace. Due to the existence of these fragments, the database performance will gradually decrease. Now the role of the system monitoring process SMON can solve these fragments. The SMON process combines the free fragments of each tablespace, making it easier for the database system to allocate shards. This improves the database performance. In addition, faults may occur due to power failure or other reasons during database operation. At this time, the dirty cache blocks in the data cache have not been written into the data file, resulting in data loss. When the database is started, the system monitoring process SMON will automatically read the redo log file and restore the database during the next startup routine. That is to say, to write committed transactions to a data file (data that has been written to a log file but not written to a data file) and to roll back uncommitted transactions. It can be seen that the SMON process is a relatively small but very important role in the Oracle database.

When managing this process, I think we need to pay attention to two problems. First, the startup time. Generally, the system monitoring process is started when the routine restarts. Then, during the running of this routine, the process will also be regularly awakened by the system, and then it will check whether there is work to be done. Most importantly, when necessary, the database administrator can start the SMON system monitoring process through other processes to complete some specific work.

Second, pay attention to the impact of table space configuration on this process. In table space management, a parameter is PCTINCREASE. If this parameter is set to 0, the role of the SMON system monitoring process to this tablespace will be compromised. When it is set to 0, the SMON process will not sort and merge idle fragments in the tablespace. That is to say, the database administrator needs to export and import data to solve the tablespace fragmentation problem. Obviously, this will increase the workload of the database administrator. For this reason, I have set this parameter to 0 unless otherwise necessary. Let the SMON process automatically manage fragments in the table space and automatically merge idle fragments in the tablespace. However, if this parameter is set to 0 for a tablespace, it does not affect other purposes of the system monitoring process. For example, it does not affect data recovery when the routine is abnormally closed. That is, even if this parameter is set to 0, you can use the record in the redo log file to restore relevant data when necessary.