Viewing Exadata Flash Cache Contents

To quote the Exadata documentation:

Exadata Smart Flash Cache intelligently determines the data that is most useful to cache based on data usage, access patterns, and hints from the database that indicate the type of data being accessed. It also avoids caching data that will never be reused or will not fit into the cache.

Every now and then we come across an inquisitive DBA (OK, all the time...) that wants to know things just because. In the above case with Smart Flash Cache, even though the very next statement in the documentation states "caching occurs automatically and requires no user or administrator effort", we still want to know what's in there, which is where I find myself today...

Let's jump on an Exadata Storage Server and look at what's available to us. According to the documentation, there's a flashcachecontent object_type.

[root@xdpmceladm01 ~]# cellcli
CellCLI> help list flashcachecontent
Usage: LIST FLASHCACHECONTENT [filters] [attribute_list] [DETAIL]
Purpose: Displays specified attributes for flash cache entries.

The detail option should tell us what attributes we can look at:

CellCLI> list flashcachecontent detail
---8<---
cachedKeepSize: 0
cachedSize: 13565952
cachedWriteSize: 13565952
clusterName:
columnarCacheSize: 0
columnarKeepSize: 0
dbID: 4154436461
dbUniqueName: TARDIS.CDB$ROOT
hitCount: 0
missCount: 0
objectNumber: 4294967295
tableSpaceNumber: 4
---8<---

So we have our list of attributes, including dbUniqueName, objectNumber and tableSpaceNumber. We'll need to also return the name of the Storage Server on which this flashcachecontent resides, so we get a holistic view of all storage servers in the cluster, and then tie it back to the database object name so we can make sense of the content.

Depending on security setup on the Storage Servers, we could use exacli or dcli. We'll end up with the same results, but today I'm going to use exacli. (read up on how to use ExaCLI here).

On the first storage server, in cellcli, create a role and user with the privileges required to see the details. 

CellCLI> create role ext_admin

CellCLI> grant privilege list on flashcachecontent attributes -
dbid,dbUniqueName,objectNumber,tablespaceNumber,cachedSize,cachedKeepSize, -
cachedWriteSize,columnarCacheSize,columnarKeepSize,hitCount,missCount to ROLE ext_admin

CellCLI> create user ext_admin_user password=*

CellCLI> grant role ext_admin to user ext_admin_user

Repeat for all other storage servers... 

Next, from the database server where you'll be connecting to the database from, hook up exacli with your storage servers:

[oracle@xdpmvm01clu01 ~]$ exacli -l ext_admin_user --cookie-jar \
/home/oracle/.exacli/my.cookie.jar -c xdpmceladm01
No cookies found for ext_admin_user@xdpmceladm01.
Password: *********
EXA-30016: This connection is not secure. You have asked ExaCLI to connect to cell xdpmceladm01 securely.
The identity of xdpmceladm01 cannot be verified.
Got certificate from server: C=US,ST=California,L=Redwood City,O=Oracle Corporation,OU=Exadata PM,CN=xdpmceladm01.xdpm.local
Do you want to accept and store this certificate? (Press y/n)
y
exacli ext_admin_user@xdpmceladm01> exit

Again, repeat for all other storage servers you're looking at.

Now that we've got the secure access out the way, let's test it out:

[oracle@xdpmvm01clu01 ~]$ exacli -l ext_admin_user --cookie-jar /home/oracle/.exacli/my.cookie.jar \
-c xdpmceladm01 -e 'list celldisk' |head -3

CELL-06015: Current user does not have privileges to run this command.

[oracle@xdpmvm01clu01 ~]$ exacli -l ext_admin_user --cookie-jar /home/oracle/.exacli/my.cookie.jar \
-c xdpmceladm01 -e 'list flashcachecontent attributes all' |head -3

CELL-06015: Current user does not have privileges to run this command.

