A customer have lately experienced ORA-1652: unable to extend temp segment once or twice a week. When I started to analyze I expected to see a bad plan with huge sorts or large hash joins.
I used a script to check which sessions uses temp and how much, I set t he limit to more than 500M in temp usage.
What we can see is that we have some sessions using several gb of memory, like sessions 312 and 128. We can also see that they run a certain module and program OLServices-1-130868727363, OLServices-1-1308687. We can also see that they have been logged on for a long time, logon time is September 16th.
So what are these sessions executing ?
I used an ash report for the past 30 minutes to check.
ASH_report_1443449792676 (load it in another window and continue to read)
Looking at the ash report we can see that among the top events, we have direct path * temp wait events. They stand for about 20% of the waiting time. If we look at the Top Event P1/P2/P3 Values we can see that the P3 column for the direct path * temp events is block cnt. For this session it seems we just read and write 1 block at the time.
In the section Top SQL with Top Events we have one sql related to the direct path * temp event and it is sql_id 8zqs9k2twz73f. The sql looks like this:
INSERT INTO CIC.LOGDUMP(DESCRIPTION, INPUTFLAG, DUMPDATE, DUMPTIME, URL, SYSEAIHOT, ART, SYSCICLOG, DUMPVALUE, SYSEAIJOB, AREA, SYSID) VALUES (:B1, :B2, :B3, :B4, :B5, NULL, :B6, NULL, :B7, NULL, NULL, NULL) RETURNING ROWID INTO :O0
We know from our first listing that the segment type for our temp usage was lob_data, we can then expect this table to have some kind of lob in its definition.
CREATE TABLE "CIC"."LOGDUMP" ( "SYSLOGDUMP" NUMBER(12,0), "DESCRIPTION" VARCHAR2(120), "INPUTFLAG" NUMBER(3,0) DEFAULT 0, "DUMPDATE" DATE DEFAULT To_date('01-01-0111','MM-DD-YYYY'), "DUMPTIME" NUMBER(10,0) DEFAULT 0, "URL" VARCHAR2(256), "SYSEAIHOT" NUMBER(12,0), "ART" NUMBER(5,0) DEFAULT 0, "SYSCICLOG" NUMBER(12,0), "DUMPVALUE" CLOB, "SYSEAIJOB" NUMBER(12,0), "AREA" VARCHAR2(25), "SYSID" NUMBER(12,0) DEFAULT 0,
We have confirmed that a lob is involved, next question is will clob use temp space ?
I did a search on My Oracle Support and I came across two notes directly,
Bug 5723140 – Temp LOB space not released after commit(Doc ID 5723140.8)
How to Release the Temp LOB Space and Avoid Hitting ORA-1652(Doc ID 802897.1)
Reading the first note we can see:
While using temporary LOBs the temp segment used for this purpose remains active until the session exits. When there is a request to cleanup those temp lobs we do free space from temp tablespace and the same session can reuse it, but the temp segment is not released which means it is not available for use to other sessions. The actual cleanup happens only on session exit. This is intentional. This fix introduces a new event 60025 such that when there are no active temp lobs in the session (ie: both cache temp lob and no-cache temp lobs used are zero) then the temp segment itself will also be freed releasing the space for other sessions to use. Note that this change is disabled by default.
So tempspace is allocated and not released until the sessions expire, sounds almost like a bug. But it is clear that this is intentional. Never the less Oracle has realized it can cause some issues so they fixed this intentional behavior with a new event, 60025.
this implies that the sessions are connected for a “long” time doing many inserts. As we saw in the first picture our sessions has been connected for along time.
What about the next note, seems we have another option besides the event 60025.
1- You can use DBMS_LOB.FREETEMPORARY where the LOB locator that was freed is marked as invalid. DBMS_LOB.FREETEMPORARY frees space from temp tablespace and it is available to that same session, but the temp segment is not released and made available to other sessions. So if the session creates another temp lob after freetemporary, the space is reused by that session. The space is not released until the session exits. That can easily lead to an ORA-1652 error when multiple concurrent sessions are doing a huge LOB operations and not exiting, thus the freed space by DBMS_LOB.FREETEMPORARY is only available within the calling session but not for the other sessions.
This requires some work from developers, but by using the DBMS_LOB.FREETEMPORARY function we can reuse the space allocated within the session.
Then we have option two.
In addition to the above approaches For 10.2.0.4 and above a new event introduced (event 60025) where when set if there are no active temp lobs in the session (ie: both cache temp lob and no-cache temp lobs used are zero) then the temp segment itself will also be freed releasing the space for other sessions to use. Note that this change is disabled by default.
alter system set events '60025 trace name context forever';
In my case I can’t see any evidence of using temporary lobs, I read about it and it should involve usage of DBMS_LOB.CREATETEMPORARY. I have asked the vendor if they use temporary lob segments. Unfortunately the segment type won’t tell us if it is a temporary lob segment or not. But I assume the vendor uses it even if I cannot see it right now. The behavior of our problem has many signs supporting it is a temporary lob.
Long running sessions doing inserts involving temp lob space can over time allocated so much temp space that it will fill the temp tablespace. This is “how it works” but Oracle have implemented an event to change the behavior. I have enabled that event and we will see what result it gives for us. If unsuccessful I’ll guess we need to discuss with the vendor how things works in the application.