Oracle Architecture: Memory structure and process architecture

oracle| Process | system

(i) Memory structure and process structure the overall structure of the Oracle database is shown in the following illustration:

1:oracle instance (Instance)
In a server, each running Oracle database is associated with a database instance that we
Means of accessing the database.
An instance is identified by Oracle_sid in the operating system, identified by the parameter instance_name in Oracle,
The values for these two are the same. When the database is started, the system first allocates the system global Area (SGA) in server memory,
Forms the memory structure of Oracle, and then initiates several operating system processes that are resident in memory, which consists of Oracle
The process structure, memory area, and background process are called an Oracle instance.

The relationship between database and instance is 1 pairs of 1/n, each Oracle database in a non parallel database system and a
instance, in a parallel database system, a database corresponds to multiple instances, and at the same time the user only
instance, when one instance fails, other instances are automatically serviced to ensure the normal operation of the database. In any
Case, each instance can only correspond to one database.

2:oracle 10g dynamic memory management
Memory is an important factor affecting database performance, oracle8i using static memory management, Oracle 10g use dynamic
Memory management. The so-called static memory management, is in the database system, regardless of whether there is a user connection, and regardless of concurrent use
Size, a fixed size of memory is allocated as long as the database service is running, and dynamic memory management allows the database to be served
When the operation of the memory of the size of the change, read large blocks of memory using large, small blocks of small memory, read
Standard memory settings are used when taking standard memory blocks.
Depending on how the system uses memory, the memory of the Oracle database can be divided into the following sections:
• System global: SGA (System global Area)
• Program Global: PGA (programe global area)
• Sort pool: (sort area)
• Big pool: (Large Pool)
Java Pool: (Java Pool)

2-1: System Global Region SGA (Systems global Area)
The SGA is a set of shared memory structures allocated to the system that can contain data or control letters for a database instance
Interest. If multiple users are connected to the same database instance, in the SGA of the instance, the data can be shared by multiple users.
When the database instance is started, the memory of the SGA is allocated automatically, and the SGA memory is reclaimed when the database instance is closed.
The SGA is one of the most memory-intensive areas and also an important factor affecting database performance.
The information about the SGA can be queried by the following statement, and the size of the sga_max_size cannot be dynamically adjusted.
=====================================
Sql> Show Parameter SGA
NAME TYPE VALUE
------------------------------------ ----------- --------
LOCK_SGA Boolean FALSE
PRE_PAGE_SGA Boolean FALSE
Sga_max_size Big Integer 164M
Sga_target Big Integer 0

Sql> alter system set sga_max_size=100m;
Alter system set sga_max_size=100m
*
ERROR at line 1:
Ora-02095:specified initialization parameter cannot be modified
======================================

The system global area can be divided into different functions:
• Data buffers
• Log Buffers
• Shared Pool

2-1-1: Data buffer (database buffer Cache)
Oracle must read all blocks of data from disk every time an action is performed and after changing it
Each piece must also be written to disk, obviously very inefficient. Data buffers store data that needs to be accessed frequently.
For use by all users. When you modify the data, you first remove the data from the data file and store it in the data buffer.
The Modify/Insert data is also stored in a buffer, commit or DBWR (described below) other strips of the process
When a piece is raised, the data is written to the data file.
The size of the data buffer can be dynamically adjusted, but it cannot exceed the sga_max_size limit.
======================================
Sql> Show Parameter Db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------
Db_cache_size Big Integer 24M

Sql> alter system set db_cache_size=128m;
Alter system set db_cache_size=128m
*
ERROR at line 1:
Ora-02097:parameter cannot to modified because specified value is invalid
Ora-00384:insufficient Memory to grow cache

Sql> alter system set db_cache_size=20m;
System altered.

Sql> Show parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
Db_cache_size Big Integer 20M

#此处我仅增加了1M都不行?
Sql> alter system set db_cache_size=25m;
Alter system set DB_CACHE_SIZE=25M
*
ERROR at line 1:
Ora-02097:parameter cannot to modified because specified value is invalid
Ora-00384:insufficient Memory to grow cache

#修改显示格式 for easy viewing.
Sql> Column name format A40 wrap
Sql> column value Format a20 wrap

