Notes on Oracle memory Parameters

1. pga_aggregate_target

Starting from Oracle9i, Oracle introduced the new feature of automatic PGA management. The PGA_AGGREGATE_TARGET parameter is used to control the overall expected goal of PGA, but in Oracle9i, the PGA_AGGREGATE_TARGET parameter is valid only for Dedicated connections of the Dedicated Server in Dedicated Server mode and invalid for Shared Server connections; PGA_AGGREGATE_TARGET takes effect for both dedicated server connections and shared server connections starting from Oracle10g.

 

If (database version> = 10gR1)
Then
If (workarea_size_policy = auto)
Then
Pga_aggregate_target is valid.
Else
Sort_area_size is valid.
End if
Else -- database version <10gr1
If (shared server connection mode)
Then
Sort_area_size is valid.
Else -- dedicated server connection mode
If (workarea_size_policy = auto)
Then
Pga_aggregate_target is valid.
Else
Sort_area_size is valid.
End if
End if
End if

 

PGA efficiency v $ SQL _workarea_histogram

 

Select
Case
When low_optimal_size <1024*1024
Then to_char (low_optimal_size/1024, '123') |
'Kb <= pga <'|
(High_optimal_size + 1)/1024 | 'kb'
Else to_char (low_optimal_size/1024/1024, '20140901') |
'Mb <= pga <'|
(High_optimal_size + 1)/1024/1024 | 'mb'
End | ':' |
Optimal_executions | '--' |
Onepass_executions | '--' |
Multipasses_executions as mystat
From
V $ SQL _workarea_histogram
Where total_executions <> 0
Order by low_optimal_size

 

Analyze by PGA workarea. 2 mb <= pga <4 mb: 2571--756--0 indicates that the workarea runs 2571 times in the memory between 2 mb and 4 mb, and the disk is used once for 756 times, disk is used multiple times for zero times.

PGA recommendation v $ pga_target_advice

 

Select
Trunc (pga_target_for_estimate/1024/1024) | 'mb'
Pga_target_for_estimate,
To_char (pga_target_factor * 100, '192. 9') | '%'
Pga_target_factor,
Trunc (bytes_processed/1024/1024) bytes_processed,
Trunc (estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
To_char (estd_pga_cache_hit_percentage, '000000') | '%'
Estd_pga_cache_hit_percentage,
Estd_overalloc_count
From
V $ pga_target_advice

 

The current pga_aggregate_target is 209715200, that is, 200 m. From the result, the hit rate is 98%, indicating that the current setting is acceptable.