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

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.