#下面语句可以用来查看内存空间分配情况, pay attention to the total size of the SGA area.
Sql> Select Name,value from v$parameter where name like '%size ' and value <> ' 0 ';

#先将java_pool_size调小, and then modify the Db_cache_size
Sql> Show parameter java_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- -----
Java_pool_size Big Integer 48M

Sql> alter system set java_pool_size=20m;
System altered.

Sql> alter system set java_pool_size=30m;
System altered.
#上面说明SGA中各区大小总和不能超过sga_max_size.
=====================================
The size of the data buffer has a direct impact on the storage speed of the database, especially when multi-user. Some should
High requirements for speed, generally require data buffer hit ratio of more than 90%.
A method for calculating the hit rate of a data buffer is given below:
• Using a data dictionary V$sysstat
=====================================
Sql> select name, value from V$sysstat
2 where name in (the ' session logical reads ',
3 ' physical reads ',
4 ' physical reads direct ',
5 ' physical reads Direct (LOB) ')
NAME VALUE
------------------------------- ----------
Session logical Reads 895243
Physical reads 14992
Physical reads Direct 34
Physical reads Direct (LOB) # 0
======================================
Hit Rate =1-(14992-34-0)/895243

A suggestion that Oracle can give data buffer size:
======================================
Sql> alter system set Db_cache_advice=on; #打开该功能
System altered.

Sql> alter system set Db_cache_advice=off; #关闭该功能
System altered.
======================================

2-1-2: Log buffers (logged buffer Cache)
The log buffer is used to store modification information for the database. This area has a small impact on database performance, and the day
There will be a detailed description of the blog after.
Query Log buffer size:

