Java programmer cainiao (5) Oracle BASICS (5) Oracle database architecture

In Oracle learning, architecture is the top priority. At the beginning, we can grasp its physical composition, file composition, and various file composition from a macro perspective. The deeper the knowledge, the better. Problems encountered in actual work can be explained in the architecture. The architecture describes the framework of a system. Is the macro work of designing a system. This is like building a building. First, you should describe the architecture of the entire building in the form of drawings. Then fill in something in it a little bit. Next we will first have a basic understanding of the Oracle architecture in a graphic way.

According to the graph, it is easy to remember. The graph consists of three parts: User process, server process, and PGA on the left side) and the following database, parameter file, password file, and archived logfiles form the Oracle server, therefore, the entire graph can be understood as a C/S architecture. Oracle server consists of two entities: instance and database ). These two entities are independent, but they are connected together. During database creation, the instance is first created before the database is created. In a typical single-instance environment, the relationship between instances and databases is one-to-one. When an instance connects to a database, the relationship between instances and databases can also be many-to-one, that is, multiple instances on different computers open a public database on the shared disk system. This multi-to-one relationship is called real application cluster (RAC) RAC, which greatly improves database performance, fault tolerance, and scalability (may consume more storage space) it is also an essential part of the concept of Oracle grid.

Let's take a closer look at the architecture of the Oracle database.

The Oracle architecture consists of two major components: database instances and database files)

Database instance

Refers to the memory and related processing programs of the database server. It is the heart of oracle. The most important relationship with Oracle performance is SGA (system global area, which is the active shared memory area in the system global area). SGA includes three parts:
1. Data Buffer to avoid repeated reading of common data;
2. The log buffer increases the speed of adding, deleting, and modifying data, and reduces disk read/write speed;

3. Share the pool to stop compiling the same SQL statement, improving the SQL Execution speed.

Another part of the Oracle database instance is some background processes, which mainly include:

1. system monitoring process

2. Process Monitoring

3. Database write process

4. Log Writing Process

5. Checkpoint Process

6. other processes

These background processes work together to complete database management tasks

When accessing the database. Start the instance in the background. Allocate a memory area before starting the instance. Then start the background process. The first five processes must be started during database startup. Otherwise, the instance cannot be created.

Note: when starting the Oracle database. Because some processes are automatically started in the startup Item. Therefore, it occupies a large part of memory. If you have other programs on your computer that occupy a large amount of memory. When starting the Oracle database, the startup may fail due to insufficient memory.

Oracle Server

The Oracle server consists of database instances and database files. They constitute a data management system (DBMS)

Physical Structure: The physical structure of Oracle refers to various files on the hard disk. The ultimate goal is to store and obtain relevant data. These files on the hard disk constitute the physical structure of the Oracle database

Oracle mainly includes three types of files:

1. Data Files

2. Control File

3. redo log files

Parameter file and password file:

Parameter file: defines the features of a database instance. It is a body file.

Password File: Authorize the user to start and close the database instance. Mainly composed of binary data

Memory Structure of Oracle Database: SGA and PGA

SGA:A part of the database instance. The database instance is first allocated to the system global zone (SGA)

1. Shared Pool:The SQL and PL/SQL code are shared, that is, the parsed SQL code is cached here, and PL/SQL is not only cached but also shared. This greatly improves the speed of directly accessing the disk to obtain SQL statements.

1) database cache: stores recently used SQL statements. Use the LRU (least recently used) algorithm to manage cache. The least recently used algorithm is a commonly used algorithm. Here, when the library cache is near full. It clears the least recently used data in the cache. Add a new cache SQL statement.

View the size of the Shared Pool. Use the shared_pool_size parameter.

SQL> show parameter shared_pool_size

Set the size of the Shared Pool

SQL> alter system set shared_pool_size = 16 m

Note: In the following content. If you mention the relevant parameters. Unless otherwise specified. The preceding methods are used to set and view the parameter values. It will not be repeated in the future. These parameters can be viewed and modified only when you log on to the system administrator system. A user similar to Scott cannot view or modify it.

Oracle provides db_cache_size (database size) and db_block_buffers (number of database blocks) to set the cache size of the database, that is, the result of both.

2) Data Dictionary Cache

Stores data files, tables, indexes, columns, users, permission information, and definitions of some other database objects. Put the relevant data dictionary information into the cache to improve the query response time. The size depends on the size of the Shared Pool.

2. database cache

Stores the data block information recently read from the data file or the data information that needs to be written back to the database after the user changes. Memory reading is much faster than disk reading. Improves the access speed. The access here also uses the LRU (least recently used) algorithm.

The Oracle Database introduces the buffer cache advisory parameter for enabling and disabling statistics. This information is used to predict different behavior characteristics caused by different buffer sizes. Three statuses: off: On: Ready

Set the advisor cache to the enabled status. You can use the dynamic performance view v $ db_cache_advice to view the recommended information of the buffer zone.

3. redo log Cache

After performing insert, update, and other operations. The data has changed. The changed data is written to the redo log buffer before being written to the database cache. Including data before changes. You can check its size through the initialization parameter log_buffer. This parameter is a static parameter and cannot be modified.

4. large pool and Java pool

Large pool)

A large pool in SGA is an optional buffer zone. It can be configured with management right as needed. It provides a large zone for backup and recovery operations such as databases.

Enabled in the following scenarios:

I. Backup and Restore operations

2. When UGA and I/O slaves are started

3. When setting the MTS server, user information is stored in the large pool.

You can use the following command to change the size of the large pool:

Alter system set large_pool_size = 64 m;

The Java pool is used to compile commands written in the Java language. Use java_pool_size to view and modify the size. The size is automatically allocated and managed by the database in SGA. Therefore, the value is 0;

Oracle connections and sessions

Connection and session)Both concepts are closely related to the user process, but they have different meanings.

Connection: the communication channel between the user process and the Oracle instance ). This communication channel is through interprocess communication mechanisms (interprocess communication mechanisms) (running user processes and Oracle processes on the same computer) or network software) (When database applications and Oracle servers run on different computers, they need to communicate over the network.

Session: the connection established between the user through the user process and the Oracle instance [the connection here has different meanings from the connection above, mainly refers to the connection between the user and the database]. For example, a user must provide a valid user name and password when starting SQL * Plus, and then oracle creates a session for this user. The session continues from the time the user connects to the user to disconnect (or exit the database application.

The same user in the Oracle database can create multiple sessions at the same time. For example, the Scott/tiger user with the username/password can connect to the same oracle instance multiple times.

When the system is not running in the shared service mode, Oracle creates a server process for each user session ). When the system is running in the shared service mode, multiple user sessions can share the same service process.