After few queries, a query run faster than before

Question

The first time I ran my query, it took 17 minutes to run. The second time I ran it, performance was unchanged. But when I ran it the next morning, the query run in just a second.

My question here :

1. Why did performance change?

2. How can I measure how long the query will really take to run given that the database caches data?

3. If data is stored in the buffer cache, how do I clear the cache without using db administrator account?

I had try to clone the table and fill it with same data as in old table, but the query still run faster.

Answer 1

Without seeing the query plan in both cases and looking at the execution statistics, it's hard to know.

It is possible that the difference is related to what blocks are in the buffer cache. It seems unlikely to me that this is the sole difference, though. You can read an awful lot of data off disk in 17 minutes and it seems unlikely that you'd be able to process that data in memory in just a second even if it was all cached. And if it was a matter of data being cached, you'd expect that the first run would be slow and subsequent runs would be fast.

If the difference is due to caching, you're not going to be able to clear the buffer cache without privileges that would normally only be granted to a DBA. The buffer cache is a shared resource. Purging it would affect everyone using the database. It also wouldn't affect caching that is being done at layers below the database-- the file system and the disk subsystem often cache recently read data in addition to the database cache. You're generally better off focusing on the amount of logical I/O your query performs and accepting that your runtime performance will vary based on how many of those logical I/O operations become physical I/O requests and how many of those physical I/O requests end up being served by file system or disk subsystem caches rather than requiring true physical reads off disk.

My guess is that it is more likely that you got a different query plan on the day that the query ran for 17 minutes than on the day the query ran for 1 second. That may be because statistics on one or more objects changed overnight. It may be because of something like bind variable peeking that causes Oracle to generate a query plan based on the first set of bind variable values that are used which may be reused subsequently by other executions of the query that would benefit from a different plan because they are much more (or less) selective. It may be something more complicated having to do with various plan stability and plan evolution options. If you are getting different plans, depending on the specific Oracle version, you generally have a number of ways to either help the optimizer or enforce some sort of plan stability but many of those will be things that you'll want to talk through with your DBA.