RE: PGA allocation

The memory assigned to PGA_AGGREGATE_TARGET is much higher than 20% of SGA as suggested in the article. But then if I look at the V$PGASTAT view, the "over allocation count" value is 521, which means Oracle tried 521 times to allocate
more PGA than the specified TARGET. This must be the effect of constantly growing PGA.
so why is it growing beyond 1G target (which equals to 100% SGA) and not getting released?

Thanks
Ashish

-----Original Message-----
Gogala
Sent: Saturday, May 17, 2003 4:07 PM
To: Multiple recipients of list ORACLE-L

Ashish, your setting for the PGA_AGGREGATE_TARGET is far too big. For an OLTP
system it is recommended that this parameter is 20% of the SGA size and of a DSS
system, the recommendation is 50%. What is happening in your case is very simple:
the maximum memory for PGA an never be reached, so the instance doesn't release
it. Take a look at the Metablink Note:223730.1

On 2003.05.17 12:41 Ashish wrote:
> Hi
>
> I am facing a problem regarding PGA allocation. In the 9.2.0.2 database on
> Windows 2000,
> PGA_AGGREGATE_TARGET is set to 1G, SGA is 1G. The database server has 4 GB
> physical memory plus
> virtual disk space. The front-end is written using ASP. The web server
> connects to the database using
> connection pooling.
>
> The problem is, the PGA allocation to sessions gradually increases over
the
> time then it reaches
> memory limit on the server and web-server connections fail with ORA-04030
> error (out of process memory).
> (The PGA allocation/per session can be seen using V$PROCESS view and total
> PGA allocation can be seen
> using V$PGASTAT)
>
> I am not able find out why the PGA is not getting released. I do not see
any
> continuously active sessions
> or huge sorts or anything that requires huge PGA. The PGA just keeps
> growing.
> Can anyone help me with this problem? Any help is appreciated.
> Also is there any way to peek into PGA and see how much is allocated to
each
> session?
>
> TIA
>
> Ashish
>

--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ashish
  INET: ash03_at_att.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).