Re: Killer SQL and PGA

  • From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • To: Robert.Laverty@xxxxxxxxxxxxxxxxxxxx
  • Date: Tue, 31 Jan 2012 11:18:45 -0800

If you have a reproducible test case, open an SR and file a bug.  Even
though PGA memory management is controlled via a "target" not a "limit",
overshooting it by a such a significant is clearly not the expected
Be sure to include a test case builder archive for the problem statement
and a SQL Monitor report. See:

On Mon, Jan 30, 2012 at 3:13 PM, Robert Laverty <
Robert.Laverty@xxxxxxxxxxxxxxxxxxxx> wrote:

> We had a problem with our PGA growing larger than physical memory,
> dragging the system down until we were forced to restart the database.  We
> recently upgraded from 9i to 11gR2 on Solaris with 16Gb physical memory
> hosting an OLTP application.  4Gb is used for SGA and 400Mb for
> PGA_aggregate_target.  AMM and ASMM have not been enabled.
>  Workarea_size_policy is set to AUTO.  This is a simple database.  No RAC,
> no shared servers, no parallel processing.
> My real question is why the 11g memory management, without AMM or ASMM,
> would allow the PGA to grow so large.  In 15 years of operations, there
> must have been similar bad queries against the database.  This happened a
> day after the 11g upgrade.  Any suggestions?

Greg Rahn  |  blog <>  |  twitter <>  |
 linkedin <>


Other related posts: