Re: High usage of temp tablespace by TEMP TEMPORARY LOB_DATA through JDBC connections

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_at_gmail.com> 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_at_gmail.com> 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>

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