Monitoring Exadata with Statistics

In Oracle 11gR2, additional Exadata cell-related system events have been added to the normal system event-based dynamic performance views. There are 58 different cell-related or Flash Cache related wait events available:

 

SQL> l
1 select name from v$statname
2* where name like '%cell%' or name like '%flash
cache%'
SQL> /
cell physical IO interconnect bytes
cell physical IO bytes pushed back due to
excessive CPU on cell
physical read flash cache hits
flash cache inserts
flash cache eviction: invalidated
flash cache eviction: buffer pinned
flash cache eviction: aged out
flash cache insert skip: not current
flash cache insert skip: DBWR overloaded
flash cache insert skip: exists
flash cache insert skip: not useful
flash cache insert skip: modification
flash cache insert skip: corrupt
cell physical IO bytes saved during optimized
file creation
cell physical IO bytes saved during optimized
RMAN file restore
cell physical IO bytes eligible for predicate
offload
cell physical IO bytes saved by storage
index
cell smart IO session cache lookups
cell smart IO session cache hits
cell smart IO session cache soft misses
cell smart IO session cache hard misses
cell smart IO session cache hwm
cell num smart IO sessions in rdbms block IO due
to user
cell num smart IO sessions in rdbms block IO due
to no cell mem
cell num smart IO sessions in rdbms block IO due
to big payload
cell num smart IO sessions using passthru mode
due to user
cell num smart IO sessions using passthru mode
due to cellsrv
cell num smart IO sessions using passthru mode
due to timezone
cell num smart file creation sessions using
rdbms block IO mode
cell physical IO interconnect bytes returned by
smart scan
cell num fast response sessions
cell num fast response sessions continuing to
smart scan
cell smart IO allocated memory bytes
cell smart IO memory bytes hwm
cell num active smart IO sessions
cell smart IO sessions hwm
cell scans
cell blocks processed by cache layer
cell blocks processed by txn layer
cell blocks processed by data layer
cell blocks processed by index layer
cell commit cache queries
cell transactions found in commit cache
cell blocks helped by commit cache
cell blocks helped by minscn optimization
chained rows skipped by cell
chained rows processed by cell
chained rows rejected by cell
cell simulated physical IO bytes eligible for
predicate offload
cell simulated physical IO bytes returned by
predicate offload
cell CUs sent uncompressed
cell CUs sent compressed
cell CUs sent head piece
cell CUs processed for uncompressed
cell CUs processed for compressed
cell IO uncompressed bytes
cell index scans
cell flash cache read hits
58 rows selected.
SQL>

 

The sections below provide some common system, session, and “my session” reports for Exadata-related events.

Script: exa_sysstat.sql

 

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select b.name,
value
from gv$sysstat a,
gv$statname b
where a.statistic# = b.statistic#
and (b.name like '%cell%' or b.name like '%flash
cache%')
and a.value > 0
order by 2 desc
/

 

Script: exa_sesstat.sql

 

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select b.name,
value
from v$sesstat a,
v$statname b
where a.statistic# = b.statistic#
and a.sid = &sid
and (b.name like '%cell%' or b.name like '%flash
cache%')
and a.value > 0
order by 2 desc
/


Script: exa_mystat.sql

 

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select b.name,
value
from v$mystat a,
v$statname b
where a.statistic# = b.statistic#
and (b.name like '%cell%' or b.name like '%flash
cache%')
and a.value > 0
order by 2 desc
/

 

Summary

Measuring Exadata with Oracle system or session statistics is vital to understanding how well your Exadata Database Machine is working in relation to non-Exadata solutions.