RE: PGA increasing continuously

From: Clay Jackson (cjackson) <"Clay>
Date: Fri, 4 Dec 2020 23:06:56 +0000
Message-ID: <>

My money’s on something in Java; given the persistent nature (across all the releases) and Oracle Support statement, probably locally developed code. You might try some of the Java instrumentation to get a good look at what’s going on “under the covers”.

There are lots of tutorials out there, and here’s the Oracle class docs.

Clay Jackson
Database Solutions Sales Engineer
[cid:image001.jpg_at_01D6CA4F.1A24E120]<><> office 949-754-1203 mobile 425-802-9603

From: <> On Behalf Of Tim Gorman Sent: Friday, December 4, 2020 2:04 PM
To: Purav Chovatia <> Cc: ORACLE-L <> Subject: Re: PGA increasing continuously

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

With Oracle Support absolving the code of errors, they have assumed responsibility for a memory leak within the RDBMS, JRE, or OS. Hope they track it down!

On 12/4/2020 12:22 PM, Purav Chovatia wrote: I agree. But since I am not a Java developer, so with my limited knowledge I verified and the rest I trust them.

They are closing the RseultSet. They are not closing the statement (it is a prepared statement) and I guess that is right because it is to be executed repeatedly.

Ofcourse, the connection too is not closed except when the application is stopped.

We opened another SR with Oracle JDBC team and they said the code is ok. I dont mind sharing the code here.


On Sat, 5 Dec 2020 at 01:39, Tim Gorman <<>> wrote: It isn't necessary to incur an "exceeded OPEN_CURSORS" error to experience a memory leak due to the memory held by unclosed explicit cursors. I agree that no occurrences of the error make that particular cause less likely, but the point is that...

Bad programming hygiene is the root cause of memory leaks. Any programmer lazy enough not to bother de-allocating dynamically-allocated memory structures (such as cursors or arrays) is more prone to blame leaks on the database platform, JRE, or operating system, which have millions of hours of regression testing against memory leaks. It is certainly possible that there is indeed an undetected memory leak still lurking within the RDBMS, JRE, or OS, but the most likely probability is simply poor hygiene within the programmer's own coding.

Request they provide proof that they have combed their code for memory leaks; this is a far more reasonable request than them requesting from you proof that the RDBMS, JRE, or OS doesn't have memory leaks. Lint filters are available to automate this. Just because application code did not previously fail due to memory leaks does not mean a leak did not exist, but rather that there wasn't enough data to be trigger detection.

On 12/4/2020 8:28 AM, Purav Chovatia wrote: 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 <<>> wrote: Are they closing cursors?

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

Problem persists with DB, 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.


(image/jpeg attachment: image001.jpg)

Received on Sat Dec 05 2020 - 00:06:56 CET

Original text of this message