Tales From A Lazy Fat DBA

Before i start explain UGA & PGA areas of Oracle Server one should always has better understanding about Shared Server (Multi Threaded Server – MTS) and Dedicated Server architecture.Let’s start with the basic knowledge about MTS and Dedicated Server Architecture.

Each user process is associated to a single server process. The server process can be Dedicated or Shared. A dedicated process has one to one relationship with user process. A dedicated process occupies certain amount of memory. In oracle database 10g, a dedicated server process consumes about 5 MB of memory. In large scale application, memory requirements for dedicated server can be a serious issue. On the other hand, a shared server process is shared by multiple user processes. We do not need a dedicated server process for every user process, so less memory is required and performance increases. At a given time, Shared server process can serve only one user process. Multiple shared server processes are configured to handle multiple user processes.

Shared server architecture consists of Listener Process, Dispatcher Process, Request Queue, Shared server process and Response Queue.

After deciding MTS is right for your environment, it is now time to dig into the implementation details. The DBA will need to consider the following two issues: User Session-Specific Data (UGA) and Routing Client Requests to Shared Server Processes.

User Session-Specific Data (UGA)
Every connection to the Oracle database has a “session-specific” memory associated with it. This memory is referred to as the User Global Area (UGA) and is used to hold the values of PL/SQL variables, the values of bind variables and other items specific to a session.

With a dedicated server connection, the UGA is stored within the dedicated server process’s Program Global Area (PGA). When an MTS connection is made though, the UGA is stored in either the “LARGE POOL” or the “SHARED POOL”.

In an MTS environment, the UGA needs to be moved into a common memory structure like the large pool or shared pool since it contains “session-specific” data.