[oracle@xdpmvm01clu01 ~]$ exacli -l ext_admin_user --cookie-jar /home/oracle/.exacli/my.cookie.jar \ 
-c xdpmceladm01 -e 'list flashcachecontent attributes dbUniqueName'|head -3
TARDIS.CDB$ROOT
CDB1.CDB$ROOT
CDB1.PDB11

There's Alex's TARDIS* and my PDB. Also note exacli security is working as expected. You'll notice I didn't give the ext_admin role access to the "celldisk" object, or the "ALL" attributes of flashcachecontent, only specific attributes, hence the CELL-06015 privilege reject notice.

We should now be able to use exadcli to loop through all the storage servers to extract the flashcachecontent attributes and organize into a nice flat file sitting in the /u01 filesystem.

TOP TIP: Note the "d" in the exadcli, it's the equivalent of running exacli and dcli at the same time.. Instead of doing multi-steps, like loop..exacli...end loop, or dcli "cellcli.." , just use the single exadcli command.

First, create a directory that will hold the temporary files and scripts:

mkdir -p /u01/app/oracle/tmp/fc_data

Create the bash script:

$ vi /u01/app/oracle/tmp/fc_data/extract_fcca.sh
#!/bin/bash
# Add /usr/local/bin to $PATH
export PATH=${PATH}:/usr/local/bin
# Modify the following line with the username to use for logging on to cells
CELLUSER=ext_admin_user
# Modify the following line with the dcli group with the list of cells
CELLGROUP=/home/oracle/cell_group

# commands used
PS=/bin/ps
GREP=/bin/grep
WC=/bin/wc
ECHO=/bin/echo

MAX_EXADCLI_SESSIONS=10

# make sure we do not spawn too many exadcli sessions
# ideally use /proc/*/stat, but we may not have permissions
num_sess=`$PS -ef | $GREP exadcli | $GREP -v grep | $WC -l`
if [ ${num_sess} -gt ${MAX_EXADCLI_SESSIONS} ]
then
  $ECHO "Exceeded Max exadcli sessions: $MAX_EXADCLI_SESSIONS"
  exit -1
fi

# Execute exadcli and substitute spaces with a delimiter, strip : from hostname
/usr/local/bin/exadcli -l ${EXACLIUSER} -g ${CELLGROUP} \
--cookie-jar=/home/oracle/.exacli/my.cookie.jar list flashcachecontent \
 attributes dbid,dbUniqueName,objectNumber,tablespaceNumber,cachedSize, \
cachedKeepSize,cachedWriteSize,columnarCacheSize,columnarKeepSize,hitCount, \
missCount | /bin/sed -e "s/[[:space:]]\+/\|/g" -e "s/://g"

Don't forget to update the permissions on the script to be executable:

chmod 700 extract_fcca.sh

A quick test shows the script is working as expected:

[oracle@xdpmvm01clu01 fc_data]$ ./extract_fcca.sh
---8<---
xdpmceladm01|1055950008|CDB1.CDB$ROOT|0|0|676061184|0|623362048|0|0|543|38
xdpmceladm01|4154436461|TARDIS.CDB$ROOT|0|0|704512|0|704512|0|0|2|0
xdpmceladm02|4054767370|CDB1.PDB$SEED|0|2147483647|12451840|0|393216|0|0|16|0
---8<--- 

You may notice a bail-out section in the shell script above. If exadcli command is run too many times at once (in our case, more than 10 concurrent sessions), it will exit out.

Now that side is done, let's jump into the database and create some objects:

$ . oraenv
ORACLE_SID = [oracle] ? CDB11
The Oracle base has been set to /u01/app/oracle

[oracle@xdpmvm01clu01 fc_data]$ sqlplus -S / as sysdba
alter session set container = PDB11;

-- create and connect as Scott, the great tamer of tigers...

