.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Thursday, May 19, 2005

v$filestat And Multiblock Read Statistics

More Updates: 19 May 2005, about 45 minutes later

  • Jonathan Lewis writes to point out that X$KCFIO does include multiblock statistic columns, but that the numbers are not consistent. So maybe later on I'll think about a methodology for determining what the cause of the inconsistency might be. But not now, because ...
  • I'm in the middle of an upgrade of my development instance to ASM. W00t, as the youngsters say. Otherwise I'd also be checking to see if my own math is consistent with the numbers in X$KCFIO. Actually, I don't see how they can be but it'll provide a diversion.
  • Another cause of the smaller block counts occurs to me -- that individual blocks are already in the buffer cache, hence they are not requested from disk and hence the number of blocks requested is below DFMRC. This is unlikely to be an issue on my (or any) data warehouse where parallel query is in use because direct reads are used (hence the buffer cache is not inspected), and the blocks are also less likely to be in the buffer cache due to the static nature of the data. I think this is highly unlikely to be the case with the Peoplesoft instance.
  • Happily though, the validation of the DFMRC was just a side effect -- what i was really looking for here was the average wait time. If the numbers in the X$KCFIO are not completely consistent then that may not be a problem. As long as some major class of read is not completely omitted, or the numbers on which the average wait are not all updated consistently, then the methodology may be viable. Can't test it right now though, of course.
  • I think I'm going to change the subtitle of my blog.
  • Note to self: get JL to check my tax returns next year. He's a sharp-eyed devil-for-detail. Thanks in advance, JL.

Update 19 May 2005 (Original posting below)

Jeff & Jan, thaks for the feedback there.

I'm thinking that some of the issues there may be due to extent sizing -- multiblock reads obviously won't fetch the full DFMRC on blocks if the extent is not that large, so I'm thinking of a query to breakout files according to extent size (that ain't going to be "performant", I'd guess). But because what we see from v$filestat is an average then numbers based on any file with lots of small extents (which may be more likely to get FTS and hence multiblock read requests) would be skewed by this.

The method might have limited application because of this -- in a data warehouse there's probably many tablespaces with only fact tables, therefore with only large extents. Not a problem, because those are the ones where the multiblock reads would be prevalent, of course.


Original Article 17 May 2005

So, the v$filestat view gives us some statistics on the number of disk reads and their duration, documented for 9i here. In 10g we have the same structure, with the enhancement that historical snapshots of v$filestat are preserved in DBA_HIST_FILESTATXS. It would be a pretty trivial task of course, to automate this collection in prior versions through DBMS_JOB. The v$filestat table is mostly based on X$KCFIO, by the way, but there do not appear to be any additional columns in X$KCFIO of interest here.

