SQL query shows the benefits of Block Change Tracking

SQL query that shows the benefits of BCT

To see how much you benefit from having Block Change Tracking enabled, use this query to calculate the percentage of blocks read and the number of blocks backed up during incremental backups.

For results to show up, you need to have Block Change Tracking enabled and have performed an incremental level 0 backup followed by one or more incremental level 1 backups.

The query returns one row for each datafile backed up using RMAN.

select file#, avg(datafile_blocks) blocks,
avg(blocks_read) blocks_read,
avg(blocks_read/datafile_blocks)*100 pct_read,
avg(blocks) blocks_backed_up
from v$backup_datafile
where used_change_tracking='YES'
and incremental_level=1
group by file#
order by file#
;