Coldfusion Query Delay Problem

["Nick B" <webforumsuser@xxxxxxxxxxxxxx>]

Folks we are having some intermittent problems with our application web servers.

 We are using coldfusion to serve data from Oracle databases. SELECT statements 
only, no updating of the database is done. The coldfusion application server 
and the oracle database server are seperate servers,

 The problem we are having is as follows.

 When the user opens the app for the 1st time a query is run to work out what 
access level they have. 95% of the time the query
 is instant. We have been logging this using getticktick() often the time taken 
is 0ms we also see 16ms and 31ms. The query returns
 1 row with 1 field in it to size of data returned is not an issue.

 Occasionally we get this query reportedly taking between 5 and 120 seconds 
using gettickcount() either side of it.

 We turned on some logging at the Oracle end and this reported that EVERY time 
a query of this type it is taking absolutely no time all.

 Our next port of call was the network. We have sniffed packets between the 
coldfusion server and the oracle server.

 We managed to trap one of the requests where the response time is slow and the 
scenario was as follows :

 1. Coldfusion -> Oracle. Hello i've got some work for you to do. Completed in 
millisenconds
 2. Oracle -> Coldfusion. Acknowledged, Yes what is it?  Completed in 
millseconds.
 3. Coldfusion -> Oracle  Acknowledged......... delay of 17 seconds (delay 
times vary)..... it's this query
 4. Oracle -> Coldfusion. Here are your results. completed in milliseconds
 5. Coldufsion -> Oracle. Acknlowledged. completed in millseconds.

 So it appears that internally within coldfusion a delay is occuring as the 
database and the network have been eliminated.

 The delay time is never the same and can range from a couple of seconds to 
over a minute. The times of day that these delays occur at is not uniform. 

 The server is busiest between 0800 and 1700. But this server is NOT under 
heavy load at all, it is however query intensive. It is up 24 hours a day and 
there is a constant user base.

 We have tuned all our queries and cached them as best we can. However i'm not 
sure how much this helps as we get delays on queries we know are performant.

 We are currently upgrading one of our servers to CF8 using oracle thin jdbc 
drivers rather than the odbc to see if this makes a difference.

 If anyone has previous experience of this or knows of anything settings we 
need to tweak at a java level that would be great.

 Spec of what we are running is as below :

 Windows 2003 Server
 Coldfusion MX 7.0.2 Standard
 Oracle 10g ODBC Drivers

 Any help gratefully received as the users are starting to get fed up of having 
these delays for no apparent reason.