Re: PGA increasing continuously

Thank you very much!

It is a shame that inspite of so many searches, we could not find this bug on MoS. But then its description is too short to be able to find it.

May I ask, how did you know of this bug? Have you encountered it before?

Oracle Support too was not able to find it inspite of 2 SRs open for over a month. They kept denying on any bugs. Then they suggested upgrading to latest RU for 11.2.0.4. That too did not fix it. This patch, though released in 2015, is still not a part of Oct 2020 RU. A few days back, after escalating one of the SRs, their manager said he knows of something and recommended this very bug.

Further findings:
- we were using *ojdbc7.jar* from 12.1.0.1. With that, and the *above patch applied* on 11.2.0.4, the issue gets fixed *but not fully*. PGA continues to increase but at a very slow pace. Something like 0.5mb in 60 minutes for 10 sessions.
- we applied the Apr2020 DBBP to 12.1.0.2 and again the same result - very slow but continuous increase of PGA.
- as per Oracle, this bug is fixed in 12.2.0.1 base release. So tried 18c (12.2.0.2) and 19c (19.8) and for both same result.

We had opened another SR with JDBC team. They suggested trying ojdbc8.jar from 19c (19.3). That *fixed the issue fully*. We tried ojdbc8 with all 4 above mentioned DB server versions (for 11g & 12.1 we did not apply the patch) and the issue was fixed.

So still wondering whether the issue was with RDBMS or with JDBC or somewhere else.

But we are out of the woods.

Thanks to all who responded and tried to help.

Regards

On Sat, 5 Dec 2020 at 00:37, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Did you check if you hit Bug 17871069?
>
> În vin., 4 dec. 2020 la 19:36, Stefan Koehler <contact_at_soocs.de> a scris:
>
>> Hello Purav,
>> „kxs-heap-p“ is just the (sub-)heap. What are the allocated chunks about
>> inside this (sub-)heap?
>>
>> As you mentioned you are running on Solaris, you can use a DTrace script
>> of mine to identify the code path that allocates these memory chunks:
>> http://soocs.de/public/scripts/dtrace_kghal_pga_code
>>
>> Hope this helps.
>>
>> Best Regards
>> Stefan Koehler
>>
>> Independent Oracle performance consultant and researcher
>> Website: http://www.soocs.de
>> Twitter: _at_OracleSK
>>
>> > Purav Chovatia <puravc_at_gmail.com> hat am 04.12.2020 17:01 geschrieben:
>> >
>> >
>> > 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-pis 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
>>
>>
>>

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