Tuesday, November 10, 2009

Oracle Processes



Oracle server processes perform all the database operations such as inserting and deleting data. The oracle processes working with the SGA (oracle memory structure) manage the database.
There are two types of Oracle process
  • User process - Responsible for running the application that connects to the database
  • Oracle server process - Perform oracle tasks that manage the database.
There are a number of server processes that could be running , Windows will only have one process called Oracle, this process will have one thread for each of the below processes.

Process Monitor
PMON
Responsible for cleaning up after abnormally terminated connections.
Responsible for monitoring other server processes and restarting them if necessary
Registers the instance with the listener dynamically (dynamic service registration).
Restarts failed server processes and dispatcher processes

System Monitor
SMON
Temporary space cleanup
Crash recovery apon restart
Coalescing free space
Recovering transactions active against unavailable files
Instance recovery of failed node in OPS (Oracle parallel server)
Cleans up OJB$ (Low Level data dictionary)
Shrinks rollback segments
Offline's rollback segments

Other processes call the SMON process when required.
Distributed database recovery
RECO
Recovers transactions that are left in a prepared state because of a crash or loss of connection during a two-phase commit.
Checkpoint process
CKPT
The checkpoint process is charged with instructing the database block buffer writers to write the database buffer cache to disk, it then updates the data file headers and control file to indicate when the checkpoint was performed. There is a relationship with checkpoints and recovery time, the more checkpointing the less recovery time is need when a crash occurs.
The ckpt process does not do the checkpoint but assists with the checkpointing process by updating the file headers of the data files.
A checkpointing process involves the following:
  • Flushing the redo log buffers to the redo log files
  • Writing a checkpoint record to the redo log file
  • Flushing the database log buffers to the data files
  • Updating the data file headers and control files after the checkpoint completes
Database block writer
DBWn
Responsible for writing dirty blocks to disk when free space within the database buffer cache is low, it writes the dirty blocks from the buffer cache out to the disk. It uses the LRU (Least Recently Used) algorithm which retains data in the memory based on how long it has been since someone asked for that data. The database buffer cache is flushed to disk
  • when the database issues a checkpoint
  • when a server process can't find a clean reusable buffer after checking a threshold number of buffers
  • every 3 seconds
  • users process has searched to long for a free buffer when reading a buffer into the buffer cache
  • Instance is shutdown
  • tablespace is put in backup mode or offline
  • segment is dropped
If you have multiple CPU's then it is advised to run multiple database writers. Use the DB_WRITER_PROCESSES parameter to increase the number of database writers, the instance has to be rebooted.
Log writer
LGWR
Responsible for flushing to disk the contents of the redo log buffer located in the SGA. Both committed and uncommitted changes are written to the redo log buffer. The redo log buffer is flushed to disk before the data blocks are written to disk. The redo log buffer is flushed to disk
  • every 3 seconds
  • whenever a user commits a transaction
  • when the redo log buffer is a third full or contains 1 Mb of buffered data
  • before the DBWn process writes when a checkpoint occurs
Archive process
ARCn
Used when the database is in archive-mode, it copies the online redo log file to another location when LGWR fills up, these log files would be used to perform media recovery. There can be a maximum of ten archive processes running ARC0-ARC9. The LOG_ARCHIVE_MAX_PROCESSES parameter determines how many archive processes will be started (default is 1).
Manageability Monitor
MMON
Collects statistics to help the database manage itself. The MMON process collects the AWR (automatic workload repository) snapshot information which is used by the ADDM (automatic database diagnostic monitor), also MMON issues alerts when database thresholds are exceeded.
Manageability Monitor Light
MMNL
The process flushes ASH information to disk when the buffer is full, it also captures session history and database metrics.
Memory Manager
MMAN
Uses the the metrics collected to determine the ideal distribution of memory within oracle. It constantly monitors the database and adjusts the memory allocations according to workloads.
Job Queue Coordination
CJQ0
Used to schedule and run user jobs. It spawns job queue slave processes (J000-J999) which actually run the job.
Job Queue Process
J000-J999
These processes are what actually run the schedule jobs requested by CJQ0.
File Mapping Monitor
FMON
Maps files to immediate storage layers and physical devices. Results are normally kept in the DBMS_STORAGE_MAP view. Generally the 3rd party LVM (logical volume manager) supplier will supply a driver to map to.
Recovery Writer
RVWR
This process is started when you implement flashback logging, it logs the before image (taken from the flashback buffers) of an oracle block before it is changed, this is written to the flashback log files.
Change Tracking Writer
CTWR
This process tracks any data blocks that have changed which then RMAN can use to speed up backups as it will no longer need to read the entire data file to see what has changed.
Queue Monitor Coordinator
QMNC
Spawns and coordinates queue slave processes.
Block server process
BSP
Used in OPS and keeps each servers SGA in the clusters consistent with each other.
Lock monitor process
LMON
Used in OPS and monitors all instances in a cluster to detect a failure of an instance.
Lock manager daemon
LMD
Used in OPS and controls the global locks and global resources for the block buffer cache in a clustered environment.
Lock process
LCKn
Used in OPS and is the same as the LMD daemon but handles requests for all global resources other than database block buffers
Dispatcher process
Dnnn
Dispatcher processes that are used when using a shared server environment
Shared Server process
Snnn
Shared Server processes that are used when using a shared server environment
Oracle process spawner
PSP0
Process spawner has the job of creating and managing other Oracle processes.
Oracle shadow process
SHAD
Oracle's shadow process, could not find much on this process
Streams Advanced Queuing process
q000 - q???
I believe this is something to do with Oracle Streams Advanced Queuing

There are a number of useful command and views you can use to get information regarding the running processes.

Useful SQL
Display all processes
select name, description from v$bgprocess;
Display process memory usage
select program, pid, spid, username, pga_used_mem, pga_alloc_mem, pga_max_mem from v$process;
Display users process
select
  substr(s.username,1,18) username,
  substr(s.program,1,20) program,
  decode(s.command,
    0,'No Command',
    1,'Create Table',
    2,'Insert',
    3,'Select',
    6,'Update',
    7,'Delete',
    9,'Create Index',
    15,'Alter Table',
    21,'Create View',
    23,'Validate Index',
    35,'Alter Database',
    39,'Create Tablespace',
    41,'Drop Tablespace',
    40,'Alter Tablespace',
    53,'Drop User',
    62,'Analyze Table',
    63,'Analyze Index',
        s.command||': Other') command
from
  v$session s,
  v$process p,
  v$transaction t,
  v$rollstat r,
  v$rollname n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
order by 1
;

Useful Views
V$BGPROCESS
displays information about the background processes
V$PROCESS
contains information about the currently active processes

1 comment:

  1. you may be also interested in another solution for pdf recover program, this application becomes a good addition to other programs, used by system administrators

    ReplyDelete