With Exadata Flash Cache you can easily expand your In-Memory Column Store into Flash and you can run analytical queries over 10s of Terabytes of data.
In his blog post Andy Rivenes (@TheInMemoryGuy) described in detail the Columnar Formats in Exadata Flash Cache.
One question that often arises is, how can I detect that the Exadata Flash Cache and the Columnar Format is actually being used in my analytical queries?
As described in the blog post above, the only thing you have to do is enable Oracle Database In-Memory on the database servers by setting INMEMORY_SIZE greater than 100 MB. This also enables the Columnar Cache on Exadata Flash Cache automatically.
In the following example we query tables which are not yet populated into the In-Memory Column Store on the database servers.
Just run this query on v$sesstat and v$statname to check that columnar cache is being used in the following statistics:
The following statistics are relevant to determine if Columnar Format is being used.
“Cell physical IO bytes saved by columnar cache” shows that only the relevant columns have been sent.
“Cellmemory IM scan CUs process for capacity” is the default and indicate that only a small portion of the Columns Units (CUs) have been scanned.
It is important to know that segments have to be scanned at least twice an hour for them to be eligible for Columnar Cache. In this case the same rule as for AUTOKEEP pool is being applied.
Note that this is also applicable for partitions, sub partitions and materialized views. Also remember that with the release of Oracle Exadata System Software 18c (18.1.0) the limitation of only supporting HCC formatted data for the Oracle Database In-Memory columnar format was removed and now uncompressed tables and OLTP compressed tables can also benefit from the Oracle Database In-Memory columnar format.
You can also use statistics to verify segments are fully loaded.
For more details see Roger Macnicol’s blog post here.
In a later blog post we will talk about Smart Scan deep dive and invalidation/repopulation of In-Memory store contents.
Original publish date: August 13, 2018