The view breaks out read statistics (and write, but I'm just interested in read right now) as follows:
  • PHYRDS: Number of physical reads done
  • PHYBLKRD: Number of physical blocks read
  • SINGLEBLKRDS: Number of single block reads
  • READTIM: Time (in hundredths of a second) spent doing reads
  • SINGLEBLKRDTIM: Cumulative single block read time (in hundredths of a second)
So here's my question: given that we have total read statistics and single-block read statistics available, can we use these as the basis for calculating multiblock read statistics? Logically, it would seem so, although maybe this would also include direct reads as well as the common or garden variety.

So, we might define the following:
  • MULTIBLKRDS: The number of physical reads in multiblock mode = (PHYRDS-SINGLEBLKRDS)
  • MULTIBLKBLKRD: The number of blocks physically read in multiblock mode = (PHYBLKRD-SINGLEBLKRDS) ... because a single block read by definition is a read of a single block.
  • BLKSPERMULTIBLKRD: The average number of blocks read for each multiblock read = MULTIBLKBLKRD/MULTIBLKRDS = (PHYBLKRD-SINGLEBLKRDS)/(PHYRDS-SINGLEBLKRDS)
  • MULTIBLKRDTIM: The total time spent in multiblock reads = (READTIM-SINGLEBLKRDTIM)
  • AVGMULTIBLKRDTIM: The average time taken per multiblock read = MULTIBLKRDS/MULTIBLKRDTIM = (PHYRDS-SINGLEBLKRDS)/(READTIM-SINGLEBLKRDTIM)
The tests that I've performed on my PC look pretty encouraging. I was looking in particular for BLKSPERMULTIBLKRD to be equivalent to DB_FILE_MULTIBLOCK_READ_COUNT (based on a single tablespace block size being used throughout the database), and that looked OK. Haven't had a chance to run it on a production database yet though.

Here's the final query I came up with:
select
file#,
PHYRDS-SINGLEBLKRDS MULTIBLKRDS,
PHYBLKRD-SINGLEBLKRDS MULTIBLKBLKRD,
(PHYBLKRD-SINGLEBLKRDS)/
Decode(PHYRDS-SINGLEBLKRDS,0,Null,PHYRDS-SINGLEBLKRDS)

BLKSPERMULTIBLKRD,
READTIM-SINGLEBLKRDTIM MULTIBLKRDTIM,
(PHYRDS-SINGLEBLKRDS)/
Decode(READTIM-SINGLEBLKRDTIM,0,null,READTIM-SINGLEBLKRDTIM)

AVGMULTIBLKRDTIM
from v$filestat
/


Perhaps you could hive it a try and see if it works for you. Feedback and comments are very welcome.

13 Comments:

At 10:50 AM, Blogger Jeff Hunter said...

This comment has been removed by a blog administrator.

 
At 10:53 AM, Blogger Jeff Hunter said...

I am seeing BLKSPERMULTIBLKRD in the range of 1 to my db_file_multiblock_read_count.
According to this,Oracle will read up to a maximum of db_file_multiblock_read_count blocks per I/O. If your BLKSPERMULTIBLKRD is consistently and considerably less than your db_file_multiblock_read_count, perhaps your db_file_multiblock_read_count is too high?

 
At 12:05 PM, Blogger David Aldridge said...

Do you mean that you see the DFMRC and the BLKSPERMULTIBLKRD being about the same, Jeff?

There's a few possible explanations if it wasn't, I think, aside from the possibility that DFMBRC is over-sized. For example you might have a funky extent size that is not an integer multiple of the multiblock read size.

Alternatively, the whole theory may be hooey of course ;)

 
At 12:06 PM, Blogger David Aldridge said...

I think that another explanation would be that the file's tablespace does not have the same block size as the system TS.

 
At 2:29 PM, Blogger Jeff Hunter said...

Do you mean that you see the DFMRC and the BLKSPERMULTIBLKRD being about the same, Jeff?

On the contrary. On the two dbs I ran it on (both development), the dfmrc was set at 16. Approximately 1/4 of my files were in the 1-4 range, 1/2 were in the 4-12 range, and 1/4 were in the 12-16 range.

 
At 2:30 PM, Blogger Jeff Hunter said...

...all Tablespaces are an 8k block size.

 
At 3:31 PM, Blogger jan van mourik said...

Ran this:
select BLKSPERMULTIBLKRD, count(*)
from (
select round((PHYBLKRD-SINGLEBLKRDS)/Decode(PHYRDS-SINGLEBLKRDS,0,Null,PHYRDS-SINGLEBLKRDS)) BLKSPERMULTIBLKRD
from v$filestat
) group by BLKSPERMULTIBLKRD
/

on a PeopleSoft Financials production database:

BLKSPERMULTIBLKRD COUNT(*)
1 156
2 6
3 1
4 3
5 15
6 21
7 63
8 17

On an nMarket caiso database:
BLKSPERMULTIBLKRD COUNT(*)
1 35
2 5
3 2
4 3
5 2
7 3
9 2
10 3
11 2
12 9
13 22
14 55
15 128
16 4

both 8k blocksize..

 
At 12:44 PM, Blogger jan van mourik said...

Ran another test on my play db..
First shutdown/startup...

Table used: PS_VCHR_ACCTG_LINE

