Sunday, July 31, 2011

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.

8 comments:

Anonymous said...

Hi Vijay,

I need to generate some reports/stats on exadata server. reports like Tables space usage (MB)
Flash Card Read hits
Database Reads
Wait Events
Cell Efficiency
Grid Efficiency.

Please help me...
ruthala_ramana@yahoo.com

Vijay R. Dumpa said...

Tablespace usage: You can get the details from dba_tablespace_usage_metrics view.
Flash Card details: You can get the details from gv$sysstat, gv$sql and gv$active_session_history views

All other you should be able to the pull it from AWR's.

Cell efficiency is noting but cell node feature. (like Smart Scans, Hybrid Columnar Compression, Storage Indexes, Flash Cache etc..)

I will post more details in my next article.

Anonymous said...

Hi Vijay,
Great article. There was some documentation that using the between function for date columns does not use the smart scan in exadata whereas >= and <= do. Can you confirm this?
Thanks.
Ricardo_Victorio@cox.com

Nataraj said...

Vijay,

there are few more columns Io% in V$sql probably needs to be included to get the exact contribution of smart scan to the specific sql id. especially this query only gives cumulative bytes of the read requests since instance startup.

-- 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%'
);


--
Regards,
Nataraj.

Vijay R. Dumpa said...

Using below SQL you should be able to pull cell offload bytes from v$SQL view, sure. But, from the original SQL, I am getting the value (in bytes) from v$mystat view, if you rerun the original SQL again in the same session, then SQL gives cumulative bytes.


SELECT inst_id inst, sql_id,
DECODE (io_cell_offload_eligible_bytes, 0, 'No', 'Yes') offloadable,
io_cell_offload_eligible_bytes,
DECODE (io_cell_offload_eligible_bytes,
0, 0,
100
* (io_cell_offload_eligible_bytes - io_interconnect_bytes)
/ DECODE (io_cell_offload_eligible_bytes,
0, 1,
io_cell_offload_eligible_bytes
)
) "IO_SAVED_%",
sql_text
FROM gv$sql s
where sql_text like 'SELECT %'
ORDER BY offloadable DESC;

Anonymous said...

Vijay,

If the "IO_SAVED_%" is negative (= -1492.73914930556), what does it mean?

Best Regards,
Eben

Jignesh Kankrecha said...

It means that storage has send more bytes than query asked for .....calculation is above on asm level. Query v$sql_plan_monitor to get actual result at operation level

Anonymous said...

Good One

http://exadatacertification.blogspot.in/