This error occurred more than four hours ago in the database: ORA-04036 PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT You want to know…

To investigate the cause of the ORA-04036 error, which indicates that PGA memory usage exceeds

the PGA_AGGREGATE_LIMIT, the appropriate views to consult are DBA_HIST_ACTIVE_SESS_HISTORY

and DBA_HIST_PROCESS_MEM_SUMMARY.

DBA_HIST_ACTIVE_SESS_HISTORY: This view provides historical information about active sessions in

the database. It includes details about the SQL executed, the execution context, and the resources

consumed by each session. By examining this view, you can identify the specific sessions and SQL

queries that were active and potentially consuming excessive PGA memory around the time the

ORA-04036 error occurred.

DBA_HIST_PROCESS_MEM_SUMMARY: This view contains historical summaries of memory usage by

processes. It can help in identifying the processes that were consuming a significant amount of PGA

memory, leading to the ORA-04036 error. This view provides aggregated memory usage information

over time, making it easier to pinpoint the processes responsible for high PGA memory

consumption.

Together, these views offer a comprehensive overview of the memory usage patterns and specific

queries or processes that might have contributed to exceeding the PGA_AGGREGATE_LIMIT,

resulting in the ORA-04036 error.

Reference:

Oracle Database Reference: DBA_HIST_ACTIVE_SESS_HISTORY

Oracle Database Reference: DBA_HIST_PROCESS_MEM_SUMMARY

Oracle Database Performance Tuning Guide: Managing Memory