CREATE USER scott IDENTIFIED BY tiger CONTAINER = CURRENT QUOTA UNLIMITED ON USERS;
GRANT CONNECT, CREATE TABLE, CREATE VIEW TO scott;
CREATE OR REPLACE DIRECTORY fc_data AS '/u01/app/oracle/tmp/fc_data';
GRANT READ,WRITE,EXECUTE ON DIRECTORY fc_data TO scott;
GRANT SELECT ON DBA_OBJECTS TO scott;
GRANT SELECT ON v_$tablespace TO scott;
GRANT SELECT ON v_$database to scott;
-- connect as scott to the pluggable database instance
connect scott/tiger@xdpmvm01clu01.xdpm.local:1521/pdb11

In SQL*Plus, create an external table that will call the exadcli script:

CREATE TABLE debug$fc_et (
       cell_name           VARCHAR2(50),
       db_id               NUMBER,
       db_name             VARCHAR2(30),
       object_id           NUMBER,
       tablespace_id       NUMBER,
       cached_size         NUMBER,
       cached_keep_size    NUMBER,
       cached_write_size   NUMBER,
       columnar_cache_size NUMBER,
       columnar_keep_size  NUMBER,
       hit_count           NUMBER,
       miss_count          NUMBER)
       ORGANIZATION EXTERNAL (
               TYPE oracle_loader
  DEFAULT DIRECTORY fc_data
  ACCESS PARAMETERS (
                    RECORDS DELIMITED BY newline
               PREPROCESSOR fc_data:'extract_fcca.sh'
                    BADFILE fc_data:'fcc.bad'
                    LOGFILE fc_data:'fcc.log'
                     FIELDS TERMINATED BY '|'
              MISSING FIELD VALUES ARE NULL
                          ( cell_name,
                            db_id,
                            db_name,
                            object_id,
                            tablespace_id,
                            cached_size,
                            cached_keep_size,
                            cached_write_size,
                            columnar_cache_size,
                            columnar_keep_size,
                            hit_count,
                            miss_count
                          )
                      )
             LOCATION (fc_data:'extract_fcca.sh'))
         REJECT LIMIT UNLIMITED;

Create a view that shows which database is consuming flashcachecontent:

CREATE OR REPLACE VIEW debug$fc_et_db_vw (
       database_name, 
       cached_mb, 
       keep_mb, 
       write_mb, 
       hits, 
       misses, 
       hit_pct)
       AS SELECT db_name,
       SUM(cached_size)/1048576 cached_mb,
       SUM(cached_keep_size)/1048576 keep_mb,
       SUM(cached_write_size)/1048576 write_mb,
       SUM(hit_count) hits,
       SUM(miss_count) misses,
       ROUND(100*SUM(hit_count)/DECODE(SUM(hit_count+miss_count),0,NULL,SUM(hit_count+miss_count)),1) hit_pct
  FROM debug$fc_et
 GROUP BY db_name
 ORDER BY SUM(cached_size);

Now we can see which databases are consuming flashcache space:

set lines 200
set pages 200
col database_name for a20
SELECT *
  FROM debug$fc_et_db_vw
 ORDER BY hit_pct DESC;

You'll obviously get different results than me, but it should look something like this:

DATABASE_NAME          CACHED_MB KEEP_MB   WRITE_MB   HITS MISSES  HIT_PCT
-------------------- ---------- -------- ---------- ------ ------ --------
CDB1.PDB11           10647.6953        0 1258.42969  21125    241     98.9
CDB1.PDB$SEED        366.148438        0  353.15625    419    136     75.5
TARDIS.PDB$SEED      365.625           0 352.515625    399    130     75.4

You may see a database with the name "UNKNOWN":

DATABASE_NAME          CACHED_MB KEEP_MB   WRITE_MB   HITS MISSES  HIT_PCT
-------------------- ---------- -------- ---------- ------ ------ --------
UNKNOWN              6555.08203        0 6436.44922    738     89     89.2

This doesn't mean someone's running a database called unknown, it just means there is no associated database with the object, usually due to it being ACFS or ADVM objects (eg. remember Exadata 21.2 New Feature ACFS I/O Caching in Flash Cache). Looking at the ts# or object_id may give additional clues.

