Solution to insufficient Oracle database shared memory allocation

Oracle DatabaseMedium,Shared MemoryWhen the allocation is insufficient, because the reserved area is not effectively used, it will lead to the failure to allocate a suitable shared area. The system global area (SGA) of an ORACLE routine contains several memory areas, including buffer cache, Shared Pool, Java pool, large pool, and redo log buffer) SGA = db_cache + shared_pool + java_pool + large_pool. Next we will introduce the processing method.

Solution:

Manually adjust the size of the SGA, and then re-allocate the size of the four major memory areas. It mainly adds shared memory and buffered high-speed cache.

 
 

  
  
SQL> show sga; // view the specific size of SGA.
  
  
 
  
  
SQL> show parameter sga_max_size // view the maximum SGA Value
  
  
 
  
  
SQL> show parameter shared_pool // view shared memory
  
  
 
  
  
SQL> show parameter db_cache // view the data cache
  
  
 
  
  
SQL> show parameter java_pool
  
  
 
  
  
SQL> show parameter large_pool
 
 

The following is a solution for allocating 450 threads;

 
 

  
  
SQL> alter system set sga_max_size = 500 M scope = spfile; // modify the maximum SGA value.
  
  
 
  
  
SQL> alter system set shared_pool_size = 240 M scope = spfile; // modify the shared memory
  
  
 
  
  
SQL> alter system set db_cache_size = 72 M scope = spfile; // modify the data cache
  
  
 
  
  
SQL> alter system set java_pool_size = 144 M scope = spfile;
  
  
 
  
  
SQL> alter system set large_pool_size = 24 M scope = spfile;
 
 

By default, the system allocates 150 threads:

SGA (164 M) = db_cache (24 M) + shared_pool (80 M) + java_pool (48 M) + large_pool (8 M)

The knowledge about Oracle database shared memory allocation is introduced here. I hope this introduction will bring you some gains!