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

Statistic Statistic value
------------------------------ ----------------
MB Requested 2255.27
MB Eligible for Offload 2255.09
Smart Scan Efficiency 89.20
Interconnect MB 243.53
Interconnect MBPS 386.56
Storage Index MB Saved .00
Flash Cache MB read 19.02
Cell MB Processed 2236.25
Cell MBPS 3549.60

The script in Listing 14-12 queries a number of I/O statistics and displays the number of megabytes requested
for your session, the number of megabytes eligible for predicate offload (in other words, smart scan), the volume of
data transmitted over the storage interconnect, the amount of I/O processed on the storage cells, Smart Flash Cache
statistics, as well as Storage Index savings.

How It Works
If you are reading this topic sequentially, this recipe is a preview to topics covered in Chapters 15 through 19. In these
chapters, we discuss how to measure smart scan and cell offload statistics, storage index savings, and flash cache
metrics. In this recipe, we have combined each of these statistics and metrics into a simple script that measures the
overall I/O load for your individual SQL statement.
Listing 14-12 queries a number of statistics for your current session and derives the rate-based measurements,
such as storage interconnect megabytes per second, by dividing the MB values by the amount of database time
consumed for your session. In the script, we use Oracle's PIvOT statement to align row values in a columnar fashion
so to easily perform calculations on a number of different values without having to join multiple times to the statistic
view, v$MYSTAT . We then UNPIvOT the final results to display the output in row format.
While not displayed in this recipe, Listing 14-13, lst14-13-sessiostats.sql uses the same logic to display
Exadata I/O statistics given a specific session, and it is included in the source code repository.

■ to learn more about smart scan, storage indexes, exadata's smart flash cache, and other storage server
software features, please read Chapters 15 through 19.

Note