RE: Poor performing Oracle Query
I'm not sure. As in the classic game of "phone," I probably have this wrong. But as I understood the explanation, the DBA thought the query was being restarted when new data was added to the underlying tables. It seemed odd to me as well, but I'm just the web programmer. Any the plan currently is to create views of this data that are updated nightly during a slow period. Then use these views to create the summation data. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Saturday, July 09, 2005 1:44 AM ....To: CF-Talk ....Subject: Re: Poor performing Oracle Query 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:211546 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Poor performing Oracle Query
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 waay 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
Re: Poor performing Oracle Query
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 > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211507 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Poor performing Oracle Query
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 ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211488 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
Re: Poor performing Oracle Query
Russ wrote: > There must be some way to tell oracle you want to read 'dirty' data. The concept of 'dirty' data doesn't apply to a MVCC database in the same way as it applies to a database that is internally based on locking. > Perhaps you need something like ? Read uncommitted is automatically upgraded to read committed in a MVCC database. I recommend chapter 5 of 'Concurrency Control and Recovery in Database Systems': http://research.microsoft.com/pubs/ccontrol/ Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211487 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Poor performing Oracle Query
There must be some way to tell oracle you want to read 'dirty' data. Are you using a cftransaction? Perhaps you need something like ? Russ -Original Message- From: Eddie Awad [mailto:[EMAIL PROTECTED] Sent: Friday, July 08, 2005 7:41 PM To: CF-Talk Subject: Re: Poor performing Oracle Query > Does oracle have something similar to "with (nolock)" that SQL server has? > This would just run the query and not wait if it can get a lock on all the > tables.. In Oracle, If you're just "select"ing rows from tables, there should not be any locks issued on the rows unless you explicitly say "for update of" followed by a list of columns. -- Eddie Awad. http://awads.net/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211477 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
Re: Poor performing Oracle Query
> Does oracle have something similar to "with (nolock)" that SQL server has? > This would just run the query and not wait if it can get a lock on all the > tables.. In Oracle, If you're just "select"ing rows from tables, there should not be any locks issued on the rows unless you explicitly say "for update of" followed by a list of columns. -- Eddie Awad. http://awads.net/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211472 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
RE: Poor performing Oracle Query
Does oracle have something similar to "with (nolock)" that SQL server has? This would just run the query and not wait if it can get a lock on all the tables.. -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, July 08, 2005 7:15 PM To: CF-Talk Subject: RE: Poor performing Oracle Query 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. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning .-Original Message- .From: Jochem van Dieten [mailto:[EMAIL PROTECTED] .Sent: Friday, July 08, 2005 1:39 PM .To: CF-Talk .....Subject: Re: Poor performing Oracle Query . .Ian Skinner wrote: .> What would be a good checklist to try and figure out why a query built .by one of our Oracle DBAs runs in about 30 seconds when he runs it .through the SQL+ tool in the Oracle development suite? But when that .same query is placed in a tag set, it takes 18 minutes to run! . .How many records does it return? . .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:211470 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Poor performing Oracle Query
> 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. Sounds like some SQL tuning may be required here! Ya all have a good weekend ... -- Eddie Awad. http://awads.net/ ~| 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:211469 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
RE: Poor performing Oracle Query
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. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday, July 08, 2005 1:39 PM To: CF-Talk Subject: Re: Poor performing Oracle Query Ian Skinner wrote: > What would be a good checklist to try and figure out why a query built by one of our Oracle DBAs runs in about 30 seconds when he runs it through the SQL+ tool in the Oracle development suite? But when that same query is placed in a tag set, it takes 18 minutes to run! How many records does it return? Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211464 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Poor performing Oracle Query
We're working on the SP version to see if that help. No it does not take nearly as long to run in SQLPlus, about 20 seconds, versus 18 minutes when run from a CF page. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning -Original Message- From: Aaron Rouse [mailto:[EMAIL PROTECTED] Sent: Friday, July 08, 2005 2:50 PM To: CF-Talk ....Subject: Re: Poor performing Oracle Query Does it run slow from SQLPlus when executed from the web server itself? For perhaps a "quick" fix I'd probably try throwing it into an SP and calling that to see if the delay in executing goes away. On 7/8/05, Ian Skinner <[EMAIL PROTECTED]> wrote: > > I'm told about 10, but those ten are summations of a lot of data from some > very large (100,000's of records) tables. > > > -- > Ian Skinner > Web Programmer > BloodSource > www.BloodSource.org <http://www.BloodSource.org> > Sacramento, CA > > "C code. C code run. Run code run. Please!" > - Cynthia Dunning > > -Original Message- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] ....> Sent: Friday, July 08, 2005 1:39 PM > To: CF-Talk > Subject: Re: Poor performing Oracle Query > > Ian Skinner wrote: > > What would be a good checklist to try and figure out why a query > built > by one of our Oracle DBAs runs in about 30 seconds when he runs it > through the SQL+ tool in the Oracle development suite? But when that > same query is placed in a tag set, it takes 18 minutes to > run! > > How many records does it return? > > Jochem > > > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211462 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Poor performing Oracle Query
Does it run slow from SQLPlus when executed from the web server itself? For perhaps a "quick" fix I'd probably try throwing it into an SP and calling that to see if the delay in executing goes away. On 7/8/05, Ian Skinner <[EMAIL PROTECTED]> wrote: > > I'm told about 10, but those ten are summations of a lot of data from some > very large (100,000's of records) tables. > > > -- > Ian Skinner > Web Programmer > BloodSource > www.BloodSource.org <http://www.BloodSource.org> > Sacramento, CA > > "C code. C code run. Run code run. Please!" > - Cynthia Dunning > > -Original Message- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Friday, July 08, 2005 1:39 PM > To: CF-Talk > Subject: Re: Poor performing Oracle Query > > Ian Skinner wrote: > > What would be a good checklist to try and figure out why a query > built > by one of our Oracle DBAs runs in about 30 seconds when he runs it > through the SQL+ tool in the Oracle development suite? But when that > same query is placed in a tag set, it takes 18 minutes to > run! > > How many records does it return? > > Jochem > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211461 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Poor performing Oracle Query
I'm told about 10, but those ten are summations of a lot of data from some very large (100,000's of records) tables. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday, July 08, 2005 1:39 PM To: CF-Talk ....Subject: Re: Poor performing Oracle Query Ian Skinner wrote: > What would be a good checklist to try and figure out why a query built by one of our Oracle DBAs runs in about 30 seconds when he runs it through the SQL+ tool in the Oracle development suite? But when that same query is placed in a tag set, it takes 18 minutes to run! How many records does it return? 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:211460 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
Re: Poor performing Oracle Query
Ian Skinner wrote: > What would be a good checklist to try and figure out why a query built by one > of our Oracle DBAs runs in about 30 seconds when he runs it through the SQL+ > tool in the Oracle development suite? But when that same query is placed in > a tag set, it takes 18 minutes to run! How many records does it return? Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211454 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
Poor performing Oracle Query
What would be a good checklist to try and figure out why a query built by one of our Oracle DBAs runs in about 30 seconds when he runs it through the SQL+ tool in the Oracle development suite? But when that same query is placed in a tag set, it takes 18 minutes to run! -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| 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:211452 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
