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

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_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> 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
>

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