Databases Reference
In-Depth Information
14-9. Measuring Cell I/O Statistics for a SQL Statement
Problem
You wish to report a comprehensive set of storage cell I/O statistics for a given SQL statement, including smart scan
behavior, storage index impact, flash cache impact, and the overall storage cell I/O statistics in order to quantify the
I/O workload contribution.
Solution
In this recipe, you will learn how to measure the overall I/O load generated by a SQL statement and quantify the
impact of Exadata smart scans, storage indexes, and Smart Flash Cache. Start by executing a query of your choice and
then execute the script in Listing 14-12.
SQL> select count(*) from d14.myobj_uncomp;
COUNT(*)
----------
20423000
Elapsed: 00:00:00.61
Listing 14-12.
lst14-12-myiostats.sql
SQL> select * from
(select phys_reads+phys_writes+redo_size mb_requested,
offload_eligible mb_eligible_offload,
interconnect_bytes interconnect_mb,
storageindex_bytes storageindex_mb_saved, flashcache_hits flashcache_mb,
round(((case
when offload_eligible=0 then 0
when offload_eligible> 0 then
(100*(((phys_reads+phys_writes+redo_size)-interconnect_bytes) /
(phys_reads+phys_writes+redo_size)))
end)),2) smartscan_efficiency,
interconnect_bytes/dbt interconnect_mbps,
(phys_reads+phys_writes+redo_size)-(storageindex_bytes+flashcache_hits)
cell_mb_processed,
((phys_reads+phys_writes+redo_size)-(storageindex_bytes+flashcache_hits))/dbt
cell_mbps
from (
select * from (
select name,mb,dbt from (
select stats.name,tm.dbt dbt,
(case
when stats.name='physical reads' then (stats.value * dbbs.value)/1024/1024
when stats.name='physical writes' then
asm.asm_redundancy*((stats.value * dbbs.value)/1024/1024)
when stats.name='redo size' then asm.asm_redundancy*((stats.value * 512)
/1024/1024)
when stats.name like 'cell physi%' then stats.value/1024/1024
Search WWH ::
Custom Search