The Exadata Smart Flash Cache is a great Performance optimizer. The contents of the flash cache can be read fro m the cell servers on Exadata. But one of the restrictions on this is that it only lists the object id’s and not the name of the object. So that requires us to map those object ID’s to Object name in DBA_OBJECTS view.
We can use Oracle’s very powerful dcli utility to colelct the object id’s from all cells and scp to the DB nodes and load it into an External table to join it with the DBA_OBJECTS view.
1. Firstly we have to add all cells to dcli utility
list flashcache
listcell
chmod 700 mycommands.scl
dcli -g cells.txt -l root -x mycommands.scl
## this will give output of commands put in file mycommands.scl from all cell nodes ##
Incase if there is error with one key then drop and recreate that cells key
[root@exatestceladm01 ~]# dcli -c exatestceladm01 --unkey -l root
root@exatestceladm01’s password:
exatestceladm01: ssh key dropped
## Rekey that cell
root@exatestceladm01 ~]# dcli -g ./cells.txt -l root -k -s ‘-o StrictHostKeyChecking=no’
root@exatestceladm01’s password:
exatestceladm01: ssh key added
exatestceladm02: ssh key already exists
exatestceladm03: ssh key already exists
————————————————————————–
Configure DB nodes SSH using dcli command utility
On Any Cell node create a text file in /root called db.txt
exatestdbadm01
exatestdbadm02
chmod 700 db.txt
dcli -g db.txt -l root -k -s '-o StrictHostKeyChecking=no'
root@exatestdbadm01’s password:
root@exatestdbadm02’s password:
exatestdbadm01: ssh key added
exatestdbadm02: ssh key added
[root@exatestceladm01 ~]# dcli -g db.txt -l root hostname
exatestdbadm01: exatestdbadm01.qiibonline.com
exatestdbadm02: exatestdbadm02.qiibonline.com
2. Create the dcli command to generate the contents of the flash cache
dcli -g /root/cells.txt -l root "cellcli -e list flashcachecontent where dbUniqueName='SWX' and tableSpaceNumber=6" > /tmp/swxoutput.log
This will generate all the flascache objects for Database ‘SWX’ and Tablespace Number 6. You can further refine this criteria to your liking
3. Now Let’s create a script to generate the flash contents, clean the output using AWK to only include the Object ID’s and copy it to
the DB node
#!/bin/bash
PATH=$PATH:$HOME/bin
export PATH
/usr/local/bin/dcli -g /root/cells.txt -l root "cellcli -e list flashcachecontent where dbUniqueName='SWX' and tableSpaceNumber=6" >
/tmp/swxoutput.log
awk -F'\t' '{ print $3 }' /tmp/swxoutput.log > /tmp/swxclean.log
scp -r /tmp/swxclean.log root@exaproddbadm01:/u03/expdp/swx
ssh root@exaproddbadm01 << EOF
chmod 777 /u03/expdp/swx/swxclean.log
chown oracle:dba /u03/expdp/swx/swxclean.log
EOF
exit
## You can schedule this in the crontab of cell server to send it daily
30 07 * * * /root/flashlogs.sh
4. Create an External Table to Read the Flash Contents inside the Database for which you generated the flash contents in above script
SQL> create or replace directory load_flash as '/u03/expdp/swx';
Directory created.
SQL> grant read,write on directory load_flash to public;
Grant succeede
create table flash_contents (
cell_object_id varchar(20)
)
organization external (
type oracle_loader
default directory load_flash
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('swxclean.log')
)
reject limit unlimited;
-- Check if External Tabling is Loading Properly --
select * from flash_contents;
5. Now create a Query to check the Flash Contents and Map it to DBA_OBJECTS to identify it
set lines 999
select DISTINCT a.OBJECT_NAME,a.OBJECT_TYPE from DBA_OBJECTS a,flash_contents b where a.owner=’SWX’ and a.DATA_OBJECT_ID=b.cell_object_id
order by 1;
OBJECT_NAME
OBJECT_TYPE
——————————————————————————————————————————–
——————-
SYS_IL0000107895C00039$$ INDEX
SYS_IL0000107898C00039$$ INDEX
SYS_IL0000107901C00006$$ INDEX
SYS_IL0000108307C00005$$ INDEX
PARTITION
SYS_IL0000108342C00006$$ INDEX
PARTITION
This will display the objects running in flash for this particular database in the particular tablespace
Update 2: If you get error like below
ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04001: error opening file
Most likely your database is an Orace RAC DB so create the same directory structure for the external table files on both nodes and make sure the clean file is SCP’ed to both compute nodes from the cell node.