show_space('PS_VCHR_ACCTG_LINE','JVMO')
Unformatted Blocks ......0
FS1 Blocks (0-25) ......0
FS2 Blocks (25-50) ......0
FS3 Blocks (50-75) ......0
FS4 Blocks (75-100)......0
Full Blocks ......108571
Total Blocks.............109568
Total Bytes..............897581056
Unused Blocks............466
Unused Bytes.............3817472
Last Used Ext FileId.....3
Last Used Ext BlockId....174216
Last Used Block..........558

Then ran a 10046 trace on
select sum(vat_trans_amt) from PS_VCHR_ACCTG_LINE;

Grepped out all the 'db file scattered read' from the trc file, resulted in 868 lines...
Summed the p3 values ==> 108571

Ran your query, and
MULTIBLKRDS => 868
MULTIBLKBLKRD => 108571
BLKSPERMULTIBLKRD => 125

Nice :-)

 
At 9:21 AM, Blogger David Aldridge said...

Nice indeed! Thanks Jan. Did the computed AVGMULTIBLKRDTIM look about right?

I'm going to try this on our production database to see what it comes up with there.

 
At 7:46 AM, Blogger jan van mourik said...

The AVGMULTIBLKRDTIM is giving me troubles.. can't square that one with my statspack report and such. For example, on the PS instance I get 18,20,21,22 for the system tablespace datafiles. In statspack I get 4.2, 4.2, 2.8, 2.9. Looks like they should be well under 5ms if i believe staspack..

 
At 7:47 AM, Blogger jan van mourik said...

PS. My statspack query:

select to_char(snap_time,'mm/dd/yyyy hh24:mi:ss') snaptime
, max(decode(event,'db file scattered read', nvl(wait_ms,0), null)) wait_ms_dbfscatrd
, max(decode(event,'db file sequential read',nvl(wait_ms,0), null)) wait_ms_dbfseqrd
, max(decode(event,'db file scattered read', nvl(waits,0), null)) waits_dbfscatrd
, max(decode(event,'db file sequential read',nvl(waits,0), null)) waits_dbfseqrd
from
(
select ps.snap_time
, event
, case
when (total_waits - lag_total_waits > 0)
then round(( (time_waited_micro - lag_time_waited_micro) / (total_waits - lag_total_waits)) / 1000)
else -1
end wait_ms
, (total_waits - lag_total_waits) waits
, (time_waited_micro - lag_time_waited_micro) time_waited
from (
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file sequential read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) - 1
)
union all
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file scattered read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) -1
)
order by event, snap_id
) a
, perfstat.stats$snapshot ss
, perfstat.stats$snapshot ps
where a.lag_snap_id = ps.snap_id
and a.snap_id = ss.snap_id
and a.lag_total_waits != a.total_waits
and a.event = a.lag_event
order by a.snap_id, event
)
group by snap_time
;

 
At 8:10 AM, Blogger David Aldridge said...

Hmmm. How do the singleblock numbers from the v$filestat view compare with the sequential read numbers from statpack, Jan? I'm thinking that if they don't correlate then this modified view has little hope of correlating either.

The other thing to consider is that SYSTEM may just be a funky tablespace to use. Just a thought

 
At 3:45 PM, Blogger jan van mourik said...

David, it all squares nicely when I use this calculation in your query:

, (SINGLEBLKRDTIM/SINGLEBLKRDS)*10 AVGSINGLEBLKRDTIM
, (Decode(READTIM-SINGLEBLKRDTIM,0,null,READTIM-SINGLEBLKRDTIM) / (PHYRDS-SINGLEBLKRDS))*10 AVGMULTIBLKRDTIM
, (Decode(READTIM,0,null,READTIM) / PHYRDS) * 10 AVGRDTIM


perfstat tablespace IO Stats
tbs Reads Av Rd(ms)
USERS01 3,080 64.8

From the query:
PHYRDS AVGRDTIM
3080 64.7597402597402597402597402597402597403

jan

 

Post a Comment

<< Home