Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the pga-related parameters". In the daily operation, I believe many people have doubts about the pga-related parameters. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what are the pga-related parameters?" Next, please follow the editor to study!

PGA

UGA:session information, WORK AREA (hash area,sort area, etc.), etc.

CGA:parse call, executive call, fetch call, etc.

Pga_aggregate_target sets the total WORK AREA area size of all processes, which mainly affects the setting of the following related parameters

_ smm_max_size: the upper limit of WORK AREA for serial processes (in KB). Default value

_ pga_max_size: the upper limit of WORK AREA for each process (in byte).

_ smm_px_max_size: the upper limit of WORK AREA shared by all parallel processes in parallel mode (in KB)

_ smm_max_size:

Pga_aggregate_target = 1000 MB, then _ smm_max_size = 10% * pga_aggregate_target, maximum 1024MB

_ pga_max_size:

_ smm_max_size 100m then _ pga_max_size = 2 * _ smm_max_size

_ smm_px_max_size:

_ smm_px_max_size = 50% * pga_aggregate_target

Degree of parallelism (DOP) 5, all slave processes share _ smm_px_max_size

From the above analysis, we can get some guidance as follows:

1. Increasing the pga_aggregate_target setting can increase the work area size of each process, which is beneficial to hash join, sorting and other operations.

2. After the PGA reaches 10G, the work area of a single process has reached the limit, and no more space can be obtained.

3. When the CPU is sufficient, if the parallelism is more than 6, you can get more work area space, for example, it will be faster to create an index.

4. If a large number of processes enable high parallelism, the operating system memory can easily be used up, because PGA only limits the space of the work area, while other components can cross the boundary.

To sum up, pga_aggregate_target is not a hard limit, and memory outside work area is not limited by pga_aggregate_target and _ pga_max_size. So it is common to see that PGA is larger than pga_aggregate_target, usually because huge amounts of data are loaded into variables and arrays in PL/SQL. A common example is bulk collect.

The PGA_AGGREGATE_LIMIT parameter is introduced in 12c, which can be used to rigidly limit the size of PGA.

In Oracle release 12.1: the greater of the following:

* 2 GB

* 200% of PGA_AGGREGATE_TARGET

* (Value of PROCESSES initialization parameter setting) * 3 MB

It will not exceed 120% of the physical memory size minus the total SGA size.

In Oracle release 12.2:

* If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.

* If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.

* If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.

In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter.

At this point, the study of "what are the relevant parameters of pga" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.