Oracle memory structure and instance Management

Oracle memory structure and instance Management

1. Memory Structure:

The overall structure of the Oracle database is as follows:

Each Oracle database is composed of an Oracle Instance and a database (data file, control file, and redo log file). The so-called Instance is the medium for the user to interact with the database, you can operate a database by connecting to an instance. The instance is composed of a unified memory structure (SGA, PGA, UGA) and a batch of memory resident processes. The instance is identified by ORACLE_SID in the operating system and the INSTANCE_NAME parameter in Oracle. The values of the two instances are the same. When the database is started, the system first allocates the system global zone (SGA) in the server memory, forming the Oracle memory structure, and then starts several operating system processes with resident memory, it constitutes the process structure of Oracle, and the memory area and background process are collectively called an Oracle instance.

The instance structure is as follows:

1.1 memory impact on the database:

Memory is an important factor affecting database performance. Oracle8i uses static memory management and Oracle 10 Gb uses dynamic memory management. Static memory management refers to the allocation of a fixed size of memory in the database system, regardless of whether there is a user connection or the amount of concurrent use, as long as the database service is running; dynamic Memory Management allows you to modify the memory size when the database server is running, use large memory when reading big data blocks, and use small memory when reading small data blocks, use the standard memory settings when reading standard memory blocks.

1.2 internal structure and management of SGA:

SGA is a group of shared memory structures allocated to the system. It can contain data or control information of a database instance. If multiple users connect to the same database instance, data in the instance's SGA can be shared by multiple users. When the database instance is started, the SGA memory is automatically allocated. When the database instance is closed, the SGA memory is recycled. SGA is the region with the largest memory usage and an important factor affecting database performance.

Query using the following statement

SQL> show parameter sga NAME

Type value -------------------------------------------------------

Lock_sga Boolean false pre_page_sga Boolean false sga_max_size big integer 164 m sga_target big integer 0

The size of sga_max_size cannot be dynamically adjusted.

For example:

SQL> alter system set sga_max_size = 100 m;

Alter system set sga_max_size = 100 m

* ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified

The global zone of the system can be divided into: • data buffer • log buffer • Sharing pool • large pool • Java pool

The last two items are optional.

1.2.1 data Buffer (Database Buffer Cache) if each operation is performed, Oracle must read all data blocks from the disk and write each block to the disk after it is changed, obviously, the efficiency will be very low. The data buffer zone stores frequently accessed data for all users. When modifying data, first retrieve data from the data file and store it in the data buffer. Modify/insert data is also stored in the buffer. commit or DBWR (details are provided below) when other conditions of the process are triggered, data is written to the data file. The size of the data buffer can be dynamically adjusted, but cannot exceed the limit of sga_max_size.

Query using the following statement

SQL> show parameter db_cache_size

Name type value ------------------------------------ ----------- ------------------- db_cache_size big integer 24 M

Modify the data buffer size:

SQL> alter system set db_cache_size = 20 m;

System altered.

# The following statement can be used to view the memory space allocation. Pay attention to the total size of each region in SGA. SQL> select name, value from v $ parameter where name like '% size' and value <> '0 ';

Other parts of the SGA area can be modified, so that the size of the Java Pool cannot exceed the sga_max_size limit. For example, use the following statement to modify the size of the Java pool.

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

The size of the data buffer has a direct impact on the database access speed, especially for multiple users. Some applications require high speed. Generally, the data buffer hit rate is higher than 90%.

The following describes how to calculate the data buffer hit rate:

• Use the data dictionary v $ sysstat ==================================== ========================================

SQL> select name, value from v $ sysstat

