Thanks for the question, Gerardo.
Asked: July 02, 2017 - 2:51 am UTC
Last updated: July 05, 2017 - 12:34 am UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
I found the querie below on the network but I dont not understad why is substracting?
select
to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') "End snapshot time",
sum(after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS) "number of IOs",
trunc(10*sum(after.READTIM+after.WRITETIM-before.WRITETIM-before.READTIM)/ sum(1+after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS)) "ave IO time (ms)",
trunc((select value from v$parameter where name='db_block_size')*sum(after.PHYBLKRD+after.PHYBLKWRT-before.PHYBLKRD-before.PHYBLKWRT)/sum(1+after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS)) "ave IO size (bytes)"
from DBA_HIST_FILESTATXS before, DBA_HIST_FILESTATXS after,DBA_HIST_SNAPSHOT sn where after.file#=before.file# and after.snap_id=before.snap_id+1
and before.instance_number=after.instance_number and after.snap_id=sn.snap_id and after.instance_number=sn.instance_number
group by to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')
order by to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS');
and Connor said...
For many of the HIST tables, we are taking moment-in-time measurements of the data.
For example, at 9am when I take a snapshot, I might have had (since the database started) 1,534,232 physical reads against my USERS tablespace.
At 9:30am, when I take the next snapshot, that *total* will obviously be the same or higher. Let's say it is 1,634,111
What I will probably have an interest in is how many reads did I do *between* 9:00 and 9:30. So I must subtract the two (ie, 1,634,111 - 1,534,232 = 99,879 reads). Hence queries such as:
select after.SOME_METRIC - before.SOME_METRIC
from DBA_HIST_TABLE after,
DBA_HIST_TABLE before
where ...
is a very common type of query to see.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment