Get the PGA adjustment suggestions based on the new 10 Gb feature.

10gOracle provides a series of Automatic Optimization suggestions,PGAHow much power can be allocated to bring the greatest 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 (each column in the view is helpful ):
Sys @ ora10g> 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
12 rows selected.
Through the above data, we can draw the following conclusions:
1. The first column indicates the specific values of different PGA
2. If pga_target_factor in the second column is set to "1", it indicates the current pga_aggregate_target size (other values are multiples based on this data). Here we are 20 mb. You can confirm it through the pga_aggregate_target parameter.
Sys @ ora10g> show parameter pga_aggregate_target;
Name type value
------------------------------------------------------
Pga_aggregate_target big integer 20 m
3. The third column indicates the percentage of cache hit rate estimated by PGA.
If the PGA of the current system is 20 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 36 MB, the PGA overload can be eliminated.
5. Based on the above conclusions, we can finally set the PGA size to 36 MB.
Sys @ ora10g> alter system set pga_aggregate_target = 36 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.
SEC @ ora10g> 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
12 rows selected.
Congratulations, you will definitely see the V $ pga_target_advice view here.
This is a dynamic process. You can view this view regularly to obtain more effective PGA size settings.
[OEM method] It is also the recommended information above. We can obtain more intuitive information through Oracle OEM.
1. Use the IP address of your server to log on to the OEM interface.
Http: // 144.194.192.183: 1158/EM/console/logon
2. Data User Name (sys) and password (sys password), connect as select "sysdb", and then click "login"
3. there are three top options: "home", "performance", "Administration", and "maintenance". Select "Administration ", in the "Database Configuration" category, the first one is "memory Parameters". Click to enter. Now you will see "SGA" and "PGA", click the second "PGA", OK, here we can see"Advice"And" PGA memory usage details "buttons. The information obtained from the first" advice "button is the information obtained from the V $ pga_target_advice view above, which is more intuitive here, it is displayed as a graph.
Another button "PGA memory usage details" is a statistical column chart obtained through the view v $ pga_target_advice_histogram.
The figure is as follows:

 
[View description] Description of views v $ pga_target_advice and V $ pga_target_advice_histogram in 10 Gb official documents

V $ pga_target_advice

V$PGA_TARGET_ADVICEPredicts how the cache hit percentage and over allocation count statistics displayed byV$PGASTATPerformance view wocould be impacted if the value ofPGA_AGGREGATE_TARGETParameter is changed. The prediction is already Med for various values ofPGA_AGGREGATE_TARGETParameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty ifPGA_AGGREGATE_TARGETIs not set. In addition, the content of this view is not updated ifSTATISTICS_LEVELParameter is setBASIC. Base statistics for this view are reset at instance startup and when the value ofPGA_AGGREGATE_TARGETInitialization parameter is dynamically modified.

Column
Datatype
Description

PGA_TARGET_FOR_ESTIMATE
NUMBER
ValuePGA_AGGREGATE_TARGETFor this prediction (in bytes)

PGA_TARGET_FACTOR
NUMBER
PGA_TARGET_FOR_ESTIMATE/The current value ofPGA_AGGREGATE_TARGETParameter

ADVICE_STATUS
VARCHAR2(3)
Indicates whether the advice is enabled (ON) Or disabled (OFF) Depending on the value ofSTATISTICS_LEVELParameter

BYTES_PROCESSED
NUMBER
Total Bytes processed by all the work areas considered by this advice (in bytes)

ESTD_EXTRA_BYTES_RW
NUMBER
Estimated number of extra bytes which wocould be read or written ifPGA_AGGREGATE_TARGETWas set to the value ofPGA_TARGET_FOR_ESTIMATEColumn. This number is derived from the estimated number and size of work areas which wocould run in one-pass (or multi-pass) for that valuePGA_AGGREGATE_TARGET.

