Oracle memory structure-view memory information + retention pool and loop pool (from wenpingshu)

The Shared Pool, large pool, Java pool, and cache memory are allocated by setting the allocation unit as the basic unit. When SGA is smaller than 1 GB, the allocation unit size is
4 MB. When SGA is larger than 1 GB, the unit size is 16 Mb. The unit size can be queried through the view v $ sga_dynamic_components. This unit determines all
The status of the dynamically allocated region. For example, the following queries the size and unit size of the SGA in the system:
SQL> select component, current_size, granule_size from V $ sga_dynamic_components;

SQL> RUN
1 * select component, current_size, granule_size from V $ sga_dynamic_components

Component current_size granule_size
--------------------------------------------
Shared Pool 67108864 4194304
Large pool 4194304 4194304
Java pool 4194304 4194304
Streams pool 0 4194304
Default buffer cache 205520896 4194304
Keep buffer cache 0 4194304
Recycle buffer cache 0 4194304
Default 2 K buffer Ca 0 4194304
Che

Default 4 K buffer Ca 0 4194304

Component current_size granule_size
--------------------------------------------
Che

Default 8 K buffer Ca 0 4194304
Che

Default 16 K buffer C 0 4194304
Ache

Default 32 K buffer C 0 4194304
Ache

Component current_size granule_size
--------------------------------------------
ASM buffer cache 0 4194304

13 rows selected.

SQL>

|

Retention pool and loop pool

Obviously, the most commonly used data should be stored in the memory structure for a long time. The purpose of the retention pool is to store some "hot" data blocks for a long time. That is to say, you should try to find out the "hot" tables in your system, and bind it to the reserved pool.
The system parameter db_keep_cache_size defines the size of the reserved pool space. The data block size is the standard data block size of the system. It should be noted that the reserved pool is not
A subset of the default pool. Therefore, you must note that the size should not exceed the capacity available by the operating system.
When faced with a engineering problem: there are hundreds of tables in the business database system, and there are thousands of indexes, which tables or indexes should be placed in the retention pool?

The selection rules are as follows:
The data in this table is not very large.
This table is frequently called in SQL operations.
This table is usually an intermediate or reference table in the database object relationship diagram.

Contrary to the significance of the retention pool, some large tables with few SQL operations do not need to be kept in the data cache for a long time.
At this time, we can set the loop pool to put these objects into the loop pool.

The loop pool is specified by the initialization parameter db_recyble_cache_size. Similarly, the loop pool is not a subset of the default pool. The following command sets db_keep_cache_size Initialization
Parameter. The size of the reserved pool is positioned at 120 MB:
SQL> alter system set db_keep_cache_size = 120 m scope = both;

 

|