Re: PGA increasing continuously

From: Mladen Gogala <>
Date: Sat, 5 Dec 2020 00:18:17 -0500
Message-ID: <>

You can trace PGA operations and usage by using the technique described by Tanel Poder:

ALTER SESSION SET EVENTS ‘immediate trace name pga_detail_get level N’ where N is the Oracle PID of the process.

Personally, I haven't tried that in a very long time. However, the article is very detailed and contains a treasure trove of information. Tanel has no equal when it comes to the Oracle internals.

On 12/4/20 11:01 AM, Purav Chovatia wrote:
> Hello Experts,
> Need some help desperately.
> We have an application that is doing a StoredProc call and a SQL call
> from a Standalone Java App which uses the Thin driver.
> The StoredProc does an UPDATE with a WHERE clause on the single column
> Primary Key.
> If the SQL%ROWCOUNT=0 then it will do an INSERT.
> The SQL does a select on the table with a WHERE clause on the Primary Key.
> No joins. No CLOB/BLOB/LOB. No XML. Prepared Statements used. Only
> thing that stands out is that the table has 100+ columns.
> PGA goes on increasing and hence free memory on DB server goes on
> decreasing.
> Problem originally started with ojdbc7.jar from
> We tried the ojdbc7.jar from and the problem worsens.
> Problem persists with ojdbc6.jar from 11.2.
> With ojdbc6.jar problem does not reproduce.
> Problem persists with DB, Problem persists on DB
> server on Linux as well on Solaris.
> On DB server, we dont see any open cursor problem. No error on App
> side or on DB side except ORA-04036 in case of 12c because it will cap
> PGA with pga_aggregate_limit.
> We dug on PGA front and identified that subheap kxs-heap-p is where
> the memory is used. But cannot understand what does that mean?
> Would appreciate any ideas.
> It was a shock to realize that there is no way beyond identifying the
> subheap! Developers are ready to fix the issue but they have been
> requesting to tell what is to be fixed. Is there no way to identify
> what is eating up the PGA?
> Thank you.

Mladen Gogala
Database Consultant
Tel: (347) 321-1217

Received on Sat Dec 05 2020 - 06:18:17 CET

Original text of this message