Re: PGA increasing continuously

Thanks.

We had tried this but it did not give any clues on the root cause.

On Sat, 5 Dec 2020 at 10:49, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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