Notes on Oracle Database SGA

SGAAndOracleWhen the instance is enabled, the system allocates an SGA instance and recycles the instance when it is disabled. All the processes on the server and the background history of Oracle can be visited by SGA. The local process has the permission to write SGA. Some memory in SGA is called Fixed SGA, which includes the State of the database instance.

SGA includes the following parts:

Buffer Cache

Copies of important block data are shared with all users. All the blocks in this cache are important to be retained by the data structure of the two queues.

Write List: stores data that has been corrected (dirty data ).

LRU List: retain three partial messages: Empty block and pinned buffer (which is not corrected and is being processed ), dirty buffer (the buffer has not been migrated to the Write List. here we can see that moving the buffer from the LRU List to the Write List is not real-time, but there is a triggering process 1 ).

The process is described as follows: the process of a DB tries to read the content of a block and first queries the buffer cache to check whether there is a corresponding block in it. In case of any block, it reads it directly (cache hit ), in case of not having, we will find a light and empty buffer from the LRU end in the LRU List. If we encounter Dirty Buffer, we will move it to the Write List (which explains the supermark 1 ), then continue searching. In case a lightweight free buffer is found, the block is loaded into the buffer cache. If it is not found at last, the DBW0 event is left and the dirty buffer is written to the disk.

Buffer cahce will store the MRU of the LRU end of the new block in the case of a full table scan, the newly read blocks will be included in the LRU end (the whole table is read by multiple blocks at the same time, not by a single block-see COB 19th page ). In case you want to move the entire table to the MRU end, you must specify the 'cache' In the select statement '. Example: SELECT last_name FROM employees hr_emp;

Redo Log Buffer

Stores some redo messages, which can be insert/update/delete/alter/create/drop in the redo statement. The redo buffer is copied from the user memory to the redo log buffer by the db process, this buffer has moved the space to continue, and the redo log buffer is written to the redo log by LGWR.

Share Pool

Important include Library cache, Data cache, and result cache (11 GB new ).

The Library cache includes Shared SQL areas, Private SQL areas (in case shared server is used), procedure and package of pl/SQL, and handle of library cache.

Share SQL Area is for all users, while Private SQL Area has an independent copy for each user. in the Shared SQL Area, maxcompute stores the SQL analysis tree and plan. When a new SQL is analyzed, Oracle allocates a space from the Share SQL Area, the size of the space varies with the mixing of SQL statements. If the space is insufficient for www.d1am.com, Oracle will release the space originally borrowed and allocate it to the new SQL statement based on the LRU algorithm.

Oracle also looks at some plsql process segments in the same way. In the Share SQL Area analysis, the relevant variables and SQL statement space are stored in the Private SQL Area. A self-reliant Share SQL Area and Private SQL Area are also allocated for SQL in PLSQL process segments.

Several measures to refresh share SQL area:

1. Use the analyze statement.

2. The referenced objects in SQL are changed.

3. The global name of the database is changed.

4. alter system flush shared_pool.

The summary of Oracle Database SGA is so much. I hope it will bring you some gains.