Oracle architecture trilogy Memory Structure

I still want to worry about it. If you want to learn the Oracle architecture thoroughly, you must learn it based on the backup and recovery experiments and principles. I will also write a blog later. Here I am introducing the memory structure, just to do some basic understanding.

Let's first look at two confusing concepts. There is no essential difference between Sid and oracle_sid. If you really want to score one point. Then, Sid is the site identifier, that is, the session identifier. it uniquely identifies an SGA with $ ORACLE_HOME, while oracle_sid can be considered as the Instance name, check the instance_name field of V $ instance.

When DBA installs the Oracle software, it first identifies the OS as 32-bit or 64-bit. What does this 32-bit or 64-bit mean? Simply put, it is the maximum range of CPU support for memory. 32-bit CPU supports up to 4 GB memory. If Oracle runs on 32-bit Linux, the default SGA cannot exceed 1.7 GB.

The instance is created from a parameter file and exists in the memory of the operating system. On Linux, there are two commands to check whether the instance is Enabled:
PS-Ef | grep ora _ -- check whether background processes are enabled;
IPCS-M | grep ora -- check whether SGA is assigned

There are three databases on my disk. I only have one instance at any time, but there are multiple databases. At any time point, I can only access one of the databases. When multiple instances are running on a server, each instance has a dedicated SGA.

Each Oracle instance has a memory structure shared by the Oracle process, which is called SGA. When the instance is turned on, the size of each memory zone is allocated according to the minimum requirement. When ora10g uses the automatic SGA memory management (asmm: automatic shared memory management), you only need to set the sga_target parameter to the desired environment, the size of each memory block is automatically expanded based on the workload. In SGA, the smallest unit of space allocation is particles. It is determined by sga_max_size. When the sum of SGA is less than m, the size of the particles is 4 M. When SGA is greater than m, the size of the particles is 16 m.

Next let's take a look at the main SGA components.
Database_buffer_cache

Blocks in the buffer cache are managed in one location, but there are two different lists pointing to these blocks:
Dirty block list: the blocks must be written to the disk by dbwn.
Non-dirty block list: In versions earlier than 8.0, the LRU algorithm is used, and then the Contact Count algorithm is used. If a block is hit in the cache, the associated counter is added. The block buffer is no longer moved to the front of the block list as before, but is left in the block list in the original place, but increases its contact count. However, for a period of time, the block will be "Moved" in the list ". For example, a dirty block is pointed to by a dirty list. When a block needs to be reused for a period of time, if the buffer zone is full, it is necessary to release a block with a small contact count, instead, it is pointed to by the non-dirty block list.

The buffer cache allows different block sizes. You can set the db_nk_cache_size parameter and restart the database. Note the size of the SGA. If you use the expansion method, for example, if your SGA size is 128 M and you want to add another 64 m for the buffer, you must set sga_max_size to m or larger. In addition, you can also use the contraction method, that is, to reduce db_cache_size, because after 9i, the size of database_buffer_cache can be directly modified by the db_cache_size parameter, so:
Show parameter db_cache_size; -- check the current size.
Alter system set db_cache_size = xxm; -- reduce it
Alter system set db_16k_cache_size = xxm; -- sets the buffer cache for 16 K data blocks.
In this way, I have two data block sizes in database_buffer_cache. The two caches are mutually exclusive. Only to transfer tablespaces. For example, OLTP and OLAP can coexist in a database.

Cache hit (Cache hit): when a user requests a query, Oracle directly returns the data required by the user from the buffer cache to the user. If the buffer cannot be found, it is called cache miss (Cache error ).
Database_buffer_cache hit rate formula is
Cache hit ratio = 1-(physical reads/(db block gets + consistent gets ))
Db block gets: the number of data blocks obtained by the DML statement.
Consistent gets: the number of data blocks obtained by the SELECT statement.
Logical reads: number of data blocks obtained by adding db_block gets and consistent gets.
Physical reads: data read from the hard disk
Hit ratio is better than 90%

Database_buffer_cache contains three different types of cache:
Dirty Buffer: data modified but not written to the database
Free Buffer: The content here is exactly the same as the content of the data file, that is, the buffer has been written into the database and can be overwritten at any time.
Pinned Buffer: the buffer being used.

Database_buffer_cache can be divided into three different types of partitions:
Recycle pool: the data stored in the recycle pool is used only when the transaction still exists. Once the transaction ends, it is released.
Retention pool: Frequently used data
Default pool: data is placed in the default pool if no scheduled time is specified.

Shared Pool
The shared pool is designed to reuse the query plan and destroy the shared pool. The easiest way is not to bind variables. Oracle provides the cursor_sharing parameter to forcibly bind variables to SQL statements. The parameter value is similar. In 10 Gb, The shared_pool_size parameter controls the size of the Shared Pool. The main components are database cache and dictionary cache. For the relationship between the two, see my blog: http://blog.csdn.net/linwaterbin/article/details/7651038
The analysis SQL statements in the shared pool can be divided into hard parse and soft parse ,. When an SQL statement enters the Oracle database, Oracle first checks whether there are identical SQL statements in the Shared Pool. If not, parse jobs are executed. If yes, parse jobs are skipped, only check user permissions.
You can use dbms_shared_pool.keep (two parameters) to forcibly leave the SQL statement in the shared pool.
1) first find the relative position of the SQL statement:
Select address, hash_value from V $ sqlarea where SQL _text = '......';
2) keep the location in the Shared Pool:
Exec dbms_shared_pool.keep ('address', 'type ');

Redo log Buffer
The default size of the redo log buffer is controlled by the log_buffer parameter. The minimum size of this region depends on the OS. Set log_buffer to 1, and then restart the database to know the minimum value. The space of the redo log buffer is divided into multiple blocks, which are basically 512 KB.
The most important concept related to this zone is the checkpoint mechanism. That is, dbwn checks whether some redo entries have been written into the redo log file. The checkpoint mechanism avoids reading too much redo information during database recovery, resulting in a long recovery time. The general checkpoint mechanism involves the following steps:
1) The current SCN number is the checkpoint SCN.
2) Check whether related redo entries are written into the online redo log file.
3) if the data is written, dbwn flush the dirty buffer protected by redo to the disk.
4) if not, dbwn will notify lgwr to write the code, and then write the code on its own.
5) ckpt update control file and Data File Header
There are many events that trigger checkpoints, such as logs and fast_start_mttr_target.