The Cost of Mediocre

Over the years I've written tons of scripts to monitor all sorts of things on the database.  Some of those scripts turned out to be helpful on an ongoing basis and some ended up dying on the vine when new features of Oracle came out.

One of my scripts looks at the top processes in a database from the CPU's perspective.  In other words, how much time did the process use on the CPU over it's entire lifetime.  About a month ago I identified a process that was using a disproportionate amount of time as compared to everything else.  It was a new process, so I was pretty sure something could be tuned.  I assigned another DBA to trace it and make tuning suggestions.  One particular query that was doing a Full Table Scan was identified as the major time consumer and suggestions were made on how to fix the query.

Over the last few weeks, the developer had been busy.  When I enquired as to the status of the slow query the response was that the job runs in an acceptable time and nothing would be done on it until later in the month.

Flash forward to a couple days ago when the process showed up on my report again.  We had burned about three hours of CPU time just doing Full Table Scans all day long.  I created an index on some fields that I thought were appropriate and a single execution of the query when from 44+ seconds to .04 seconds.  My report the next day showed the process down to about 10 minutes of CPU time.

The kicker: The developer said "Oh, I thought of that a month ago but never implemented it."  Meanwhile, I burned about 84 hours of CPU time.