Re: table with keep as buffer pool see much more physical reads than the number of blocks in the table

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 11 Nov 2007 17:57:50 -0000


Sorry about the delay in replying, I've been a bit busy.


And that's the
point, if KEEP buffer is super large to contain all the blocks in the
tables, the number of physical read should be equal to the blocks in
the tables


Not necessarily; that's the way you WANT it to be, not the way is has to be.

It may be "obvious" that that's the way it ought to be - so the next thing
to do is to find out why it doesn't seem to work that way.

You've added the fact that you can see 'free' buffers in this pool.
Did you check whether they were free buffers that had never been
used, or were they free buffers that had once held blocks from the
two tables ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


----- Original Message ----- From: "qihua wu" <staywithpin@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, November 06, 2007 11:55 PM
Subject: Re: table with keep as buffer pool see much more physical reads than the number of blocks in the table


Hi,

From v$buffer_pool_statistics, I can see that the number of physical
read is much more than the number of total blocks  in the two
tables(with Keep as default buffer, only two tables use keep), and
from v$bh I can see that there are many buffer block with "free"
status which means many buffers in KEEP are very used. And that's the
point, if KEEP buffer is super large to contain all the blocks in the
tables, the number of physical read should be equal to the blocks in
the tables


--
//www.freelists.org/webpage/oracle-l


Other related posts: