Settings and adjustments of PGA

PGA (Program global area) is an important component of Oracle Systems. The total overhead of the Oracle database for system memory is PGA + SGA. SGA master
It must be composed of database cache (shared SQL zone and PL/SQL zone) and data dictionary cache. PGA contains a set of server processes derived from the client connection server. Each server process has
Private memory area of data and control information. The client process corresponds to the server process. The server process completes user requests and returns data to the client process.

1. Main features and structure of PGA
1. Main Features
Unlike SGA, this part is not shared memory
When a server process is started or created, it is allocated and released at the end. It can only be used by one process (during system running, sorting, connection, and other operations may require further PGA allocation)
The content of PGA varies with the server mode (dedicated mode/Shared Server mode ).
The total PGA allocated by all server processes is generally referred to as the PGA aggregate (aggregated PGA). This value is determined by the pga_aggregate_target parameter.

2. Structure of PGA
PGA includes the following structures:
Sorting area (SORT)
Cursor)
Session information area)
Stack)
PGA = UGA + sorting area + hash area + bitmap merging area

3. UGA
Another concept related to PGA is UGA (User global area), that is, the user global area, which 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.

4. Common operations that result in sorting (consuming sort_area_size)
Order by, group by, rollup, distinct
Minus, intersect, Union
Min (), max (), count ()
Hash connection
Bitmap merge
Bitmap Creation
Batch Data Loading

Ii. PGA management mode and settings
1. PGA has two management modes:
Manual PGA memory management: the memory used by the user to specify the sorting and hash areas. Each connection uses the same memory.
Automatic PGA memory management: tells Oracle the total amount of PGA that can be used, which is determined by oraclce based on the system load. That is, the PGA memory can be dynamically expanded and recycled.

In 9ir1, manual PGA memory management is used by default, and automatic PGA memory management is used by default after 9ir2.

2. settings of PGA memory management mode (parameter workarea_size_policy)
Set to manual and use manual management.
Enable Automatic Memory Management when set to auto and pga_aggregate_target is not 0.

3. PGA-related parameters
Bitmap_merge_area_size --> value allocated in the bitmap merge Area
Create_bitmap_area_size --> Create the bitmap allocation value.
Hash_area_size --> mainly used for the allocation size during hash join.
Sort_area_size --> total memory used for sorting, affecting one-pass, multi-pass, and optimal
Sort_area_retained_size --> total memory size of sorting information stored in memory after sorting
These parameters are used before Oracle 9i. You can manually allocate these parameters on Oracle 9i or automatically assign them by the system.
When workarea_size_policy is set to auto and pga_aggregate_target is set to a non-zero value, the preceding four parameters are recommended to be automatically adjusted based on the system's automatic load.
SQL> show parameter area_size
Name type value
-----------------------------------------------------------------------------
Bitmap_merge_area_size integer 1048576
Create_bitmap_area_size integer 8388608
Hash_area_size integer 131072
Sort_area_size integer 65536
Workarea_size_policy string auto

Pga_aggregate_target
A. determines the maximum PGA memory value available for all sessions. This parameter can be dynamically modified. When the number of connections is small, the actual allocated PGA memory is usually less than the target value.
When the number is large, the actual allocated PGA memory will exceed the target value. The target value is the target value to be kept as much as possible.
B. Global PGA allocation and private workspace allocation are limited by this parameter.
C. This parameter only supports the VPC mode in 9i. After 10 Gb, the VPC mode and the Shared Server mode are supported.
D. pga_aggregate_target is divided into adjustable and unadjustable areas. The adjustable areas are SQL work areas, and the remaining areas are unadjustable areas.
E. When a small number of users connect to the database, each user enjoys a relatively large amount of PGA memory, while when a large number of users connect, each user allocates a relatively small amount of PGA memory.
F. A single serial query (non-parallel query) may include multiple sort/HASH operations. Each sort/hash operation can use up to 5% PGA memory.
H. A single parallel query can be used up to 30% of the PGA memory, regardless of the number of parallel processes.
 
