PGA Monitor 
This submonitor in the SAP/Oracle Database Monitor lets you monitor the Program Global Area (PGA).
Use
You choose Detailed Analyses ® Resource Consumption ® PGA Monitor.
You can view history information in this monitor.
Structure
Entries marked “RAC only” are only relevant for Oracle Real Application Cluster (RAC).
· Status
¡ PGA Status
This tab page shows the following information about the PGA configuration based on the view GV$PGASTAT:
Column |
Description |
Name |
Name of the statistic |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
Statistic value |
Statistic value |
Unit |
Statistic unit, such as bytes. |
¡ SQL Workarea
§ View SQL WORKAREA
This tab page shows the following information about the PGA configuration based on the view GV$SQL_WORKAREA:
Column |
Description |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
Workarea Address |
Address of the parent cursor handle |
Parent address |
Address of the work area handle. This is the "primary key" for the view. |
Hash Value |
Hash value of the parent statement in the library cache |
Chil… – Number of this child cursor |
Number of the child cursor that uses this work area |
Operation Type |
Type of operation using the work area (SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, or BITMAP CREATE) |
Oper. … – Operation ID |
Unique number used to identify the operation in the execution plan |
Sizi – Sizing |
Sizing policy for this work area (MANUAL or AUTO) |
Est OptSiz – Estimated Optimal Size |
Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). |
Est 1p Siz – Estimated Onepass Size |
Estimated size in KB required by this work area to execute the operation in a single pass |
Last Mem – Memory Used for Last Execution |
Memory in KB used by this work area during the last execution of the cursor |
Last Exec – Last Execution |
Indicates whether this work area runs using OPTIMAL, ONE PASS, or ONE PASS memory requirement (or MULTI-PASS), during the last execution of the cursor |
Last De… – Degree of parallelism of last exec |
Degree of parallelism used during the last execution of this operation |
Tot. Execs – Total Executions |
Number of times this work area was active |
Opt. Execs – Optimal Executions |
Number of times this work area ran in optimal mode |
Onepass – Onepass Executions |
Number of times this work area ran in one-pass mode |
Multipa… – Multipasses Executions |
Number of times this work area ran below the one-pass memory requirement |
Act. Time – Average Active Time |
Average time this work area is active in hundredths of a second |
Max Tseg… – Maximum Temporary Segment Size |
Maximum temporary segment size in bytes created by an instantiation of this work area. This column is null if this work area has never spilled to disk. |
Last Tseg – Last Temporary Segment Size |
Temporary segment size in bytes created in the last instantiation of this work area. This column is null if the last instantiation of this work area did not spill to disk. |
§ Top 10 mem. cache con
This tab page shows the top 10 consumers of memory cache, based on the view GV$SQL_WORKAREA. The information shown is the same as in the table above.
§ One-multipass workarea
This tab page shows the work areas, the SQL text, the number of executions in the different modes, and the percentage of the total number of executions. The information shown is based on the views GV$SQL and GV$SQL_WORKAREA:
Column |
Description |
SQL Text |
First thousand characters of the SQL text for the current cursor |
Optimal … – Optimal Executions |
Number of times this work area ran in optimal mode |
Optimal Pe – Optimal Percentage |
Optimal Executions as a percentage of Total Executions |
Onepass … – Onepass Executions |
Number of times this work area ran in one-pass mode |
Onepass Pe – Onepass Percent |
Onepass Executions as a percentage of Total Executions |
Multipass … – Multipasses Executions |
Number of times this work area ran below the one-pass memory requirement |
Multipass … – Multipasses Percent |
Multipasses Executions as a percentage ofTotal Executions |
Total Exec – Total Executions |
Number of times this work area was active |
¡ SQL Workarea Histogram
§ Histogram
This tab page shows how many work areas were executed in optimal, one-pass, or multi-pass mode. The information shown is based on the view GV$SQL_WORKAREA_HISTOGRAM:
Column |
Description |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
Low bound |
Lower bound for the optimal memory requirement of work areas included in this row (bytes) |
High Bound |
Higher bound for the optimal memory requirement of work areas included in this row (bytes) |
Opt. Execs – Optimal Executions |
Number of times this work area ran in optimal mode |
Onepass – Onepass Executions |
Number of times this work area ran in one-pass mode |
Multipa – Multipass Executions |
Number of times this work area ran below the one-pass memory requirement |
Tot. Execs – Total Executions |
Number of times this work area was active |
§ Percent optimal
This tab page shows how many work areas were executed in optimal, one-pass, or multi-pass mode and the percentage. The information shown is based on the view GV$SQL_WORKAREA_HISTOGRAM:
Column |
Description |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
Optimal – Optimal Executions |
Number of work areas with an optimal memory requirement between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE that have been executed in optimal mode since instance startup |
Optimal Pe – Optimal Percent |
Optimal Executions as a percentage of Total Executions |
Onepass … – Onepass Executions |
Number of work areas with an optimal memory requirement between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE that have been executed in one-pass mode since instance startup |
Onepass Pe – Onepass Percent |
Onepass Executions as a percentage of Total Executions |
Multipas … – Multipasses Executions |
Number of work areas with an optimal memory requirement between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE that have been executed in multi-pass mode since instance startup |
Multipas … – Multipasses Percent |
Multipasses Executions as a percentage of Total Executions |
Total Exec – Total Executions |
Number of times this work area was active |
¡ Workarea Executions
This tab page shows how often work areas were executed in different modes. The information shown is based on the view GV$SYSSTAT.
Column |
Description |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
Name |
Statistic name |
Value |
Statistic value |
% |
Percentage of executions for each statistic name |
· Snapshot
¡ Current Operations
This tab page shows currently active operations. The information shown is based on the view GV$SQL_WORKAREA_ACTIVE.
Column |
Description |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
SID |
Session identifier |
Oper. Type |
Type of operation using the work area (sort, hash join, group by, buffering, bitmap merge, or bitmap create) |
Exp. Size – Expected workarea size |
Expected size in KB for the work area |
Act. Used – PGA Memory Currently |
Amount of PGA memory in KB currently allocated for this work area |
Max Mem – Maximum memory used |
Maximum amount of memory used by this work area |
Passes – Number of Passes |
Number of passes for this work area |
TmpSeg … – Temporary Segment Size |
Size in bytes of the temporary segment used for this work area. |
SQL Text |
Text of SQL statement |
¡ PGA Memory Usage
This tab page shows currently active operations. The information shown is based on the view GV$PROCESS.
Column |
Description |
OS Program Name |
Operating system program name |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
PGA Memory |
PGA memory currently used by the process |
PGA Memory |
PGA memory currently allocated by the process |
Max PGA Memory |
Maximum PGA memory allocated by the process |
Process St. |
Process status |
SQL Text |
Text of SQL statement |
· PGA Advice
¡ Target Advice Size
This tab page predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.
The information shown is based on the view V$PGA_TARGET_ADVICE.
Column |
Description |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
TARGET (MB) |
Operating system program name |
Val – Estimated value of the cache hit percent |
Value of PGA_AGGREGATE_TARGET for this prediction (in bytes) |
Overall.Cn. – Overalloc. count |
Estimated number of PGA memory over-allocations if the value of PGA_AGGREGATE_TARGET is set to PGA_TARGET_FOR_ESTIMATE. |
¡ Advice Histogram
Column |
Description |
Inst Id RAC only |
Instance ID |
Inst Name RAC only |
Instance name |
PGA_TARGET |
PGA_TARGET_FACTOR, equal to PGA_TARGET_FOR_ESTIMATE divided by current value of PGA_AGGREGATE_TARGET. |
LOW KB |
Lower boundary for the optimal memory requirement of work areas included in this row, in bytes |
HIGH_KB |
Upper boundary for the optimal memory requirement of work areas included in this row, in bytes |
Optimal Ex – Optimal Executions |
Number of work areas with an optimal memory requirement between LOW KB and HIGH_KB, which are predicted to run optimally when PGA_AGGREGATE_TARGET = PGA_TARGET_FOR_ESTIMATE. |
Onepass Ex – Onepass Executions |
Number of work areas with an optimal memory requirement between LOW KB and HIGH_KB, which are predicted to run one-pass when PGA_AGGREGATE_TARGET = PGA_TARGET_FOR_ESTIMATE. |
Multipasse – Multipasses Executions |
Number of work areas with an optimal memory requirement between LOW KB and HIGH_KB, which are predicted to run multi-pass when PGA_AGGREGATE_TARGET = PGA_TARGET_FOR_ESTIMATE. |