Some basic concepts of Oracle

Let's start by talking about the two basic concepts of a database:

• Instance (Oracle Instance): an instance is a collection of Oracle background processes (referred to as UNIX systems)/threads (Windows systems) and a shared memory area (referred to as the SGA), a means of implementing access to the database, In other words, Oracle uses instances to give users access to the database

• Database: Refers to a set of files on the operating system

Database and instance relationships: one instance can mount only one database, and in the case of a non-cluster, a database is loaded (mount) by one instance, and a database can be loaded by multiple instances in a clustered environment

Here's the SGA:

Shared pool : Our data dictionary cache and library cache. It is known that SQL,/PL, in the execution of syntax validation and parsing, compiling, especially SQL statements and optimizer optimizations, which will take time to process, so in order to improve performance, Oracle will compile the completed SQL statement cache, The next time you execute the same SQL, you no longer have to perform these processes. Where are these compiled SQL, PL/SQL places? is placed in the shared pool, more specifically in the shared pool in the library cache, this is a queue, the most recently used SQL on the team head, long time unused SQL placed in the tail, when the cache reaches the maximum, the tail of the SQL will be cleared to hold the latest SQL. The data dictionary cache, which contains the definition and permission information of the relevant data files, tables, indexes, columns, users, other data objects when the SQL statement is executed.

Note: the size of the shared pool can seriously affect performance, too large or too small to cause serious performance problems. If you do not use bound variables in SQL, then SQL will not be reused, remember.

Data buffer (block buffer cache): or a block buffer , now the performance bottleneck of the computer has been stuck on the IO, the disk IO speed can never keep up with the CPU calculation speed. Because disk IO is the slowest part of the computer, the IO operation must be reduced in order to improve performance. A data buffer is a function that puts data that has been recently read from a data file into a data buffer so that when the user queries the data, Oracle will first look in the buffer and, if there is data required for the request, It is returned directly from the buffer to the user (to know that the memory speed is several orders of magnitude faster than the disk), which is called the cache hit, which reduces the number of Io, and if there is no data required for the request, it is called the cache Miss (Miss), The data is then read from the data file, first placed in the database buffer, and then transmitted to the user.

Log Buffers (that is, redo log buffers): The redo log records the database changes to each block of data in the database, including modifications to the data and changes to the database structure. When we make changes to the data, the relevant data is not immediately written to the data file, but the first generation of the redo information, and write to the log buffer, when the following conditions, LGWR will write this buffer log into the redo log:

• Write once every 3 seconds
• When a user commits
• When buffer usage reaches 1/3
• LGWR when switching log files

When the redo log is full, it is archived, the archive log is generated, the archive log and the redo log are used, and the data file loss can be recovered.

Large pool : the memory space allocated for large background process operations, mainly refers to backup recovery, large IO operations, parallel queries, and so on.

logical Structure of the database : Table Space----and block----data blocks

tablespace : The largest logical unit of a database is a tablespace (tablespace), a table space can consist of multiple data files (a data file refers to a. dbf file), a tablespace can consist of multiple data files, but a data file can belong to only one table space. A database has at least one tablespace (System tablespace) that holds the Oracle data dictionary, and the size of the tablespace equals the sum of all the data file sizes that make up the tablespace.

Segment: A segment is a logical storage structure that forms a table space that consists of a set of extents that can be distributed across different data files. According to the characteristics of the data stored in the segment, segments are divided into several types, including data segments, index segments, fallback segments, and temporary segments.

Zone : consists of contiguous data blocks.

The tablespace User_data is physically composed of two data files user_data01.dbf and user_data02.dbf two data files, which logically consists of three segments. DATA01 is divided into two districts, Data02 is divided into two districts, the segment T1 consists of two districts, respectively, distributed in different data files, and T2 and II have only one zone.

Data Block : is the smallest data storage unit in the database, the size of the general block is a multiple of the current operating system file system block, this is the Oracle's official recommendation, use show parameter db_block_size to view the block size.

Some basic concepts of Oracle