Host and Database Performance Monitoring - Oracle Exadata Recipes: A Problem-Solution Approach - page 431

14-8. Monitoring Exadata with Statistics and Counters
Problem
You wish to monitor database performance using statistics and counter metrics in a similar manner so that you may
measure performance for other Oracle features or performance behavior.
Solution
In this recipe, you will learn about the new system, session, and SQL statistics that Oracle introduced for
Exadata and how to build a framework or reference for designing queries and scripts for Exadata database
performance monitoring.
Begin by running the following query, which will list all of the Exadata statistics available in 11gR2:
SQL> select name from v$statname
where (name like 'cell%'
or name like 'flash %')
/
cell IO uncompressed bytes
cell flash cache read hits
cell index scans
cell physical IO bytes eligible for predicate offload
cell physical IO bytes saved by storage index
... Ouput omitted
cell physical IO bytes sent directly to DB node to balance CPU
cell physical IO interconnect bytes
... Ouput omitted
flash cache insert skip: corrupt
... Ouput omitted
56 rows selected.
SQL>
As you can see, there are roughly 56 Exadata-specific statistics available in our database, which is running
Oracle 11.2.0.3. Each of these statistics is a counter-based statistic; each time an Oracle session encounters the
condition that triggers the statistic, the statistic's value is incremented. Run the following query to report on
the number of bytes eligible for predicate offload, systemwide, since the instance was started:
SQL> select name,value/1024/1024/1024 val
from v$sysstat
where name='cell physical IO bytes eligible for predicate offload'
/
Stat Name GB
------------------------------------------------------- ---------
cell physical IO bytes eligible for predicate offload 57,415
SQL>
We see that in our instance, over 56 TB of data was eligible for predicate offload. You can query or monitor
statistics information at the system level using v$SYSSTAT , at the session level with v$SESSTAT , using AWR reports
or data, or in the case of specific offload information at a per SQL ID level. As listed above, Oracle tracks many
Exadata-specific performance statistics. Table 14-2 displays some of the more commonly monitored statistics and
their meanings.