Oracle Memory Architecture Detailed (iii) Oracle management share Pool

The shared pool in the SGA consists of a library cache, a dictionary cache (Dictionary cache), a buffer for parallel execution messages, and a control structure.

The size of the Shared POOL is determined by the parameter shared_pool_size. 9i, the default value of this parameter is 8M under 32-bit system, and the default value of 64-bit system is 64M. The maximum is 4G. 10g can be adjusted automatically by Sga_target parameters.

Memory management for shared pool is achieved through a modified LRU algorithm table.

1. Library Cache

The library cache includes a shared SQL area (Shared SQL Areas), pl/sql stored procedures, and control structures such as locks and library cache handles.

Any user can access the shared SQL area (which can be accessed via V$sqlarea and then introduce this important view). Therefore, the library cache exists in the shared pool of the SGA.

(1) shared SQL and private SQL areas

Oracle will run each SQL statement (Oracle will open a cursor for each statement) to provide a shared SQL area (Shared SQL Areas) and a private SQL zone (the private SQL Areas belongs to the PGA). When two (or more) users are found to be running the same SQL statement, Oracle will rearrange the SQL area so that they can reuse the shared SQL zone. But they also keep a copy of this SQL statement in the private SQL section.

A parse tree and query plan that holds a statement in a shared SQL area. In multiuser systems, Oracle saves memory by running multiple times with the same shared SQL area for SQL statements.

When a new SQL statement is parsed, Oracle allocates a chunk of memory from the shared pool to store the shared SQL area. The size of this block of memory is related to the complexity of the statement. If the shared pool does not have enough space to allocate to the sharing SQL area, Oracle releases the most recently used block of memory from the LRU list until there is enough space for the new statement's shared SQL area. If Oracle frees up memory for a shared SQL zone, the corresponding statement needs to parse and reassign the shared SQL area again the next time it executes. And from parsing statements to allocating shared SQL areas is a CPU-consuming project. That's why we advocate the use of binding variables. In the absence of binding variables, the value of the variables in the statement is different, Oracle as a new statement (9i can be controlled by cursor_sharing), repeat the above parsing, memory allocation of the action, will greatly consume system resources, reduce system performance.

(2) Pl/sql program unit

Oracle is similar to the process of Pl/sql program units (stored procedures, functions, packages, anonymous pl/sql blocks, and triggers) and the processing of individual SQL statements. It assigns a shared area to store the parsed, compiled program unit. Assign a private area to hold the parameter values (including local, global, and package variables-This is also called the instantiation of the package) for the program unit specified by the session that runs the program unit and the memory needed to execute the program. If multiple users run the same program unit, they share the same shared area, and each maintains a private area for the value of the variable specified in the user's session.

The processing of each individual SQL statement in a PL/SQL program unit is the same as that of the SQL statement described above. Note that although these statements are from the PL/SQL program unit, Oracle assigns the statements a shared SQL area and assigns each user a corresponding private SQL area.

2. Dictionary caching (Dictionary cache)

A data dictionary is a set of tables and views that have reference information about the database, the structure of the database, and the user information in the database, such as the v$ view, the Dba_ view, which is commonly used in the data dictionary. In the process of parsing SQL statements, Oracle can access the data dictionary very quickly (if needed), in SQL Trace, this access to the data dictionary is counted as a callback (recursive calls).

Because Oracle has access to data dictionaries so frequently, there are two places in memory that are dedicated to storing data dictionaries. One place is data dictionary caching (Dictionary cache). The data dictionary cache is also referred to as row caching, because it stores data in a recording behavior unit, unlike buffer cache, which stores data in blocks of data. Another place in memory where the data dictionary is stored is the library cache. All Oracle users can access the two places to get data dictionary information.

3, the shared pool of memory management

In general, the shared pool is based on the modified LRU algorithm to see if the objects (shared SQL area and data Auto record rows) are in them, otherwise they remain in the shared pool. If a shared pool needs to allocate memory for a new object, and if there is not enough memory in the shared pool, those infrequently used objects in memory are freed. A shared pool object that has been used by many sessions, even if the process that originally created it has ended, as long as it is useful, the modified LRU algorithm remains in the shared pool. This minimizes the processing and memory consumption of SQL statements by a multiuser Oracle system.

