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).
Received on Sat May 17 2003 - 15:06:59 CDT
