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

  • From: "qihua wu" <staywithpin@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 1 Nov 2007 11:25:57 +0800

Hi, everyone, the oracle I use is 10.2.0.2 on HPUX IA64

Our job ran very slow and from AWR report I can see that a table named
ATTRIBUTE_VALUE
consumes most of the physical IO (Physical read was more than 2.5M), as I
know the blocks the table has is only 0.3M(block size is 8K), so I created a
keep buffer and set the buffer pool of ATTRIBUTE_VALUE to "keep buffer". I
was doing this to hope that once ATTRIBUTE_VALUE is read into the keep
buffer, it will never be paged out so there the physical read will not
exceed 0.3M (the blocks the table has). I already set the size of the keep
buffer large enough (4G) to hold all the blocks.

But after that change, ATTRIBUTE_VALUE was still read physically much more
times than 0.3M (about 1.6M).  Shouldn't it be only 0.3M if the table was
"kept" in the buffer?

Other related posts: