Oracle software structure

The Oracle database server consists of databases and instances.

Instance

Memory Structure of the instance: SGA (system global areas) and PGA (Program global areas ).
The process structure of the instance: service process and background process.

Memory Structure:

SGA (system global areas)

1. database cache db_cach_size
. Keep cache pool buffer_pool_keep
. Recycle cache pool buffer_pool_recycle
. Default cache pool db_cache_size-(buffer_pool_keey + buffer_pool_recycle)

2. redo log cache log_buffer

3. Shared Pool shared_pool_size

Database cache: Used to cache the parsed and executed SQL and PL/SQL program code

Shared SQL Workspace: stores the syntax analysis results and execution plans of SQL statements. If there is no free space in the shared SQL workspace, Oracle will use the LRU algorithm to generate SQL statements that are rarely used recently -----> SGA

Private SQL Workspace: the other part of the SQL statement is saved in the private SQL workspace, including the binding variables, environment, and session parameters in the SQL statement, this information is private to the user who executes the statement. Other users cannot use this information even if they execute the same SQL statement. The private SQL workspace is created in SGA only when the Shared Server (servers = shared) is used. Generally, the private SQL workspace is located in PGA.

The size of the library cache is closely related to open_cursors. The open_cursors parameter is used to specify the number of cursors that can be opened simultaneously in the database. Each query statement requires a cursor.

Database dictionary cache: this is a "Row cache" in which records are stored, rather than cache blocks.

4. Java pool

5. large pool large_pool_size

. Back up and restore the database
. Execute SQL statements with a large number of sorting operations
. Entertain parallel database operations

If a large pool is not created in the SGA area, the cache space required for the above operation is allocated in the shared pool or PGA.

PGA (Program global areas)

The memory structure that stores the data and control information of a service process. The memory structure is non-shared. Only the service process itself can access its own PGA zone. Each service process has its own PGA zone. The sum of the PGA of each service process is the size of the PGA zone of the instance.

1. Private SQL Workspace

When executing complex queries (such as query statements in the Decision Support System), you often need to create a large dynamic cache area in PGA, it is used to perform the following operations that must be performed in the memory:

. Sort (order by, group)
Sort_area_size
Sort_area_retained_size
. Join)
Hash_area_size
. Set operation (Union, interset, minus)
Create_bitmap_area_size