Contents
- 1 What is PGA and SGA in Oracle?
- 2 How does Oracle 11g calculate SGA and PGA size?
- 3 What are the components of PGA?
- 4 What is difference between SGA_Target and Memory_Target Oracle?
- 5 What is _pga_max_size?
- 6 What does the initialization parameter do in Oracle?
- 7 What does it mean to configure memory in Oracle?
What is PGA and SGA in Oracle?
Basic Memory Structures The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process.
What is Sga_max_size and Sga_target?
SGA_MAX_SIZE and SGA_TARGET both are the parameter are used to change the SGA SIZE. SGA_MAX_SIZE sets the maximum value for sga_target. it specifies the total amount of SGA memory available to an instance. This feature is called Automatic Shared Memory Management.
How does Oracle 11g calculate SGA and PGA size?
The Oracle DBA can use math to determine the optimal RAM allocation for a MS-Windows server….The total RAM SGA and PGA estimates for Oracle are as follows:
- OS RAM : 20 percent of total RAM for MS-Windows, 10% of RAM for UNIX.
- Oracle SGA RAM : determined with the show sga command.
What is PGA in Oracle?
A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. The PGA is used to process SQL statements and to hold logon and other session information.
What are the components of PGA?
Automatic Shared Memory Management. All SGA components allocote and deallocate areas in units of granules.
What are the background processes in Oracle?
The background processes in an Oracle instance can include the following:
- Database Writer Process (DBWn)
- Log Writer Process (LGWR)
- Checkpoint Process (CKPT)
- System Monitor Process (SMON)
- Process Monitor Process (PMON)
- Recoverer Process (RECO)
- Job Queue Processes.
- Archiver Processes (ARCn)
What is difference between SGA_Target and Memory_Target Oracle?
SGA_Target is the size of the SGA not including the PGA. Memory_Target includes both. If the PGA_target + SGA_Target are > Memory_Target, you can get this error.
What is the Sga_max_size?
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance. On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters.
What is _pga_max_size?
On to its sister parameter, _pga_max_size. This parameter regulates the size of the PGA memory allocated to a single process. Oracle sets this using calculations based on pga_aggregate_target and pga_max_size and, since it is an ‘undocumented’ parameter, it should NOT be changed at the whim of the DBA.
What are Oracle processes?
PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.
What does the initialization parameter do in Oracle?
Oracle reads the initialization parameter values from either a PFILE or SPFILE as the database is starting. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and the location of existing datafiles. A parameter has a name and a value.
What’s the minimum memory target for Oracle ASM?
The minimum MEMORY_TARGET for Oracle ASM is 1 GB. If you set MEMORY_TARGET lower, then Oracle increases the value for MEMORY_TARGET to 1 GB automatically. For the recommended settings of memory initialization parameters in an Oracle Exadata environment, refer to the Oracle Exadata documentation.
What does it mean to configure memory in Oracle?
Configuring memory allocation involves distributing available memory to Oracle memory structures, depending on the needs of the application. The distribution of memory to Oracle structures can affect the amount of physical I/O necessary for Oracle to operate.
What does the init.ora file in Oracle mean?
When the Oracle database is started, one of the first things it needs to do is read the database initialization parameter file. The parameter file (init.ora) is created by the DBA and defines the overall instance configuration, such as how much memory should be allocated to the instance, the file locations, and internal optimization parameters.