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.    

While watching the NBA finals I am constantly being bombarded by statistics related to past finals and the amount of wins that Phil Jackson has or the number of times the Lakers have gone on to win the Championship after being ahead 2-0. While these are all interesting statistics I find myself wanting more. I would love it if I could get a database dump of all their data so I can put together my own queries. Better yet, give me a webapp that is loaded up with all of the data they are quoting and allow me to make my own queries and come up with my own statistics.

For example, allow me to enter a query such as, "Show me all the players with 15 assists in an NBA finals game in the last 9 years". Then I can share it with friends through whatever social network device I choose. Also, perhaps there could be voting on interesting user generated statistics that the broadcasters could then share on the air.

This can work in the favor of many sports broadcasts. There are already have all the data in their database, and they dole it out in little bits throughout their programs. Broadcasters can engage their users more if they had a way to interact and discover new and interesting statistics while watching their favorite team play.

Rather than idly watching a team or individual playing a sport, whether it is Tiger Woods or the Lakers, allow me to go more in depth. The first station or sport that allows me to do this will instantly grab my attention and viewership.