An Oracle server: a DBMS that provides an open, comprehensive, integrated approach to information management, Consists of an Instance and a database.
An Oracle Instance: a means to access an Oracle database, always opens one and only one database and consists of memory structures and background process.
An Oracle database: a collection of data that is treated as a unit, Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log).
Instance memory Structures:
System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance. SGA Memory structures: Includes Shared Pool, Database Buffer Cache, Redo Log Buffer among others.
Shared Pool : Consists of two key performance-related memory structures Library Cache and Data Dictionary Cache.
Library Cache: Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.
Data Dictionary Cache : Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
Database Buffer Cache: Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.
Redo Log Buffer : Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.
User process: Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.
Server process: Connects to the Oracle Instance and is Started when a user establishes a session. fulfills calls generated and returns results. Each server process has its own nonshared PGA when the process is started.
Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information.
In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.
Program Global Area (PGA): Memory area used by a single Oracle server process. Allocated when the server process is started, deallocated when the process is terminated and used by only one process. Used to process SQL statements and to hold logon and other session information.
Background processes:
Started when an Oracle Instance is started. Maintains and enforces relationships between physical and memory structures. Mandatory background processes: SMON, PMON, DBWn, LGWR, CKPT.
DBWn: Writes when: Checkpoint occurs, Dirty buffers reach threshold, There are no free buffers, Timeout occurs, TBS level alterations.
LGWR: Writes when: Commit, When one-third full, Every three seconds, Before DBWn writes, When there is 1 MB of redo.
SMON: Responsibilities: Opens database, Instance recovery, Rolls forward changes in redo logs, Rolls back uncommitted transactions, Coalesces free space, Deallocates temporary segments.
PMON: Cleans up after failed processes by: Rolling back the transaction, Releasing locks, Releasing other resources, Restarting dead dispatchers.
CKPT: Responsiblities : Signaling DBWn at checkpoints, Updating datafile headers and control files with checkpoint information.