Temporary Tables

Temporary tables in Oracle are a very interesting thing. They are used all over in our users reports and scripts but not a lot of thought is given to their limitations or benefits. We ran into one such limitation recently when we were seeing an excessively high load on our database. When I say excessive I mean almost 5 times our highest previous peak! The problem affected an application that has been around for several years (like almost all of our applications). This particular application is centered around getting Continuing Education students information about available courses. The person who originally developed the application no longer works for the university, but their impact is still evident in many of our systems. This particular application does not evoke fond memories for anyone on my team as they have all seen the mess of code and hacks that is, SPIRAL.

Back to the problem at hand. Previously the SPIRAL application was using a table to store temporary session information pertaining to a users query. This temporary table was being inserted to and deleted from multiple times per page request. This by itself is perfectly fine, except for the fact that owa_util does not create new sessions for every new web request.

This is the point that we got in to trouble. The original developer was unaware that temporary tables in Oracle do not implement locking or allow indexing. The problem arose when more than one user would view the web page, thus causing multiple insertions and deletions on the temporary table. This caused a high amount of concurrency as the database was trying to insert rows in to the table while also deleting them, all without the ability to lock the table to complete any of the transactions. This meant that for periods of hours a user would make a web request, there would be multiple actions queued up, and none of them would get completed as the temporary table was left spinning its wheels.

The fix came pretty easily once we understood the problem. We changed the table from a temporary table to a normal table, as well as added a unique identifier to each request so that each session could be dealt with separately. Making the table a normal table helped with the locking problem as it was now allowed to lock the table on inserts and deletes, as well as be indexed by the unique identifier.

The two things I learned from this problem were: do not use temporary tables for concurrent actions and if code is deemed by everyone on your team as crazy and half voodoo then it is probably in need of a close inspection.