What’s in your Exadata Smart Flash Cache?

Posted in: Technical Track

One of the exclusive Exadata features is the Smart Flash Cache (Oracle White Paper PDF). On a full rack, there is 5 TB of flash cache, which can store a significant amount of data. Quite often it’s several times more than the working set for a given reporting system.

What’s so cool about the Exadata Smart Flash Cache?

This flash cache is quite smart: it can differentiate between full table scans and single I/O reads. There’s been speculation that the cache can even prioritize things like file headers, controlfile headers and index root blocks, but I have no official information on the subject. The granularity is all the way to the object level, where you can define higher priority for caching (KEEP) or not to cache at all (NONE). NOTE: When you use the KEEP option on objects, even full table scans will become cacheable, with reads happening concurrently on flash cache and disk for maximum throughput. To achieve the 51 GB/sec marked scan speed, the objects need to have that option.

Unfortunately, there is no easy way to monitor what’s in that cache. All Oracle has provided is a “list flashcachecontent” command in the cellcli tool, which has no summarization options, and only displays object numbers. For example:

[sql] CellCLI> list flashcachecontent where objectNumber = 130387 detail;
cachedKeepSize: 0
cachedSize: 16384
dbID: 1890751346
dbUniqueName: QADW
hitCount: 14
missCount: 1
objectNumber: 130387
tableSpaceNumber: 5

Thus, why I wrote this handy little tool. It allows you to query the cell flash content on all cells, in a similar manner that you can query the buffer cache (db_cache) contents in v$bh.

The way it works is by using 11g’s new External table feature “preprocessor” and a perl parser script that executes the commands simultaneously on all cell nodes.