As shown above: stack space is a storage area used to store user session variables and arrays; user session data is an additional store that is used for user sessions. |--session Information |--sort Area |--cursor Information Note The session information (user sessions information) is in the exclusive server and in the shared server The memory area is different. 2-3: Sort district, Large pool, Java pool The sort region (sort area) provides memory space for SQL statements that have sorting requirements. The system uses a dedicated memory area field to sort the data, which is the sort area. In an Oracle database, the sort of user data can be used Two regions, one is the memory sort area, one is the disk temporary segment, the system takes precedence to use the memory sort area to sort. If there is not enough memory, Orcle automatically uses the disk temp table space for sorting. To improve the speed of data sorting, it is recommended that Use the memory sort area as much as possible, rather than using a temporary segment. Parameter sort_area_size is used to set the sort area size. (as if not dynamically modified?) ） Dachi (Large Pool) is used for the database backup tool-recovery Manager (Rman:recovery Manager). The size of the Large pool is determined by large_pool_size and can be queried and modified with the following statement: ========================================= Sql> Show Parameter Large_pool_size NAME TYPE VALUE -----------------   ----------- ------- Large_pool_size Big Integer 8M Sql> alter system set large_pool_size=7m; System altered. ========================================= Java pools are primarily used in Java language development and are generally not less than 20M. Its size comes from java_pool_size OK, you can adjust dynamically. 2-4:oracle Automatic shared memory management (Automatic shared Memory (SGA) Management) In Oracle 8i/9i, the database administrator must manually adjust the values of each parameter in the SGA area, depending on the The load weight is set separately, if improper setting, for example, when a certain area load increases, does not adjust this area memory size, then There may be ora-4031:unable to allocate ... bytes of shared memory errors. In Oracle 10g, set the parameter statistics_level to Typical/all, using the sga_target point The total size of the SGA area, the database automatically allocates the memory size to each component as needed. The following are areas where the system is automatically adjusted: • Fixed SGA area and other • Shared pools • Data buffer java Chi Dachi. Note: If you do not set Sga_target, the automatic Shared memory management feature is disabled. ========================================== Sql> Show Parameter Statistics_level NAME TYPE VALUE --------------------- ----------- ------------ Statistics_level string Typical Sql> alter system set Statistics_level=all; System altered. #typical和all有什么区别? Sql> alter system set statistics_level=typical; System altered. Sql> Show Parameter Sga_target NAME TYPE VALUE ------------- ----------- ---------- Sga_target Big Integer 0 Sql> alter system set sga_target=170m; Alter system set sga_target=170m * ERROR at line 1: Ora-02097:parameter cannot to modified because specified value is invalid Ora-00823:specified value of Sga_target greater than sga_max_size Sql> alter system set sga_target=20m; System altered.    #不过后来又发现sga_target的值变成了140M? The following is the execution of the statement.   SQL> Show parameter Sga_target   NAME           type        VALUE   -------------------------------   sga_target    Big integer 140M      SQL> alter system Set sga_target=0;   system altered. Sql> Show Parameter Sga_target NAME TYPE VALUE ------------------------------------ ----------- ------ Sga_target Big Integer 0 #改为20M Sql> alter system set sga_target=20m; System altered. #显示的是140M Sql> Show Parameter Sga_target NAME TYPE VALUE ------------------------------------ ----------- ------ Sga_target Big Integer 140M #不可缩减? Sql> alter system set sga_target=130m; Alter system set sga_target=130m * ERROR at line 1: Ora-02097:parameter cannot to modified because specified value is invalid Ora-00827:could not shrink sga_target to specified value #不可增加 Sql> alter system set sga_target=141m; Alter system set SGA_TARGET=141M * ERROR at line 1: Ora-02097:parameter cannot to modified because specified value is invalid Ora-00823:specified value of Sga_target greater than sga_max_size 3:oracle the process structure of the instance (Structure) Oracle contains three types of processes: • User Process • Server process • Background processes (Background process) 3-1: User processes and Server processes Initiates a user process when a database user requests a service to connect to Oracle (for example, when Sqlplus is started). • The user process must first establish a connection. • Users cannot be directly associated with Oracle servers and must interact through the server process. • The server process is a bridge between user processes and servers that can interact directly with Oracle server. • Server processes can have two forms of sharing and exclusivity. 3-2: Background processes (Backgroung process) The interaction between the physical structure of the database and the memory structure is done through a background process. Background Process Pack for database There are two types, one is necessary, the other is optional: Mandatory background processes 3-2-2:LGWR (log Writer, Journal write process) Writes log data from the log buffer to the disk log file group. When the database is running, if the database is repaired Gerze generates log information, and log information is first generated in the log buffer. When the log reaches a certain number, the LGWR will Writes log data to the log file group, then logs switch, and the archive process (ARCH) writes log data to File process (provided that the database is running in archive mode). The database follows the write-log precedence principle, that is, before data is written Write the log first. Simple Introduction Arch (archiver, archive process) Oracle databases have two modes of operation, Archive (Archivelog), and not archive (noarchivelog) mode. Runtime log in non-archive mode is directly overwritten when switching, does not produce archive logs, which is the default runtime model for the database Expression When the database is running in archive mode, the arch process writes the log information to disk before the log switch, which is Automatically backs up online logs. Oracle databases have a limited number of redo files, so Oracle writes to them in a circular fashion. It shun The sequence is filled with every redo file, and when the last one is reached, recycle back to start filling out the first redo file. If the Can restore the database and want to save the log files, then they need to be backed up before they are reused, archived into Process to manage this work. ckpt (Check point, checkpoint process) When running CKPT, the system modifies the synchronization signals of all data files and control file headers to ensure the data The synchronization of the library. The checkpoint appears in the following situations: |--generated when each log is switched. |--The last checkpoint was followed by a specified time. |--After the last checkpoint, after a predefined number of log blocks are written to disk. | |-database shutdown. |--dba forced generation. |-When the table space is set to offline. Smon (System moniter, systems monitoring process) Smon performs instance recovery when the instance is started and cleans up temporary segments that are no longer in use. Pmon (Process Monitor, progress monitoring) Pmon restores when a user process fails, responsible for cleaning up the memory area and releasing the resources used by the process. reco (Recovery, recovery process) Reco is used for the consistency of data in a distributed database maintained in a distributed environment. Lckn (lock, lock process) A blockade between multiple instances in a parallel server. dnnn (Dispatcher, dispatch process) DNNN exists in a multithreaded server architecture, is responsible for connecting the user process to the server process, and then returning the results Back to the user process.