This forum is currently locked. You can't register or post questions at this time. (read more)

File response time

All posts relating to Oracle database administration.

Moderator: Tim...

Posts: 2
Joined: Mon Sep 16, 2013 1:55 pm
Location: Buenos Aires, Argentina

File response time

Postby svampap » Thu Mar 27, 2014 6:36 pm

Hi Tim,

I'm trying to establish a relationship between an Oracle Doc ID, a system view and file response times.

I was reading "How to Tell if the I/O of the Database is Slow (Doc ID 1275596.1)". This great doc explains many things, shows a table with expected response times and it says, in brief, that "A typical multi-block synchronous read of 64 x 8k blocks (512kB total) should have an average of, at most, 20 milliseconds before worrying about 'slow IO'. Smaller requests should be faster (10-20ms) whereas for larger requests, the elapsed time should be no more than 25ms".
Then I was playing with DBA_HIST_FILESTATXS and it contains, for example, the number of physical reads done and the time (in hundredths of a second) spent doing reads.
Then I thought, if I add all the time spent doing physical reads and dividing it by the sum of all physical reads done, I can get the average time spent to read a single block. We can imagine we have a system with a balance between multi-block and single-block reads, so I can multiply this average by 64 (standar 8kb block size * 64 = 512kb). Then I can get the average time spent to read 512kb from the disk.

I generated the following query:

Code: Select all

SELECT FILENAME "Name of the datafile",
         TSNAME "Name of the tablespace",
         BLOCK_SIZE "Block size of the datafile",
         SUM (PHYRDS) "# of physical reads done",
         SUM (PHYWRTS) "# times DBWR required to wrt",
         SUM (SINGLEBLKRDS) "of single block reads",
         SUM (READTIM * 10) "Time (ms) spent doing reads", --if the TIMED_STATISTICS parameter is true; 0 if TIMED_STATISTICS is false
         ROUND ( ( ( (SUM (READTIM) / SUM (PHYRDS))) * 10), 2) "Avg time (ms) to read 8kb",
         ROUND ( ( ( (SUM (READTIM) / SUM (PHYRDS)) * 64) * 10), 2) "Avg time (ms) to read 512kb", --See metalink note "How to Tell if the IO of the Database is Slow [ID 1275596.1]"
         SUM (WRITETIM * 10) "Time (ms) spent doing writes", --if the TIMED_STATISTICS parameter is true; 0 if TIMED_STATISTICS is false
         SUM (SINGLEBLKRDTIM * 10) "Sum single blocks read (ms)", --Cumulative single block read time (in hundredths of a second)
         SUM (PHYBLKRD) "# of physical blocks read",
         SUM (PHYBLKWRT) "# of blocks written to disk", --which may be the same as PHYWRTS if all writes are single blocks
         SUM (WAIT_COUNT) "Wait Count",
         SUM (TIME) "Wait Time"
   WHERE 1 = 1

I multiply some columns by ten because the view shows values in hundreds of seconds and I want it in milliseconds.

Do you think this reasoning could be right? Do you think the Oracle Doc ID is talking about 20ms to take blocks direct from the disk or maybe this time includes cached values?
A typical 15K RPM disk has a 3-12ms seek time. If all of this 64 blocks are in different tracks into the disk, then the minimun seek time I need to take them is 192ms (64 * 3ms), so I can't understand how to get 20ms for 512kb, maybe if they are all contiguous... However the sequencial read time expected it's not very different and I can't imagine 512kb of sequencial read all together.


Thanks in advance!



Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: File response time

Postby Tim... » Fri Mar 28, 2014 9:02 am


Interesting question!

The difficulty when discussing storage and read times is it is confused by caching.

- The obvious cache is the buffer cache in the SGA. This will make single block reads quicker, but it might confuse multiblock reads (db file scatter reads) because a multiblock read may be broken up into smaller reads if some of the blocks are in the cache. Worst case scenario, ever other block is in cache and the multiblock read becomes single block reads, half of which come from cache. That is unless you are on later versions of the database that can perform "direct path reads" directly into the PGA, missing out the buffer cache.

- The file system cache. If you are using a file system, rather than ASM, most OSes will use any spare RAM as a file system cache to improve I/O performance. As a result, a physical read from Oracle may really be a read from the file system cache on the OS, not a read from disks.

- Storage cache. Your enterprise storage will undoubtedly have a caching layer in front of it, so commonly used blocks will be fed from there.

- Disk cache. Your disks will probably have a very small amount of cache to, so occasionally some reads might come from that.

With this in mind, the only thing we know for sure is a logical read comes from the buffer cache! Any physical read could come from the spinning disk or one of the layers of cache.

Now rules of thumb are probably all lies, but this is the rule of thumb you will often see quoted, assuming we are not talking SSD/Flash...

Physical I/O below 5ms - It's come from memory somewhere.
Physical I/O below 10ms - You might have got it from disk or memory depending on the nature of the read.
Physical I/O above 10ms - You probably got it from disk.

This is all complicated further because your multiblock reads could get very big, so the comparison between a regular multiblock read and a huge one becomes unfair. ... ead-count/

On top of this, averages are kind-of evil. One or two outliers (fast or slow) can have a profound impact on the average. You really need to consider the minimum and maximum times for those events also, to make sure the average is not being skewed by a few freak events.

I realise I've not answered your question, just given you a bunch of other stuff to think about. The reason for this is my answer is probably, I don't know. :) Storage is a very complicated beast.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests