It was an internal framework and though it was supposed to make call to
DBMS_LOB.FREETEMPORARY it never really did and so the lobs were never
released in session.
They fixed it by making sure that lobs are getting closed.
On Wed, 29 Sep 2021, 11:28 Mohamed Houri, <mohamed.houri@xxxxxxxxx> wrote:
Jacek
*The problem was session lifetime and the keep-alive pings that kept
sessions open forever (they never recycled).*
Yes, that is exactly what is happening here. The *SQL_ID* from the
*gv$tempseg_usage* shown at the TOP of the LOB_DATA segment type was this
famous* keep alive *select:* select 1 from dual.* This is, of course, not
the SQL statement responsible for the TEMP LOB overconsumption but it
represents the *last *SQL statement executed by the JDBC session that has
created the TEMPORARY LOB.
*I've raised an issue with framework and that got fixed.*
What did they do exactly?
Thanks
Mohamed
Le mer. 29 sept. 2021 à 10:19, Jacek Gębal <jgebal@xxxxxxxxx> a écrit :
One thing I've noticed when dealing with LOBS over JDBC was that TEMP
lobs are getting freed when session closes.
In my case we had an issue that LOBs were never freed by the Java
framework used over JDBC but no other project was facing TEMP issues, only
ours.
The problem was session lifetime and the keep-alive pings that kept
sessions open forever (they never recycled).
I've raised an issue with framework and that got fixed.
Are you sure it was ok before and nothing else has changed from Java
side? Especially look at session lifetime and connection pool size.
Maybe it's a pre-existing issue and you were lucky to not hit the TEMP
limit before. That was the problem in my case.
Not sure if it's of any help to you.
Regards
Jacek Gebal
On Wed, 29 Sep 2021, 10:02 Mohamed Houri, <mohamed.houri@xxxxxxxxx>
wrote:
Hello,
We upgraded an Oracle 12cR2 database to 19.8. After a couple of days
working with the new version the application started suffering from
ORA-1652:
unable to extend temp segment by 128 in tablespace TEMP error.
The investigation shows that this high TEMP consumption is due to a
bunch of JDBC sessions from a connection pool creating a TEMPORARY LOB
(LOB_DATA) which is not released, because of the session that created it,
returns back to the pool without being disconnected.
My concern is that, before the 19c migration, using the
*DBMS_LOB.FREETEMPORARY* in the PL/SQL package called by the JDBC
session was enough to free the TEMP.
The Oracle support has assured us that there is no evidence that
DBMS_LOB.FREETEMPORARY has changed to work in 19c as it used to do in 12c.
The
Oracle support suggested using the event 60025.
SQL> alter session set events '60025 trace name context forever'
However, this event will free up the LOB TEMP only if both CACHE_LOB
and NOCACHE_LOB values are equal to zero in GV$TEMPORARY_LOBS which is
not the case, unfortunately once again.
Have you been confronted with this situation?
Thanks
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
