The usage of V $ pga_target_advice

Oracle 10g provides a series of Automatic Optimization suggestions, telling us how powerful the PGA allocation can bring the maximum performance to the system? The V $ pga_target_advice view provides a good "prediction "!

Let's take a look at what information this view can bring to us:
SQL> select pga_target_for_estimate/1024/1024 "PGA (MB)", pga_target_factor, estd_pga_cache_hit_percentage, estd_overalloc_count from V $ pga_target_advice;

PGA (MB) pga_target_factor estd_pga_cache_hit_percentage estd_overalloc_count
----------------------------------------------------------------------------
10. 125 100 1
20. 25 100 1
40. 5 100 1
60. 75 100 0
80 1 100 0
96 1.2 100 0
112 1.4 100 0
128 1.6 100 0
144 1.8 100 0
160, 2, 100, 0
240, 3, 100, 0

PGA (MB) pga_target_factor estd_pga_cache_hit_percentage estd_overalloc_count
----------------------------------------------------------------------------
320 4 100 0
480 6 100 0
640 8 100 0

14 rows selected.

Through the above data, we can draw the following conclusions:
1. The first column indicates the specific values of different PGA

2. The second column pga_target_factor is "1", indicating the current pga_aggregate_target size (other values are multiples of this data). Here I am 80 m,

You can check the pga_aggregate_target parameter.
SQL> show parameter pga_aggregate_target

Name type value
--------------------------------------------------------------------------------------------------
Pga_aggregate_target big integer 80 m

3. The third column indicates the percentage of cache hit rate estimated by PGA.
If the PGA of the current system is 10 MB, the hit rate is 100%.

4. If the fourth column is "0", the PGA overload can be eliminated.
As shown in the preceding figure, when the PGA is 60 MB, the PGA overload can be eliminated.

5. Through the above conclusion, we can set the PGA size to 60 m.
SQL> alter system set pga_aggregate_target = 60 m;

System altered.

6. After the adjustment, query the V $ pga_target_advice view again to obtain the following suggestions. You can see that the current system requirements are basically met.
SQL> select pga_target_for_estimate/1024/1024 "PGA (MB)", pga_target_factor, estd_pga_cache_hit_percentage, estd_overalloc_count 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
60 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

12 rows selected.

As described above, you will have learned about the V $ pga_target_advice view.