The memory structure of Oracle consists of SystemGlobalArea (SGA) and ProgramGlobalArea (PGA ). SGA: When an instance is started
The memory structure of Oracle consists of System Global Area (SGA) and Program Global Area (PGA ). SGA: When an instance is started
The memory structure of Oracle consists of System Global Area (SGA) and Program Global Area (PGA ).
SGA: allocated is a basic component of an instance when it is started.
PGA: allocated when a Server Process is started. Server Process.
Memory-> SGA
SGA is dynamic and can be set by the SGA_MAX_SIZE parameter. The increase and decrease of SGA have a basic unit granule.
In SQL plus, we can view the SGA allocation and granule.
SQL> show sga
SQL> select component, granule_size from
2 v $ sga_dynamic_components;
SGA also has its components. Here we mainly introduce Shared Pool, Large Pool and Java Pool. Of course, there are also some other buffer and cache, such as Data Buffer Cache. There is also a Stream Pool in Oracle11g.
Large Pool and Java Pool are optional memory structures in SGA. The former will be used in backup, IO processing and parallel operations, and the latter will be used in Java.
Memory-> SGA-> Shared Poll
The Shared Pool is mainly used to store the vast majority of recently executed SQL statements and recently used data definitions ). We can use SHARED_POOL_SIZE to set its size. Statement: alert system set SHARED_POOL_SIZE = 64 M; Shared Pool includes two main memory structures that affect performance: Library Cache and Data Dictionary Cache.
Libray Cache is used to store the vast majority of recently used SQL and PL/SQL statements, and to share recently used statements. It mainly includes Shared SQL area and Shared PL/SQL area. Its size is determined by the Shared Pool, which is managed by the Least recently used mechanism (Least recently used, LRU algorithm ).
Data Dictionary Cache is a set of definitions in most recently used databases, including database files, tables, columns, users, and permissions. It provides server process with object name resolution and access verification (validate access ). Its size is also determined by the Shared Pool.
The Shared Pool also stores data blocks and redo log buffering.
Memory-> PGA
Every user connected to the Oracle database will have their own PGA. It is created with the creation of the user process and ended with the termination of the user process.