Monitoring Exadata Cell using some Views

When we have Exadata, we might ask some questions, what dynamic V$ views we can use for monitoring?
V$CELL - to display Exadata Cell Identification.

SQL> desc v$cell
Name Null? Type
----------------------------------------- -------- ----------------------------
CELL_PATH VARCHAR2(400)
CELL_HASHVAL NUMBER

CELL_PATH - IP addresses of the cell.
CELL_HASHVAL - A numeric hash value for the cell.

SQL> column CELL_PATH format a20
SQL> select * from v$cell;

CELL_PATH CELL_HASHVAL
-------------------- ------------
192.168.101.5 2520626383
192.168.101.6 4087315787
192.168.101.7 2611738764
192.168.101.8 1901900577
192.168.101.9 1557764708
192.168.101.10 3737599385
192.168.101.11 3126672640

We can use CELL_HASHVAL column in V$CELL view join in V$SESSION_WAIT and V$ACTIVE_SESSION_HISTORY views (P1)

SQL> select a.sid ,'Event: ' ||event || ' on '|| b.cell_path ||' '|| P3 || ' '|| P3TEXT from V$SESSION_WAIT a, V$CELL b where b.cell_hashval = a.p1 and a.event like 'cell%';

SID 'EVENT:'||EVENT||'ON'||B.CELL_PATH||''||P3||''||P3TEXT
---------- ---------------------------------------------------------------------
3603 Event: cell single block physical read on 192.168.101.5 8192 bytes
1719 Event: cell single block physical read on 192.168.101.5 8192 bytes
209 Event: cell single block physical read on 192.168.101.5 8192 bytes

V$CELL_STATE - to display Exadata Cell Performance Statistics

SQL> desc V$CELL_STATE
Name Null? Type
----------------------------------------- -------- ----------------------------
CELL_NAME VARCHAR2(1024)
STATISTICS_TYPE VARCHAR2(15)
OBJECT_NAME VARCHAR2(1024)
STATISTICS_VALUE CLOB

CELL_NAME - IP addresses of the cell.
STATISTICS_TYPE - statistics type, such as thread_stats.
OBJECT_NAME - key for a specific statistics type, such as the thread ID if STATISTICS_TYPE is thread_stats.
STATISTICS_VALUE - statistic values in an XML document with attribute and value pairs.

SQL> SPOOL /tmp/cell_state_test.log
SQL> SET PAGESIZE 10000 LONG 500000
SQL> SELECT cell_name, statistics_type, object_name, XMLTYPE(statistics_value) FROM v$cell_state;
SQL> SPOOL OFF

V$CELL_THREAD_HISTORY - to display the samples that Cell Server takes of the threads in the cells visible to the database client.

SQL> desc V$CELL_THREAD_HISTORY
Name Null? Type
----------------------------------------- -------- ----------------------------
CELL_NAME VARCHAR2(1024)
SNAPSHOT_ID NUMBER
SNAPSHOT_TIME DATE
THREAD_ID NUMBER
JOB_TYPE VARCHAR2(32)
WAIT_STATE VARCHAR2(32)
WAIT_OBJECT_NAME VARCHAR2(32)
SQL_ID VARCHAR2(13)
DATABASE_ID NUMBER
INSTANCE_ID NUMBER
SESSION_ID NUMBER
SESSION_SERIAL_NUM NUMBER

CELL_NAME - IP addresses of the cell.
SNAPSHOT_ID - ID of the snapshot(NUMBER).
SNAPSHOT_TIME - date and time of the snapshot.
THREAD_ID - thread ID (NUMBER).
JOB_TYPE - job that the thread was running when the snapshot was taken.
WAIT_STATE - A unique state that identifies the location of the wait, if any exists.
WAIT_OBJECT_NAME - Object being waited on, if any exists. For example, the mutex name.
SQL_ID - The identifier of the SQL command that the client was processing for the job that is running.
DATABASE_ID - ID of the database (NUMBER).
INSTANCE_ID - ID of the instance (NUMBER).
SESSION_ID - ID of the session (NUMBER).
SESSION_SERIAL_NUM - session serial number (NUMBER).

