Comprehensive introduction to Oracle architecture (1)

By studying databases and instances, we can know that the Oracle architecture can be divided into three categories: the internal structure of the database (such as tables), the internal structure of the storage area includes the shared storage area and process) and the external structure of the database.

I. Internal Structure in Oracle Architecture
The internal structure of the database, that is, the logical presentation layer of Oracle data, also known as oracleschema, includes the following:
1. Tables, columns, constraints, and data types (including abstract data types)
2. partitions and subpartitions
3. Users and modes
4. Indexes, clusters, and hash Clusters
5. View
6. Sequence
7. processes, functions, software packages, and triggers
8. Synonyms
9. permissions and Roles
10. Database Link
Section 11, Disk Area, and block
12. rollback segments
13. snapshots and graphics
14. The specific sections will be discussed in the Oralceschema section later.

2. Internal Storage Structure in Oracle Architecture
Including the memory buffer pool and background processes:

1. system global zone SGA), mainly including:

1) Data Block cache Zone
Datablockbuffercache is a high-speed cache area in SGA that stores data blocks (such as tables, indexes, and clusters) that read data segments from the database ). The size of the data block cache is determined by the DB_LOCK_BUFFERS parameter in the init. ora file of the database server (expressed by the number of database blocks ). When adjusting and managing databases, adjusting the size of the data block cache is an important part.

Because the size of the data block cache is fixed, and its size is usually smaller than the space used by the database segment, it cannot load all the database segments in the memory at a time. Generally, the data block cache is only 1%-of the database size ~ 2%. Oracle uses the least recently used (LRU, leastrecentlyused) algorithm to manage available space. When the storage area requires free space, the minimum block used recently will be removed, and the new data block will be replaced in the storage area. This method saves the most frequently used data in the storage area.

However, if the size of the SGA is insufficient to accommodate all the most commonly used data, different objects will compete for the space in the data block cache. This is likely to happen when multiple applications share the same SGA. In this case, the most recent use segments of each application compete for space in the SGA with those of other applications. The result is that data requests to the data block cache area will have a low hit rate, leading to a reduction in system performance.

2) dictionary cache area
The database object information is stored in the data dictionary table. The information includes the user account data, data file name, segment name, disk location, table description, and permissions, when the database needs this information (such as checking the authorization for a table to be queried by the user), it will read the data dictionary table and store the returned data in the SGA of the dictionary cache.

The data dictionary cache area is managed by least recently used (LRU) algorithms. The size of the dictionary cache is managed by the database. The dictionary cache is part of the SQL sharing pool. The size of the Sharing pool is set by the SHARED_POOL_SIZE parameter in the database file init. ora.

If the dictionary cache is too small, the database has to repeatedly query the data dictionary table to access the information required by the database. These queries are called recuesivecall ), the query speed is lower than that in the dictionary cache area when the query is completed independently.

3) redo log Buffer
The redo item description modifies the database. They are written to online redo log files to be used for forward scrolling during database recovery. However, before being written to the online redo log file, transactions are first recorded in the SGA called redologbuffer. The database can periodically and in batches write the content of the redo item to the online redo log file to optimize this operation. The size (in bytes) of the redo log buffer is determined by the LOG_BUFFER parameter in the init. ora file.

4) SQL sharing pool
The SQL Shared Pool stores the data dictionary cache and library cache, that is, the statement information for database operations. When the data block buffer and dictionary cache can share the structure and data information between database users, the database cache can share common SQL statements.

The SQL sharing pool includes the execution plan and the syntax analysis tree of SQL statements running the database. When you run the same SQL statement for the second time (by any user), you can use the available syntax analysis information in the SQL shared pool to speed up execution.

The SQL sharing pool is managed using the LRU algorithm. When the SQL sharing pool is full, the execution path and syntax analysis tree that are least recently used will be deleted from the database cache to free up space for new entries. If the SQL sharing pool is too small, the statement will be continuously loaded into the database cache, thus affecting operation performance.

The size (in bytes) of the SQL sharing pool is determined by the SHARED_POOL_SIZE parameter of the init. ora file.

5) large pool
LargePool is an optional memory area. If you use the thread server option or frequently perform backup/recovery operations, you only need to create a large pool to manage these operations more effectively. The large pool will be dedicated to supporting large SQL commands. By using a large pool, you can avoid these large SQL commands from overwriting entries into the SQL Shared Pool, thus reducing the number of statements that are then loaded into the database cache. The size (in bytes) of the large pool is set through the LARGE_POOL_SIZE parameter of the init. ora file. You can use the LARGE_POOL_MIN_ALLOC parameter of the init. ora file to set the minimum position in the large pool. This parameter is no longer required for Oracle8i. As a solution for using LargePool, you can use the SHARED_POOL_RESERVED_SIZE parameter of the init. ora file to reserve some SQL sharing pools for large SQL statements.

6) Java pool
According to its name, the Java Pool provides syntax analysis for Java commands. The Java pool size (in bytes) is set by the JAVA_POOL_SIZE parameter of the init. ora file introduced in Oracle8i. The default value of JAVA_POOL_SIZE In the init. ora file is 10 MB.

7) Multiple Buffer pools
You can create multiple buffer pools in SGA. Multiple Buffer pools can be used to separate large datasets from other applications to reduce their possibility of competing for the same resources in the data block cache zone. For each created buffer pool, you must specify the LRU latch size and quantity. The number of buffers must be at least 50 times more than the number of LRU latches.

When creating a buffer pool, you must specify the size of the keeparea and the recyclearea. Like the retention area of the SQL sharing pool, the retention area maintains entries, while the recycling area is frequently recycled. You can use the BUFFER_POOL_KEEP parameter to specify the size of the storage area. For example:
The storage and recycling buffer pool capacity reduces the available space in the data block buffer storage area (set through the DB_BLOCK_BUFFERS parameter ). For tables that use a new buffer pool, specify the buffer pool name through the buffer_pool parameter in the storage clause of the table. For example, if you want to quickly delete a table from the memory, assign it to the RECYCLE pool. The DEFAULT pool is called DEFAULT, so that a table can be transferred to the DEFAULT pool by using the altertable command later.