FusionReactor is a great tool diagnose such problems. Start digging which page that takes time to process; and then drill down to the queries. See which piece of code that caused CF Server CPU utilization spikes.
Fusion reactor is cool but It didnt really show me anything new I couldnt see in the CF administrator itself.
Problem still persists, programmers are getting fed-up because their report builders are forever hanging and accountants the same with certain reports taking up to 10 Minutes to complete.
We are now looking at the possibility that there is a problem on one of our Core switches but I personally feel this is unlikely because none of the other Servers show any sign of this.
First, run the query on the database server. How long does it take?
If the query completed in a reasonable time, then run the client/page directly on the Web server. How long does it take?
Network issue could be the problem. It is unlikely, but it happened to me.
It's a pretty open-ended question, because a number of things can be causing slow performance. You mention needing to look at ODBC connection performance - I found that using ODBC drivers for iSeries was very slow in the past. You might want to look at using the JDBC driver in the IBM JTOpen Toolkit - it made a big difference for us when we were querying iSeries in the past.
When you refer to "reports" in this thread's title, do you mean reports from ColdFusion Report Builder? Or to just ColdFusion .cfm pages? How complex are these reports? How much information is displayed, especially in tabular form? Just rendering huge HTML tables (as in thousands of rows of data) will often cause browsers to become temporarily unresponsive or hang. If you provide more details, we might better be able to target where the "pain point" really is.