Database Architecture

Investigate memory structures of the instance

1. Show memory component details :

select component, current_size, min_size, max_size from v$sga_dynamic_components.

2. How much memory is allocated to PGA ?

select name, value from v$pgastat where name in ('maximum PGA allocated','total PGA allocated');


Dirty block = A block whose image in memory is not the same as that on disk (due to modification).

SMON (System Monitor)
Locates controlfile and mounts database.
Locates datafiles and logfiles and then opens database.
Does housekeeping like collating free space in datafiles.

PMON (Process Monitor)
Locates abnormally terminated user session and cleans them up freeing memory and rollbacking incomplete transactions.

DBWn (Database writer)
Writes dirty buffers from buffer cache to datafiles. Writes according to lazy algorithm, as little as possible, as rarely as possible.

Four circumstances will cause DBWn to write
1. No free buffers
2. Too many dirty buffers
3. A three second timeout
4. When there is a checkpoint

LGWR (Log writer) = writes the contents of the log buffer to the online log files on disk. DML causes change vectors to be written to the log buffers (this happens before the changes are actually applied to the data block).

Three circummstances that force log writer to write to disk :
1. A Commit has been issued
2. When log buffer is one third full
3. Before DBWn writes to disk



CKPT (Checkpoint)

The CKPT no longer has to signal fullcheckpoints, but it does incremental checkpoint and keeps track of where in the redo stream the incremental checkpoint position is, and if necessary instruct DBWn to write out some dirty buffers in order to push the checkpoint position forward. The current checkpoint position, also known as the RBA (the redo byte address), is the point in the redo stream at which recovery must begin in the event of an instance crash. CKPT continually updates the controlfilewith the current checkpoint position.


Checkpoint can be defined as an event when "all" dirty blocks are written to disk. From version 8i onwards Oracle does incremental checkpoints (writing some blocks each time to disk), instead of complete checkpoint. This is to avoid dips in performance. Full checkpoint occurs on request or as a part of an orderly database shutdown. Forcing a Checkpoint : Alter system checkpoint;


MMON (Manageability Monitor) = By default every hour captures statistics from SGA and writes them to data dictionary (by default kept for only eight days).

MMNL (Manageability Monitor Light) = By default MMON is assigned to flush the stats buffers only once every hour to the data dictionary. If buffer gets filled up before the due time then MMNL take responsibility to write to disk.

MMAN (the Memory Manager) = Enables the automatic management of memory allocations.

In release 11g all the DBA need do is set an overall target for memory usage, and MMAN will observe the demand for PGAmemory and SGA memory, and allocate memory to sessions and to SGA structuresas needed, while keeping the total allocated memory within a limit set by the DBA.

ARCn (the Archiver) = When an online redo log file gets filled up, the archiver writes it as a archivelog file. The LGWR writes the onlinelog files; ARCn reads them. In normal running, no other processes touch them at all.



RECO (Recoverer) = If a (distributed) transaction (two way commit) cannot be completed because something goes wrong, then RECO takes action to cancel the commit and and rollback the work in all the databases.



Determine what processes are running, and how many of each:



select program from v$session order by program;

select program from v$process order by program;