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, 4 Nov 2007 19:19:37 -0000

I think Mark's comments are probably in the right area.
If you update the table in one session, then other sessions
are going to start creating read-consistent clones.  Do you
have a recycle pool ? If not, then the clones will end up in the Keep pool.

It's possible that if you generate too many clones of blocks
1 to 100 (say) then the "originals" of some other blocks have
to be kicked out of memory to make way for them - and then
get read back later.

You could check with a query like:

select file#, block#, count(*) from v$bh where objd = {data object id of table}
group by
        file#, block#
having count(*) > 1

to see how many copies there are of each block.

Or possibly:
select ct, count(*)
select file#, block#, count(*)  ct
from v$bh where objd = {data object id of table}
group by
 file#, block#
group by ct

To get a complete distribution pattern of how heavily
cloned the blocks are.

The queries might be a little brutal on your buffer cache
for several seconds, though.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

---- Original Message ----- -

a.       block cleanouts could cause some blocks to be updated (I think
that would be a worst case of doubling.)


Other related posts: