Tutorial corner

Informatica,ETL,oracle,sql/plsql

Process Global Area in Oracle Database

Spread the love

In previous tutorial , we talked about the oracle database architecture in details. We also checked the importance of PGA (Process Global Area in Oracle) in the memory management part. In below tutorial , we will  go through the detailed explanation of Program Global Area in Oracle Database  like components of PGA , Parameter related PGA and important SQL related to PGA.

Process Global Area in Oracle Database

What is Process Global Area (PGA) ?

Apart from SGA (System Global Area), Oracle allocates global area specific and attached  it to each process and session. PGA stands for Process Global Area which is also known as Program Global Area. It is called global area because it keeps information which is required by all modules of Oracle Code.

Properties of PGA

  • PGA is a nonshared memory region that contains data and control information specific for an Oracle process.
  • PGA memory is  allocated  by Oracle Database when an Oracle process is started.
  • A private SQL area contains data such as bind information and run time memory structures. Each session that issues an SQL statement has a private SQL area.
  • Database initialization parameters set the size of the instance PGA, not individual PGAs.

Check here for oracle database architecture in detail

 When PGA is used

Oracle’s PGA or program global area is a memory area that keeps data and information about a single process and sql operators like

  • Hash join
  • Bitmap merge
  • Bitmap create
  • Order by / Group by
  • ROLLUP / CUBE
  • Analytical functions (ROW_NUMBER, NTILE, LEAD, LAG …)
  • Write buffers which are used in BULK operations (BULK statements increase the usage of PGA)

 Component of PGA:

Process Global Area in Oracle

Process Global Area in Oracle

1. Private SQL Area

It stores information like (bind variable  values and run time memory allocations) for a parsed SQL statement. A user session issuing SQL statements has a Private SQL Area that may be associated with a Shared SQL Area (Which contains Parsed SQL Statement) and it basically passes the bind variable and other information to the parsed SQL statement.This play an important role in OLTP system , where same SQLs are being issued with different values of bind variables.

  • Dedicated Server environment – the Private SQL Area is located in the Program Global Area. If a session is connected through a dedicated server, private SQL areas are located in the server process’ PGA
  • Shared Server environment – if a session is connected through a shared server, part of the private SQL area is kept in the SGA.

2.  Session Memory

  • Memory that holds session variables and other session information.

3. SQL Work Areas

  • SORT_AREA_SIZE: Use by sort operation
  • HASH_AREA_SIZE: Use by hash join operation
  • CREATE_BITMAP_AREA_SIZE: use by building bitmap index. Larger value would help speed up create the bitmap index.
  • MERGE_BITMAP_AREA_SIZE: use by merge comparison operation. Larger value would help speed up the merge operation.

How to check PGA of an oracle instance ?

You will need to connected as SYSDBA to know about the SGA of oracle instance

SQL> connect / as sysdba
Connected.
SQL> Show parameter pga_aggregate_target
NAME TYPE VALUE
——————————————–
pga_aggregate_target big interger 0
SQL> Show parameter pga_aggregate_target
NAME TYPE VALUE
——————————————-
merge_bitmap_area_size interger 1023204
create_bitmap_area_size interger 8387690
hash_area_size interger 131072
sort_area_size interger 21345
workarea_size_policy string MANUAL
SQL>

Parameters related to PGA:

Parameter Description
WORKAREA_SIZE_POLICY Oracle 9i and later versions enable automatic sizing of the SQL Work Areas by setting the WORKAREA_SIZE_POLICY = AUTO parameter
 PGA_AGGREGATE_TARGET  PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance

Check here for System global area in Oracle

Some Important SQL related to PGA

PGA usage statistics:

select * from v$pgastat;

Determine a good value for pga_aggregate_target:

select * from v$pga_target_advice order by pga_target_for_estimate;

Show the maximum PGA usage per process :

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

Sources :

https://docs.oracle.com/en/
www.orafaq.com

Hope you enjoyed the above tutorial , in which we have gone through the basics of Process Global Area (PGA) in oracle database architecture , basic component and parameter of PGA .

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme