Introduction to the system global zone of Oracle 10 Gb Memory Structure

We know that the memory structure is one of the most important parts of the Oracle architecture. According to the system's different memory usage methods, it can be dividedSystem global ZoneSGA), program global PGA), sorting Area Sort Area), Large Pool), and java Pool). This article first introducesOracle 10 Gb Memory StructureAnd then we will introduce this part.

System Global Area)

It is a group of Memory sharing structures allocated to the system. It can contain data and control information of a database instance. If multiple users connect to an instance, data can be shared by multiple users in the global zone of the instance, which is also called the shared global zone. The global zone of the system can be divided into data buffer, log buffer, and sharing pool according to its function.

Data Buffer Zone:

The data buffer is used to share the data read from the disk.

The modified data and inserted data are stored in the data buffer. When the modification is completed or other conditions of the DBWR process are triggered, the data is written to the data file.

How data buffer works:

LRU Least recently used): This is short for the principle of Least recent use. It is a management mechanism of a data buffer. It only retains the latest data, not the old data.

Dirty: Indicates Dirty data. Dirty data is the data that has not been written to the data file after modification.

The memory setting parameters of the Oracle10g Database are no longer determined by DB_BLOCK_BUFFERS, but by the new oracle parameters DB_CACHE_SIZE and DB_nK_CACHE_SIZE. Different data segments can use different data blocks. Large tables can be stored in large data block tablespaces, while small tables can be stored in small data block tablespaces to optimize I/o performance. You can use the standard data block DB_BLOCK_SIZE to determine the system tablespace, temporary tablespace, and Other Default tablespaces.

The standard data block DB_BLOCK_SIZE is used for system tablespace and default tablespace. Other tablespaces can be used when non-standard data block BLOCKSIZE is used to create a tablespace. The values are 2 k, 4 k, 8 k, 16 k, 32 k, respectively, the data buffer of non-standard data blocks is determined by the DB_Nk_CACHE_SIZE parameter.

It should be noted that BLOCKSIZE cannot be used for standard blocks. If DB_BLOCK_SIZE = 2048 is set, DB_2K_CACHE_SIZE cannot be set. The standard block must be set using the DB_CACHE_SIZE parameter. You can also modify the data buffer parameters online: SQL> alter system set db_2k_cache_size = 10 M. to query the data buffer size, you can: SQL> show parameter db.

When creating different data block tablespaces, you must use the BLOCKSIZE parameter to indicate the size of the data block, and use DB_Nk_CACHE_SIZE In the parameter file for configuration, which corresponds to the number of BLOCKSIZE, otherwise, an error occurs.

When setting dynamic memory, you can write multiple parameters to the parameter file in the following format:

 
 

  
  
# cache and i/o  
  
  
 DB_BLOCK_SIZE=4096 
  
  
 DB_CACHE_SIZE=20971520 
  
  
 DB_2K_CACHE_SIZE=8M 
  
  
 DB_8K_CACHE_SIZE=4M 
  
  
 …….. 
 
 

The DB_CACHE_SIZE parameter is only applicable to system tablespace, temporary tablespace, and default tablespace. DB_2K_CACHE_SIZE is suitable for 2 k tablespaces with BLOCKSIZE. The same is true for 8 K.

The data buffer has a direct impact on the database access speed. Generally, the buffer hit rate should be above 90%. For example, use the data dictionary v $ sysstat to calculate the data buffer hit rate:

 
 

  
  
SQL> select a.value+b.value “logical_reads” , c.value “phys_reads”,  
  
  
      Round(100* ( ( a.value+b.value)- c.value) /  
  
  
      ( a.value+b.value ) ) “buffer hit radio “  
  
  
      From v$sysstat a, v$sysstat b,v$sysstat c  
  
  
      Where a.statistic#=38 and b.statistic#=39 and c.statistic#=40; 
 
 

The following is another method for calculating the data buffer hit rate:

 
 

  
  
SQL> select name, value  
  
  
      From v$sysstat  
  
  
      Where name in ( ‘session logical reads’,’physical reads’,physical reads direct’, ‘physical reads direct (lob)’); 
 
 

Where: Session logical reads is the total number of reads. Physical reads is read from the data file. Physical reads direct is read from the buffer without lobs ). Physical reads direct (lobs) is read from the buffer zone with lobs ). Hit Ratio = 1-(physical reads-physical reads direct (lob)/session logical reads) = 95%.

Log Buffer

The log buffer is used to store database modification information. Log information is first generated in the log buffer. When the number of logs in the log buffer reaches a certain level, the log writing process LGWR writes the log data to the log file group, and then switches, the archive process ARCH writes log data to the archive media.

The log buffer size is determined by the LOG_BUFFER parameter. to query the log buffer size, use the following method:

Method 1: In the parameter file:

 
 

  
  
 ……  
  
  
 Processes = 150 
  
  
 Parallel_max_servers = 5 
  
  
Log_buffer = 32768 
  
  
…….. 
 
 

Method 2:

 
 

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

Method 3:

 
 

  
  
SQL> show parameter log_buffer 
 
 

The failure rate can be calculated for the log buffer, and the failure rate of the log buffer is calculated using the data dictionary v $ latch.

 
 

  
  
SQL>select name,gets,misses,immediate_gets,immediate_misses,  
  
  
     Decode(gets,0,0,misses/gets*100) ratiol,  
  
  
     Decode (immediate_gets+immediate_misses,0,0,  
  
  
immediate_misses/(immediate_gets+immediate_misses)*100)   ratio2  
  
  
     from v$latch  
  
  
     where name in (‘redo allocation’, ‘redo copy’); 
 
 

Where

Gets indicates the number of times the log buffer is successfully waiting.

Immediate gets indicates the number of times the log buffer is successfully obtained immediately.

Immediate misses indicates the number of times the log buffer is not successfully obtained immediately.

Waiting indicates that when a log enters the log buffer, the log buffer is too small and there is no free space. Therefore, the failure of the log buffer can indicate whether the log buffer is large enough or not, user log writing will generate a waiting process. The failure rate of the log buffer should be less than 1%.

In addition, you can query the number of times a user process waits for the log buffer, obtained through the data dictionary v $ sysstat:

 
 

  
  
SQL> select name,value from v$sysstat  
  
  
      Where name = ‘ redo buffer allocation retries’ ; 
 
 

Here is an introduction to the global zone of the Oracle 10g memory structure system. I hope this introduction will be helpful to you!