SQL> select name, value, display_value
2 from V $ parameter where name in ('pga _ aggregate_target ', 'workarea _ size_policy ');
NAME value display_value
-----------------------------------------------------------------
Pga_aggregate_target 199229440 190 m
Workarea_size_policy auto

Iii. PGA allocation principles
The pga_aggregate_target value should be set based on the total amount of memory available for the Oracle instance. Assume that the current server can allocate 4 GB of physical memory and only one instance runs in
For this server, you can allocate 80% of the available memory to the Oracle instance, that is, 3.2 GB. The remaining memory is allocated to the operating system and other applications. Division in memory
The SGA and PGA regions.

In the OLTP system, the typical PGA memory settings should be a small part of the total memory (for example, 20%), and the remaining 80% is allocated to SGA.
OLTP: pga_aggregate_target = (total_mem * 80%) * 20%

In the DSS System, because some large queries are run, a typical PGA memory can allocate up to 70% of the memory.
DSS: pga_aggregate_target = (total_mem * 80%) * 50%
In this example, the total memory is 4 GB. For the DSS system, you can set pga_aggregate_target to 1600 MB and OLTP to 655 MB.

Iv. PGA adjustment suggestions

<P> PGA monitoring and adjustment, you can use the following views for reference <br/> V $ pgastat <br/> V $ pga_target_advice <br/> V $ pga_target_advice_histogram <br/> 1. View v $ pgastat view to obtain PGA-Related Information <br/> SQL> select * from V $ pgastat; <br/> name value unit <br/> parameter ------------ <br/> aggregate PGA target parameter 199229440 bytes -- current pga_aggregate_target value <br/> aggregate PGA auto target 1271 53152 bytes -- remaining memory that can be used by the workarea (Auto Mode) <br/> global memory bound 39845888 bytes -- maximum PGA memory usage for a single SQL serial operation (Auto Mode) <br/> total PGA inuse 62769152 bytes -- the consumed PGA (including all occupied PGA, such as workarea PL/SQL) <br/> total PGA allocated 132114432 bytes -- total allocated PGA memory of the current instance <br/> maximum PGA allocated 299982848 bytes -- maximum expanded by PGA <br/> total freeable PGA memory 10223616 bytes -- released size of PGA <br/> process count 19 -- Current process <br/> MAX processes count 42 -- process at the maximum time <br/> PGA memory freed back to OS 46708359168 bytes -- accumulated value returned by PGA to the Operating System <br/> total PGA used for auto workareas 4829184 bytes -- total size of the PGA allocated to auto workareas <br/> maximum PGA used for auto workareas 39851008 bytes -- PGA assigned to auto workareas peak value <br/> total PGA used for manual workareas 0 bytes <br/> maximum PGA used for manual workareas 531456 bytes <B R/> over allocation count 1 -- number of times the usage exceeds the PGA size <br/> bytes processed 96178293760 bytes -- bytes used by PGA <br/> extra bytes read/written 1855111168 bytes -- directed bytes written in the temporary segment <br/> cache hit percentage 98 percent -- hit rate <br/> recompute count (total) 1473108 <br/> --> from the preceding statistics, we can see that the value of maximum PGA allocated is much greater than that of aggregate PGA target parameter, and over allocation count, <br/> --> increase the PGA target value </P> <p> 2. V $ pga_target_advice_h Istogram view <br/> This view displays the usage of different sorting workspaces. <br/> optimal_executions is the number of operations that do not need to be exchanged through temporary tablespace. <Br/> onepass_executions is the number of temporary tablespace swap operations. <Br/> multipasses_executions is the number of temporary tablespace swap operations. <Br/> in principle, if 100% of operations are memory operations, it is the best choice. If the memory is insufficient, some large operations are onepass and acceptable. <Br/> the multipasses operation may cause a sharp decline in performance, in this case, you should consider adjusting the PGA target <br/> /***************************** * *******************/<br/>/* Author: robinson Cheng */<br/>/* blog: http://blog.csdn.net/robinson_0612 */<br/>/* MSN: robinson_0612@hotmail.com */<br/>/* QQ: 645746311 */<br/> /******************************** * ****************/<br/> SQL> select low_optimal_size/1024 low_kb, (highh_optimal_size + 1)/1024 High_kb, <br/> 2 optimal_executions opt_exec, onepass_executions onepass_exec, multipasses_executions multipass_exec <br/> 3 from V $ region <br/> 4 where total_executions! = 0; <br/> low_kb high_kb opt_exec onepass_exec multipass_exec <br/> ---------- ------------ <br/> 2 4 1013515 0 0 <br/> 64 128 7041 0 0 0 <br/> 128 256 1499 0 0 <br/> 256 512 1222 0 0 <br/> 512 1024 67917 0 0 <br/> 1024 2048 19057 0 0 <br/> 2048 4096 3450 0 0 <br/> 4096 8192 770 4 0 <br/> 8192 16384 37 0 0 <br/> 32768 65536 2 7 0 <br/> 131072 262144 0 24 0 <br/> low_kb high_kb opt_exec onepass_exec multipass_exec <br/> ---------- ------------ <br/> 262144 524288 0 4 0 <br/> 3. Obtain the recommended PGA setting value from V $ pga_target_advice <br/> SQL> select pga_target_for_estimate/1024/1024 pgamb, <br/> 2 pga_target_factor p_tr_fct, <br/> 3 estd_pga_cache_hit_percentage e_p_c_hit_prct, <br/> 4 estd_overalloc_count e_or_cnt <br /> 5 from V $ pga_target_advice; <br/> pgamb p_tr_fct e_p_c_hit_prct e_or_cnt <br/> ---------- -------------- <br/> 23.75. 125 82 1179 <br/> 47.5. 25 82 1179 <br/> 95. 5 95 233 <br/> 142.5. 75 99 6 <br/> 190 1 99 1 <br/> 228 1.2 99 0 <br/> 266 1.4 99 0 <br/> 304 1.6 99 0 <br/> 342 1.8 99 0 <br/> 380 2 99 0 <br/> 570 3 99 0 <br/> 760 4 99 0 <br/> 1140 6 99 0 <br/> 1520 8 99 0 <br/> -- the PGA overload can be eliminated when the PGA size is set to MB. </P> <p> 4. Use v $ sysstat to view the sorting area <br/> --> View the optimal, onepass, the ratio of multipass in different sorts <br/> --> the sorting should be optimal as much as possible, avoid excessive multipass <br/> SQL> Col profile format A40 <br/> SQL> select name profile, CNT, decode (total, 0, 0, round (CNT * 100/total, 4) Percentage <br/> 2 from (Select name, value CNT, (sum (value) over ()) total from V $ sysstat where name like 'workarea exec % '); <br/> profile CNT percentage <br/> ------------------------------------------ ---------- <br/> workarea executions-optimal 1113054 99.999 <br/> workarea executions-onepass 11. 001 <br/> workarea executions-multipass 0 0 <br/> --> check the percentage of memory sorting to total sorting. The higher the value, the better. if the value is small, you should consider adjusting PGA with V $ pga_target_advice <br/> SQL> select round (M. value/(m. value + D. value), 4) * 100 | '%' memory_disk_sort_ratio <br/> 2 from (select value from V $ sysstat where name = 'sorts (memory) ') m, <br/> 3 (select value from V $ sysstat where name = 'sorts (Disk) ') D; <br/> memory_disk_sort_ratio <br/> --------------------------------------- <br/> 100%

5. PGA Adjustment Command
System Level Change:

Alter system set workarea_size_policy = {auto | manaul };
 
Alter system set pga_aggregate_target = 239075328;
 
Alter system set sort_area_size = 65536 scope = spfile; --> this parameter does not work when auto is used.
 
Alter system set hash_area_size = 65536 scope = spfile; --> this parameter does not work when auto is used.

Session-level changes

Alter session set workarea_size_policy = {auto | manaul };
 
Alter session set sort_area_size = 65536; --> this parameter does not work when auto is used.
 
Alter session set hash_area_size = 65536; --> this parameter does not work when auto is used.

6. More references

Oracle hard parsing and soft Parsing

Shared Pool Tuning)

Buffer cache adjustment and optimization (1)

Buffer cache adjustment and optimization (2)

Use of Oracle table cache (caching table)