Eliminate database caching from skewing your performance test results

If you repeat the execution of short, say 1 hour bursts, of performance tests, you run the risk of skewing your performance results as a result of database caching. This can happen, as we often want to repeat a test utilising the same data, for baselining and then comparison benchmark runs. Using the same data in repeated tests may retrieve data from the database internal cache, reducing or even eliminating disk reads, which can give better preceived performance results.

The simplest method would be to turn off database caching during the execution of each performance test to emulate the worst case scenarios. Disk reads are a major time consuming event. Some database engines do not have the option to turn off caching, as in Oracle, but you can simulate this same option by flushing both the buffer and shared pool cache. Flushing the data buffing cache can save you from the other option of bouncing (stopping and restarting) your database instance between each test run.

To allow you to have the authority to do this, rather than get database privileges you dont really want to risk having, ask the DBAs to create a procedure to flush the shared pool and buffer cache. Then ask the DBA to create a user that has execute privileges to enable the flushing of the cache between tests.

The procedure to create is as follows: –

SQL> create or replace procedure flush_pool
as
begin
EXECUTE IMMEDIATE 'alter system flush buffer_cache';
EXECUTE IMMEDIATE 'alter system flush shared_pool';
END FLUSH_POOL;
/

SQL> GRANT EXECUTE ON FLUSH_POOL TO MONITOR_RO;

where MONITOR_RO is your user created with execute privilges to this proc.

Whenever the database pool needs to be flushed, log on as the MONITOR_RO user and issue the following command

SQL> exec sys.flush_pool;