Oracle database Architecture - Part 2

PGA – Program Global Area

It is the memory region which contains the data and control information for a server process.  PGA memory is not shared and it is created by oracle when a server process is started.

PGA is a memory heap that contains session-dependent variables required by dedicated or shared server processes.  Server process allocates memory that it requires in the PGA.

 Two components of 2 PGA are.

1. Stack Space and 

2.       User Global Area.

Stack Space:  It holds the bind variables, plsql array,etc. 

User Global Area: UGA contains the following.

1. Session Memory 

2. SQL work Area

3. Private SQL area.

Private SQL area: Whenever a user initiates a session and issues a SQL statement, it will have Private SQL area to store bind variable values, query execution state information and query execution work areas, i.e., in short it uses a single shared SQL area.  Thus many private SQL areas can be associated with the same shared SQL area. 

But do not confuse that private SQL area is in UGA, while the shared SQL area which stores the execution plan in SGA.  

Eg. 50 executions of  Select * from test_table in one session and 30 executions of same query in a different session can share the same plan.

Private shared SQL area are not shared and may contain different values and data

Cursor

Cursor on the other side is a name or pointer to the private SQL area.  Cursor is like a pointer on the client side and as a state on the server side.

Private SQL area is divided into following areas.

1. Run-time area – Contains query execution state information.  It also tracks the number of rows retrieved so far when the SQL is going for Full Table Scan (FTS). This is the area oracle creates initially when the execute request is passed.  For DML statements, the run-time area is freed when the SQL statement is closed.

2.  Persistent Area – It contains the bind variables. When the user executes the statement, the bind variable is supplied at run time. This area is freed only when the cursor is closed.

The allocation and deallocation of private SQL areas mainly dependent on the application design, eventhough the private sql areas for client process is limited by OPEN_CURSORS.

               

SQL Work Area

This private allocation of PGA memory is used for memory-intensive operations like sorting, has joins, bitmap merge join,etc.

Ø  Sort area is used for sorting operation. 

Ø  Hash area is used by hash join for building the hash table

Ø  Bitmap merge area is used by bitmap merge operation which uses to merge data retrieved from scans of multiple bitmap indexes.

When we use automatic memory management for PGA using PGA_AGGREGATE_TARGET , these work areas are automatically managed by oracle itself.

The bigger the work areas, the more the performance of the operator but with higher cost in memory consumption.

We have to allocate size of the work area size such that it should be big enough to accommodate the input data and auxillary memory structures allocated by its SQL operator.  If not, the response time increases, because the input data has to spill to temporary disk storage.  In extreme cases, when the size of sort area size is too small, multiple passes over the data pieces must be performed. This would increase the response time drastically.

We can see these with more examples in PGA tuning post.