Oracle Database architecture : for Beginers

Oracle Server = Oracle Instance + Oracle Database

Oracle Instance:

* Oracle Instance = Background processes + memory structure
* Instance starts ---> SGA allocates ----> Back ground processes starts
* Background processes functions on behalf of invoking processes.

Oracle Database:

* Consist of OS files called DATABASE FILES.
* Actual physical storage.

Non – Database Files:

Used to configure instance, authenticate privileged user , recover database
E.g. . Parameter files, Password files, Archived log files.

shared Global Area (SGA)

System global area:

* Also called as shared global area
* Stores database information that is shared by multiple processes.
* Data control information plus allocation of VM (Virtual machine)


To see SGA information
SQL> show sga;

Total System Global Area 922746880 bytes
Fixed Size 1222624 bytes
Variable Size 226494496 bytes
Database Buffers 687865856 bytes
Redo Buffers 7163904 bytes


Dynamic SGA:

* Beginning from Oracle 9i
* Allows SGA configuration to change without shutting down instance.

Some other stuff related to SGA:

* SGA is allocated in terms of GRANULES.
* Granule is a unit of continuous virtual memory allocation.
* Its size depend on SGA_MAX_SIZE

4 MB if SGA < 128 MB
Otherwise 16MB

DB buffer cache, shared pool can grow or shrink based in granule boundaries.
Owner of granule , no of granules , pending operation against component etc can be seen through V$BUFFER_POOL

SQL> select ID,NAME,BLOCK_SIZE,TARGET_SIZE from v$buffer_pool;

ID NAME BLOCK_SIZE TARGET_SIZE
---------- -------------------- ---------- -----------
3 DEFAULT 8192 648

Minimum SGA Configuration is three granules
1) Fixed SGA (including redo buffer)
2) DB buffer cache
3) Shared pool