The central point of my Exadata presentation is that data flows from the disks and into and out of memory caches in a different way on Exadata when using a Smart Scan than in non-Exadata Oracle databases. An interesting consequence of the way data is cached in Smart Scans is that it is cached in flash memory which is slower than regular RAM. Non-Smart Scan table accesses may use the faster block buffer cache in the database server’s RAM. I have on my presentation that it takes about 1 millisecond to read from flash memory versus 10 nanoseconds on regular RAM. I got those numbers from an Exadata class put on by Oracle and from memory statistics published on the internet. But, I couldn’t remember if I had verified the 1 millisecond number for flash cache access experimentally so I did a simple test that came back with about .4 milliseconds to read 32K from the flash cache. This is still much slower than RAM but faster than I thought.
Here are my test scripts, logs, and a spreadsheet calculating the result: zip
I slightly modified the script from my previous post to set autotrace on and timing on.
SQL> select sum(BLOCKS) from test; SUM(BLOCKS) ----------- 1983309312 Elapsed: 00:00:00.73
The query ran in .73 seconds.
NAME VALUE ------------------------------- ---------- physical read total IO requests 1841 cell flash cache read hits 1837
Almost every IO was cached in flash memory so I’ll do the calculation as if all the execution time for the query is accounted for by the flash cache reads.
.73 seconds X 1000 milliseconds per second = 730 ms
730 ms/1837 flash cache reads = .4 milliseconds/flash cache read (rounded up from .397)
So, this isn’t perfect but it is one piece of evidence that the flash cache reads are about .4 milliseconds on the Exadata V2 system this was tested on.
– Bobby
PS. I determined that the flash cache reads were 32 K using the following information:
Statistics ---------------------------------------------------------- 7239 physical reads NAME MB --------------------------- ---------- physical read total bytes 56.5546875 NAME VALUE ------------------------------- ---------- physical read total IO requests 1841
56 megabytes read/1841 physical IOs is about 32K. Also, 7239 physical blocks read/1841 physical IOs is about 4 8K blocks per read = 32 K. So, these tests appear to show that 32K flash cache reads take about .4 milliseconds.
