Monitoring Exadata with Statistics

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.