Home » RDBMS Server » Performance Tuning » Testing performance tweaks
Testing performance tweaks [message #216022] Wed, 24 January 2007 12:52 Go to next message
sournote1
Messages: 3
Registered: January 2007
Junior Member
I am new at this so bear with me. I have a slow query I would like to speed up, and have been reading up on performance and query tuning and have several ideas to try out. But when I try them, it is hard to measure their effect, since the query times vary so widely.

What I would like to do is run the original, then run a tweaked version, and compare the execution times. I'd really like to run them alternately for five or ten cycles, to control for varying DB load. But caching causes all test runs after the first to be dramatically faster. I'm having to wait 24 hours to get my next meaningful execution time. And when I do that with the original untweaked query, the results vary by a factor of two or three from one repetition to the next, presumably because of varying load. If a tweak improved performance by 50% I might never know it because the repeatability of the test is so poor.

Is there some other way to compare the performance of the original and tweaked query? I have looked at the execution plans, but I have read that the values there are relative and not absolute. Are they valid for comparisons between queries?

What gets cached? The indexes, the execution plan, the data?

Is there a way to clear the cache?

Is there a way to specify that the query be run without using the cache?

Any advice would be appreciated.

This is Oracle 9i Enterprise, Release 9.2.0.6.0

Thanks in advance.

Report message to a moderator

Re: Testing performance tweaks [message #216024 is a reply to message #216022] Wed, 24 January 2007 12:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can use 10046 event based tracing.
But to start with, Runstats would be handy
http://asktom.oracle.com/tkyte/runstats.html

Report message to a moderator

Re: Testing performance tweaks [message #216056 is a reply to message #216024] Wed, 24 January 2007 20:10 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think the following helps:
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;


You will need ALTER SYSTEM privs though, or a patient DBA who is prepared to do it for you before each test run.
Ross Leishman

P.S. Did Mahesh drop the ball here? It was from him that I first learned the above.... Confused

Report message to a moderator

Re: Testing performance tweaks [message #216059 is a reply to message #216056] Wed, 24 January 2007 20:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Is there some other way to compare the performance of the original and tweaked query?
I responded partially, only to above question.
I am getting old...

Report message to a moderator

Re: Testing performance tweaks [message #216345 is a reply to message #216022] Fri, 26 January 2007 13:25 Go to previous messageGo to next message
sournote1
Messages: 3
Registered: January 2007
Junior Member
Thanks for your help, everyone.

It turns out I do have ALTER SYSTEM privileges (Don't tell anyone or they'll revoke it), but clearing the BUFFER_CACHE and the SHARED_POOL did not clear the query caching mechanism. They still run about 50 times faster the second time than the first.

I'm still working on the privileges for runstats.sql.

Does anyone have any more thoughts in the meantime?

Thanks again.

Report message to a moderator

Re: Testing performance tweaks [message #216367 is a reply to message #216345] Fri, 26 January 2007 16:11 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Since you are on 9i, how did you actually clear your BUFFER CACHE? Mahesh demonstrates a method to clear the BUFFER CACHE for 9i databases in this thread.

Report message to a moderator

Re: Testing performance tweaks [message #216368 is a reply to message #216022] Fri, 26 January 2007 16:16 Go to previous messageGo to next message
sournote1
Messages: 3
Registered: January 2007
Junior Member
I went poking around the web and found discussion of the same undocumented equivalent in 9i:

alter session set events = 'immediate trace name flush_cache';

Thanks for the tip, I'll read the link more thoroughly and see if I missed something.

Report message to a moderator

Re: Testing performance tweaks [message #216407 is a reply to message #216368] Sat, 27 January 2007 04:01 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Also consider you could have a hardware-level cache that Oracle does not know about.

Report message to a moderator

Previous Topic: Adding ORDER BY increases parse time dramatically
Next Topic: DB Performace with different size drives.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 17 21:53:21 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.