Oracle 11g - query appears to cache even with NOCACHE hint

Question

I'm doing some database benchmarking in Python using the cx_Oracle module. To benchmark results, I'm running 150 unique queries and timing the execution of each one. I'm running something like this:

c = connection.cursor()
starttime = time.time()
c.execute('SELECT /*+ NOCACHE */ COUNT (*) AS ROWCOUNT FROM (' + sql + ')')
endtime = time.time()
runtime = endtime - starttime

Each query is passed in through the variable sql, and they vary significantly in length, runtime, and the tables they access. That being said, all queries exhibit the following behavior:

1st run: very slow (relatively)

2nd run: significantly faster (takes anywhere from 1/2 - 1/5 the time)

3rd run: marginally faster than 2nd run

All subsequent runs >= 4: approximately equal to 3rd run

I need the cache disabled to get accurate results, but the first few runs are really throwing off my data; it's as if NOCACHE isn't working at all... what's going on here?

Edit: Allan answered my question, but for anyone who might be interested, I did a little more research and came across these two pages which were also helpful:

How to clear all cached items in Oracle

http://www.dba-oracle.com/t_flush_buffer_cache.htm

Ophiuchus

You want the live system to do caching, because that improves performance. So shouldn't you arrange your benchmark so that the first two runs for each query are discarded (a.k.a. "burn-in"). What kind of queries do you have in sql? The way you're currently passing in the query doesn't allow using bind variables, i.e. if the queries contain input parameters, these will be embedded into the query literal, which will cause a hard parse to precede each execution, which I assume is causing the lag in #1. – Voiceless

I'm a little confused. Since I am benchmarking with the assumption that the query will be hard parsed in production, I want to keep the slower runs. In fact, I'm actually looking to make them all consistent with the first runs. This situation calls for queries that aren't cached, because the testing is taking place across multiple databases with different configurations, and the users on the system will only run a query once, which renders cached times invalid for benchmarking. – Ophiuchus

Okay, I didn't know you're testing queries that will only be run once in production. If the queries are really heavy, then the hard-parsing is probably not the biggest factor here, but instead the query execution is just slow. Could you use an Oracle utility like tkprof for profiling? – Voiceless

I might look into that on Monday. I'm not the DBA, I'm just the guy writing the benchmark scripts for him. I'm pretty new to this stuff, so I still have much to learn! – Ophiuchus

@Ophiuchus It's important to note that Oracle does not cache the query results, it caches the most frequently used table and index blocks. This means that different queries can take advantage of the same cache. You very likely do want to exclude the "burn-in" time with any OLTP testing. Usually an alter system flush buffer_cache; would only help make realistic tests in a data warehouse. Unfortunately, this makes your testing much more difficult - without perfectly matching queries, data, and environments it's difficult to replicate cache behavior. – Sundae

Thanks for your help, Jon Heller and Mick Mnemonic. My coworker and I weighed the options and determined that it's better not to do a burn-in for our use case, but I really appreciate the suggestions and their respective explanations. – Ophiuchus

Answer 1

From the documentation:

The NOCACHE hint specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.

It seems from this that the nocache hint simply doesn't do what you expect it to.

You can clear the shared cache by running ALTER SYSTEM FLUSH SHARED_POOL and the buffer cache by running ALTER SYSTEM FLUSH BUFFER_CACHE. You'll need to do this between each query to prevent the cache from being used.

Chippewa