I was working on a problem with one of our reports. The customer said it took too long to get results in the report, something like 5 minutes or more. Of course they want to see their results in the blink of an eye, but seeing the kind of calculation and querying that has to be done, that's not possible.

But, as always, we think there is room for performance improvement so I went off and took SQLMonitor (A tool bundled with Toad) to see what queries took up the most time and how much time that was. The ones with the biggest amount of time spent would be our best shot to improve.

The first query run, used up 0 seconds according to SQLMonitor, so that was apparently not one to spend lots of time on. Or was it. I did see that when this query completed, it took quite some time before the next query was fired. That made me wonder if there was a lot going on in the front-end, but a quick scan of that code showed no bottlenecks I could easily identify. When I ran the same query (with the bind variables bound) in PL/SQL Developer, it told me that it took almost 3 minutes to complete.

How come this is so different from the monitor application? I don't know. But I have learned that to see how much time a query really takes, you have to run it in a different environment and see how much time it takes there.