SQL> select cell_name, snapshot_id, job_type, wait_state, sql_id, session_id, session_serial_num from V$CELL_THREAD_HISTORY where sql_id ='ab3swhv5g138y';

CELL_NAME SNAPSHOT_ID JOB_TYPE WAIT_STATE SQL_ID SESSION_ID SESSION_SERIAL_NUM
------------------------------ ----------- -------------------------------- -------------------------------- ------------- ---------- ------------------
192.168.101.9 7530062 CacheGet waiting_for_SKGXP_send ab3swhv5g138y 1892 2965

V$CELL_REQUEST_TOTALS - to diaplay a historical view of the types and frequencies of the requests being run by a cell.

SQL> desc V$CELL_REQUEST_TOTALS
Name Null? Type
----------------------------------------- -------- ----------------------------
CELL_NAME VARCHAR2(1024)
SNAPSHOT_ID NUMBER
SNAPSHOT_TIME DATE
STATISTICS_NAME VARCHAR2(32)
STATISTICS_VALUE NUMBER

CELL_NAME - IP addresses of the cell.
SNAPSHOT_ID -ID of the snapshot(NUMBER).
SNAPSHOT_TIME - date and time of the snapshot.
STATISTICS_NAME - name of the statistic.
STATISTICS_VALUE - value of the statistic.

SQL> select * from V$CELL_REQUEST_TOTALS where snapshot_id=7530062;

CELL_NAME SNAPSHOT_ID SNAPSHOT_ STATISTICS_NAME STATISTICS_VALUE
------------------------------ ----------- --------- -------------------------------- ----------------
192.168.101.9 7530062 04-JUN-11 CacheGet Jobs 1
192.168.101.9 7530062 04-JUN-11 CachePut Jobs 0
192.168.101.9 7530062 04-JUN-11 Predicate Disk Read Jobs 0
192.168.101.9 7530062 04-JUN-11 Process Ioctl Jobs 4
192.168.101.9 7530062 04-JUN-11 Smart IO Total Request Size 0

V$SYSSTAT - this is view what we often use for monitoring database and be able to use monitoring Exadata.

SQL> SELECT name, value/1024/1024 MB from v$sysstat a WHERE
a.name = 'physical read total bytes' OR
a.name = 'physical write total bytes' OR
a.name = 'cell physical IO interconnect bytes' OR
a.name = 'cell physical IO bytes eligible for predicate offload' OR
a.name = 'cell physical IO bytes saved during optimized file creation' OR
a.name = 'cell physical IO bytes saved during optimized RMAN file restore' OR
a.name = 'cell IO uncompressed bytes' OR
a.name = 'cell physical IO interconnect bytes returned by smart scan' OR
a.name = 'cell physical IO bytes saved by storage index';

NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 1586343.81
physical write total bytes 7141430.75
cell physical IO interconnect bytes 18947204.6
cell physical IO bytes saved during optimized file creation 700946.953
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 1105701.38
cell physical IO bytes saved by storage index 365.953125
cell physical IO interconnect bytes returned by smart scan 5990.22793
cell IO uncompressed bytes 403520.695

