Oracle SGA and PGA

OracleThe SGA contains several main parts.
1. Shared Pool
2. Database buffer cacheDatabaseHigh-speed buffer zone
3. redo log buffers redo log Buffer
4. large pool
5. Java poolJavaPool

A. Shared Pool:
Oracle shared pool includes library cache and dictionary cache)

Library cache contains sharedSQLAnd the shared PL/SQL area. They store SQL and PL/SQL statements and related information respectively. Oracle uses an algorithm called the LRU (least recently used) queue to manage the library cache. The algorithm is roughly as follows: The used memory block (Address) is placed on the head of the LRU queue, when a server process needs the memory space of the library cache, the process obtains the required memory blocks from the end of the LRU pair column. Once these memory blocks are used) immediately put it at the beginning of the LRU pair column, so that those memory blocks that have not been used for a long time will naturally move to the end of the LRU pair column and will be used first. To share SQL and PL/SQLCodeLibrary cache must be large enough. However, Oracle does not modify the library cache size parameter. You can only indirectly modify the size of the library cache by modifying the Shared Pool. Using the shared pool for a long time will produce many fragments. If the shared pool does not have enough continuous space to load the target data, an error will occur. Therefore, Oracle provides the flush_shared_pool parameter to refresh the Shared Pool.
Statement:
Alter system set shared_pool_size = 48 m;
Alter system flush_shared_pool;

Dictionary cache: the information of database objects is stored in the data dictionary table. When the database needs this information, it reads the dictionary table and stores the returned data in the dictionary cache. Dictionary cache uses data blocks through the LRU algorithm. Dictionary cache is part of the shared pool and cannot be set separately. It can be modified only when the shared_pool_size parameter is modified. Shared_pool_size should be increased when the data dictionary query speed is slow.

B. Database buffer cache:
When Oracle modifies data, the server process will first search for the required data in the database buffer cache. If the data is found, it will be used directly without searching the disk. If the data is not found, the server will perform disk operations, read the data in the data file into the database buffer cache. After the modification, when certain conditions are met, the dbwn process is responsible for writing data in the database buffer cache to the data file. Oracle uses the LRU algorithm to implement database buffer cache.Management. You can use db_block_size and db_block_buffers to set the database buffer cache size. db_block_size is fixed during database creation and cannot be modified after the database is created. The db_block_buffers algorithm is 48 m/db_block_size. If the size of the SGA is insufficient to accommodate the data used, other buffers will compete for the database buffer cache space, reducing the database performance. When multiple programs share the same SGA, this phenomenon may occur. When will the dbwn process write data in the database buffer cache to the data file:
1. Checkpoint
2. When the buffer cache does not have enough free buffer.

C. redo log buffers:
When the data in the database is modified, the background process lgwr records the modified content to the redo log for rollback during database recovery. However, before being written to the redo log, the transaction is first recorded in the redo log buffers. After certain conditions are met, the lgwr process will be activated to write data to the redo log file. These conditions are as follows:
1. Submission occurred.
2. Time limit for reaching lgwr inactivity.
3. When the data in the redo log buffers reaches 1/3 of the redo log buffers.
4. dbwn refreshes the database buffer cache at the checkpoint.

D. large pool:
The large pool is an SGA buffer pool similar to the shared pool. Unlike the Shared Pool, only a few types of objects can be created in the large pool. Large pool space is not allocated in the shared pool. It is allocated when the database is started. The large pool size is determined by large_pool_size. The large pool can also be disabled for Oracle databases. Since Oracle 8, the Oracle database introduced the large pool. in Oracle 8, the main functions of the large pool are as follows:
1. Allocate UGA space for sessions in the MTS (earlier versions of the Shared Server) mode.
2. As a buffer for continuous file I/O, such as system-managed recovery and RMAN backup recovery (when RMAN uses multiple Io slaves)
In general, the use of large pool is not required, so it is not necessary to set a large pool.

E. Java pool:
The purpose of introducing the Java pool is to compile Java-language commands. To use the Java language, you must set the Java pool. The Oracle database stored in the PL/SQL language in the Java language is almost identical. You can set the Java pool size by using the value of java_pool_size, in bytes ). In Oracle9i, the default Java pool size is 24 MB.

 

PGA

(Program global area program global area) is a memory area that contains data and control information of a service process. It is created when Oracle starts a service process and is not shared. An Oracle process has a PGA memory zone. A pga can only be accessed by the service process that owns it. Only the Oracle code in this process can read and write it. Therefore, the structure in PGA does not require latch protection.
We can set the total PGA memory of all service processes to be limited by the overall PGA (aggregated PGA) allocated by the instance.
In the VPC (dedicatedServer), Oracle starts an oracle process for each session;
In multi-thread server MTS mode, multiple sessions share the same oracle service process.
PGA contains information about the operating system resources used by the process, as well as some information about the process status.
The information about Oracle shared resources used by the process is in SGA.
In this way, resources can be released and cleared in a timely manner when they are terminated outside the process.

Related View
V $ pgastat
V $ pgastat provides statistics on PGA memory usage and when automatic PGA memory management is started.
The accumulated data in the view is accumulated since the instance is started.

V $ sysstat, V $ sesstat
The two views show the statistical data of the system (session. Their statistical items are basically the same,
However, the difference is that one is system-level and the other is session-level.

V $ Process
This view displays information about all Oracle processes. The following fields describe the usage of the PGA memory of the process.
Pga_used_mem: PGA memory used by the Process
Pga_allocated_mem: PGA memory allocated to the Process
Pga_max_mem: Maximum PGA memory used by the Process