Database System Monitor Process (SMON)
DETAILS
Concepts
The System Monitor Process (SMON) is the most critical background process for Oracle Database.It performs critical tasks such as instance recovery,dead transaction recovery and maintenance tasks such as temporary space reclamation, data dictionary cleanup, and undo tablespace management.
SMON performs many database maintenance tasks, including the following:
- Creates and manages the temporary tablespace metadata
- Reclaims space used by orphaned temporary segments
- Maintains the undo tablespace by onlining, offlining, and shrinking the undo segments based on undo space usage statistics
- Cleans up the data dictionary when it is in a transient and inconsistent state
- Maintains the SCN to time mapping table used to support Oracle Flashback features
- In an Oracle RAC database, the SMON process of one instance can perform instance recovery for other instances that have failed.
SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.
Instance Recovery
Instance recovery ensures that the database is in a consistent state after an instance failure.If an instance of an open database fails, either because of a SHUTDOWN ABORT statement or abnormal termination, then the following situations can result:
- Data blocks committed by a transaction are not written to the data files and appear only in the online redo log. These changes must be reapplied to the database.
- The data files contains changes that had not been committed when the instance failed. These changes must be rolled back to ensure transactional consistency.
Instance recovery uses only online redo log files and current online data files to synchronize the data files and ensure that they are consistent.
When Oracle Database Performs Instance Recovery
Whether instance recovery is required depends on the state of the redo threads. A redo thread is marked open in the control file when a database instance opens in read/write mode, and is marked closed when the instance is shut down consistently. If redo threads are marked open in the control file, but no live instances hold the thread enqueues corresponding to these threads, then the database requires instance recovery.
Oracle Database performs instance recovery automatically in the following situations:
- The database opens for the first time after the failure of a single-instance database or all instances of an Oracle RAC database. This form of instance recovery is also called crash recovery. Oracle Database recovers the online redo threads of the terminated instances together.
- Some but not all instances of an Oracle RAC database fail. Instance recovery is performed automatically by a surviving instance in the configuration.
The SMON background process performs instance recovery, applying online redo automatically. No user intervention is required.
Instance Recovery Phases
Cache Recovery
The first phase of instance recovery is called cache recovery or rolling forward, and involves reapplying all of the changes recorded in the online redo log to the data files. Because rollback data is recorded in the online redo log, rolling forward also regenerates the corresponding undo segments.
Rolling forward proceeds through as many online redo log files as necessary to bring the database forward in time. After rolling forward, the data blocks contain all committed changes recorded in the online redo log files. These files could also contain uncommitted changes that were either saved to the data files before the failure, or were recorded in the online redo log and introduced during cache recovery.
Transaction Recovery
After the roll forward, any changes that were not committed must be undone. Oracle Database uses the checkpoint position, which guarantees that every committed change with an SCN lower than the checkpoint SCN is saved on disk. Oracle Database applies undo blocks to roll back uncommitted changes in data blocks that were written before the failure or introduced during cache recovery. This phase is called rolling back or transaction recovery.
Fig 1: Basic Instance Recovery Steps: Rolling Forward and Rolling Back
Oracle® Database Concepts 11g Release 2 (11.2)
Part Number E25789-01
http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CEGCHJGF
Part Number E25789-01
http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CEGCHJGF
Temporary Segment Cleanup
Oracle Database often requires temporary workspace for intermediate stages of SQL statement execution. Typical operations that may require a temporary segment include sorting, hashing, and merging bitmaps. While creating an index, Oracle Database also places index segments into temporary segments and then converts them into permanent segments when the index is complete.
Oracle Database does not create a temporary segment if an operation can be performed in memory. However, if memory use is not possible, then the database automatically allocates a temporary segment on disk.
Temporary segments will also be created for the following operations a well.
- CREATE TABLE AS SELECT
- ALTER INDEX REBUILD
- DROP TABLE
- CREATE SNAPSHOT
- CREATE PARTITION TABLE
When does SMON cleanup temporary segments?
During normal operations, user processes that create temporary segments are responsible for cleanup. If the user process dies before cleaning them up, or the user process receives an error causing the statement to fail, SMON is posted to do the cleanup.
- Sort segments residing in PERMANENT tablespace are cleaned up by SMON after the sort is completed.
- For performance issues, extents in TEMPORARY tablespaces are not released or deallocated once the operation is complete. Instead, the extent is simply marked as available for the next sort operation. SMON cleans up the segments at startup.
SMON also might get tied up cleaning uncommitted transactions though, and be too busy to process requests to grow an existing sort segment. Starting with Oracle 8i, playing around with fast_start_parallel_rollback might workaround that.In addition, actions like CREATE INDEX create a temporary segment for the index, and only convert it to permanent once the index has been created. Also, DROP
Temporary Segments in a Permanent Tablespace
The background process System Monitor (SMON) frees the temporary segments when the statement has been completed.If a large number of sort segments has been created, then SMON may take some time to drop them; this process automatically implies a loss of overall database performance.After SMON has freed up the temporary segment, the space is released for use by other objects.Temporary Segments in a Temporary Tablespace
The background process SMON actually de-allocates the sort segment after the instance has been started and the database has been opened. Thus, after the database has been opened, SMON may be seen to consume large amounts of CPU as it first de-allocates the (extents from the) temporary segment, and after that performs free space coalescing of the free extents created by the temporary segment cleanup. This behavior will be exaggerated if the temporary tablespace, in which the sort segment resides, has inappropriate (small) default NEXT storage parameters.How to identify whether SMON is cleaning up temporary extents ?
Check whether there are a large number of temporary extents that might be being cleaned up by running the following query a few times
SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';
If the count returned by the above query is dropping while SMON is working, it is likely that SMON is performing temp segment cleanup.Effects on Database
- SMON will continually acquire and then release the ST enqueue in exclusive mode. This can cause contention with other processes and lead to
- It should be noted that a normal/immediate shutdown will not complete until all temporary segments have been cleaned up. Shutdown will 'kick' SMON to complete cleanup.
- If you are using TEMPORARY type temporary tablespaces then SMONs cleanup of the segment can be a problem as it will not service sort segment requests while performing cleanup.
- If SMON is busy cleaning up a TEMP segment containing a lot of extents it cannot service 'sort segment requests' from other sessions. Pointing the users at a PERMANENT tablespace as their temporary tablespace can help keep the system running until SMON is free again.
- Starting with Oracle8i, rather than reverting back to a PERMANENT tablespace if SMON is cleaning up an old sort segment at startup, you can potentially drop and recreate the tempfiles of the existing TEMPORARY tablespace. The cleanup should be faster anyway since by rule a TEMPORARY tablespace made of tempfiles need to be LOCALLY MANAGED.You can remove tempfiles from TEMPORARY tablespaces and keep the logical structure empty.
Avoidance
- Do not create temporary tablespaces with small initial and next default storage parameters.
- Use tablespaces of type TEMPORARY. Sort segments in these tablespaces are not cleaned up. This reduces contention on ST enqueue and also reduces CPU usage by SMON **UNLESS** the database is shutdown and restarted.
- Beware of creating large objects with inappropriate (small) extents. If the creation of the object fails, SMON cleans up. Also, dropping such an object will create a lot of cleanup work for the user process.
Force Temp Segment cleanup
DROP_SEGMENTS event could be set set to force the cleanup of temporary segments.
This routine does what SMON does in the background, i.e. drops temporary segments. It is provided as a manual intervention tool which the user may invoke if SMON misses the post and does not get to clean the temp segments for another 2 hours.
level - tablespace number+1. If the value is 2147483647 then temp segments in ALL tablespaces are dropped, otherwise, only
segments in a tablespace whose number is equal to the LEVEL specification are dropped.
level - tablespace number+1. If the value is 2147483647 then temp segments in ALL tablespaces are dropped, otherwise, only
segments in a tablespace whose number is equal to the LEVEL specification are dropped.
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';ion are dropped.
If ts# is 5, an example of dropping the temporary segments in that tablespace would be:
alter session set events 'immediate trace name DROP_SEGMENTS level 6';
If ts# is 5, an example of dropping the temporary segments in that tablespace would be:
alter session set events 'immediate trace name DROP_SEGMENTS level 6';
Note 177334.1 Overview of Temporary Segments
Note 35513.1 Removing 'Stray' TEMPORARY Segments
Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
NOTE:160426.1 - TEMPORARY Tablespaces : Tempfiles or Datafiles ?
Note 102339.1 Temporary Segments: What Happens When a Sort Occurs
Note 1039341.6 Temporary Segments Are Not Being De-Allocated After a Sort
Note 68836.1 How To Efficiently Drop (or Truncate) A Table With Many Extents
Note 47400.1 EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments
Note 132913.1 How To Free Temporary Segment in Temporary Tablespace Dynamically
Note 35513.1 Removing 'Stray' TEMPORARY Segments
Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
NOTE:160426.1 - TEMPORARY Tablespaces : Tempfiles or Datafiles ?
Note 102339.1 Temporary Segments: What Happens When a Sort Occurs
Note 1039341.6 Temporary Segments Are Not Being De-Allocated After a Sort
Note 68836.1 How To Efficiently Drop (or Truncate) A Table With Many Extents
Note 47400.1 EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments
Note 132913.1 How To Free Temporary Segment in Temporary Tablespace Dynamically
Free Space COALESCE
A free extent in a dictionary-managed tablespace is made up of a collection of contiguous free blocks. When new extents are allocated to a tablespace segment, the database uses the free extent which is closest in size to the required extent.When the segments are dropped,their extents are deallocated and marked a free. It is not combined with adjacent free extents to form a larger free extents.The result is fragmentation that makesallocation of larger extents more difficult.
Fragmentation is addressed such that
- When attempting to allocate a new extent for a segment, the database first tries to find a free extent large enough for the new extent.Whenever the database cannot find a free extent that is large enough for the new extent, it coalesces adjacent free extents in the tablespace and looks again.
The SMON background process periodically coalesces neighboring free extents when the PCTINCREASE value for a tablespace is not zero.
Oracle Database automatically and transparently coalesces the free space of a data block only when the following conditions are true:
- An INSERT or UPDATE statement attempts to use a block that contains sufficient free space to contain a new row piece.
- The free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block.
After coalescing, the amount of free space is identical to the amount before the operation, but the space is now contiguous.
Fig 2: Datablock before and after the Coalesce
If you set PCTINCREASE=0, no coalescing of free extents occurs. If you are concerned about the overhead of ongoing coalesce operations of SMON, an alternative is to set PCTINCREASE=0, and periodically coalesce free space manually.
Manually Coalesce any adjacent free extens user this command:
ALTER TABLESPACE TABLESPACENAME COALESCE
SMON wakes itself every 5 minutes and checks for tablespaces with default pctincrease != 0.
SELECT COUNT(*) FROM DBA_FREE_SPACE;
If the count returned is dropping while SMON is working, it is likely that SMON is coalescing free space.
Effects:
- Because SMON acquires the Space Transaction (ST) enqueue in exclusive mode, other processes requiring the enqueue will be blocked.
- THE DATABASE CAN BE SHUTDOWN CLEANLY WITH UN-COALESCED EXTENTS. If SMON is performing the coalesce, a shutdown will NOT undo the work completed so far.
- Use the 'alter tablespace
coalesce' command. This is quicker than SMON, and the work is performed in in fewer space transactions, and therefore makes fewer enqeueue acquisitions. HOWEVER, IF THE COMMAND IS INTERRUPTED, ALL ITS COALESCING WORK WILL BE LOST. - Offlining the tablespace/datafiles containing the extents to be coalesced has NO effect.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing (Doc ID 61997.1)
Note 105120.1 Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
======================================================================
Master Note: Overview of Oracle Background Processes (Doc ID 1503146.1)
NOTE:444149.1 - New Background Processes In 11g
NOTE:1509616.1 - Master Note: Troubleshooting Oracle Background Processes
NOTE:1625912.1 - New Background Processes In 12c
==================================================================
Note 105120.1 Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
======================================================================
Master Note: Overview of Oracle Background Processes (Doc ID 1503146.1)
PURPOSE
The background processes of the Oracle instance manage memory structures, asynchronously perform I/O to write data to a file on a disk, and perform general maintenance tasks.
The purpose of this Document is to provide an overview of Oracle Background Processes. This note will help customers have a better understanding of what they are and the functions of some of the main Oracle Background Processes.
This is a LIVE document. Work is in progress to improve the quality of the content.
SCOPE
This document applies to a single instance environment and looks at a basic typical database configuration.
DETAILS
Concepts
An Oracle database system consists of an Oracle database and an Oracle instance. The database consists of a set of disk files that store user data and metadata, while the Oracle/database instance contains the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work.
A process is a mechanism in an operating system that can run a series of steps. The mechanism depends on the operating system.
For example, on Linux an Oracle background process is a Linux process.
On Windows, an Oracle background process is a thread of execution within a process.
A process is a mechanism in an operating system that can run a series of steps. The mechanism depends on the operating system.
For example, on Linux an Oracle background process is a Linux process.
On Windows, an Oracle background process is a thread of execution within a process.
Multiple-process Oracle (also called multiuser Oracle) uses several processes to run different parts of the Oracle Database code and additional processes for the users. Each process in a database instance performs a specific job. By dividing the work of the database and applications into several processes, multiple users and applications can connect to an instance simultaneously while the system gives good performance.
A database instance contains or interacts with the following types of processes:
- Client processes run the application or Oracle tool code.
- Oracle processes run the Oracle database code. Oracle processes including the following subtypes:
- Background processes perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
- Server processes perform work based on a client request.
- Slave processes perform additional tasks for a background or server process.
Oracle® Database Administrator's Guide (11.2)
Part Number E25494-02
5 Managing Processes
About Oracle Database Background Processes
https://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc.htm#ADMIN11000
Part Number E25494-02
5 Managing Processes
About Oracle Database Background Processes
https://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc.htm#ADMIN11000
Overview of Background Processes
The background processes of the Oracle instance manage memory structures, asynchronously perform I/O to write data to a file on a disk, and perform general maintenance tasks. The background processes consolidate functions that would otherwise be handled by multiple Oracle Database programs running for each user process. They monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.
Each background process has a separate task, but works with the other processes.
For example, the LGWR process writes data from the redo log buffer to the online redo log. When a filled log file is ready to be archived, LGWR signals another process to archive the file.
Oracle Database creates background processes automatically when a database instance starts. The background processes that are present depend on the features and options that are being used in the database. The startup and shutdown of background processes are normally seen logged in the database alert.log.
Each background process has a separate task, but works with the other processes.
For example, the LGWR process writes data from the redo log buffer to the online redo log. When a filled log file is ready to be archived, LGWR signals another process to archive the file.
Oracle Database creates background processes automatically when a database instance starts. The background processes that are present depend on the features and options that are being used in the database. The startup and shutdown of background processes are normally seen logged in the database alert.log.
The following query can be used to list the background processes running on your database:
SELECT PNAME
FROM V$PROCESS
WHERE PNAME IS NOT NULL
ORDER BY PNAME;
FROM V$PROCESS
WHERE PNAME IS NOT NULL
ORDER BY PNAME;
Following query could be used in 12c as all the shared BG process belongs ti con_id=0
SQL>select PNAME,con_id,spid from V$process
where con_id=0;
where con_id=0;
There are
- Mandatory Background Processes
- Optional Background Processes
- Slave Processes
See Oracle Database Reference for a complete list of Oracle Database background processes.
Mandatory Background Processes
The mandatory background processes are present in all typical database configurations. These processes run by default in a database instance started with a minimally configured initialization parameter file.
| Name | Expanded Name | Description |
|---|---|---|
| PMON | Process Monitor | Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally |
| SMON | System Monitor | Performs critical tasks such as crash recovery when the instance is started following a failure, dead transaction recovery, and maintenance tasks such as temporary space reclamation, data dictionary cleanup, and undo tablespace management |
| DBWn | Database Writer | Writes modified blocks from the database buffer cache to the data files |
| LGWR | Log Writer | Writes redo entries to the online redo log |
| CKPT | Checkpoint | Signals DBWn at checkpoints and updates all the data files and control files of the database to indicate the most recent checkpoint |
| MMON | Manageability Monitor | Performs many tasks related to manageability, including taking Automatic Workload Repository snapshots and performing Automatic Database Diagnostic Monitor analysis |
| MMNL | Manageability Monitor Lite | Performs tasks relating to manageability, including active session history sampling and metrics computation |
| RECO | Recoverer Process | Resolves distributed transactions that are pending because of a network or system failure in a distributed database |
Optional Background Processes
An optional background process is any background process not defined as mandatory. This would include background processes that are specific to tasks or features installed in a database. For example, the ARCn process only exists when the database is in ARCHIVELOG mode and automatic archiving is enabled. Also, there are many background processes only needed to support Oracle Automatic Storage Management (ASM).
| Name | Expanded Name | Description |
|---|---|---|
| ARCn | Archiver | Copies the redo log files to archival storage when they are full or an online redo log switch occurs |
| CJQ0 | Job Queue Coordinator | Selects jobs that need to be run from the data dictionary and spawns job queue slave processes (Jnnn) to run the jobs |
| SMCO | Space Management Coordinator | Coordinates the execution of various space management tasks |
Slave Processes
Slave processes are background processes that perform work on behalf of other processes. This section describes some slave processes used by Oracle Database.
| Name | Expanded Name | Description |
|---|---|---|
| Innn | Disk and Tape I/O Slave Process | Serves as an I/O slave process spawned on behalf of DBWR, LGWR, or an RMAN backup session |
| Pnnn | Parallel Query Slave Process | Perform parallel execution of a SQL statement (query, DML, or DDL) |
| Jnnn | Job Queue Slave Process | Executes jobs assigned by the job coordinator |
V$BGPROCESS
V$BGPROCESS displays information about the background processes.
| Column | Datatype | Description |
|---|---|---|
PADDR | RAW(4 | 8) | Address of the process state object |
PSERIAL# | NUMBER | Process state object serial number |
NAME | VARCHAR2(5) | Name of this background process |
DESCRIPTION | VARCHAR2(64) | Description of the background process |
ERROR | NUMBER | Error encountered |
Specifying the Maximum Number of Processes
The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle Database concurrently.
The value of this parameter must be a minimum of one for each background process plus one for each user process. The number of background processes will vary according the database features that you are using. If you are using ASM, Advanced Queuing or the file mapping feature, then you will have additional background processes.
The value of this parameter must be a minimum of one for each background process plus one for each user process. The number of background processes will vary according the database features that you are using. If you are using ASM, Advanced Queuing or the file mapping feature, then you will have additional background processes.
For example, if you plan on running about 50 user processes, a good estimate would be to set the PROCESSES initialization parameter to at least 70.
New Background Processes
It is likely that there will be new background processes with new Oracle releases and as new features are implemented.
The following highlight new background processes in the respective Oracle releases:
Note:1625912.1 - New Background Processes In 12c
REFERENCES
NOTE:268197.1 - New Background Processes In 10gNOTE:444149.1 - New Background Processes In 11g
NOTE:1509616.1 - Master Note: Troubleshooting Oracle Background Processes
NOTE:1625912.1 - New Background Processes In 12c
==================================================================
Comments
Post a Comment
Oracle DBA Information