| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: testing sql and perf
I also have a similar issue with timing differences of queries related = to buffer cache. In my opinion Daniel made some good comments. 10g's = new flush buffer cache command is pretty cool and I like his work around = for earlier versions, although I agree with his concerns of other = caches. Keep in mind in most environments you have multiple layers of = data caching. Not only do you have Oracle's buffer cache to worry about = the disks and file system are generally trying to assist in this ares. = Unless you are certain that you have no disk cache and that you are = bypassing the file system cache, with raw devices, Quick I/O, etc..., = then the flush buffer cache will not help, in fact bouncing the database = doesn't help either. In some cases you would have to bounce the = database, machine and storage array. =20
I have found that the best way in our environment to work around this is = to use large sets of random search criteria. If I am testing a = particular query I will extract sample data from the database for input = criteria, generally from a separate database using a sample query. I = would then use this data as search criteria and use calculated averages = to determine the affect of changing my sql. For example, I would run = the query with my first 100 search criteria, calculate an average, = modify the query and then run with the next 100 search criteria and = calculate an average. Then comparing the averages you can get a good = feel for the progress you have made. The random sample data generally = provides a good test set and since you are not using the same search = criteria you do not have to be so concerned with caching. My queries = are generally a few seconds, in your case you might want to run fewer = than 100 unless you have plenty of spare time on your hands.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Daniel Fink
Sent: Monday, May 03, 2004 12:09 PM
To: oracle-l_at_freelists.org
Subject: Re: testing sql and perf
David,
By output differences, do you mean time and stats related to logical i/o and physical i/o? Or do you mean execution plans, row counts, etc.
In the first case, you can flush the buffer cache if you want. By flushing the cache between executions, your stats will be close to worst-case, but not quite as the blocks are probably going to be read from the storage cache and not disk on subsequent operations. If the sql is to be reexecuted by the application/users, the blocks needed may be still in the database buffer cache.
In 10g, you can flush the whole buffer cache with the command 'alter system flush buffer_cache'. If you are on an earlier release, I *think* there is an event or system call you can make to flush the buffer cache. However, I have used a different method to flush the cache of the blocks of interest. If you alter a tablespace offline, all the blocks relating to objects contained in the tablespace in the buffer cache are marked. If you then online the tablespace, the blocks will all be reread from disk, even if they are currently in the cache. The advantage of this approach is that you flush a subset of the cache and not the whole thing.
If your execution plans, row counts are changing, you need to explore it further (and see if others more wise and experienced on the list can assist, 'cuz I'd be shrugging my shoulders).
Daniel
David Green wrote:
>=20
> How do you all test sql and code adjustment changes taking into =
account
> caching of data from previous code related runs as well as other users =
in
> database having data in cache?
>=20
> If I run a query the first time I get tkproff output vastly different =
from
> subsequent runs. I can't see it being practical to restart the =
database
> between each run.
>=20
> - David
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed May 05 2004 - 07:29:08 CDT
 |
 |