From Oramosswiki
WITH snaps AS
--
-- Change History
--
-- Date Author Description
-- =========== ================= ================================================
-- 07-APR-2008 Jeff Moss Created
--
(
SELECT dbid
, instance_number
, TRUNC(begin_interval_time) snap_date
, MIN(snap_id) start_snap_id
, MAX(snap_id) end_snap_id
FROM dba_hist_snapshot
GROUP BY dbid
, instance_number
, TRUNC(begin_interval_time)
)
, fs AS
(
SELECT snap_id
, dbid
, instance_number
, filename
, block_size
, file#
, phyrds
, singleblkrds
, ROUND((phyrds - singleblkrds),2) multiblkreads
, phywrts
, readtim
, singleblkrdtim
, writetim
, phyblkrd
, phyblkwrt
, wait_count
, time
, ROUND((phyblkrd - singleblkrds),2) phyblkrdmbr
, 'DATAFILE' file_type
, (CASE WHEN (phyrds - singleblkrds) = 0
THEN NULL
ELSE ROUND((phyblkrd - singleblkrds) / (phyrds - singleblkrds),2)
END) avgmultiblockreadcount
FROM dba_hist_filestatxs
UNION ALL
SELECT snap_id
, dbid
, instance_number
, filename
, block_size
, file#
, phyrds
, singleblkrds
, ROUND((phyrds - singleblkrds),2) multiblkreads
, phywrts
, readtim
, singleblkrdtim
, writetim
, phyblkrd
, phyblkwrt
, wait_count
, time
, ROUND((phyblkrd - singleblkrds),2) phyblkrdmbr
, 'TEMPFILE'
, (CASE WHEN (phyrds - singleblkrds) = 0
THEN NULL
ELSE ROUND((phyblkrd - singleblkrds) / (phyrds - singleblkrds),2)
END) avgmultiblockreadcount
FROM dba_hist_tempstatxs
)
, calc_deltas AS
(
SELECT s.snap_date
, s.dbid
, s.instance_number
, fs.block_size
, fs.file_type
, ROW_NUMBER() OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) rn
, NVL((LEAD(fs.phyrds) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.phyrds),0) delta_phyrds
, NVL((LEAD(fs.phywrts) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.phywrts),0) delta_phywrts
, NVL((LEAD(fs.singleblkrds) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.singleblkrds),0) delta_singleblkrds
, NVL((LEAD(fs.multiblkreads) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.multiblkreads),0) delta_multiblkreads
, NVL((LEAD(fs.readtim) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.readtim),0) delta_readtim
, NVL((LEAD(fs.writetim) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.writetim),0) delta_writetim
, NVL((LEAD(fs.singleblkrdtim) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.singleblkrdtim),0) delta_singleblkrdtim
, NVL((LEAD(fs.phyblkrd) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.phyblkrd),0) delta_phyblkrd
, NVL((LEAD(fs.phyblkwrt) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.phyblkwrt),0) delta_phyblkwrt
, NVL((LEAD(fs.time) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.time),0) delta_time
, NVL((LEAD(fs.phyblkrdmbr) OVER(PARTITION BY s.snap_date,fs.dbid,fs.instance_number,fs.filename ORDER BY snap_id) - fs.phyblkrdmbr),0) delta_phyblkrdmbr
FROM snaps s
, fs
WHERE fs.snap_id IN(s.start_snap_id,s.end_snap_id)
AND fs.dbid = s.dbid
AND fs.instance_number = s.instance_number
)
, fileio AS
(
SELECT snap_date
, dbid
, instance_number
, block_size
, file_type
, ROUND(((delta_singleblkrds + (delta_phyblkrd - delta_singleblkrds)) * block_size) / (1024*1024*1024),2) read_volume_gb
, ROUND(delta_phyblkwrt * block_size / (1024*1024*1024),2) write_volume_gb
, delta_phyrds total_physical_reads
, delta_singleblkrds total_single_block_reads
, delta_multiblkreads total_multi_block_reads
, delta_phywrts physical_writes
, delta_phyblkrd physical_blocks_read
, delta_phyblkwrt physical_blocks_written
, delta_phyblkrdmbr physical_blocks_read_by_mbr
, delta_readtim total_read_time
, delta_singleblkrdtim single_block_read_time
, (delta_readtim - delta_singleblkrdtim) multi_block_read_time
, delta_writetim
, (CASE WHEN delta_multiblkreads = 0
THEN NULL
ELSE ROUND((delta_phyblkrdmbr / delta_multiblkreads),2)
END) average_blocks_read_per_mbr
, (CASE WHEN delta_singleblkrds = 0
THEN NULL
ELSE ROUND((delta_singleblkrdtim / delta_singleblkrds) * 10,2)
END) single_block_read_time_ms
, (CASE WHEN delta_multiblkreads = 0
THEN NULL
ELSE ROUND(((delta_readtim - delta_singleblkrdtim) / delta_multiblkreads) * 10,2)
END) avg_multi_block_read_time_ms
, (CASE WHEN delta_singleblkrdtim = 0
THEN NULL
ELSE ROUND(((delta_singleblkrds * block_size) / (1024 * 1024) / delta_singleblkrdtim * 100),2)
END) single_blk_read_thrput_mbps
, (CASE WHEN (delta_readtim - delta_singleblkrdtim) = 0
THEN NULL
ELSE ROUND(((delta_multiblkreads * block_size) / (1024 * 1024) / (delta_readtim - delta_singleblkrdtim) * 100),2)
END) multi_blk_read_thrput_mbps
, delta_time
FROM calc_deltas
WHERE rn = 1
)
SELECT snap_date
, dbid
, instance_number
, block_size
, file_type
, SUM(read_volume_gb) read_volume_gb
, SUM(write_volume_gb) write_volume_gb
, ROUND(SUM(CASE WHEN NVL(total_read_time,0) != 0
THEN (total_physical_reads / total_read_time)
END
)
,2) read_iops
, ROUND(SUM(CASE WHEN NVL(delta_writetim,0) != 0
THEN (physical_writes / delta_writetim)
END
)
,2) write_iops
, SUM(total_physical_reads) total_physical_reads
, SUM(total_single_block_reads) total_single_block_reads
, SUM(total_multi_block_reads) total_multi_block_reads
, SUM(physical_writes) physical_writes
, SUM(physical_blocks_read) physical_blocks_read
, SUM(physical_blocks_written) physical_blocks_written
, SUM(physical_blocks_read_by_mbr) physical_blocks_read_by_mbr
, SUM(total_read_time) total_read_time
, SUM(single_block_read_time) single_block_read_time
, SUM(multi_block_read_time) multi_block_read_time
, SUM(delta_writetim) delta_writetim
, ROUND(AVG(average_blocks_read_per_mbr),2) average_blocks_read_per_mbr
, ROUND(AVG(single_block_read_time_ms),2) single_block_read_time_ms
, ROUND(AVG(avg_multi_block_read_time_ms),2) avg_multi_block_read_time_ms
, SUM(single_blk_read_thrput_mbps) single_blk_read_thrput_mbps
, SUM(multi_blk_read_thrput_mbps) multi_blk_read_thrput_mbps
FROM fileio
GROUP BY snap_date
, dbid
, instance_number
, block_size
, file_type
ORDER BY 4,5,1,6 DESC NULLS LAST
/
