Usage of view V$pga_target_advice in Oracle

Take a look at what information this view can bring to us (each column in the view is helpful):
[Email protected]> Select pga_target_for_estimate/1024/1024 "PGA (MB)",
2 Pga_target_factor,
3 Estd_pga_cache_hit_percentage,
4 Estd_overalloc_count
5 from V$pga_target_advice;

PGA (MB) pga_target_factor estd_pga_cache_hit_percentage Estd_overalloc_count
---------- ----------------- ----------------------------- --------------------
10.5 34 13
15.75 34 13
20 1 100 13
24 1.2 100 13
28 1.4 100 13
32 1.6 100 3
36 1.8 100 0
40 2 100 0
60 3 100 0
80 4 100 0
120 6 100 0
160 8 100 0

Rows selected.

The following conclusions can be obtained from the above data:
1. The first column indicates the specific values of the different PGA

2. The second column Pga_target_factor "1" indicates the current Pga_aggregate_target setting size (other values are based on this data multiplier), and here I am 20M, through Pga_aggregate_ The target parameter can be used to confirm
[Email protected]> show parameter pga_aggregate_target;

NAME TYPE VALUE
----------------------- -------------------- -----------
Pga_aggregate_target Big Integer 20M

3. The third column represents the percentage of the PGA's estimated cache hit rate
At present, if the PGA is 20M, it can achieve a 100% hit rate.

4. Fourth column if "0" means that the PGA overload can be eliminated
From the above data can be obtained, when the PGA is 36M, can eliminate the overload of the PGA.

5. Combined with the above conclusions, we can eventually set the PGA size to 36M.
[Email protected]> alter system set pga_aggregate_target=36m;

System altered.

6. After the adjustment, once again query the V$pga_target_advice view to get the following suggestions, you can see that basically has met the current system requirements.
[Email protected]> Select pga_target_for_estimate/1024/1024 "PGA (MB)",
2 Pga_target_factor,
3 Estd_pga_cache_hit_percentage,
4 Estd_overalloc_count
5 from V$pga_target_advice;

PGA (MB) pga_target_factor estd_pga_cache_hit_percentage Estd_overalloc_count
---------- ----------------- ----------------------------- --------------------
18.5 94 2
27.75 94 2
36 1 100 0
43.1992188 1.2 100 0
50.3994141 1.4 100 0
57.5996094 1.6 100 0
64.7998047 1.8 100 0
72 2 100 0
108 3 100 0
144 4 100 0
216 6 100 0
288 8 100 0

Rows selected.

Congratulations, you must have "seen" this v$pga_target_advice view.

This is a dynamic process that can periodically view this view to get more efficient PGA size setup recommendations.

Usage of view V$pga_target_advice in Oracle