Hi Aaron. Eegad! If an SP chokes an oracle server for 15 minutes, then it is HIGH time to go back to the drawing board and rewrite that SP. And not rewrite it slightly different than it currently exists, but to totally find a new approach to solving the problem that this particular SP addressed in the first place. Perhaps the data needs to be redistributed into a different table structure, perhaps the SP itself should be broken up into more discreet SPs that call each other, perhaps incorporating some temporary holding tables in the midst of the process somewhere to speed up joins...anything at all that would constitute a new, fresh approach to the issue.
Having said that, in my own experience with CF and oracle, I have found that at times I got much better overall responses if I shared the load between CF and Oracle. For instance, I COULD write one query that would return my dataset just the way i needed it, but Oracle took too long to execute it. So, I had Oracle return a couple of basic datasets to CF (after having removed a few joins), and then had CF do a query of queries in order to complete the transformation and joins for me. I was able to get total page load time to less than 50% of what it had taken when Oracle was doing all the work. I share that last tidbit to inspire you and your database gnomes (no offense intended :P) to start at a high level overview of this issue and address the real core of the challenge: the query and/or SP itself. Hope this helps. :) >We have a project here where they are using CF5/Oracle and make some SP >calls that "run big queries" They said that when they make one of the SP >calls the whole server will choke and stop responding to requests until the >SP has finished running which takes upwards of 15 minutes. If they take the >query out of the SP and put it into a CFQUERY then it no longer chokes, it >is my understanding the query still takes a long time to run this way or >just through SQLPlus. It has been tried using ODBC and native connection >data sources and with connection pooling enabled. Is this typical for all >resrouces to just get consumed for SPs and not for cfqueries or is there >maybe something I could pass onto them to check? > >-- >Aaron Rouse >http://www.happyhacker.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238634 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
