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.

“SmartScan Eligibility Ratio”
What percentage of the total sum of the physical reads are eligible for SmartScan?

= (100 / ‘physical read total bytes’) * ‘cell physical IO bytes eligible for predicate offload’

physical read total bytes‘  –  all non-system data which was read from the storage cells
cell physical IO bytes eligible for predicate offload‘  –  data that is eligible for SmartScan processing (total direct path reads)

“Storage Index Filter Ratio”
What percentage of the SmartScan eligible data is filtered by the Storage Indexes?

= (100 / ‘cell physical IO bytes eligible for predicate offload’) * ‘cell physical IO bytes saved by storage index’

cell physical IO bytes saved by storage index‘  –   data that the cells did not need to scan through because the storage indexes told them they were not in that region.

“SmartScan Efficiency Ratio”
What percentage of the SmartScan eligible data size was returned by the SmartScan? This is probably the most-used / “best” ratio to use, if you could only use one.

= 100 – ((100 / ‘cell physical IO bytes eligible for predicate offload’) * ‘cell physical IO interconnect bytes returned by smart scan’)

cell physical IO interconnect bytes returned by smart scan‘  –  data returned to the database from the SmartScan.

“SmartScan Offloading Ratio”
What percentage of the uncompressed, filtered data is returned by the SmartScan?

= (100 / ‘cell IO uncompressed bytes’) * ‘cell physical IO interconnect bytes returned by smart scan’

cell IO uncompressed bytes‘  –  data read from the storage cells for predicate offloading, after the storage index filtering and data decompression. N.B. compressed data has to be uncompressed first before applying the predicates on the storage cells.

Which formula(e) do YOU use to measure your SmartScan usage?

Advertisements
Tagged , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: