The 3s of Oracle...ARCHITECTURE.

Question 1

The 3’s of Oracle Architecture.

Answer 1

Oracle is divided into 3 main components.

1. Memory
2. Processes
3. Physical Files.

Memory + Processes = Oracle Instance.

Memory + Processes + Physical files = ORACLE DB.

Physical File or Disk = [ Data_files + Control_files + Redo log files]

Processes = [ Background processes + Server Processes + User Processes]

Memory( sga ) = [ Shared Pool + Buffer cache + Redo log buffer ]

INSTANCE =[ SGA + Background Processes ]

User Process [ types sqlplus i.e starting a user process for a Database.

User then connects to server

Server then to Database listener….

Question 2

ROW CHAINING

Answer 2

Row too big for one block and so divided into MULTIPLE blocks.

Question 3

ROW MIGRATION

Answer 3

When UPDATE is bigger than FREE SPACE and Oracle moves the ENTIRE ROW to a different BLOCK.

Question 4

DATA BUFFER CACHE

Answer 4

1. Used to hold Data Blocks read from Data_Files by SERVER.
2. Contains DIRTY or MODIFIED Blocks and CLEAN or UNUSED or UNCHANGED Blocks.

DIRTY and CLEAN Blocks are managed in lists called DIRTY LIST and the LRU.

LRU - Least Recently Used.

Question 5

FREE SPACE

Answer 5

Is created by DBWr writing out DIRTY Blocks from LRU

Question 6

REDO LOG BUFFER CACHE

Answer 6

A CIRCULAR BUFFER that contains redo entries(changes)

Runs every 3 SECONDS.

Question 7

SHARED POOL

Answer 7

Consists multiple smaller areas:

1. LIBRARY CACHE
   a. Shared SQL area - contains parsed SQL and execution plans for statements against the Database.
   b. Procedure and Package Storage.
2. DICTIONARY CACHE aka ROW CACHE
   a. Contains NAMES of all TABLES, VIEWS and Data_Types in the Database.

Question 8

PGA (Program or Process Global Area)

Answer 8

1. Used for a SINGLE process.
2. NOT SHAREABLE with other processes. One per process.
3. WRITABLE ONLY by the SERVER process.
4. Allocated when a process is CREATED and De- allocated when process is TERMINATED.
5. Maps USER process to SGA.