Note that even if a shared SQL area is associated with an open cursor, it may be released from the shared pool if it is not used for a long time. At this point, if an open cursor still needs to run its related statements, Oracle will reparse the statement and allocate a new shared SQL area.

When an SQL statement is submitted to Oracle for execution, Oracle automatically performs the following memory allocation steps:

(1) Oracle checks the shared pool to see if there is already a shared SQL area for this statement. If it exists, the shared SQL area is used to execute the statement. If it does not exist, Oracle assigns a new shared SQL area from the shared pool to the statement. At the same time, regardless of whether the shared SQL zone exists or not, Oracle assigns a private SQL zone to the user to hold the statement-related information, such as the value of the variable.

(2) Oracle assigns a private SQL zone to the session. The private SQL area is related to the way the session is connected.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Oracle also frees the shared SQL zone from the shared pool in the following situations:

(1) When using the Analyze statement to update or delete statistics for a table, cluster, or index, all shared SQL areas associated with the parsed object are freed from the shared pool. When the next released statement is executed, it is again parsed in a new shared SQL area based on the updated statistics.

(2) When the object structure is modified, all the common SQL areas associated with the object are identified as invalid (invalid). The statement is again parsed the next time the statement is run.

(3) If the database name (global database name) is modified, all information in the shared pool is emptied.

(4) The DBA empties the shared pool manually: ALTER SYSTEM FLUSH Shared_pool;

Shared pool can be divided into several areas, respectively by different latch (the latch number is maximum 7, can be set by the implied parameters _kghdsidx_count) protection.

Table x$kghlu can view a list of LRU in a shared pool. When one of the following conditions is met, the shared pool is divided into multiple zones, each with a different LRU list management:

(1) Prior to 10g, if the shared pool is greater than 128M and the number of CPUs is greater than 4;

(2) Oracle database version 10g

At this time, in the x$kghlu will correspond to different records.

4. Keep Shared Pool

As mentioned earlier, if Oracle resolves a pl/sql program unit, it also needs to allocate memory from the shared pool to these program unit objects. Because these objects are generally relatively large (such as packages), the allocated memory space is also relatively large. After a long running time, the shared pool may have a large amount of memory fragmentation, resulting in a failure to meet the allocation of large chunks of memory.

In order to have enough space to cache large chunks of the program, Oracle specifically built a zone from the shared pool to allocate memory to keep these chunks. The default size of this reserved shared pool is 5% of the shared pool. Its size can also be adjusted by parameter shared_pool_reserved_size. The reserved area is allocated from the shared pool, not directly from the SGA, which is the reserved portion of the shared pool for storing large chunks.

A large segment of memory larger than 5000 bytes in a shared pool is stored in the reserved portion of the shared pools. This size limit is set by the implied parameter _shared_pool_reserved_min_alloc (as mentioned previously, the implied parameter does not modify it). All memory segments smaller than this number will never be placed in the retention section except during instance startup, and large memory segments greater than this value will never be stored in the unreserved area, even if the shared pool is not in sufficient space.

The free memory of the reserved area is not included in the free list of the normal shared pool. It maintains a separate, free list. The reserved pool will not be able to be rebuilt in its LRU list (recreatable about the various states of the memory segment we'll introduce in the later section). These large segments are not cleared when the memory on the free list of the normal shared pool is freed, and the memory in the normal shared pool is not purged when the large memory segment on the free list of the reserved pool is freed.

The statistics for the reserved pool can be traced through the view v$shared_pool_reserved. Where the field Request_misses records the number of large memory segment requests that are not immediately available from the free list. This value is 0. Because the reserved area must have enough free memory to accommodate those short-term memory requests without having to clear the rebuilt segments that are not being pin-held for a long time cache. Otherwise you need to consider increasing the shared_pool_reserved_size.