ESTD_PGA_CACHE_HIT_PERCENTAGE
NUMBER
Estimated value of the cache hit percentage statistic whenPGA_AGGREGATE_TARGETEqualsPGA_TARGET_FOR_ESTIMATE. This column is derived from the above two columns and is equalBYTES_PROCESSED/(BYTES_PROCESSED+ESTD_EXTRA_BYTES_RW)

ESTD_OVERALLOC_COUNT
NUMBER
Estimated number of PGA memory over-allocations if the valuePGA_AGGREGATE_TARGETIs setPGA_TARGET_FOR_ESTIMATE. A nonzero value means thatPGA_TARGET_FOR_ESTIMATEIs not large enough to run the work area workload. Hence, the DBA shocould not setPGA_AGGREGATE_TARGETToPGA_TARGET_FOR_ESTIMATESince Oracle will not be able to honor that target.

V $ pga_target_advice_histogram

V$PGA_TARGET_ADVICE_HISTOGRAMPredicts how Statistics displayed byV$SQL_WORKAREA_HISTOGRAMDynamic View wocould be impacted if the value ofPGA_AGGREGATE_TARGETParameter is changed. This prediction is already Med for various values ofPGA_AGGREGATE_TARGETParameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty ifPGA_AGGREGATE_TARGETIs not set. In addition, the content of this view is not updated whenSTATISTICS_LEVELInitialization parameter is setBASIC. Base statistics for this view are reset at instance startup or when the value ofPGA_AGGREGATE_TARGETInitialization parameter is dynamically modified.

Column
Datatype
Description

PGA_TARGET_FOR_ESTIMATE
NUMBER
ValuePGA_AGGREGATE_TARGETFor this prediction (in bytes)

PGA_TARGET_FACTOR
NUMBER
PGA_TARGET_FOR_ESTIMATE/The current value ofPGA_AGGREGATE_TARGETParameter

ADVICE_STATUS
VARCHAR2(3)
Indicates whether the advice is enabled (ON) Or disabled (OFF) Depending on the value ofSTATISTICS_LEVELParameter

LOW_OPTIMAL_SIZE
NUMBER
Lower Bound for the optimal memory requirement of work areas attached ded in this row (in bytes)

HIGH_OPTIMAL_SIZE
NUMBER
Upper Bound for the optimal memory requirement of work areas attached ded in this row (in bytes)

ESTD_OPTIMAL_EXECUTIONS
NUMBER
Number of work areas with an optimal memory requirement comprisedLOW_OPTIMAL_SIZEAndHIGH_OPTIMAL_SIZEWhich are predicted to run optimal given a valuePGA_AGGREGATE_TARGETEqualPGA_TARGET_FOR_ESTIMATE

ESTD_ONEPASS_EXECUTIONS
NUMBER
Number of work areas with an optimal memory requirement comprisedLOW_OPTIMAL_SIZEAndHIGH_OPTIMAL_SIZEWhich are predicted to run one-pass given a valuePGA_AGGREGATE_TARGETEqualPGA_TARGET_FOR_ESTIMATE

ESTD_MULTIPASSES_EXECUTIONS
NUMBER
Number of work areas with an optimal memory requirement comprisedLOW_OPTIMAL_SIZEAndHIGH_OPTIMAL_SIZEWhich are predicted to run multi-pass given a valuePGA_AGGREGATE_TARGETEqualPGA_TARGET_FOR_ESTIMATE

ESTD_TOTAL_EXECUTIONS
NUMBER
SumESTD_OPTIMAL_EXECUTIONS,ESTD_ONEPASS_EXECUTIONS, AndESTD_MULTIPASSES_EXECUTIONS

IGNORED_WORKAREAS_COUNT
NUMBER
Number of work areas with optimal memory requirementLOW_OPTIMAL_SIZEAndHIGH_OPTIMAL_SIZEIgnored in the advice generation due to memory and CPU Constraints

-- The end --