Re: PGA increasing continuously

Are they closing cursors?

On Fri, Dec 4, 2020, 08:02 Purav Chovatia <puravc_at_gmail.com> 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.
>

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