Execution time of query with high variance
957715Aug 31 2012 — edited Sep 1 2012I have an Oracle Database 11.2 R2 which is set up just for testing purposes, so there is no other activity except mine. Now I have a query which I ran 10 times in a row. Between the executions I always flushed BUFFER_CACHE and SHARED_POOL. The strange thing is, that the execution time of the query is strongly varying from 13 seconds up to 207 seconds. From the 10 executions I have 4 times <25 seconds and 4 times > 120 seconds.
What could be the reason for this? As I've said, there is no other activity on the database and it is always the same query with the same parameters running on the same set of data.
The background to this is that I would like to compare the execution time of exactly the same query with different database settings. So I thought I could just run the query ten times and use the average but I didn't expect such a high variance.
Kind regards
Peter