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.
* Technical Architecture Relational Database Information System, not sure what you thought it meant...
Gavin is a product manager in Oracle’s Exadata team, with a focus on software and hardware roadmap. Prior to the Exadata team, Gavin was a founding member of the team responsible for launching the industry’s first on-premises public cloud technology - Oracle Cloud at Customer. In his 15 years in Oracle Product Management, Presales and Consulting roles, Gavin has developed a robust understanding of all things Oracle, helping customers architect and implement a variety of infrastructure and application technologies.
Next Post