Exadata Smart Scan(cell offload processing), Exadata Smart Flash Cache and Exadata Storage Index Capability's

Exadata Smart Scan(cell offload processing), Exadata Smart Flash Cache and Exadata Storage Index Capability's

a).
-- Exadata Smart Scan(cell offload processing)

Exadata Smart Scan processes queries at the storage layer, returning only relevant rows and columns to the database server.
As a result, much less data travels over fast 40GB Infiniband interconnect, dramatically improving the performance and concurrency of simple and complex queries.


-- Which predicates are supported by a cell? What happens when predicates contain functions or expressions?

a) Numeric functions: they can all be offloaded with a single exception, the
WIDTH_BUCKET function. For example the predicate "width_bucket(n,1,10,100) = 1"
is not offloaded.

b) Character functions returning character values: they can all be offloaded.

c) Character functions returning number values: they can all be offloaded.

d) Datetime functions:
DATE datatype is supported.
When a TIMESTAMP datatype is involved, offloading almost never happens.
While predicates like "d = sysdate" (note that "d" is a column of DATE datatype)
and "add_months(d,1) = to_date('01-01-2010','DD-MM-YYYY')" can be offloaded,
something like "add_months(d,1) = sysdate" cannot.
Every datetime function can be offloaded provided that it is not used along with
SYSDATE or CURRENT_DATE.

Note: There is no synchronization between the db layer and the storage layer for the purpose of offloading datetime functions. Whatever they would do, it is simply impossible to have exactly synchronized timers. Hence, inconsistent results might be generated. Therefore, IMO, the date/time for stuff like SYSDATE must be provided by the instance where a SQL statement is started.

months_between(d,sysdate) = 0
months_between(d,current_date) = 0
months_between(d,to_date('01-01-2010','DD-MM-YYYY')) = 0

e) NULL-related functions: they can all be offloaded.

-- List of the functions supporting offloading, is available through the V$SQLFN_METADATA view.
SELECT offloadable, count(DISTINCT name)
FROM v$sqlfn_metadata
GROUP BY offloadable;

-- Smart Scan Disablers
IOT
Clustered Tables
LOB
Rowdependencies Enabled

-- You can use the below query to see the "cell physical IO interconnect bytes returned by smart scan" in bytes
SELECT a.NAME, b.VALUE
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME IN
('physical read total bytes', 'physical write total bytes',
'cell IO uncompressed bytes')
OR a.NAME LIKE 'cell physical%'
);


b).
-- How to create and drop Exadata Smart Flash Cache?

-- Drop flashcache (To flush Exadata Smart Flash Cache).
CellCLI> DROP FLASHCACHE

-- Create flashcache on all disks.
CellCLI> CREATE FLASHCACHE ALL

-- Flashcache cell disk and size deatils.
CellCLI> LIST FLASHCACHE detail
name: atlcel01_FLASHCACHE
cellDisk: FD_10_atlcel01,FD_00_atlcel01,FD_04_atlcel01,FD_14_atlcel01,FD_15_atlcel01,FD_02_atlcel01,FD_08_atlcel01,FD_07_atlcel01,FD_05_atlcel01,FD_09_atlcel01,FD_13_atlcel01,FD_03_atlcel01,FD_12_atlcel01,FD_01_atlcel01,FD_11_atlcel01,FD_06_atlcel01
creationTime: 2011-03-27T03:18:23-04:00
degradedCelldisks:
effectiveCacheSize: 365.25G
id: 40ef10f1-8c82-4419-b5a1-9d51706813fe
size: 365.25G
status: normal


-- To determine whether an object is currently being kept in the Exadata Smart Flash Cache

-- Get the object id
atldb01:ODS1\sys: SQL> select object_id, object_type from dba_objects where object_id = 8104207;

OBJECT_ID OBJECT_TYPE
---------- -------------------
8104207 TABLE PARTITION


-- Look at the segment statistics and get the DATAOBJ#
atldb01:ODS1\sys: SQL> select distinct inst_id, owner, object_name, ts#, obj#, dataobj# from gv$segment_statistics
2 where owner = 'PIN_USER'
3 and object_name = 'EVENT';

INST_ID OWNER OBJECT_NAME TS# OBJ# DATAOBJ#
---------- ------------------------------ ------------------------------ ---------- ---------- ----------
2 PIN_USER EVENT 197 8104207 8104207
1 PIN_USER EVENT 197 8104202 8104202
3 PIN_USER EVENT 197 8104201 8104201
2 PIN_USER EVENT 197 8104218 8104218
2 PIN_USER EVENT 197 8104221 8104221
2 PIN_USER EVENT 197 8104212 8104212
2 PIN_USER EVENT 197 8104215 8104215
2 PIN_USER EVENT 197 8104227 8104227
1 PIN_USER EVENT 197 8104218 8104218
1 PIN_USER EVENT 197 8104212 8104212
1 PIN_USER EVENT 197 8104205 8104205


-- List the object details from Flash Cache.
CellCLI> LIST FLASHCACHECONTENT where objectNumber=8104207 and tableSpaceNumber=197 and dbUniqueName=ODS detail
cachedKeepSize: 0
cachedSize: 4194304
dbID: 3108331340
dbUniqueName: ODS
hitCount: 5972
missCount: 8820
objectNumber: 8104207
tableSpaceNumber: 197

Note: DBA can cache an object by setting CELL_FLASH_CACHE setting to KEEP. The default value of this setting is DEFAULT.


When you are accessing the object data from the Flash Cache, you should see high number of optimized request("physical read requests optimized" and "cell flash cache read hits") than the high number of I/O requests("physical read total IO requests").

-- You can use the below query to see the "flash cache read hits" in bytes.
SELECT a.NAME, b.VALUE
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME LIKE '%flash cache read hits'
OR a.NAME LIKE 'cell physical%'
OR a.NAME LIKE 'physical read tot%'
OR a.NAME LIKE 'physical read req%'
);


c).
-- Storage Index Capability's

Storage Indexes are memory structures which do not persist when the Exadata cells are restarted. They are dynamically built when tables are referenced for the first time after the cells restart.


-- You can use the below query to see the savings of Storage Index in MB.
SELECT a.NAME, b.VALUE / 1024 / 1024 value_in_mb
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME IN
('physical read total bytes', 'physical write total bytes',
'cell IO uncompressed bytes')
OR a.NAME LIKE 'cell physical%'
);

Value of "cell physical IO bytes saved by storage index" shows the savings of the physical I/O by storage index and the total Physical I/O is recorded under "physical read total bytes".

Queries that benefit from storage index can execute more quickly using fewer resources which allows other workloads to use the unused I/O resources.