O-understanding the sharing pool

We can use the show sga command to view the overall components of the Shared Pool:

... Waiting ....

I. SGA Memory Structure

In Oracle, SGA mainly includes:

1. FIXED data structure (FIXED Size) 2. data block BUFFER (database buffer) 3. redo log buffer (redo buffers) 4. Shared Pool (in variable size ).

The fixed area contains the addresses, pointers, resources, and locks of all other shared memory structures and important data structures. You can use the following command to list details:

Select pool, name, bytes from v $ segstat where pool = 'shared pooled ';

As the name suggests, a shared pool is a buffer pool set for everyone to share data, and a shared pool is also a buffer pool for many sessions to allocate shared memory for their own SQL Execution, these sessions allocated in the shared pool are frequently used. You must allocate them multiple times at a time and share them with other sessions in the instance.

Ii. SGA Memory Allocation

The db cache can be evenly allocated according to the data block size rules, so there is no fragmentation problem, and the db cache allocation and return are relatively simple. However, unlike the db cache, the space required for each session to allocate space in the Shared Pool varies greatly, sometimes dozens of bytes, sometimes hundreds of bytes or even megabytes, therefore, it is very complicated to allocate and return the Shared Pool. To ensure the access performance of shared data in the Shared Pool, each memory allocation in the shared pool must be continuous memory space. Due to this feature, after a period of time, some fragments may appear in the shared pool more or less, which are distributed among some continuously allocated spaces.

The memory space allocated in the shared pool is permanently used, but is not released and marked as PERMANENT. These structures are mostly allocated when the instance is started, and some are allocated during system operation, however, these memories are only allocated and will not be released before the instance is closed, such as process information data, session data, and special content process segments. The process and session information are allocated through the PROCESSES and session parameters. The instance is allocated at one time when it is started, and will not be dynamically expanded after the allocation. Therefore, to increase the processes parameter, you must restart the instance, in addition, memory arrays for some special purposes are also allocated when the instance is started. They are dynamically scalable. They are only allocated but not released. They can be dynamically scalable in the future, for example, enqueues (locks), enqueue resource, transactions, transaction branches, and so on. For RAC, there are also gcs resource and ges resource. If these Dynamic Scaling actions occur frequently, a large number of small fragments cannot be released in the continuous space of the Shared Pool, resulting in fragmentation of the Shared Pool. (Typical case: In 9i, shared fragment problems are often caused by ges resource extension, and 4031 errors are generated. Version 10.2 has been improved. For more information, see Baidu)

Some Shared Pool instances can be released after allocation. These memories are marked as freeable or recreateable. Freeable can be directly released, and can be released after unpin exists in the recreateable, so they are all reusable memory.

Ii. Share pool management

The Shared Pool memory is managed through the general ORACLE memory management (generic oracle memory manager). This is what we call KGH heap manager. Under the KGH mechanism, all the FREE memory of the Shared Pool is mounted on the idle linked list called freelists. This idle linked list is created based on the bucket mechanism and depends on the size of the idle memory segment, hanging on different buckets. For example, the shared pool freelists of 9i contains 256 buckets, and each Bucket is connected with idle memory blocks of different sizes.

* For buckets smaller than 812B, the increment is 4B, for example, 16B, 20B, 24B ...... 812B;

* For buckets larger than 812B, The step size of 64B is increased, for example, 876B, 940B,... 4012B;

* A bucket exceeding 4096 B is multiplied by a factor of, such ,.....

When a session needs to allocate space from the shared pool, it will find a bucket based on the Size allocated by itself, and then find an idle memory, resulting in the remaining memory, it will be mounted to the corresponding bucket for other sessions to be allocated, and the released memory will be mounted to freelists. In order not to be fragmented, the released memory will be automatically merged, if two memory segments are adjacent, ORACLE merges them into one.

The size of the shared pool is defined by the shared_pool_size parameter. You do not need to set this parameter in the Automatic Memory Management Mode. If this parameter is set, the shared pool uses this parameter as the initial size and minimum value, regardless of how the memory is adjusted, the size of the Shared Pool cannot be smaller than this parameter.

Iii. Internal Structure of the Shared Pool (HEAP)/* It is difficult to continue here... pay attention to the 64 pages of <DBA idea sky> */

The HEAP mode is used for ORACLE memory space allocation. The basis of HEAP management is KGH. Common memory HEAP in ORACLE includes sga heap and pga heap. In addition, most of the tables are also HEAP tables. The basic concept of space management is KGH.

 

Iv. Brief description of Shared Pool dump files

The dump file contains several hundred megabytes of memory in Shenzhen. The basic commands for dumping the content of the shared pool to the file can be used to dump the content of LIBRARY_CACHE to the TRACE file:

Alter session set events 'immediate trace name LIBRARY_CACHE level <level> ';

Level indicates the level. The meanings of different levels after 9.2 are as follows:

* Level = 1 the cache statistics for the database to be transferred.

* Level = 2 dump hash Summary

* Level = 4: the object to be cached in the database, which only contains basic information

* Level = 8: The cache object of the OSS, including the child reference and pin waiters)

* Level = 16 increase heap size information

* Level = 32 add heap Information