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 which process and query were at fault. Which two views should you use for this purpose?

The ORA-04036 error indicates that the instance-wide PGA memory usage exceeded the PGAAGGREGATELIMIT. To investigate this past event, historical data from the Automatic Workload Repository (AWR) is required. DBAHISTACTIVESESSHISTORY contains snapshots of active sessions, including the SQLID and PGAALLOCATED for each sample. By querying this view for the time of the error, you can identify the session and SQLID with the highest PGA consumption. The DBAHISTSQLTEXT view is then used to retrieve the full text of the problematic query using the identified SQLID.

B. DBAHISTSQLSTAT provides aggregated historical statistics for SQL statements but does not link a specific execution to a process or a point-in-time memory spike.
D. DBAHISTPGASTAT contains historical instance-wide PGA statistics, which is useful for trend analysis but not for identifying a specific faulty process or query.
E. DBAHISTPROCESSMEMSUMMARY provides a historical summary of memory usage by process but does not contain the SQLID to link memory usage to a specific query.

1. Oracle Database Performance Tuning Guide, 19c. Chapter 7, "Automatic Performance Diagnostics". This chapter describes using AWR views like DBAHISTACTIVESESSHISTORY to diagnose transient performance problems.

2. Oracle Database Reference, 19c. "DBAHISTACTIVESESSHISTORY". The documentation for this view lists columns such as SAMPLETIME, SQLID, and PGAALLOCATED, confirming its utility for this diagnosis.

3. Oracle Database Reference, 19c. "DBAHISTSQLTEXT". This view is documented as the source for historical SQL text, to be joined by SQLID.