Memory, Storage, and Processes

Oracle Database Server Architecture

Three Important Components of Oracle Database

  • Components for Daily DBA Management:

    • Memory

    • Storage

    • Processes

Server Architecture Overview

  • Divided into Three Areas:

    • Memory Structures

    • Operating System Process Structures

    • Storage Structures

Memory Structures

  • Server Memory: Contains the

    • System Global Area (SGA)

    • Program Global Area (PGA)

    • Memory is occupied by server processes that manage requests.

  • Client Utilization:

    • Clients use both PGA and SGA to temporarily store data from tables.

  • Server Processes:

    • Comprise Oracle code that handles user requests.

Storage Structures

  • Storage Architecture:

    • Includes structures on disk that hold database tables.

    • Storage size can vary significantly:

    • Small databases: a few gigabytes

    • Large databases (e.g., e-commerce): terabytes to petabytes

  • Client Interaction with Storage:

    • Clients interact by issuing SQL statements (e.g., SELECT, INSERT).

Detailed Memory Structures

System Global Area (SGA)

  • Shared Memory Structure that contains:

    • Most prominently, Database Blocks from tables

    • Shared Pool: Stores recent SQL statements executed by the instance.

  • Shared by All Processes:

    • Every user process and background process shares the SGA.

Program Global Area (PGA)

  • Definition:

    • Private memory for each user process.

  • Uses of PGA:

    • Allocated to user sessions mainly for SQL work areas:

    • Memory allocation for sorting result sets

    • Aggregated table rows allocation

    • Includes buffer for private session data, not shared with other processes.

Database Structure Understanding

Segments

  • Definition:

    • Most commonly refers to a Table, but can include other data structures.

  • Components of a Segment:

    • Comprised of Extents

    • Which further break down into Data Blocks

    • At the lowest level, a Disk Block stored in the file system.

Types of Segments

  • Primary Segments:

    • Table or Data Segments

    • Index Segments

    • Undo Segments

    • Temporary Segments

  • User Perspective:

    • Users typically see tables and indexes, while DBAs manage undo and temporary segments.

Tablespaces

  • Definition:

    • Database structure that holds all segments.

  • Types of Tablespaces:

    • Small File Tablespace:

    • Default type; can contain more than one data file.

    • Each data file limited to 32 GB for an 8k block size.

    • Big File Tablespace:

    • Contains only one data file.

    • Easier management for DBA; size can be up to 32 TB.

Creating New Databases

  • Default Tablespaces Created on Database Creation:

    • System Tablespace

    • Sysaux Tablespace

    • Users Tablespace

    • Temporary Tablespace (temp) for temporary tables

    • Undo Tablespace (undo TBS1) for managing undo segments in transaction control.

Key DBA Insights

  • Understanding the components, such as memory, disk, and processes, is critical.

  • Operational Knowledge Required:

    • It’s essential to understand the functionalities to manage these components effectively, ensuring efficient database operation and consistent response times.