Slow JDBC time

Might the Oracle client be optimizing the query perhaps?

In any case, run code similar to the following, and share with us the value of the query's execution time. 

<cfquery name="yourQuery" datasource="yourOracleDSN" result="queryResult">
SELECT <fields>
FROM <view> s
WHERE <field value> = 30
ORDER BY <fields>
</cfquery>

<p><cfoutput>The query's execution time is #queryResult.executionTime# milliseconds</cfoutput></p>

There are surprisingly a lot of factors that may explain this (none being, "cf sucks at doing queries"--which, while you may not be suggesting it, some could. To be clear, cf itself should add no overhead to the processing of a query in the db.)

First and foremost, when you run the oracle client, is that Oracle client on the server where cf is running? If not, endeavor to put it there, even if a hassle. That way, you can know whether even a call from the oracle client is slow, if made from the cf server.

If it is, perhaps there's a networking or other configuration issue (on either box or between them, or perhaps even in oracle).

If it works well from an oracle client on the CF server, are you logging in with the same user that the cf dsn is using? If not, you'd get a different query plan.

If using the same user, are you using 100% the identical sql, down to line end characters and spacing? That, too, is how most db query plan compilers create a key on the plan--via a hash of the sql string.

Finally (for now), is the query as generated in cf using cfqueryparam? If so, are you hard-coding the col value in the client? If so, again those are NOT the same query, beyond all I've said above. The db engine would create and cache a compiled plan based on the value of such a queryparam the FIRST time the plan was compiled, which may be a different than would be compiled for another value. (Plans are cleared on db restarts, and certain db ops, or can be cleared manually.) This is one reason why even the same query in cf could sometimes be fast then slow.

Of course, other reasons can make "the same query be sometimes fast and sometimes slow", even from the same "client", like cf) such as conflicts with other running queries, or other processes in the db, or other processes on the db server. I'll assume that when you do your test like above, it's at the same moment that it's slow in cf. In that case, I hope one of the other several ideas above may help.

Pleass do let us know, either way. There are still other possibilities we might want to consider, based on what you'd say.