Memory management - focus is to maintain optimal sizes for memory structures.
- DBA specifies the target size for instance memory.Memory is managed based on memory related initialization parameters.
- These values are stored in the init.ora file for each database.
Three basic options for memory management are as follows :
- Automatic memory management :
- DBA specifies the target size for instance memory.
- The database instance automatically tunes to the target memory size.
- Database redistributes memory as needed between the SGA and the instance PGA.
- Automatic shared memory management :
- This management mode is partially automated.
- DBA specifies the target size for the SGA.
- DBA can optionally set an aggregate target size for the PGA or managing PGA work areas individually.
- Manual memory management :
- Instead of setting the total memory size, the DBA sets many initialization parameters to manage components of the SGA and instance PGA individually.
If you create a database with Database Configuration Assistant (DBCA) and choose the basic
installation option, then automatic memory management is the default.
The memory structures include three areas of memory :
- System Global Area (SGA) – this is allocated when an Oracle Instance starts up.
- Program Global Area (PGA) – this is allocated when a Server Process starts up.
- User Global Area (UGA) – this is allocated when a user connects to create a session.
System Global Area :
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 the Shared 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 :
- Library Cache
- Data Dictionary Cache
- Other structures (for example, lock and latch management, statistical data)
- Additional optional memory structures in the SGA include :
- Large Pool
The SHOW SGA SQL command will show you the SGA memory allocations.
- This is a recent clip of the SGA for the DBORCL database at SIUE.
- In order to execute SHOW SGA you must be connected with the special privilege SYSDBA (which is only available to user accounts that are members of the DBA Linux group).
- SQL> connect / as sysdba;
Connected. - SQL> show sga;
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 1006633528 bytes
Database Buffers 587202560 bytes
Redo Buffers 14692352 bytes
Program Global Area (PGA) :
A PGA is :
- A non-shared 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.
No comments:
Post a Comment