Category Archives: AWR

How Do *YOU* Measure SmartScans?

With SmartScans being the most important (and most unique) performance feature for Exadata, it’s incredibly useful to measure how well you’re making use of it.

But how?

There are a number of ways you can measure this, but none of these seem to be the DEFINITIVE method to do so.  Instead, it’s probably a good idea to more than one formula, if not all, to get a good idea of our SmartScan usage.

Why are there multiple formulas?  Because the existing database metrics don’t quite capture what we’re looking to measure.  For instance:

  • physical read total bytes‘ –  is all the data including compressed data AND SmartScan-ineligible data.
  • cell physical IO interconnect bytes‘  –  includes the writes (multiplied due to ASM mirroring) AND the reads.
  • cell IO uncompressed bytes‘  –  is the data volume for predicate offloading AFTER the Storage Index filtering and any decompression
  • cell physical IO interconnect bytes returned by smart scan‘  –  includes uncompressed data.

Continue reading

Advertisements
Tagged , , ,

Exadata-Specific Metrics in AWR

After our ExCite review earlier in the year, I managed to borrow/steal the following metrics to determine IOPS, HCC efficiency and SmartScan efficiency.

I’m a bit wobbly on the SmartScan formula, so if anyone has any corrections, please feel free to offer them 🙂

IOPS

% of all physical reads served by the FlashCache
— physical read total IO requests / cell flash cache read hits

adjusted total read requests
– we had to double this because WE are using 16k blocks and IOPS is based on 8k blocks
— (physical read total IO requests – cell flash cache read hits) * 2

adjusted total write requests
– we had to double this because WE are using NORMAL redundancy on the ASM diskgroup
— physical write total IO requests * 2

I/O operations
– per second if we take the “Per Second” metric in AWR
— “adjusted total read requests” + “adjusted write read requests”

N.B. an X3-2 quarter-rack has an IOPS rating of 6,000 – I discovered this when the ExCite experts pointed it out (and we had exceeded it).

EHCC

average number of rows that each compression unit contains – effectiveness of what the data we do compress with EHCC
— EHCC total rows for decompression / EHCC CUs decompressed

SmartScan

total physical IO bytes saved by SmartScan
— cell physical IO bytes eligible for predicate offload – cell physical IO interconnect bytes returned by smart scan

ratio of SmartScan efficiency
— cell physical IO interconnect bytes returned by smart scan / (cell IO uncompressed bytes + cell physical IO bytes saved by storage indexes)

Tagged , , , , , , ,
Advertisements
%d bloggers like this: