CF Server Choking When Calling STORED PROC

[mostof]

Experts,

I really hope someone can help out with this problem.

I have a ColdFusion 5 server running on a windows 2000 server.

Our application is basically a reporting tool that runs pretty heavy queries.
In the past, queries were run in <cfquery> tags; after moving to <cfstoredproc>
as a more centralized technique, the whole application hangs until a
cfstoredproc returns its results. This happens across all users (10000+) and
not just the same user with two open sessions. This means that if someone is
running a report that returns in 9 minutes, all other users cannot perform any
activity on the entire application until this report is rendered. Of course
when I say "any activity" I mean any database interaction - and since all our
pages access the database the entire applications just FREEZES until the
cfstoredproc is finished.

We have been advised to move from ODBC to Native Data Sources but that didn't
help much. Please note that the database is Oracle 9i and that some of these
queries take well over 5 minutes to return.

[mostof]

anyone?

[thejuggler]

Check your stored procedures transaction handling. Make sure your not locking
the whole table. When doing select statements you should do readcommited
transactions.

Also, make sure connection pooling is enabled in your ODBC settings.