Re: testing sql and perf

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 03 May 2004 10:08:35 -0600
Message-id: <40966E83.701AE441@sun.com>

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_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 Mon May 03 2004 - 11:07:08 CDT