Large Pool

🧠 Introduction
The Large Pool is an optional memory area within the System Global Area (SGA) of an Oracle Database instance. It handles large memory allocations separately to reduce pressure and fragmentation in the Shared Pool, boosting overall performance for specific operations.

🧩 Key Uses of the Large Pool

  1. User Global Area (UGA) for Shared Server & XA Transactions
  • Stores session memory (UGA) in shared server environments.
  • Used by Oracle XA interface to manage global (distributed) transactions across multiple databases.
  • In dedicated server mode, UGA is stored in the Program Global Area (PGA) instead.
  1. I/O Buffer Area
  • Supports large I/O operations like:
    • I/O server processes
    • Parallel query message buffers
    • Recovery Manager (RMAN) I/O workers
    • Advanced Queuing (AQ) memory tables
  1. Deferred Inserts Pool (Fast Ingest)
  • Supports the MEMOPTIMIZE FOR WRITE feature for fast single-row inserts.
  • Deferred inserts buffer data in the Large Pool before asynchronous disk writes.
  • Managed by Space Management Coordinator (SMCO) and worker background processes (Wxxx).
  • Dynamically allocated up to 2 GB, with fallback to smaller sizes if memory is tight.
  • Disabled if allocation fails until instance restart.
  1. Parallel Execution Message Pool (PX Msg Pool)
  • Used by parallel query processes to exchange messages efficiently, enhancing parallel execution speed.
  1. Free Memory
  • Any leftover Large Pool memory is available for dynamic allocation as needed.

🧩 Shared Server Process Workflow in Large Pool

Dedicated Server vs. Shared Server Mode:

  • Dedicated Server: One server process handles one client using CPU and memory resources.
  • Shared Server: Multiple server processes handle many clients efficiently, relying on the Large Pool.

How a Shared Server Process Works:

  • Client sends request → Dispatcher Process (Dnnn) queues it in the Large Pool request queue.
  • Shared Server Process (Snnn) picks requests in FIFO order, then:
    • Accesses the Library Cache to validate SQL and privileges.
    • Reads data from Buffer Cache or disk.
    • Different processes may handle parsing, fetching, or closing steps.
    • Uses UGA stored in shared memory for session data accessible by any server process.
  • After processing, results are placed in the Dispatcher’s response queue and sent back to the client.

🛠️ Why Use the Large Pool?

  • Prevents fragmentation in the Shared Pool.
  • Improves performance for:
    • Shared Server environments
    • Parallel Query execution
    • RMAN backup and recovery
    • High-throughput insert operations (MEMOPTIMIZE FOR WRITE)

ADVERTISEMENT