Introduction to Oracle 10 Gb memory structure in sorting area and Java pool

Previously we introduced:Oracle 10 Gb Memory StructureThis article describes the knowledge and usage of the Sharing pool, including:Program global Zone,Sorting area,Java poolAnd so on. Next we will introduce this part.

Program global Zone

The global area of the program is the memory area that contains data and control information of a single user or server. The global area of the program is automatically allocated by oracle when a user process connects to the oracle database and creates a session. Unlike sga, pga is not a shared area and is mainly used for storing variables and arrays during programming. In this region, stack space is the storage area used to store user session variables and arrays, and user session data is the additional area used by USER sessions. User session ends pga release.

Sorting area

Sort Area provides memory space for sal statements with sorting requests. The system uses a dedicated memory Area to Sort data, user Data Sorting can be divided into two types: Memory sorting area and disk temporary segments. The system preferentially uses the memory sorting area for sorting. If the memory is insufficient, oracle automatically uses the tablespace of the temporary disk segment for sorting.

To understand the size of the sorting area, you can view the sort_area_size method as follows:

Parameter file:

 
 

  
  
….  
  
  
Compatible = 8.1.0  
  
  
Sort_area_size = 65536 
  
  
Sort_area_retained_size = 65536 
  
  
….. 
 
 

Method 2:

 
 

  
  
SQL>select name,value from v$parameter where name like ‘%size’; 
 
 

Method 3:

 
 

  
  
SQL> show parameter sort_area_size 
 
 

If you want to know the usage of the sorting area in the database and confirm the memory sorting volume and disk sorting volume, you can query the data dictionary v $ sysstat

SQL> select name, value from v $ sysstat where name like '% sort % ';

Large pool

Large pool) used for database backup tools-recovery manager rman)

The parameters of the large pool are determined by large_pool_size.

There are three methods to query this value. Here we will introduce one of the methods:

SQL> show parameter large_pool_size

Java pool

The size of java_pool_size should be no less than 20 mb.

Oracle10g automatic shared memory management

In oracle, set the STSTISTICS_LEVEL parameter to the TYPICAL default value) or ALL. Use the New Parameter SGA_TARGET to specify the total size of the SGA memory allocation. You do not need to define SHAR_POOL_SIZE DB_CACHE_SIZE and other parameters, the database server is automatically allocated as needed. Note that if sga_targe is not available, the automatic shared memory management function is disabled and you can still set each parameter. After SGA_TARGET is set, other parameters are set to 0 by default.

Congratulations on setting the minimum memory parameter limit for memory management: if the database administrator understands the memory size required by an application, you can specify the minimum value for the memory component. You can set the minimum value with the corresponding parameters, for example:

 
 

  
  
SGA_TARGET= 256m 
  
  
SHARE_POOL_SIZE= 32M 
  
  
DB_CACHE_SIZE = 100M 
 
 

That is, during automatic allocation, the value of the Shared Pool and data buffer must be no less than 32 m and 100 m, and the remaining 124 m memory will be allocated to other components. You can query the data dictionary v $ sga_dynamic_components to learn the distribution values of each component.

After the automatic shared memory management function is enabled, you can also manually set the automatically set parameters. For example, if pai_pool_size is set to a small value for the sga component parameter, it does not take effect immediately, for example, if SGA_TARGET = 512 M, pai_pool_size = 256 M, and the current shared pool is 284 M, if dba dynamically reduces the value of pai_pool_size, it does not work when it is lower than m or a lower value. If the parameter value exceeds the current component value, the value of the component increases accordingly. If the value of pai_pool_size is 300 M, the shared pool is increased to 300 M.

When the memory is automatically managed, when the parameter value increases, the additional memory is used for automatic adjustment, and vice versa.

In addition, some sga parameters must be set by the database administrator and will not be automatically adjusted. These parameters include:

• LOG_BUFFER
• DB_KEEP_CACHE_SIZE
• DB_RECYCLE_CACHE_SIZE
• DB_nK_CACHE_SIZE
• STREAMS_POOL_SIZE

These parameter settings will affect the automatic allocation of other parameters, and the available memory changes ).

Manual memory management to Automatic Memory Management

To switch from manual memory management to automatic memory management, you only need to add the SGA_TARGET parameter. First, calculate the values of each SGA parameter, and add 16 Mfixed SGA overhead) to obtain the total size of SGA. Then, the values of each memory component are removed from the parameter file. For example:

• Pai_pool_size = 256 M
• DB_CACHE_SIZE = 512 M
• LARGE_POOL_SIZE = 256 M
• LOG_BUFFER = 16 M

SGA_TARGET = 256 + 512 + 256 + 16 + 16 = 1056 M, you can also query the data dictionary to get:

 
 

  
  
SQL> select ( ( select sum(value) from v$sga ) – (select current_size from v$ sga_dynamic_free_memory ) ) “sga_target” from dual; 
 
 

For example, if a manually managed instance is SGA_MAX_SIZE = 1200 M

 
 

  
  
SHARE_POOL_SIZE = 200M 
  
  
DB_CACHE_SIZE = 500M 
  
  
LARGE_POOL_SIZE = 200M 
 
 

The query result is as follows:

 
 

  
  
SQL> select sum (value) from v $ sga; 1200 m
  
  
SQL> select current_size from v $ sga_dynamic_free_memory;
 
 

The result is 208 M.

SGA_TARGET = 1200-208 = 902 M.

Cancel pai_pool_size DB_CACHE_SIZE LARGE_POOL_SIZE from the parameter file

In the automatic memory management mode, if spfile is applied to the database, after the database is closed, the system records the status of the Last Database shutdown, the value of each component of the adjusted memory parameter will be saved in the parameter file. Therefore, if you use automatic memory management, it is best to use spfile.

This article introduces Oracle 10 Gb memory structure sorting area and Java pool. We hope this article will help you gain some benefits.