RE: PGA increasing continuously

From: "Clay Jackson (cjackson)" <Clay.Jackson@xxxxxxxxx>
To: "tim.evdbt@xxxxxxxxx" <tim.evdbt@xxxxxxxxx>, Purav Chovatia <puravc@xxxxxxxxx>
Date: Fri, 4 Dec 2020 23:06:56 +0000

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.

https://docs.oracle.com/javase/7/docs/api/java/lang/instrument/Instrumentation.html

Clay Jackson
Database Solutions Sales Engineer
[cid:image001.jpg@01D6CA4F.1A24E120]<https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.quest.com%2Fsolutions%2Fdatabase-performance-monitoring%2F&data=02%7C01%7CClay.Jackson%40quest.com%7C6c57a6f83b6c490b3b4708d83edb5e24%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637328459211276950&sdata=QId%2B4IzV9yh9ogPm8bagnDkPxeBziolrNZGcYO8%2FrYk%3D&reserved=0>
clay.jackson@xxxxxxxxx<mailto:clay.jackson@xxxxxxxxx>
office  949-754-1203  mobile 425-802-9603

From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Tim Gorman
Sent: Friday, December 4, 2020 2:04 PM
To: Purav Chovatia <puravc@xxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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.

Thanks

On Sat, 5 Dec 2020 at 01:39, Tim Gorman
<tim.evdbt@xxxxxxxxx<mailto:tim.evdbt@xxxxxxxxx>> 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
<tim.evdbt@xxxxxxxxx<mailto:tim.evdbt@xxxxxxxxx>> 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 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.

References:
- PGA increasing continuously
  - From: Purav Chovatia
- Re: PGA increasing continuously
  - From: Tim Gorman
- Re: PGA increasing continuously
  - From: Purav Chovatia
- Re: PGA increasing continuously
  - From: Tim Gorman
- Re: PGA increasing continuously
  - From: Purav Chovatia
- Re: PGA increasing continuously
  - From: Tim Gorman

RE: PGA increasing continuously

Other related posts: