Voltar
-
Título
Monitoring PGA statistics in the Oracle agent -
Descrição
What PGA statistics are monitored in the Oracle agent?
How can a user monitor when the Oracle database is reaching its maximum PGA (Program Global Area) allocation?
What steps can you do to remedy the situation?
What are the DBO_PGA_Hit_Low and DBO_PGA_Hit_Medium registry values used for?
Where can the PGA metrics be found?
-
Resolução
What metrics are obtained by the PGA Statistics collection
The PGA statistics collection obtains three different PGA metrics
- Aggregate PGA target parameter: is the target size for the PGA, which is the amount of memory that the Oracle database aims to allocate for PGA. You can compare this with actual usage to understand if you're close to or exceeding the target. If the value of this parameter is set correctly, all work areas executed by the system since start-up are processed exclusively in the PGA memory, requiring no extra passes. In such a case, the PGA hit percent’s value is 100%, and Oracle does not need to allocate extra memory to the PGA. However, when this value is set too small, the hit percent’s value decreases, and Oracle may need to perform one or more extra memory allocations.
- Over allocation count: tracks the number of times the PGA memory allocation exceeds its target. If this count increases significantly, it indicates that the database is using more memory than the allocated PGA target, which could lead to performance issues. This can happen if the PGA_AGGREGATE_TARGET is set too small for the workload, forcing Oracle to allocate extra PGA memory. Ideally, the PGA cache hit ratio should be near 100%, with very few over-allocations. High values of the Over Allocation Count suggest the need to increase the PGA_AGGREGATE_TARGET to better accommodate the workload and prevent performance degradation.
- Cache hit percentage: how efficiently the PGA memory is being used. A low cache hit percentage indicates that PGA memory is not being used effectively, possibly requiring more PGA memory to improve performance. A high percentage means most of the required memory is already in cache, resulting in less additional memory allocation. When the cache hit percentage reaches 100%, it signifies that all work areas processed data exclusively via PGA memory, making optimal use of it. Any lower value suggests that some work areas needed extra passes over the input data, indicating less efficient memory usage.
- PGA Hit: The total number of bytes processed in the PGA versus the total number of bytes processed plus extra bytes read/written in extra passes
The SGA Components collection obtains an additional related metric.
PGA currently allocated: how much memory is currently allocated for the PGA. Monitoring this value helps in understanding whether the database is approaching its maximum PGA allocation.
How can a user monitor when the Oracle database is reaching its maximum PGA (Program Global Area) allocation?
- Approaching the PGA Target: If PGA Currently Allocated approaches or is greater than Aggregate PGA target parameter, this means the database is nearing its PGA allocation limit. If it's consistently close to the target, you should consider increasing the target or tuning PGA memory usage.
- Over Allocation Count: When this value is greater than 0 (especially if it's increasing), it indicates that the database is running out of PGA memory and is over-allocating. This should trigger an investigation into whether more PGA memory is needed or if there are inefficiencies in how memory is being used.
What steps can you do to remedy the situation?
- Increase the Aggregate PGA target parameter: If you're reaching the target or frequently over-allocating, consider increasing the this value to give the database more memory to work with for the PGA. You can adjust this parameter based on your system's available resources.
- Monitor Regularly: Keep an eye on these metrics over time. If the over-allocation count is rising, it may indicate that the system’s workload has increased, and it's time to adjust the PGA memory configuration.
- Optimize Workloads: If you're frequently hitting PGA limits, consider optimizing queries or operations that consume large amounts of PGA memory (such as complex sorts or hash joins).
What are the DBO_PGA_Hit_Low and DBO_PGA_Hit_Medium registry values used for?
The DBO_PGA_Hit_Low and DBO_PGA_Hit_Medium registry values are related to the PGA Statistics collection and are used to define the thresolds displayed in the PGA Hit popup available from the Oracle agent Realtime metrics page. The threshold defaults are 70% for low and 95% for medium.
Where can the PGA metrics be found?
From within the UI
The PGA Total MB and PGA Hit metrics can be found on the Real Time metrics page for the Oracle database agent.
From within the Oracle agent topology
- pga_agr_target: All Data[]->DBO_Data_Model-->clusters[]-->instances[]-->pga_statistics-->pga_agr_target
- pga_hit: All Data[]->DBO_Data_Model-->clusters[]-->instances[]-->pga_statistics-->pga_hit
- pga_over_alloc: All Data[]->DBO_Data_Model-->clusters[]-->instances[]-->pga_statistics-->pga_over_alloc
- pga_cur_alloc: All Data[]->DBO_Data_Model-->clusters[]-->instances[]-->sga_components-->pga_cur_alloc