A large NHS hospital had been successfully deploying an Oracle database to store patient data for 5 years. However, over the space of a few days, system performance dropped dramatically. Hospital staff were suddenly having to work overtime to keep the throughput of the laboratory at an acceptable level.

Every corporate database evolves as it grows in volume. The performance choices which are appropriate when a database is commissioned do not necessarily hold throughout its life. This is often evident in a gradual loss in database performance over time. On some occasions the loss in performance is much more dramatic. In this case, the sudden performance loss was turning subsecond tasks into coffee break tasks.

By using our performance analysis tools, Foremost quickly identified two problems. The first was a batch report which ran at 2am each day. As data volume increased, the runtime or this report had started to impinge on the working day. If the batch job continued past 9am, it started a log jam slowing down all other processes. The second problem was more subtle; a query had grown from taking 0.1 seconds to 1 second (over a period of 7 years). This query was called thousands of times within the working day. At one second per call, no call was finishing before the next started. By the end of the day, the backlog was snarling up the system.

By appropriate indexing of the table, and rewriting the batch process system, performance was enhanced beyond the original level.

• Staff overtime was cut, with significant cost savings
• Mission-critical information (patient laboratory results) were delivered more quickly
• Fewer coffee breaks for users (perhaps a mixed blessing)

A coffee break task is one which is started just before a user goes for a coffee break. Therefore, regardless of the time the task actually takes, 15 minutes elapse.