Key Statistics on V$SYSSTAT view about Exadata:
cell flash cache read hits - The number of read requests that were a cache hit on exadata flash cache.
cell IO uncompressed bytes - The total size of uncompressed data that is processed on the cell. For scan on hybrid-columnar-compressed tables, this statistic is the size of data after decompression.
cell physical IO interconnect bytes returned by smart scan - The number of bytes that are returned by the cell for Smart Scan only, and does not include bytes for other database I/O.
cell physical IO bytes saved by storage index - The number of bytes saved by storage index.
cell physical IO bytes eligible for predicate offload - The total number of I/O bytes processed with physical disks when processing was offloaded to the cell.
cell physical IO bytes pushed back due to excessive CPU - The number of I/O bytes sent back to the database server for processing due to CPU usage on Exadata Cell.
cell physical IO bytes saved during optimized file creation - The number of I/O bytes saved by the database host by offloading the file creation operation to cells. This statistic shows the Exadata Cell benefit due to optimized file creation operations.
cell physical IO bytes saved during optimized RMAN file restore - The number of I/O bytes saved by the database host by offloading the RMAN file restore operation to cells. This statistic shows the Exadata Cell benefit due to optimized RMAN file restore operations.
cell physical IO interconnect bytes - The number of I/O bytes exchanged over the interconnection between the database host and cells.
physical read requests optimized - Total number of read requests satisfied either by using Exadata Smart Flash Cache or storage index.
physical read total bytes - Total amount of I/O bytes for reads processed with physical disks. This includes when processing was offloaded to the cell and when processing was not offloaded.
physical read total bytes optimized - Total number of bytes read from Exadata Smart Flash Cache or storage index.
physical write total bytes - Total amount of I/O bytes for writes processed with physical disks. This includes when processing was offloaded to the cell and when processing was not offloaded.

Other Views:
V$BACKUP_DATAFILE - contains columns relevant to Exadata Cell during Oracle Recovery Manager (RMAN) incremental backups.
V$SEGMENT_STATISTICS - segment-level statistics can be used to detect specific objects what are performing optimized reads from the cell. The "optimized physical read" statistic name is the number of read requests for an objects that were read from Exadata Smart Flash Cache or from the storage index.

SQL> SELECT object_name, value FROM V$segment_statistics WHERE statistic_name='optimized physical reads' AND value>1000 ORDER BY value;

OBJECT_NAME VALUE
------------------------------ ----------
TB_TEST 63153

V$SQL - lists statistics on shared SQL areas. List columns:
* PHYSICAL_READ_BYTES
* PHYSICAL_WRITE_BYTES
* IO_INTERCONNECT_BYTES
* IO_CELL_OFFLOAD_ELIGIBLE_BYTES
* IO_CELL_UNCOMPRESSED_BYTES
* IO_CELL_OFFLOAD_RETURNED_BYTES
* OPTIMIZED_PHY_READ_REQUESTS
We can see these columns in V$SQLAREA/V$SQLAREA_PLAN_HASH/V$SQLSTATS/V$SQLSTATS_PLAN_HASH views also.

SQL> select sql_id, physical_read_bytes, physical_write_bytes, io_interconnect_bytes, io_cell_offload_eligible_bytes, io_cell_uncompressed_bytes, io_cell_offload_returned_bytes, optimized_phy_read_requests from v$sql where sql_id='a28xja6zsgnb7';

SQL_ID PHYSICAL_READ_BYTES PHYSICAL_WRITE_BYTES IO_INTERCONNECT_BYTES IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_CELL_UNCOMPRESSED_BYTES IO_CELL_OFFLOAD_RETURNED_BYTES OPTIMIZED_PHY_READ_REQUESTS
------------- ------------------- -------------------- --------------------- ------------------------------ -------------------------- ------------------------------ ---------------------------
a28xja6zsgnb7 2184994816 0 66946880 2184912896 2185003008 66864960 3

SQL> SELECT sql_text,
io_cell_offload_eligible_bytes/1024/1024 cell_offload_eligible_mb,
io_cell_uncompressed_bytes/1024/1024 io_uncompressed_mb,
io_interconnect_bytes/1024/1024 io_interconnect_mb,
io_cell_offload_returned_bytes/1024/1024 cell_return_bytes_mb,
(physical_read_bytes + physical_write_bytes)/1024/1024 io_disk_mb
FROM v$sql WHERE
sql_id='a28xja6zsgnb7';

SQL_TEXT CELL_OFFLOAD_ELIGIBLE_MB IO_UNCOMPRESSED_MB IO_INTERCONNECT_MB CELL_RETURN_BYTES_MB IO_DISK_MB
------------------------------ ------------------------ ------------------ ------------------ -------------------- ----------
SELECT * from TB_TEST 2083.69531 2083.78125 63.84552 63.767395 2083.77344