Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: table with keep as buffer pool see much more physical reads than the number of blocks in the table
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(*)
from
(
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.
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
> a. block cleanouts could cause some blocks to be updated (I think
> that would be a worst case of doubling.)
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Nov 04 2007 - 13:19:37 CST
![]() |
![]() |