Oracle Database------Architecture

Oracle architectures include: Instances (Instance), database files, user processes, server processes, and other files.

1.ORACLE instance (instance)

1). To access the database, you must first start the instance, allocate the memory area when the instance starts, and then start the background process, after which the process performs input to the database, outputs, and monitors other processes.

During the database startup process, five processes must start:

1.SMON (System monitoring process)

2.PMON (Process monitoring)

3.DBWR (Database write process)

4.LGWR (log write process)

5.CKPT (Checkpoint process)

2). An Oracle DB instance consists of a memory area and a background process

(1.) Memory Area: Database cache, redo log cache, shared pool, stream pool, and other optional memory areas (such as Java pools), which are also known as the memory structure of the database

(2.) Background process: System monitoring process (smon), process monitoring (Pmon), database write process (DBWR), log write process (LGWR), checkpoint process (CKPT), other processes, etc.

2.ORACLE Servers (server)

1). Oracle is a server that consists of a database instance and a database filegroup, which is often said to be a database management system.

2). In addition to maintaining the instance and database files, the database server starts the server process and allocates the PGA (Program Global Zone) when the user establishes a connection to the database.

3. Physical Structure

1). data file

2). Control file

3). Redo log File

Links to 4.Oracle databases session

1). Link: A link refers to a physical line between a client process and a DB instance------network connection

2). Session: Refers to an explicit database connection, that is, a logical entity that exists in the instance

The memory structure of the 5.Oracle database

1). SGA (System global Zone): Contains several important memory areas, database caches, redo log caches, shared pools, large pools, and Java pools.

(1.) Shared pool: Shared or PL/SQL code

The shared pool consists of the library Cache and the data dictionary cache

Library cache (size cannot be manually adjusted): The most recently used SQL or SQL/PL statements are stored, the library cache capacity is limited and cannot exceed the size of the shared pool, and Oracle uses the LRU algorithm to manage the library cache.

Data dictionary caching: A data dictionary-related buffer that stores data files, tables, indexes, columns, users, permission information, and other definitions of database objects. is to put the relevant data dictionary information into the cache to improve query response time.

Set and view shared pool size

alter system set shared_pool_size=20m;

Show parameter shared_pool_size;

(2.) Database Cache

Stores information that has recently been read from a data file or that needs to be written back to the database after the user has changed, and the information that is not submitted to the database after these changes is called dirty data.

setting and viewing the size of the library cache

alter system set db_cache_size=200m;

Show parameter db_cache_size; (parameter: Parameters)

Show sga;//Query the allocation of SGA

Buffer Advisor parameter (Buffer Cache advisory Parameter): The goal is to have Oracle provide some recommendations for database buffer memory allocations for starting or shutting down statistics.

(3.) Redo Log Cache

When the user performs the Insert,update,delete,create,alter and drop operations, the data changes, and these changes in the rate of data are written to the redo log before they are written to the database cache. Storage area, and the data before the change is also put into the Redo log cache area, so that when data is restored, Oracle knows which ones need to roll forward and what needs to be rolled back.

Redo Log buffer parameters (Log_buffer) are static parameters and cannot be modified dynamically

To view the size of the redo log buffer

Show parameter Log_buffer;

(4.) Large pool and Java pool

The Large pool is an optional memory area for the SGA, and the large pool is configured only in a shared server environment, and most of the user process area UGA will be allocated in the large pool, reducing the burden on the shared pool. Large pools are also required as cache space for large-scale input and output and backup processes. Large_power_size sets the size of the large pool.

Show parameter Large_power_szie;

The Java pool is also an optional section of memory, but the Java language is installed, or Java programs must be set up to compile instructions written in the Java language. Java_pool_size sets the size of the Java pool.

Show parameter java_pool_size;

2). PGA (Process Global Zone): A piece of memory dedicated to the server process

Oracle Database------Architecture