Re: Physical Read Waits less than Physical Reads

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: charlottejanehammond@xxxxxxxxx
  • Date: Wed, 10 Nov 2010 12:22:55 -0800 (PST)

As Dion said, one reason for this "anomaly" is multiblock read. Oracle has been 
using incorrect terms for many years; "physical reads" means number of times of 
physical reads in some places and blocks which were physically read in some 
other places. A long time ago, I wrote a short note about it:

An easy way to check is something like this:

create table test as select * from dba_tables;
insert into test select * from test;
--repeat if you wish

--get SID for this session and in another session watch v$sess_io and 
v$session_event (only check 'db file % read')
--find the number of blocks not completely empty; I use the bottom script at

alter system flush buffer_cache;
select /*+ full(t) */ count(*) from test t;

V$sess_io should show physical_reads increase matching the blocks that have 
been read.

Yong Huang

> V$SESS_IO shows 200,000 physical  reads
> V$SESSION_EVENT shows < 100,000  total waits for “db file * read”  (and
> “read by other session”)  etc.
> Does that mean half my physical  reads didn’t wait at all?  How is that
> possible?  (I see this under high load - under low load number of 
> reads is approximately the same as the number of waits for the same SQL)
> I'd be very grateful if you could explain this observation to me! 
> (Platform is 10g on AIX)


Other related posts: