Background processes | Oracle DBA Tutorial pdf

Background processes

Database Writer (DBWn)
Oracle marks buffers in memory as dirty when the data they contain is changed. DBWn writes content of dirty buffer to data file when – a server process can’t find a clean buffer after searching set threshold of buffers, a checkpoint occurs, change table space to read only/offline/backup mode, drop/truncate table etc.
Log Writer (LGWR)
It is responsible to redo log buffer management. Almost all activities against the database are tracked in the online redo logs. As transaction are initiated and eventually committed or rolled back, a record of this activity is written to these log files.
Log writer writes to redo logs sequentially.
Checkpoint (CKPT)
Helps to reduce time required for instance recovery. A checkpoint is an event that flushes modified data from buffer cache to disk and updates control file and data files. The CKPT process updates header of data files and control files and DBWn writes actual blocks to file. Checkpoint occurs automatically when an online redo log file fills (log switch).
System Monitor (SMON)
At startup, SMON’s job is to ensure that all the database files are consistent and perform recovery if required. There is also an assortment of other cleanup activities that may need to be done, which are SMON’s responsibility. The SMON process by itself checks every so often to see whether there are any tasks waiting for its attention.
Process Monitor (PMON)
Cleans up failed user processes and frees all resources used by failed process.
Archiver (ARCn)
It automatically saves copies of redo logs in a DBA specified storage location when media recovery is enabled.
Recover (RECO)
Is used with distributed transaction to resolve failure.
Lock (LCKn)
It is used in RAC.
Manageability monitor (MMON)
From 10g, it makes snapshots of the database’s health (statistics) and stores this information in the automatic workload repository.
Processing SQL
The following steps show how Oracle processes SQL
1. Statement is passed to Oracle for processing
2. Before it is placed in the library cache, a hash value is computed that represent s a number of characteristics of the SQL.
3. Oracle compares the computed hash value against those values in a hash table where it maintains for SQL statements already in the cache.
4. If a match is found, the new SQL statement is thrown away and the one sitting in the cache is executed on its behalf.
5. In no match is found, further processing is done on the new SQL statement, an entry is made in the library cache hash table for newly arrived code, and it is placed in the library cache.
6. There are 3 stages of SQL processing – parse, execute and fetch
=> During parsing, Oracle server checks the syntax and validates
table, column names against data dictionary
=> Determines whether user has privilege to execute the statement
=> Determines optimal execution plans for statement
=> Finds a shared SQL area for the statement
=> In execution stage, for UPDATE and DELETE statement, Oracle locks the affected rows, looks for data blocks in DB buffer cache, if found, executes becomes faster, if not then Oracle has to read from physical data files to buffer cache. For SELECT and INSERT statements, locking is not necessary.
=> During fetch operation, rows are fetched to user process.