Tech Day: Oracle Physical Reads on V$FILESTAT

When one says physical reads, does it really mean physical read?

Obviously not, in the case of working with an oracle database.

I've been working on compiling server statistics on our oracle database, and i hit upon the assessment of io usage. Thankfully, i've discovered a system table within the oracle database which seems to contain self described columns called readtim, writetim, phyblkrd, phyblkwrt and a whole other slew of columns which indicates physical reads and writes on each datafile component on the database.

I thought, "God sent, do i have my work cut out for me".

Upon closer query, i checked the documentation and it seems to confirm my assumptions of the names (the oracle documentations can be found in the official oracle website):

COLUMN - DESCRIPTION
PHYRDS - Number of physical reads done
PHYWRTS - Number of times DBWR is required to write
PHYBLKRD - Number of physical blocks read
PHYBLKWRT - Number of blocks written to disk; which may be the same as PHYWRTS if all writes are single blocks
READTIM - Time (in hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE
WRITETIM - Time (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE

Confirming my assumptions, i proceeded to collect and compile the data for proper io statistics evaluation and projection.

I pore over the numbers. I crunched them. I summed them up. I calculated, I analyzed, and finally, I conquered... NOT.

After going through loads of calculations, I find that the numbers dont seem right. I was ended up with a maximum io of 700MB/sec, which sounds suspiciously wrong. My PC at home has a 2 IDE 133 HDs and it takes loads of time to copy over a mere 300MB file. Asking me to believe that a io figure of 700MB/sec sounds incredulous. That would be like... Flash. I look up HD specs across the web to confirm this seemingly out of this world figure. Of course, If i had several top of the line SCSI disks, i suppose the io would be possible... but i just had to make sure.

And I just might have to possibly take a chance and make a fool out of myself.

I approached my supervisor, who also handles most of the network details. To confirm if this figure is remotely possible. I stood up. I approached, and I asked. Then finally, I squirmed.

Once a fool, always a fool, i suppose.

As you can probably tell, its not possible. Not even probable. The figures are most definitely defying the limits of our system. Unless a ghost popped up and suddenly decided to make things a hell of a lot faster, to save the company time and money.

I start hyperventilating. 2 month's statistics, and they might possibly go down the drain. I can't believe this. I have to get to the bottom of this. Owing to the my obsessive compulsive nature, I pore over the web. Read through documents, hoping something will explain this figure. I will not let 2 month's statistics go down the drain. I go through the official website. I messaged my friends, hoping against hope that I will be able to find a sane coworker Oracle DBA who can confirm the internal workings of the huge monster called an Oracle database.

I mean, physical reads are.. physical disk reads, right? IT norms usually refer to physical as disks, memory as real memory or cache, so the figures can't be wrong. And besides, oracle documentation says so. they say its "Physical" reads/writes. The only possible explanation is that the server is retrieving large chunks of free space from disk and passes them on as data.

Finally, after a gruelling day, I sat at my chair, resigned, and posted a message in the forum boards describing my situation. Hoping some oracle expert will explain this little conundrum of mine (and quell my ever growing fear and hyperventilation).

I got the answer this morning, from an Oracle employee. And im not very happy about it:

(Dear .. yada yada yada... bunch of formulas a bit of explanation... blah blah blah...)

Use OS specific tools when monitoring Disk I/O.An Oracle IO is a call to the Operating System to perform an IO operation and may not result in a physical disk IO .

(...yada yada.. thanks.. regard.. sincerely... blah blah blah)

Oh great. Talk about misleading. Physical doesnt really mean physical. Juuuust great. People, please take note, don't ever make this mistake. It will likely make you lose your job. Gawd. TWO MONTHS. io stats going down the drain!!!!

Now i have to figure out how to savage these statistics to make them usable. Otherwise, I may find myself bumming around the house all day, all week, really really soon.

ARGH!