Wednesday 8 August 2018

Difference between PGA and UGA in Oracle Database | Explained

What is the difference between PGA and UGA in Oracle Database?

Major difference between UGA and PGA | difference between program global area and user global area in oracle | difference between dedicated and shared server processes in oracle

While working with Oracle Database, you must have heard of the SGA (Shared Global Area), PGA (Program Global Area) and UGA (User Global Area) Memory components which are basically part of memory architecture and act as the base for all the Database operations. Though most of the DBAs are quite familiar with SGAs and PGAs as they are the mostly used memory components. Be it the Automatic Memory Management (MEMORY_TARGET, MEMORY_MAX_SIZE) in Oracle or Manually managing the Memory by defining SGA and PGA independently (SGA_TARGET, SGA_MAX_SIZE, PGA_AGGREGATE_TARGET) but most of us don't really have the idea of what basically a UGA is and when and how it is used?

Before telling you what UGA Actually is, let me just give you a quick overview of How sessions are connected when a User gives a connection request? I am assuming you are already quite familiar with the concept of Dedicated and Shared Server Processes in Oracle Database. Mostly the Dedicated Server process method is used in most of the environments but the shared server processes are also beneficial and are used whenever you are forced to do so (typical system memory issues | High CPU Usage | Degraded OS Performance and much more).

So a connection a made when a client process sends a request to the server process and server process reverts back in estabilishing the session. But this happens in Dedicated Server Processes where each every user process has it's own dedicated server process attached to it and serving it's requests. In case of Shared Server Process, we have dispatchers and they take the User process requests, put them in a queue and from there, they are managed by shared server process. The number of Dispatcher and Shared Server processes can be managed by database parameters DISPATCHERS, MAX_DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS.

In case of Dedicated Server Processes, all the session activity status is completely managed in PGA only i.e. Sessions PGA keeps track of the state of that Session and the order in which the queries are getting run the same. In case of Shared Server Processes, that information gets stored in SGA and that memory part of SGA is known as UGA (User Global Area). Why store in SGA? Since the dispatchers put all the requests from different sessions in a single COMMOM QUEUE from where shared processes picks them one by one (without knowing to which sessions it belong to), executes it and put the fetched data in a separate RESPONSE QUEUE which is different for every DISPATCHER process. The Shared Server Process doesn't directly contact with User Processes and hence it becomes impossible for them to keep the track of Sessions. That's why this information in stored in SGA in the form of UGA from where it becomes easier to track the State of the Sessions.

Things to Consider:

Using UGA also helps in memory saving since it is easier for Oracle to manage the memory in SGAs than the memory occupied in PGAs. But you also need to increase you SGA accordingly. The size for the UGA is taken from LARGE POOL memory of SGA. The same can be managed by defining LARGE_POOL_SIZE Parameter.

Storing Session Information in SGA doesn't mean the PGA gets wasted in Shared Server Architecture. PGA also holds some more information regarding Sessions which is known as Stack Space. It's only the session state information, that gets removed.

Using Shared Server Architecture is not always a recommended method. Nor does it guarantee the performance boost as it requires a lot of tuning and proper attention. Every technology has its PROS and CONS and so does the Shared Server Processes. You need to make sure that there should be NO QUEUES at all because having long queues mean the sessions are waiting for their turn which is usually a big issue. So just make sure you are using it when you are actually ready to use it.

I hope this helps !!

No comments:

Post a Comment