2 where name in ('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

Let Oracle give suggestions on the data buffer size: ========================================================== ======================================

SQL> alter system set db_cache_advice = on; # enable this function System altered.

SQL> alter system set db_cache_advice = off; # disable this function System altered.

1.2.2: the Log Buffer is used to store database modification information. This area has little impact on database performance.

Query log buffer size:

SQL> show parameter log_buffer

Name type value ----------------------------

Log_buffer integer 262144

1.2.3: the Share Pool is the memory area for SQL, PL/SQL program syntax analysis, compilation, and execution.

It consists of three parts: • The Library Cache contains the analysis code and execution plan of SQL, PL/SQL statements. • Data Dictionary Cache Table, column definition, and permission. • User MTS session information in the Usr Global Area.

The size of the three parts cannot be defined separately. The size of the share pool must be indirectly defined through the share pool and can be dynamically modified: ========================================================== ======================================

SQL> show parameter shared_pool_size

Name type value -----------------------------------------------------__

Shared_pool_size big integer 80 M

Shared_pool_size big integer 80 M

SQL> alter system set shared_pool_size = 78 m

System altered.

2. Program Global PGA (Programe Global Area)

The global area of the program is the memory area that contains the data and control information of a single user or server. It is automatically allocated by Oracle when the user process connects to Oracle and creates a session. PGA contains information about the operating system resources used by the process and the status of the process, while the Oracle shared resources used by other processes are in SGA. PGA is private rather than shared. This mechanism is necessary because these resources can be cleared and released when the process dies. In addition, the structure of PGA is related to the running mode of Oracle. The structure of PGA is different in two running modes: Dedicated Server and Shared Server. As shown in:

For example:

• Stack Space is the storage area used to store user session variables and arrays;

• User Session Data is an additional storage area for User sessions.

| -- Session Information

| -- Sort Area

| -- Cursor Information

Note that Session information is different from the memory area on the shared server in the exclusive server.

3. sorting area, large pool, and Java pool

The sorting Area provides memory space for SQL statements with sorting requirements. The system uses a dedicated memory area to sort data. This space is the sorting area. In Oracle databases, user data can be sorted in two areas: Memory sorting area and disk temporary segments. The system uses the memory sorting area for sorting first. If the memory is not enough, Orcle automatically uses the temporary disk tablespace for sorting. To speed up data sorting, we recommend that you use a memory sorting area instead of a temporary segment. The sort_area_size parameter is used to set the size of the sorting area.

Large Pool is used for the Recovery Manager (RMAN: Recovery Manager), a Database Backup Tool ). The Large Pool size is determined by large_pool_size. You can use the following statement to query and modify it: ========================================================== ======================================

SQL> show parameter large_pool_size

Name type value -----------------------------------

Large_pool_size big integer 8 M

SQL> alter system set large_pool_size = 7 m;

System altered. ========================================================== ======================================

The Java pool is mainly used for Java Development. Generally, it is no less than 20 mb. The size is determined by java_pool_size and can be dynamically adjusted.

4. in Oracle 8 I/9i, the database administrator must manually adjust the parameter values of each region of the SGA instance, each area should be set according to the weight of the load, if improperly set, for example, when the load of a zone increases, the memory size is not adjusted, the ORA-4031 may appear: unable to allocate... bytes of shared memory error. In Oracle 10 Gb, set the statistics_level parameter to typical/All and use sga_target to specify the total size of the SGA zone. The database will automatically allocate the memory size between each component as needed.

NOTE: If sga_target is not set, the automatic shared memory management function is disabled.

5. Process Structure of Oracle instances Oracle contains three types of processes: • User process • Server process • background process)

5.1 user processes and server processes when database users request to connect to Oracle services, start user processes (such as starting SQLPlus ). • The user process must first establish a connection. • Users cannot directly interact with the Oracle server and must interact with each other through the server process. (This process is mainly implemented through the Listener process of Oracle) • Server processes are a bridge between user processes and servers, and can directly interact with Oracle servers. • Server processes can be shared or exclusive.

5.2 The interaction between the physical structure of the Backgroung Process database and the memory structure should be completed through the background Process. There are two types of background processes in the database. One is required and the other is optional:

• Mandatory background processes

| -- DBWn (Database Writer): Write Data

| -- PMON (Process Moniter): Process Monitoring

| -- LGWR (Log Writer): Write logs

| -- SMON (System Moniter): System Monitoring

