I work in a small team. There are just five of us at the 'coalface' for the application, responsible for both development and production. One of the five acts as the 'DBA/Sysadmin' (as well as doing programming and acting team as leader when the actual team leader is on leave and filling in a couple of other roles). And he was on leave on Friday when we got the plaintive email with the subject "The application is broken".
We could log in and out, so it wasn't 'catastrophic'. The application is mostly APEX, so my next step was to check the Apex activity log where I saw "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP". The error was at least 'extreme'.
Yes, a DBA probably would have gone to the alert.log first. I am not a DBA :)
Prompted by that message, I ran a query across V$TEMPSEG_USAGE to find several inactive sessions under APEX_PUBLIC_USER holding hundreds of MBs of temporary LOB segments. With a temp tablespace around 2GB, this level of usage isn't sustainable. The quick solution was just to shoot the sessions.
APEX lies on top of HTTP, a stateless protocol and the session state is actually preserved in tables (check out Scott Wesley's post on viewing Apex session state). It generally isn't a problem if you kill one of the database sessions forming the connection pool and it isn't active.
On further investigation, we have identified one trouble spot, which is an application component that allows users to upload and download files. It seems that recently they have been doing large files (hundreds of MB). When the files are uploaded or download it allocates a temporary LOG segment and it holds onto the segment even after a commit or a DBMS_LOB.FREETEMPORARY or a reset package state.
Those end users aren't getting the same database session each time oo if they do several file transfers, multiple sessions can be hanging onto these hundreds of MB of temporary segments. With around 20 sessions at peak, that was locking up our 2GB temp file.
As a stop-gap, we've introduced a scheduled job every three hours to kill off greedy sessions. It is an ugly solution though, and we'll be discussing options when we're all back at work after the long weekend. These include
- A support note that points to event 60025 to free up the stray LOB segments.
- Tying the file upload/download component to one or two database sessions
- File size limits on uploads and downloads
- Is Apex the appropriate security gate-keeper to those fils
- Can we avoid dealing with the 'entire' file, and just have a few MBs at a time
If you have any other suggestions, feel free to add a comment.
2 comments:
Hi Gary,
You haven't listed the first solution I thought about while reading your post: increasing temp space.
Since 2 GB is tiny compared to today's HDDs capacities, is there a reason why you don't want to let the application grow its temp space? Would there be downsides beside lost HDD free space?
Cheers,
--
Vincent Malgrat
Given we typically have around 20 APEX database sessions, to allow for any and all of those sessions to deal with 500MB files (about our maximum size), then we'd need about a 10GB TEMP space.
Our whole database is around 50 GB (actually about half that is real data) and those files don't actually live in the database.
Considering the volume of data in those files, 10GB isn't big. It just feelds that way for the size of DB.
We'll look at it as an option
Post a Comment