Oracle Memory Architecture (VI) UGA, CGA and software code area

1, UGA (the User Global area)

The PGA is a piece of memory that contains data and control information for an Oracle service or background process. The PGA size depends on the configuration of the system. In dedicated services (dedicated Server) mode, a service process is associated with a user process, and the PGA includes heap space and UGA. However, the UGA (user Global Area UI) consists of user session data, cursor State, and index area. In Shared Services (MTS) mode, a shared services process is shared by multiple user processes, at which point UGA is part of a shared pool or large pool (dependent and configured).

Many DBAs do not understand the difference between PGA and UGA. In fact, this distinction can be simply understood as a direct difference between process and session. In the dedicated service mode, processes and sessions are one-to-one, while in MTS mode, processes and sessions are one-to-many relationships. The PGA is a service to the process, it contains information about the process, and UGA is a service to the session, it contains information about the session. Therefore, in MTS mode, the relationship between PGA and UGA is also one-to-many.

The UGA contains information about a session, including:

1 Open the Permanent zone and the operating area of the cursor;

2 The state information of the packet, especially the variable of the package;

3 Java session information;

4) active role;

5 Active Trace Event (ALTER session SET event ...) ）；

6 The NLS parameters in effect (SELECT * from nls_session_parameters;);

7 all open DB link;

8 Managed access token for the session for trusted Oracle (Mandatory access control (MAC)

Like the PGA, UGA is also composed of two sets of regions, fixed UGA and variable UGA (or UGA heap). The fixed UGA contains about 70 atomic variables, small data structures, and pointers to the UGA heap.

The segments in the UGA heap can be traced through the table X$ksmup (its structure and X$KSMSP are the same). The UGA heap contains permanent memory that stores some fixed tables (x$ tables) (depending on the settings for specific parameters, such as Open_cursors,open_links and Max_enabled_roles). In addition, most UGA are used for private SQL areas. UGA memory depends on the settings of the session. In dedicated service mode, sessions and processes are one-to-one relationships, and UGA is located in the PGA. The fixed UGA is a segment of memory in the PGA, and the UGA Heap is the PGA's sub heap. In MTS mode, the fixed UGA is a segment of memory in the shared pool, and the UGA Heap is a child heap of the large pool, which is allocated from the shared pool if the allocation fails from the large pool.

In MTS mode, the total size of the SGA used by each UGA can be controlled by the PRIVATE_SGA item in the profile (viewed through dba_profiles), but this is not recommended.

After Oracle 9.2, there is a new suppressed parameter: _use_realfree_heap. When this parameter is set to True, Oracle allocates the heap separately for CGA, UGA, and not from the PGA. Its default value is False, and when Pga_aggregate_target is set, its value is automatically changed to true.

2, CGA (the call Global area)

Unlike other global zones, the presence of the CGA (call Global area invocation) is instantaneous. It exists only in one call process. Some low-level calls to instances require CGA, including:

1) parsing an SQL statement;

2 execute an SQL statement;

3) takes the output value of a SELECT statement.

If the statement produces a recursive call, you need to assign a CGA to each recursive call. As mentioned above, recursive calls are calls that need to query or modify data dictionary information when statement parsing, optimizer generation statement query plan, DML operation.

Whether UGA exists in the PGA or SGA,CGA is the PGA's subheap. Because regardless of that pattern, the session always needs one for processing when making the call. This is important, especially when in MTS mode, you may need to increase the size of the PGA if you find that a call is not responding for a long time.

Of course, the call does not work only through the data structures in the CGA. In fact, most of the important data structures required by the call come from UGA. For example, the private SQL fetch and sort areas are stored in UGA, because they are retained after the call has ended. The CGA contains only those data that can be freed after the call has ended. For example, CGA contains a direct IO cache, information about recursive calls, heap space for expression evaluation (when a query plan is generated), and some other temporary data.

Java call memory is also allocated in CGA. It is divided into three parts: heap space, new space and old space. During the call (the length and length of the call depends on the duration and size of the usage), the memory segment that is no longer used in the new space and in the old space is reclaimed by the garbage collector.

3, software code area (Software)

The software code area is part of the memory area that is used to store code that is running and can be run (the code of Oracle itself). Oracle code is typically stored in a software code area that is different from the user program store, and the user program store is an exclusive, protected area.

The size of the software area is generally fixed and will only change after Oracle software upgrades or reloading. Under different operating systems, this part of the region requires different sizes.

The software area is read-only and can be installed as shared or unshared. Where possible, Oracle code is shared so that all Oracle users can access the code directly without having to save a copy in their own memory. This can save a lot of memory and improve overall performance.

User programs can also be shared or unshared. Some Oracle tools, such as SQL Plus, can be installed as shared, but some cannot. If a machine runs multiple instances, these instances can use the same Oracle code area.

Also note that not all operating systems can install software areas as shared, such as Windows.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/