| -- RECO (Recovery): Restore

| -- CKPT (Chekpoint): Checkpoint

• Optional background processes

| -- ARCn (Archiver): archiving process

| -- LCKn (Lock): Lock Process

| -- Dnnn (Dispatcher): scheduling process

| --...... You can use the following statement to view the running background process: ========================================================== ======================================

SQL> select * from v $ bgprocess where paddr <> '00 ';

Paddr pserial # name description -------- ---------- ----- 20176b0ed064 1 PMON process cleanup 6B0ED4E4 1 MMAN Memory Manager 6B0ED964 1 DBW0 db writer process 0 6B0EDDE4 1 LGWR Redo etc. interval 1 CKPT checkpoint limit 1 SMON System Monitor Process limit 1 RECO distributed recovery 6B0EEFE4 1 CJQ0 Job Queue Coordinator limit 1 qmnc aq Coordinator limit 1 MMON Manageability Monitor Process 6 B0F0AE4 1 MMNL Manageability Monitor 2 ========================================================== ======================================

5.2.1 DBWR (Database Writer, Data Writing Process) writes data in the data buffer to a data file, which is a background process responsible for data buffer management. When a data in the data buffer is modified, it is marked as dirty. The DBWR process writes "dirty" data in the data buffer into the data file to keep the data buffer clean. As the data in the data buffer zone is modified and occupied by users, the idle data buffer zone is continuously reduced, when a user's process needs to read data blocks from the disk to the data buffer but cannot find enough idle data buffer, DBWR writes the data buffer content to the disk, this allows the user process to get enough free data buffer.

DBWR: • Manage data buffers so that user processes can always find enough idle buffers. • Write all modified buffer data to the data file. • The LRU (least recently used) algorithm is frequently used to keep the buffer data. • Optimized disk I/0 read/write through delayed write.

5.2.2 LGWR (Log Writer, Log Writing Process) writes Log data from the Log buffer to the disk Log file group. When the database is running, if you modify the database, log information is generated first in the log buffer zone. When the number of logs reaches a certain level, LGWR writes the log data to the log file group, and then switches the log, the archive process (ARCH) write log data to the archiving process (provided that the database runs in archive mode ). The database follows the log writing priority principle, that is, to write logs before writing data.

5.2.3 Brief Introduction to other processes • ARCH (Archive, Archive process) Oracle database has two operating modes: ARCHIVELOG and NOARCHIVELOG. Running logs in non-archive mode are directly overwritten during switchover without generating archive logs. This is the default running mode for databases. When the database runs in archive mode, before the log switch, the ARCH process writes online log information to the archiving destination, which can be a local disk, it can also be a remote storage medium (such as a tape library) that can be accessed by the network ). The number of Redo files in the Oracle database is limited, so Oracle writes data to them cyclically. It fills every Redo file in sequence. When the last Redo file is reached, it is recycled back and starts to fill in the first Redo file. If you want to save log files in order to restore the database, you need to back up the log files before they are reused. Archive Process manages this operation.

• When CKPT (Check Point, Checkpoint Process) is running, the system modifies the synchronization signals of all data files and control file headers to ensure Database Synchronization. The check points are as follows:

| -- Generated during each log switch.

| -- A specified time has elapsed since the last checkpoint.

| -- After the previous checkpoint, a predefined Number of log blocks are written to the disk.

| -- The database is closed.

| -- Mandatory generation of DBA.

| -- When the tablespace is set to OFFLINE.

• SMON (System Moniter, System monitoring process) performs instance recovery when the instance is started and cleans temporary segments that are no longer in use.

• PMON (Process Monitor) PMON recovers when a user's Process fails. It cleans up the memory area and releases the resources used by the Process.

• RECO (Recovery process) RECO is used for distributed databases to maintain data consistency in distributed environments.

• LCKn (Lock process) is used in parallel servers to block Multiple instances.

• Dnnn (Dispatcher, scheduling process) is stored in the multi-threaded Server architecture (Shared Server). It connects user processes to Server processes and returns the results to user processes.