Home » SQL & PL/SQL » SQL & PL/SQL » Compare query speed (10.2.0.4.0)
Compare query speed [message #418729] Tue, 18 August 2009 06:35 Go to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
Is there anyway I can see which of 2 queries is faster.
I.e run them from the same session of TOAD and see which one finishes quicker.
I am trying to improve the speed and want to see which changes improve it.
Re: Compare query speed [message #418730 is a reply to message #418729] Tue, 18 August 2009 06:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. If you test which one runs quicker, you find out which one ran quicker in that moment. Which doesn't tell you anything about which one will run quicker tomorrow, since when you run a query multiple times the results will be already cached in RAM.

You should look at the execution plan, and trace a session and analyse the result with TKPROF to see I/O and CPU change with each change.

There is more information in the Performance tuning FAQ category
Re: Compare query speed [message #418734 is a reply to message #418730] Tue, 18 August 2009 07:21 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member

Thanks for that. Is there anyway that two queries can be run at the same time so that we can see relative speed?
Re: Compare query speed [message #418735 is a reply to message #418729] Tue, 18 August 2009 07:26 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'd run them in two different sessions - but that won't really prove anything.
Re: Compare query speed [message #418742 is a reply to message #418735] Tue, 18 August 2009 07:59 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Assuming that they are comparable, they will be accessing a lot of the same data. If they run at the same time, one may benefit from the others reads from disk into the buffer cache.

It is very hard to compare queries on equal terms due to the effect of disk caching (which can be managed by power-cycling the disk devices) and Oracle's buffer cache (which can be managed by getting your DBA to clear the buffer cache).

Even so, this does not reflect "normal" usage because your buffer cache and disk cache will not be empty normally.

Usually there is no point going to these lengths though. Between any two queries there is usually either a huge difference - in which case you don't need an empty cache to identify the better one - or there is a minor difference - in which case it doesn't matter which one you choose.

Just run both queries a few times - one at a time - to get an average. Choose the one with the faster average.

Ross Leishman
Previous Topic: All the Oracle Scripts in Single XML File
Next Topic: uneven rows saved
Goto Forum:
  


Current Time: Tue Dec 06 12:39:36 CST 2016

Total time taken to generate the page: 0.06287 seconds