Re: PGA increasing continuously

From: Purav Chovatia <puravc_at_gmail.com>
Date: Fri, 4 Dec 2020 21:58:57 +0530
Message-ID: <CADrzpjHRH3U+cQ0DMiEgj3pYoZqJ7mMWHAuRxsu81yFpOpB6GA_at_mail.gmail.com>



Yes, according to the developers.

But just in case they werent, it should have shown as open cursors, isnt it? And we are not getting any errors around cursors neither are we seeing high count of open cursors.

On Fri, 4 Dec 2020 at 9:49 PM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> 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
Received on Fri Dec 04 2020 - 17:28:57 CET

Original text of this message