Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

What is the difference bitween sga and pga memory in oracle?

user-image
Question ajoutée par Shiva Kumar , Oracle Database Administrator , Veritaz Health Care Pvt Ltd
Date de publication: 2016/12/05
Ahmed Hassan
par Ahmed Hassan , Experienced Technology Consultant. , Raya international services

System Global Area (SGA)

The SGA is a read/write memory area that stores information shared by all database processes and by all users of the database (sometimes it is called theShared Global Area).

 This information includes both organizational data and control information used by the Oracle Server.

 The SGA is allocated in memory and virtual memory.

 The size of the SGA can be established by a DBA by assigning a value to the parameter  SGA_MAX_SIZE in the parameter file—this is an optional parameter.

The SGA is allocated when an Oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE or SPFILE).

The SGA has the following mandatory memory structures:

 Database Buffer Cache

 Redo Log Buffer

 Java Pool

 Streams Pool

 Shared Pool – includes two components:

o Library Cache

o Data Dictionary Cache

 Other structures (for example, lock and latch management, statistical data)

Additional optional memory structures in the SGA include:

 Large Pool

 

 

Program Global Area (PGA)

A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process.

A PGA is created by Oracle Database when an Oracle process is started.

One PGA exists for each Server Process and each Background Process. It stores data and control information for a single Server Process or a single Background Process.

It is allocated when a process is created and the memory is scavenged by the operating system

when the process terminates. This is NOT a shared part of memory – one PGA to each process

only.

The collection of individual PGAs is the total instance PGA, or instance PGA.

Database initialization parameters set the size of the instance PGA, not individual PGAs.

The Program Global Area is also termed the Process Global Area (PGA) and is a part of memory

allocated that is outside of the Oracle Instance.

Utilisateur supprimé
par Utilisateur supprimé

1.PGA (PROCESS GLOBAL AREA) - is a memory used by the server process 'Dynamically' , i mean - it wil grow as it is "needed " and released back to OS automatically

2.SGA (SYSTEM GLOBAL AREA) ACCORDING TO THE VERSION OF ORACLE DATABASE -

IN ORACLE (9i) - it is all "owned by Oracle", you set the sizes of the individual components - but it is pretty much the size it is. It would all be "in use" IN ORACLE(10g) and above, the SGA has a target and a max_size (two parameters). The sga_max_size will be reserved and the sga_target would be "in use".IN ORACLE (11g) and above you can also set memory_target and memory_max_target. That will control the sizing of the SGA and PGA - the max targets will be reserved, but the "in use" amount would dynamically change (for both sga and pga) as the workload changes. 

SGA 

 

Sga stand for system global area. Sga is use RAM memory. When instance will start then sga is reserve memory from RAM. When Instance is shutdown then it’s automatically release the memory. In sga some different area allocate like data buffer cache, redo log buffer cache, shared pool, large pool, java pool, stream pool, fix sga  

 

PGA

User session is started then allocates the memory. This memory called pga(program global area). Pga is coming from ram. When the session is started the memory reserve from ram and this particular session is close the pga memory will be release. Pga is auto size for oracle from 9i