Re: PGA increasing continuously

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

https://tanelpoder.com/2014/03/26/oracle-memory-troubleshooting-part-4-drilling-down-into-pga-memory-usage-with-vprocess_memory_detail/

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 12.1.0.1.
> We tried the ojdbc7.jar from 12.1.0.2 and the problem worsens.
> Problem persists with ojdbc6.jar from 11.2.
> With ojdbc6.jar problem does not reproduce.
>
> Problem persists with DB 11.2.0.4, 12.1.0.2. 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


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