Installation is very simple. Considering a standard Exadata installation:

  • On any compute node, create the /tmp/pythian directory and download pythian_cell_cache_extract.pl (link) into it
  • Make it executable:
    chmod +x /tmp/pythian/pythian_cell_cache_extract.pl
  • As the “oracle” user, copy the /tmp/pythian directory to all compute nodes in /home/oracle:

    for n in $(/u01/app/11.2.0/grid/bin/olsnodes) ; do scp -rp /tmp/pythian $n:/home/oracle ; done
  • Ensure there are no prompts and that you can reach every cell:

    perl -ne '/cell="([\d.]+)"/ && system "ssh celladmin\@$1 hostname"' /etc/oracle/cell/network-config/cellip.ora
  • As a DBA user, create an Oracle directory object pointing to that directory:
    [sql]create directory pythian_cc as ‘/home/oracle/pythian’;[/sql]
  • Create the external table:
    [sql] create table pythian_cell_cache_usage (
    cellNode varchar(50),
    cachedKeepSize number,
    cachedSize number,
    dbID number,
    dbUniqueName varchar2(30),
    hitCount number,
    missCount number,
    objectNumber number,
    tableSpaceNumber number
    DEFAULT DIRECTORY pythian_cc
    PREPROCESSOR pythian_cc: ‘pythian_cell_cache_extract.pl’
    LOCATION (‘.’)/*location mandatory, using dummy*/
  • Flash cache information is now available in the pythian_cell_cache_usage table

I’ve provided this sample report query, but feel free to customize:

[sql] select t.name, o.owner, nvl(o.object_name, ‘(‘||max(cc.objectnumber)||’)’) name, o.object_type, round(sum(cachedkeepsize)/1024/1024) keep_mb, round(sum(cachedsize)/1024/1024) tot_mb,
round(ratio_to_report(sum(cachedsize)) over ()*100) "%", sum(hitcount) hits,sum(misscount) misses, round((sum(hitcount)/nullif(sum(hitcount)+sum(misscount),0)*100)) hit_ratio
from pythian_cell_cache_usage cc, v$tablespace t, dba_objects o
where cc.objectnumber = o.data_object_id (+)
and cc.TABLESPACENUMBER = t.ts# (+)
group by t.name, o.owner, o.object_name, o.object_type order by tot_mb desc;

Sample output

DATA1 DW FACT_1 TABLE SUBPARTITION 0 434834 22 112603679 31810169 78
DATA1 DW SALE_1 TABLE SUBPARTITION 0 287343 14 28676301425 2540494574 92
DATA1 DW FACT_2 TABLE SUBPARTITION 0 253586 13 221315469 101387498 69
DATA2 DS TRAN TABLE SUBPARTITION 0 218011 11 183859969 1242401286 13
DATA2 DS BACK1 TABLE SUBPARTITION 0 132479 7 56857485 265896016 18
DATA2 DS PROD TABLE PARTITION 0 114091 6 267917339 504285619 35
DATA1 DW FACT_1_OLD TABLE SUBPARTITION 0 111587 6 600855731 64207493 90
DATA1 DW DIM_1 TABLE PARTITION 0 85746 4 67589302 1919066582 3
NYN_DATA DS BAK_1_PK INDEX 0 44406 2 9003500 1939610 82
NYN_DATA DS LOG1 TABLE SUBPARTITION 0 26926 1 709001 5225315 12
NYN_DATA DS TRAN_ID INDEX SUBPARTITION 0 24260 1 185520035 860841 100
NYN_DATA DS PRODUCT_PK INDEX 0 19030 1 22859063 529640 98
NYN_DATA DS PROCESS_PK INDEX 0 16092 1 3631500 434151 89

What conclusions can we extract from this information, and can we tune the smart flash cache?

  • Determine if high space use objects have low hit ratio – Some workloads have such usage patterns that perhaps cannot benefit as much from the smart flash cache. Imagine a data usage scenario where historical data access is much higher than recent data access. However recent data access is still predominant. Using the data from cell flash cache usage, you will be able to determine that recent partitions have high hit ratio compared to historical ones. An action item from such application behavior could be to make recent partitions in “keep” mode.This query can be handy in that case (PARTITIONED tables only – a bit slow to run): [sql] with cc as ( select dbid, dbuniquename, t.name,objectnumber, sum(cachedkeepsize) cachedkeepsize, sum(cachedsize) cachedsize, sum(hitcount) hitcount, sum(misscount) misscount from pythian_cell_cache_usage c, v$tablespace t where c.TABLESPACENUMBER = t.ts# group by dbid, dbuniquename, t.name, objectnumber),
    o as (select distinct o.data_object_id, o.owner, o.object_name,o.object_type, tsp.table_owner, tsp.table_name, tsp.partition_name, ds.bytes from dba_objects o, dba_tab_subpartitions tsp, dba_segments ds
    where tsp.table_owner = o.owner and tsp.table_name = o.object_name and tsp.subpartition_name = o.SUBOBJECT_NAME and ds.owner = tsp.table_owner and ds.segment_name = tsp.table_name and ds.partition_name = tsp.subpartition_name
    and o.object_name = ‘YOUR_TABLE’ and o.owner = ‘YOUR_OWNER’
    select cc.name, o.owner, o.table_name, o.partition_name, o.object_type,
    round(sum(cachedkeepsize)/1024/1024) keep_mb,round(sum(cachedsize)/1024/1024) tot_mb,
    round(sum(cachedsize)/nullif(sum(o.bytes),0)*100) "%obj C",round(ratio_to_report(sum(cachedsize)) over() * 100) "%", sum(hitcount) hits, round(sum(hitcount)/sum(nullif(cachedsize,0))*1024*1024) hits_p_mb,
    sum(misscount) misses, round((sum(hitcount) / nullif(sum(hitcount) + sum(misscount), 0) * 100)) hit_ratio
    from cc, o
    where cc.objectnumber = o.data_object_id
    group by cc.name, o.owner, o.object_name, o.object_type, o.table_name, o.partition_name
    order by tot_mb desc;
  • Determine if your important objects are getting their fair share of cache space – If you have ETL jobs running at night, and user queries during the day, it’s very likely that the nightly running jobs are polluting the cache with data that will not be accessed again. One example is data in staging tables. The information about flash usage can help determine if this is happening, and you can avoid such tables making it into the flash cache.
  • Determine how much of your “keep” objects you are using – If you are already leveraging the ability to have different cache policies based on importance, then this information will be vital. You will be able to determine if kept objects indeed have high hit ratios (saved IOs).
    The cell_cache_usage table is also the only way to determine how much space your “kept” objects are actually consuming, and how many more you can “keep”.
  • See the cache hit ratio of keep objects – calculate “saved IOs” in comparison to other objects
    Identify high consumer objects and consider compressing them to trade cache space for CPU time. Hit ratios are very relevant for the flash cache, as they are less likely to be artificially inflated, since there’s already a cache layer above that will absorb most abuse. Any “cache hit” in the flash cache is a saved IO. This is not the case with the buffer cache where it’s normal to re-visit the same block multiple times during a single query. An example is a nested loop join with an index. The root index block is accessed many times.

Known issues

  • Some object numbers do not exist in the database. The query still accounts for those, and tries to match the tablespace, but only gives the “max” object_id for further research
  • Multiple databases on the same Exadata machine would require extra joins in the query
  • Hit and miss statistics are totals since last cell startup. To accurately use this information, snapshots needs to be taken and only deltas analyzed

UPDATE 2011/May/06
Please note that the tool has been updated to v1.12. This solves an error related to Oracle’s new security model and a perl safety check.

Post a comment below or on the tool home page and tell us what you think.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

19 Comments. Leave new

Announcing the Flash Cache Query Tool for Oracle Exadata | The Pythian Blog
April 26, 2011 11:01 am

[…] Christo’s release blog post for the Flash Cache Query Tool for Oracle Exadata and bookmark the tool home […]

Clayton Rocha
May 5, 2011 4:29 pm

Hello Christo!

First of all, congrats for this pearl you created. It seems to be very good!

I did all the steps to install it, but I’m trying to execute it for testing but I’m having the error below. Do you know what can be the root cause?

SQL> select t.name, o.owner, nvl(o.object_name, ‘(‘||max(cc.objectnumber)||’)’) name, o.object_type, round(sum(cachedkeepsize)/1024/1024) keep_mb, round(sum(cachedsize)/1024/1024) tot_mb,
2 round(ratio_to_report(sum(cachedsize)) over ()*100) “%”, sum(hitcount) hits,sum(misscount) misses, round((sum(hitcount)/nullif(sum(hitcount)+sum(misscount),0)*100)) hit_ratio
3 from pythian_cell_cache_usage cc, v$tablespace t, dba_objects o
4 where cc.objectnumber = o.data_object_id (+)
5 and cc.TABLESPACENUMBER = t.ts# (+)
6 group by t.name, o.owner, o.object_name, o.object_type order by tot_mb desc;
select t.name, o.owner, nvl(o.object_name, ‘(‘||max(cc.objectnumber)||’)’) name, o.object_type, round(sum(cachedkeepsize)/1024/1024) keep_mb, round(sum(cachedsize)/1024/1024) tot_mb,
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command
/home/oracle/pythian/pythian_cell_cache_extract.pl encountered error “Insecure
$ENV{PATH} while running setgid at
/home/oracle/pythian/pythian_cell_cache_extract.pl line 40, line 1.

Thanks in advance,

Clayton Rocha

Christo Kutrovsky
May 5, 2011 5:08 pm

Hy Clyton,

This appears to be specific to security model. I have a fix ready, just waiting to confirm from another person having the same problem.

Email me directly if you would like to try the new version as well. My last name at pythian.

Christo Kutrovsky
May 6, 2011 2:50 pm


The tool has been updated to v1.12. Please download it again.

Feel free to contact us if you run into problems.

March 19, 2019 5:12 am

where is the tool “pythian_cell_cache_extract.pl” available now

Clayton Rocha
May 10, 2011 9:51 am

Hi Christo,

After downloading the new version, the problem was solved for me.

Thank you very much!

Clayton Rocha

Cristina Rasinar
March 28, 2013 9:10 am

Hello. I have the same issue, I just downloaded the tool, where can I find version 1.12?


Cristina Rasinar
March 29, 2013 3:01 am


I had another problem with the user equivalence between oracle and celladmin, fixed that, and now everything is ok.

Thank you and best regards.


Dear Christo,

With apologies, I see some problems with this approach. will you clarify.

You cannot calculate hit ratio by this technique and assume that this hit ratio will be across all the cells.

for example, if you full rack and DIM_1 object has hit ratio of 3% as show by your tool but this is an aggregated value, which does not show the condition of cell. It may be possible that cell01 hit ratio is 90% for object DIM_1 but on remaining cell that percentage is less the 1%. when you aggregate or use database views as you did above you due to huge variance difference neutralize the 90% hit ratio of cell01. because as whole its hit ratio will be small.

I suggest you seriously look at your approach.

Christo Kutrovsky
May 30, 2011 3:42 pm

Hi Amir,

The sample query I provided average hit ration per object across all cells.
It is intended to assess per object hit ratios.

If you wanted to asses cell level hit ratio, then you can modify the sample query to aggregate on a per cell basis.


query I provided average hit ration per object across all cells.

this what i am trying to convey.

The cells are shared nothing architecture and database is share everything. It is quite possible( as i found out) that an index is being spread on multiple cell, with only one cell which has 90% hit ratio while others 14 cells have hit ratio less the 1%. when you average out across all 15 cells you may get hit ratio of say 3%, indicating you that index is not being used much and you can take it off from flash cache. which is a false conclusion. The one cell oracle exadata has hit ratio in 90% and it may be possible that only that section of index is hot.

The only valid case to say that the index should not be kept in flash cache is, if its ratio is very small across all the 15 cells. which this tool don’t.

I filed my case based on real world experience as i stated above.

Christo Kutrovsky
June 7, 2011 9:44 am

As I said, feel free to do any analysis you see fit.

What I want to see is cache usage and hit ratios at object level, regardless of which cell it came from. And I want to see averages from all cells.
Whether cell got rebooted, and their caches got cleared, is not of concern.

NOTE: If you want to see current hit ratios, you have to take snapshots and do deltas. The Sample query I provided does averages since last stats reset.


Have you tried querying the external table while connected from SQL*Developer using SQL*Net

Christo Kutrovsky
January 11, 2012 6:39 pm

Yes of course, that’s how I use it.

Exadata « Oracle Scratchpad
July 12, 2012 11:44 am

[…] What’s in your smart flash cache […]

Cristina Rasinar
March 29, 2013 4:50 am


It seems to be a problem with the perl script, because it does not show any object with keep option,but in fact the command

cellcli -e list FLASHCACHECONTENT detail

shows always 0 bytes,

but the following command shows the actual values for cachedKeepSize

cellcli -e list flashcachecontent attributes dbUniqueName, objectNumber, cachedKeepSize, cachedSize, hitCount, missCount

Best regards.
Cristina Rasinar.


Hi Christo,

Thanks for the great post, the link to the perl script doesn’t seem to work anymore. Could you share the script in some way?



Does it mean that
Smart Scan will always use the Smart Flash Cache for all I/Os for full table scans. ?
Single block reads can benefit from Smart Flash Cache.?
Multi block reads can benefit from Smart Flash Cache.

March 19, 2019 5:11 am


Can u post the contents of the script “pythian_cell_cache_extract.pl” as its not available anymore


Leave a Reply

Your email address will not be published. Required fields are marked *