Oracle memory structure-PGA

I. Overview
SGA, PGA, and UGA are all memory zones managed by Oracle.
SGA (System Global Area) is the most important memory Area in Oracle.
PGA (Process Global Area), that is, the Global Area of the program, a dedicated memory Area of the Process.
UGA (User Global Area), that is, the User Global Area, is associated with a specific session.
Dedicated server connection mode, which is allocated by UGA in PGA.
Shared Server connection mode, which is allocated by UGA in the Large Pool of SGA.
If the dedicated server connection mode is used, the PGA contains UGA, and other regions are used for sorting. The hash and bitmap are merged.
In short, PGA = UGA + sorting area + hash area + bitmap merging area.
Ii. PGA management mode.
PGA has two management modes:
1) manual PGA memory management. The User specifies the memory used by the sorting and hash areas. Each connection uses the same memory.
2) the automatic PGA Memory Management informs Oracle of the total amount of PGA that can be used, and the Oraclce determines the specific allocation based on the system load.
In 9iR1, manual PGA memory management is used by default, and automatic PGA memory management is used by default after 9iR2.
The PGA memory can be dynamically expanded and recycled.
The PGA memory management mode is controlled by WORKAREA_SIZE_POLICY.
1) set to MANUAL to enable MANUAL memory management.
2) Enable Automatic Memory Management when set to AUTO and PGA_AGGREGATE_TARGET is not 0.
Iii. Manual PGA Memory Management
Three parameters have the greatest impact on PGA.
SORT_AREA_SIZE: total memory used for sorting information
SORT_AREA_RETAINED_SIZE: total memory size for storing sorting information in the memory after sorting.
HASH_AREA_SIZE: memory used to store the hash list.
The following three parameters are described:
1) SORT_AREA_SIZE:
If SORT_AREA_SIZE is set to 512KB and SORT_AREA_RETAINED_SIZE is also 512KB, Oracle uses the memory of 512KB for sorting. After sorting, all data remains in the memory.
2) SORT_AREA_RETAINED_SIZE:
If SORT_AREA_SIZE is set to 512KB and SORT_AREA_RETAINED_SIZE is set to 384KB, Oracle uses the memory of 512KB for sorting and then retains the sorted data of 384KB, in addition, sorted data of 512KB-384KB = KB is written to the temporary tablespace.
If SORT_AREA_RETAINED_SIZE is not set, its value is 0, but the actual reserved sorting data is the same as SORT_AREA_SIZE.
3) HASH_AREA_SIZE:
The HASH_AREA_SIZE parameter is used when a large set is connected with another set. A small table is placed in the memory as the driver table, and then the large table is connected by PROBE. If HASH_AREA_SIZE is too small, the performance of the two sets (tables) is affected.
Note:
1) if the data volume to be sorted is greater than SORT_AREA_SIZE, Oracle will sort the data in batches. Save the sorted data to the temporary tablespace and sort the remaining data. Finally, the sorted data stored in the temporary tablespace is sorted, because the sorted data saved to the temporary tablespace is only sorted by part of the data, the data to be sorted as a whole is partially ordered.
2) * _ AREA_SIZE is only a limitation on an operation. A query may have multiple operations, and each operation has its own memory zone. If SORT_AREA_SIZE is set to 5 MB, a query may have 10 sorting operations, which will occupy 50 MB of sorting memory.
3) 3. * _ AREA_SIZE memory is allocated on demand. If a query requires 5 MB of memory for sorting, even if 1 GB of SORT_AREA_SIZE is allocated, only 5 MB of memory is used.
Iv. Automatic PGA Memory Management
To enable automatic PGA memory management, set the following parameters:
1, WORKAREA_SIZE_POLICY = AUTO
2, PGA_AGGREGATE_TARGET = non-zero
PGA_AGGREGATE_TARGET:
1) PGA_AGGREGATE_TARGET is a target value. When the number of connections is small, the actual allocation of PGA memory will be smaller than it. When the number of connections is large, the actual allocation of PGA memory will be larger than that of it, But Oracle will try to keep the total PGA within the PGA_AGGREGATE_TARGET value.
For example, set PGA_AGGREGATE_TARGET to 300 MB. When five users are connected, each user may allocate 10 MB of PGA memory and 50 MB of PGA memory. When 300 users are connected, each user may allocate MB of PGA memory, with a total allocation of MB of PGA memory. When many users are connected, Oracle will reduce the usage of PGA memory for each user.
2) A serial query (non-parallel query) may contain multiple sort/HASH operations. Each sort/hash operation can use up to 5% PGA memory.
3) a parallel query can be used up to 30% of the PGA memory, regardless of the number of parallel processes.
5. Manual PGA memory management and automatic PGA Memory Management
Compared with manual PGA memory management, automatic PGA memory management has many advantages.
1. When there are few users connected
A) Manual PGA memory management allocates no matter how much available memory is available. For example, if the current idle memory is 300 MB, the connection requires 10 MB of memory for sorting, and the size of the Set sorting area is 5 MB, as a result, although there is enough idle memory, it cannot be allocated to the current connection, resulting in low execution efficiency.
B) automatic PGA memory management will be allocated based on the current idle memory. When the idle memory is 300 MB, the current user needs 10 MB of memory for sorting, and Oracle will allocate 10 MB of memory to the current user.
2. When many users are connected
A) Manual PGA memory management allocates memory based on the preset values. If the total physical memory is 1 GB and the sorting area is set to 5 MB, Oracle will allocate 300 GB of memory when users are connected, which has exceeded our actual physical memory!
B) automatic PGA memory management will be allocated based on the current connection situation. If the total physical memory is 1 GB and PGA_AGGREGATE_TARGET is 300 MB, when the number of users increases from 10 to 300, the memory connected by each user will gradually decrease from 10 MB to 1.3 MB, although the total amount will also exceed PGA_AGGREGATE_TARGET, it is much better than manual PGA memory management.
When can I use automatic PGA memory management? When can I use manual PGA memory management?
Automatic PGA memory management is suitable for normal system operation during the day, allowing Oracle to automatically manage and allocate PGA memory based on the current load.
When there are few users at night, you can set the current session to use manual PGA memory management during maintenance, so that the current maintenance operation can obtain as much memory as possible and speed up execution.
For example, if the server is running in the automatic PGA memory management mode and a task needs to be sorted and connected to a large table during the night, it can be changed to manual PGA memory management in this operation session temporarily, then allocate a large SORT_AREA_SIZE and HASH_AREA_SIZE (50% or even 80% of memory, to ensure that no other user is used), which can greatly speed up the system operation without affecting the impact on the system during peak hours during the day.
Vi. Operation commands
System Level Change:
Alter system set WORKAREA_SIZE_POLICY = {AUTO | MANAUL };
Alter system set PGA_AGGREGATE_TARGET = 100000000;
Alter system set SORT_AREA_SIZE = 65536 SCOPE = SPFILE;
Alter system set HASH_AREA_SIZE = 65536 SCOPE = SPFILE;
Session-level changes
Alter session set WORKAREA_SIZE_POLICY = {AUTO | MANAUL };
Alter session set SORT_AREA_SIZE = 65536;
Alter session set HASH_AREA_SIZE = 65536;
VII. Application
1. sorting area:
Pga_aggregate_target is 100 MB. A single query can use 5%, that is, the time required for sorting 5 MB.
SQL> create table sorttable as select * from all_objects;
The table has been created.
SQL> insert into sorttable (select * from sorttable );
You have created 49735 rows.
SQL> insert into sorttable (select * from sorttable );
You have created 99470 rows.
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from sorttable order by object_id;
Row 198940 has been selected.
Used time: 00: 00: 50.49
Time required to change the sorting area to 30 mb at the Session level
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
The session has been changed.
Used time: 00: 00: 00.02
SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;
The session has been changed.
Used time: 00: 00: 00.01
SQL> select * from sorttable order by object_id;
Row 198940 has been selected.
Used time: 00: 00: 10.76
We can see that the time required is reduced from 50.49 seconds to 10.31 seconds, and the speed is significantly improved.
2. Hash area:
Pga_aggregate_target is 100 MB. The time required for table connection is 5% MB for a single query.
SQL> select/* + use_hash (tb1 tb2) */* from sorttable tb1, sorttable tb2 where tb1.object _ id = tb2.object _ id;
Row 49735 has been selected.
Used time: 00: 00: 40.50
Time required to modify the hash area at the Session level to 30 mb
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
The session has been changed.
Used time: 00: 00: 00.01
SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;
The session has been changed.
Used time: 00: 00: 00.01
SQL> select/* + use_hash (tb1 tb2) */* from sorttable tb1, sorttable tb2 where tb1.object _ id = tb2.object _ id;
Row 49735 has been selected.
Used time: 00: 00: 04.47
The required time is increased from 40.50 seconds to 4.47 seconds, and the effect is equally obvious.
Note: All the above experiments run a full table scan to ensure that the relevant tables are read into the buffer zone, so as to avoid errors caused by data not being read into the cache.

Conclusion: After 9iR2, PGA no longer bothers DBAs as before. Oracle will help us allocate PGA. However, this does not mean that DBAs do not need to have a deep understanding of the PGA. Mastering the PGA and applying it as appropriate will make the work more powerful.