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
behavior.
Be sure to include a test case builder archive for the problem statement
and a SQL Monitor report. See:
http://blogs.oracle.com/optimizer/entry/oracle_keeps_closing_my_tar_because_i_cannot_provide_a_testcase_can_you_help
https://raw.github.com/grahn/oracle_scripts/master/tcb.sh
http://structureddata.org/2008/01/06/oracle-11g-real-time-sql-monitoring-using-dbms_sqltunereport_sql_monitor/


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?
>

-- 
Regards,
Greg Rahn  |  blog <http://bit.ly/u9N0i8>  |  twitter <http://bit.ly/v733dJ>  |
 linkedin <http://linkd.in/gregrahn>


--
//www.freelists.org/webpage/oracle-l


Other related posts: