A number of years ago, one of the most visible database-driven systems at a large energy company began to hang.  Rebooting the system (the first silver bullet tried by many a systems professional) did not help.  It also did not matter whether one or a hundred users attempted to log on - the application and database moved at a snail’s pace. 

Nothing had changed at the hardware level, and all server diagnostics signaled that everything was in tiptop shape.  No changes had been made at the database level either – all the database settings that had run what had previously been a fast system, remained constant.  What could have happened to cause such a dramatic shift in performance?

Only when the DBA began to examine what was going on behind the scenes, did the performance gremlin come to light.  It seemed that the application made heavy use of the Oracle job queues. 

For some reason, the application that was used to create new jobs in the queues went haywire and inserted tens of thousands of jobs into the job queue.  The problem was quickly found on the application side, and all the erroneous jobs were deleted. 

Now, however, whenever the job queue system table was referenced (and it was referenced every 10 seconds), the system seemed to grind to a halt.  The DBA discovered that the job queue table contained only three rows in it, but the highwater mark  of the table (the last block Oracle will read when a scan occurs) was thousands of blocks high. 

The delete of all the mistakenly submitted jobs may have removed the actual jobs, but Oracle thought that the system table still contained lots of data because of the highwater mark setting. 

To rectify the situation, the DBA entered a truncate for the system table, which reset the table’s highwater mark .  Once this was done, the database completely returned to normal.  In this case, a storage-related problem for a table had brought the entire database to an absolute standstill. 

While DBAs focus on memory settings and tuning SQL, they oftentimes forget just how dangerous and insidious storage problems can be.  This is not a good mindset because storage headaches can play a major role in wrecking an otherwise well-running database. 

If you're a DBA who's looking for real world Oracle tuning techniques, Oracle scripts, and advice on how to get to the heart of critical Oracle performance problems, then you've come to the right place. Oracle Performance Troubleshooting: With Dictionary Internals SQL & Tuning Scripts was written by one the world's most widely-read DBAs and Oracle internals experts. Robin Schumacher focuses his incredible knowledge of the Oracle data dictionary into a superb book that shows how to quickly troubleshoot and correct Oracle performance problems. 

Plus! The online code depot is available immediately!

http://www.dba-oracle.com/bp/bp_book5_perf.htm