Create a view to connect the flashcachecontent details table with this database's objects table:

CREATE OR REPLACE VIEW debug$fc_et_ob_vw (
       database_name, 
       tablespace_name, 
       owner, 
       object_name, 
       object_type, 
       keep_mb, 
       tot_mb, 
       perc, 
       hits, 
       misses, 
       hit_ratio )
       AS SELECT d.name database_name,
                 t.name tablespace_name,
                 o.owner,
                 NVL(o.object_name, '('||MAX(cc.object_id)||')') object_name,
                 o.object_type,
                 ROUND(SUM(cached_keep_size)/1024/1024) keep_mb,
                 ROUND(SUM(cached_size)/1024/1024) tot_mb,
                 ROUND(RATIO_TO_REPORT(SUM(cc.cached_size)) OVER ()*100) perc,
                 SUM(cc.hit_count) hits,
                 SUM(cc.miss_count) misses,
                 ROUND((SUM(cc.hit_count)/NULLIF(SUM(cc.hit_count)+SUM(cc.miss_count),0)*100)) hit_ratio
            FROM debug$fc_et cc, v$tablespace t, dba_objects o, v$database d
           WHERE cc.object_id = o.data_object_id (+)
             AND cc.tablespace_id = t.ts# (+)
             AND cc.db_id = d.dbid
           GROUP BY d.name, t.name, o.owner, o.object_name, o.object_type;

Word of warning with the above view, DBA_OBJECTS can get quite large, especially with FusionApps or alike, and the column dba_object_id is not indexed, so you may want to rework this view's SQL if that's the case. Also, sometimes the dbid may not match the v$database.dbid, so you may need to rework the query to take that into account.

We can now see what is sitting in the flashcache for my PDB database:

set lines 200
set pages 200
col object for a40
col tablespace_name for a20
SELECT owner||'.'||object_name ||'('||object_type||')' object,
       tablespace_name,
       keep_mb,
       tot_mb,
       hit_ratio
  FROM debug$fc_et_ob_vw
 WHERE OWNER IN ('SCOTT','SH','OE','HR','BI','IX','OE')
 ORDER BY tot_mb DESC;

Again, you'll get different results than me, but it should look something like this:

OBJECT                                TABLESPACE_NAME   KEEP_MB TOT_MB HIT_RATIO
------------------------------------- ---------------   ------- ------ ---------
SH.SALES_TIME_BIX(INDEX PARTITION)    EXAMPLE                 0     13       100
SH.SALES_PROMO_BIX(INDEX PARTITION)   EXAMPLE                 0      9       100
SH.SALES_PROD_BIX(INDEX PARTITION)    EXAMPLE                 0      9
SH.SALES_CUST_BIX(INDEX PARTITION)    EXAMPLE                 0      7       100
SH.COSTS_PROD_BIX(INDEX PARTITION)    EXAMPLE                 0      5        96
OE.PRODUCT_REF_LIST_NESTEDTAB(TABLE)  EXAMPLE                 0      5
SH.PRODUCTS_PROD_SUBCAT_IX(INDEX)     EXAMPLE                 0      4
SH.COSTS(TABLE PARTITION)             EXAMPLE                 0      3       100
SH.COSTS_TIME_BIX(INDEX PARTITION)    EXAMPLE                 0      3
OE.ORD_CUSTOMER_IX(INDEX)             EXAMPLE                 0      3

(Not much happening in my demo system..)

I hope this helps those DBAs out there that like to dig around and see some of the inner workings of Exadata.

A big thank you to Cecilia for help on this (who you may remember from one of our previous posts on Ten Tips for Database Performance Tuning, on Exadata and in General).

We are always interested in your feedback. You're welcome to engage with us via Twitter @ExadataPM, @GavinAtHQ or @alex_blyth.

Related posts

• Introducing Exadata X9M: Dramatically Faster, More Cost Effective, and Easier to Use (September 2021)

* Technical Architecture Relational Database Information System, not sure what you thought it meant...