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 PMSubject: 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