Oracle architecture

If you want to be an expert in an area you must understand working principals and architecture of that system. Many people use Google to find solutions. But this doesn't give you a profession. Our subject is Oracle now. If you have a problem with your database server you must identify it and find best solution. Since every database system has its own environment with full of parameters your solution must fit that environment. If you set up your system with wrong parameters you will get wrong results. So you must learn architecture of Oracle. I copied some useful items about this topic from Oracle documents. Also you can read a detailed article here.

System Global Area
• The SGA consists of several memory structures:
– Shared Pool
– Database Buffer Cache
– Redo Log Buffer
– Other structures (for example, lock and latch management, statistical data)
• There are two additional memory structures that can be configured within the SGA:
– Large Pool
– Java Pool
• SGA is dynamic
• Sized by the SGA_MAX_SIZE parameter
• Allocated and tracked in granules by SGA components
– Contiguous virtual memory allocation
– Granule size based on total estimated SGA_MAX_SIZE

Shared Pool
• Used to store:
– Most recently executed SQL statements
– Most recently used data definitions
• It consists of two key performance-related memory structures:
– Library Cache
– Data Dictionary Cache
• Sized by the parameter SHARED_POOL_SIZE
ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

Library Cache
• Stores information about the most recently used
SQL and PL/SQL statements
• Enables the sharing of commonly used statements
• Is managed by a least recently used (LRU) algorithm
• Consists of two structures:
– Shared SQL area
– Shared PL/SQL area
• Size determined by the Shared Pool sizing

Data Dictionary Cache
• A collection of the most recently used definitions in the database
• Includes information about database files, tables, indexes, columns, users, privileges, and other database objects
• During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access
• Caching data dictionary information into memory improves response time on queries and DML
• Size determined by the Shared Pool sizing

Database Buffer Cache
• Stores copies of data blocks that have been retrieved from the datafiles
• Enables great performance gains when you obtain and update data
• Managed through an LRU algorithm
• DB_BLOCK_SIZE determines primary block size

Redo Log Buffer
• Records all changes made to the database data blocks
• Primary purpose is recovery
• Changes recorded within are called redo entries
• Redo entries contain information to reconstruct or redo changes
• Size defined by LOG_BUFFER

Program Global Area
• Memory reserved for each user process connecting to an Oracle database
• Allocated when a process is created
• Deallocated when the process is terminated
• Used by only one process