The usage of V $ PGA_TARGET_ADVICE

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 us: SQL> SELECT pga_target_for_estimate/1024/1024 "PGA (MB)", pga_target_factor, assign, 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 128 1.6 100 144 1.8 100 160 100 2 240 100 3 0 PGA (MB) PGA_TARGET_FACTOR into ESTD_OVERALLOC_COUNT ---------- ----------------- ----------------------------- ---------------------- 320 4 100 0 480 6 100 8 640 014 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", which indicates the current pga_aggregate_target size (other values are multiples of this data). Here I am 80 M, through p The ga_aggregate_target parameter can be used to confirm the SQL> show parameter pga_aggregate_targetNAME TYPE VALUE -------------------------------------------------- pga_aggregate_target big integer 80M3. the third column indicates the percentage of Cache hit rate estimated by PGA. Currently, if the PGA is 10 MB, the system can achieve a hit rate of 100%. if the fourth column is "0", the PGA overload can be eliminated from the above data. When the PGA is 60 m, 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 distinct/1024/1024 "PGA (MB)", pga_target_factor, distinct, estd_overalloc_count FROM v $ distinct; PGA (MB) PGA_TARGET_FACTOR into ESTD_OVERALLOC_COUNT ---------- --------------- limit 18. 5 94 2 27. 75 94 2 60 1 100 043.1992188 1.2 100 050.3994141 1.4 100 057.5996094 10 0 064.7998047 1.8 100 0 72 2 100 0 108 3 100 4 144 0 100 6 216 0 100 8 288 012 rows selected. as described above, you will have learned about the v $ pga_target_advice view.