Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

What is the difference between SGA and PGA

702258May 20 2009 — edited Dec 21 2011
hi guys i know what is SGA but have little difficulty in PGA please explain

below two explainations are for advance user however i understand sga please explain me pga with simple logic.

Edited by: user11180084 on May 20, 2009 3:49 AM
This post has been answered by Aman.... on May 20 2009
Jump to Answer

Comments

Kamran Agayev A.
[SGA |http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/glossary.htm#sthref4221] is - A group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes referred to as the shared global area.

[PGA |http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/glossary.htm#sthref4190] is - A memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the Oracle configuration.

- - - - - - - - - - - - - - - - - - - - -
Kamran Agayev A. (10g OCP)
http://kamranagayev.wordpress.com
[Step by Step install Oracle on Linux and Automate the installation using Shell Script |http://kamranagayev.wordpress.com/2009/05/01/step-by-step-installing-oracle-database-10g-release-2-on-linux-centos-and-automate-the-installation-using-linux-shell-script/]
1 person found this helpful
SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up. The SGA's size and function are controlled by initialization (INIT.ORA or SPFILE) parameters.
Contents


In general, the SGA consists of the following sub-components, as can be verified by querying the V$SGAINFO:

SELECT * FROM v$sgainfo;

The common components are:

* Data buffer cache - cache data and index blocks for faster access.
* Shared pool - cache parsed SQL and PL/SQL statements.
* Dictionary Cache - information about data dictionary objects.
* Redo Log Buffer - committed transactions that are not yet written to the redo log files.
* JAVA pool - caching parsed Java programs.
* Streams pool - cache Oracle Streams objects.
* Large pool - used for backups, UGAs, etc.


SQL> SHOW SGA
Total System Global Area 638670568 bytes
Fixed Size 456424 bytes
Variable Size 503316480 bytes
Database Buffers 134217728 bytes
Redo Buffers 679936 bytes

SQL> SELECT * FROM v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 456424
Variable Size 503316480
Database Buffers 134217728
Redo Buffers 679936

The size of the SGA is controlled by the DB_CACHE_SIZE parameter.


PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.
[edit] Auto tuning
PGA areas can be sized manually by setting parameters like hash_area_size, sort_area_size etc.
To allow Oracle to auto tune the PGA areas, set the WORKAREA_SIZE_POLICY parameter to AUTO and the PGA_AGGREGATE_TARGET to the size of memory that can be used for PGA. This feature was introduced in Oracle 9i.

PGA usage statistics:
select * from v$pgastat;

Determine a good setting 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;
2 people found this helpful
Aman....
Answer
user11180084 wrote:
hi guys i know what is SGA but have little difficulty in PGA please explain

below two explainations are for advance user however i understand sga please explain me pga with simple logic.
Well I shall share the example that I give when I talk about the same. Assume that there are 10 people sitting and listening to you in a class. They all would have some notebook like things with them in which they would write some thing. Now assume you also being a student there in that session. Will you let someone else write in your own notebook? I assume that the ansswe would be no for these possible reasons

1) Redundancy The other person may want to write some thing which you have already written.

2) Privacy You don't want theother person to see what you have done and written.

3) Security for the data (This would not happen with humans but with processes) Other person may want to write on the same place where you have written already , leading to the corruption of data.

Now if we come back to oracle, if you give an order by clause , does every body else also starts seeing the data in the sorted manner? No I guess would be the answer . Thismeans that there is some data which needs to be specific for your session only, which should be only usable, viewable by your session. Now if we can call this in a bit more casual language, we need a private copy for each session to maintain its private data. This private copy is assigned to eash connected session in oracle in the form of its own Program Global Area(PGA).

Hope this should clear it. Let me know if you are not still clear.

HTH
Aman....
Marked as Answer by 702258 · Sep 27 2020
6 people found this helpful
906675
Hi Aman,

very good explanation. ty :)
Aman....
That's a very old thread but still, thanks a bunch and I am glad that you liked it :) .

Aman....
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 18 2012
Added on May 20 2009
#general-database-discussions
5 comments
75,127 views