Re: Killer SQL and PGA

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 31 Jan 2012 11:18:45 -0800
Message-ID: <CAGXkmiuHFdRmsTQ_+WcuHCP_P6-9wUK1gnrBzNp4CTpPdFKqSQ_at_mail.gmail.com>

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_at_molinahealthcare.com> 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>


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

Received on Tue Jan 31 2012 - 13:18:45 CST