Shulou(Shulou.com)06/01 Report--

When the oracle database instance starts, the oracle database allocates a memory area and starts the background process.

The information stored in memory area is as follows:

Program code

Information for each connection session, including currently inactive sessions

Information needed during program execution, such as getting the query status of the current row

Lock data information shared and communicated between processes

Cached data, such as blocks and rerecords, also exist on disk

Basic memory structure

The oracle database contains several memory regions, each of which contains several subcomponents

The basic memory structures related to oracle databases include:

System global area (SGA) system global area

SGA is a set of shared memory structures called SGA components that contain data and control file information for an oracle database instance. All server processes and background processes share the SGA memory area, and the data stored in SGA includes cached data blocks and SQL shared areas.

Program global area (PGA) program global area

PGA is a non-shared memory area that contains only the data and control information used by Oracle processes. When the Oracle process starts, the Oracle database creates a PGA.

Each server process and background process has its own PGA memory area, the collection of a single PGA is the size of the total instance PGA, and the size of the instance PGA is set in the system initialization parameters.

User global area (UGA) user global area

UGA is the memory associated with the user session.

Software code areas

The software code area is used to store the area where code is running or can be run. Oracle database code is stored in a software domain, usually in different locations, from user programs-- more advanced or protected locations.

The following figure shows the relationship between these memories:

Oracle database memory management

Memory management includes allocating the optimal size for the memory structure of the oracle database instance to meet the needs of database changes. Memory management of oracle database based on initialization related memory parameter settings

The basic mode of memory management is as follows:

Automatic memory management

First allocate a target memory size to the database instance, and the instance automatically adjusts to the target memory size and reallocates memory between SGA and PGA as needed.

Automatic shared memory management

This management mode is partially automated, setting a target size for SGA, and then you can choose to set the aggregate target size for PGA or manage the PGA work area separately.

Memory management manual

Unlike setting the total memory size, you can set a number of initialization parameters to manage SGA and instance PGA components separately.

If you create an oracle database through DCBA (Database Configuration Assistant) and choose the default initialization settings, the automated management mode is the default memory management mode.

Overview of UGA (User Global Area)

UGA is session memory, which is allocated to session variables. It mainly stores login information and other information needed for database sessions. In fact, UGA stores the state of the session.

The following figure shows the structure of UGA:

If the session loads the PL / SQL package into memory, the UGA will contain the package state, that is, the set of values stored in all package variables at a specific time.

The state of the package also changes when its subroutine changes the variable. By default, the package variable is unique and persisted.

The OLAP page pool is also stored in UGA. This pool manages OLAP data pages, which are equivalent to data blocks. The page pool is allocated at the beginning of the OLAP session and released at the end of the session. When a user queries a cube, such as a cube, the OLAP session opens automatically.

UGA must be available for database sessions. For this reason, UGA cannot be stored in PGA when using shared server connections because PGA is specific to a single process. Therefore, UGA is stored in SGA when using a shared server connection, making it accessible to any shared server process. When a dedicated server connection is used, the UGA is stored in PGA.

Overview of PGA (Program Global Area)

PGA is a memory area unique to oracle server processes, not shared by other processes or threads on the system. Because PGA is process-specific, it is never allocated in SGA.

PGA is a memory heap that contains session variables required by dedicated and shared server processes. The server process allocates the memory structure it needs in PGA.

The following figure shows an instance PGA (a collection of all PGA) that is not configured for the shared server. The maximum value of PGA can be set using the initialization parameter, and the personal PGA can be increased to this target size as needed.

PGA details

PGA is subdivided into different regions, each with a different role.

The following figure shows the PGA memory structure required for dedicated server sessions:

Private SQL Area (private SQL zone)

A Private SQL Area contains information about parsed SQL statements and other session-specific information.

When the server process executes SQL or PL / SQL code, the process uses a private SQL area to store the value of the bound variable (the binding variable details the http://blog.csdn.net/tianlesoftware/article/details/5458896), query the execution status information, and query the execution work area.

Don't confuse the private SQL zone in PGA with the shared SQL zone, which stores the execution plan in SGA. In the same or different sessions, multiple private SQL zones can point to an execution plan in SGA. For example, 20 executions of "SELECT * FROM sales" in one session and 10 executions of the same query in different sessions can share the same plan. Each executed private SQL region is not shared and may contain different values and data.

The cursor is the name of a specific private SQL zone. As shown in the following figure, you can think of cursors as pointers on the client and state on the server. Because cursors are closely related to private SQL regions, terms can sometimes be used interchangeably.

A private SQL zone can be divided into the following areas:

The run-time area (Runtime Zone)

This area contains query execution status information. For example, The run-time area can track the number of rows retrieved so far in a full table scan. The The run-time area is released when the cursor execution ends.

Oracle creates a The run-time area as the first step in executing the request. For DML statements, The run-time area will be released when the SQL statement is closed.

The persistent area (continuous data area)

This area contains the value of the bound variable. A binding variable value is provided to the SQL statement when the statement is executed. The persistent area is released only when the cursor is closed.

The client process is responsible for managing the private SQL zone. Although the number of private SQL zones that client processes can allocate is limited by the initialization parameter "OPEN_CURSORS", the allocation of private SQL zones depends largely on the application.

Although most users rely on automatic cursor handling of database utilities, the Oracle database programming interface provides developers with more control over cursors. In general, applications should close all open cursors that are no longer used to free up persistent data areas and minimize the memory required by users.

SQL Work Areas (SQL Workspace)

A workspace is a private area of PGA memory for memory-intensive operations.

For example, the sort operator uses the sort region to sort a row of data. Similarly, a hash join operator uses a hash region to build a hash table from its left input, while bitmap merge uses bitmap merge regions to merge data retrieved from multiple bitmap index scans.

The following figure shows the query relationship between employees and departments:

In the previous example, the run time zone tracks the progress of the full table scan. The session performs a hash join in the hash area to match the rows in the two tables. Sorting is done in the sort area.

If the amount of data processed is larger than the capacity of the workspace, the oracle database divides the input data into small chunks. In this way, the database will first process the pieces of data in memory, and then write the rest of the data to the temporary disk for later processing.

When you start automatic PGA memory management, the database automatically resizes the SQL workspace, or you can resize the SQL workspace manually.

In general, larger SQL workspaces trade high memory consumption for excellent operational performance. The ideal SQL workspace is large enough to accommodate the input data and secondary memory structure allocated by its associated SQL operator. Otherwise, the response time will increase because some of the input data must be cached on disk. In extreme cases, if the capacity of the SQL workspace is much smaller than the input data size, the database must perform multiple passes on the data blocks, greatly increasing response time.

The use of PGA in dedicated server and shared server mode

The allocation of PGA memory areas depends on whether the database uses a dedicated server connection or a shared server connection.

The following picture shows the difference between them:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.