FTS MB/s vs Filestat MB/s

Hi,

I was just trying to do some analysis yesterday on my employer's old 8i repository that they are still using for various applications. I noticed that the levels of FTS operations had increased 4-fold (in overall wait time) over the last few months, and was busy trying to see if this was related to excessive growth in the targets of those scans, or some problem with CBO statistics/code/indexing.

As part of my exercise, I decided to run some overview stats on how much IO throughput per sec. was related to the FTS operations as a whole.

To aid me in this, I took snapshot intervals of 15 minutes, and calculated the difference in "table scan blocks gotten" from v$sysstat, and "phyblkrd" from v$filestat.

I expected that phyblkrd would be significantly higher than the difference in "table scan blocks gotten", as it would include all the "sequential access blocks" against files by rowid as well as all the index scans etc...

Instead, what I am seeing is that the difference in the v$sysstat blocks for table scans is significantly higher than what I am seeing in v$filestat, and am puzzled as to why this can be so...

For isolated, short duration operations, I held the thought some of the "blocks gotten" within v$sysstat could be logical IO and factored into this statistic.

Anyone any ideas on this one?

For example... in 1 15 minute period, I am seeing the equivalent of 154 MB/s in terms of blocks gotten for FTS operations from v$sysstat.

38 MB/s is recorded against phyblkrd in v$filestat.

121 MB/s is recorded against logical IO (session logical reads via v$sysstat).

So, I'm wondering if we are seeing the FTS generate 38MB of physical reads, but the client is having to make multiple visits to the same blocks in cache (multiple fetches) to retrieve all the rows - thus accounting for the larger size of "blocks gotten" for the FTS...

- Tony.

why do n't u look at statspack report for the same period and look for the physical reads and buffer gets. That should clear your doubt.