Hello, I've never heard of oracle "restarting" a query because the underlying data had changed. Oracle uses a multi-version view of data to show you results consistent with what the database looked like at the start of the query (at the very least - it can also do "consistent with the start of the transaction"): if a row has been changed since the query started then oracle will read the "old" value from rollback and use it in the query. If you are executing a long running select query and making lots of modifications to the underlying database at the same time, oracle will eventually need to read info from a rollback segment that has been flushed to disk and you will get the dreaded ORA 01555 error (snapshot too old)... but Oracle won't restart the query. Of course, you could have written your query in some kind of try-catch block (in either cf or pl/sql) that just repeats whenver this error comes up, but i assume you would have mentioned it.
Step 0 (if possible): export your live schema and import it to your testing environment. You won't be able to recreate usage but you will have "realistic" data. The very first thing to do in investigating why this query takes so much more time to execute in production than in development is to make sure that you are comparing apples to apples: ie run the query using coldfusion against both databases, and run the query using sql*plus against both databases and check the results. In the past, i've seen queries that ran much slower in cf than in TOAD because cf needed all the results before it could return whereas TOAD only needed the first rows. If your query returns a lot of records this is something to check earlier rather than later. If you see that the same query run from the same client still takes a huge amount more time on the production database then the next step is to do an EXPLAIN PLAN on both databases and make sure that the results are identical. If they aren't, then ask yourself why not: different volumes of data, lack of table stats in one of the databases, user resource limitation, etc. Finally, if the plans are the same and the query is still taking waaaaaay more time in production, then you need to turn on tracing and use the tkprof utility to see exactly what is being done. This will slow down your live db even more but will give you the definitive answer as to what is happening in the db. /t >-----Original Message----- >Subject: Poor performing Oracle Query >From: Dustin Tinney <[EMAIL PROTECTED]> >Date: Sun, 10 Jul 2005 11:27:56 -0400 >Thread: >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages >&threadid=41091&forumid=4#211507 > >Does your query select any clob or blob data? > >On 7/9/05, Jochem van Dieten <[EMAIL PROTECTED]> wrote: >> Ian Skinner wrote: >> > Well we have more information on the problem. The query >that was running fairly well at 30 seconds or so was being run >against a validation database where the data is static. >> > >> > The query that was performing poorly at 18 minutes was >being run against the production server where the data is very >active. Apparently every time data was being added to the >main tables, this query would be started over. So depending >on the traffic sometime it performs relatively well other >times it performs very poorly. >> >> That sounds rather vague. Does the DBA mean the database was >> running out of rollback segments and instead of just returning an >> error was somehow retrying the query? >> >> Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211528 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
