Re: testing sql and perf
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 03 May 2004 10:08:35 -0600
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:
>
> 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?
>
> 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.
>
> - David
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
