Re: More blocks in buffer cache than object on disk

Hi,

Ahmed is probably referring to the status or dirty columns of v$bh, both are useful in seeing what’s going on. It’s worth doing an aggregate query to see what the distribution is like.

“Lingering transactions” aren’t really a big problem here with Oracle, if the buffers are dirty then they can still be written to disk if they don’t get touched for a while and if Oracle wants to use the cache space for something else.

It would be useful to see how your object is being updated.

Hope this helps,
Andy

On Wed, 26 Feb 2020 at 23:30, Ahmed Aangour <ahmed.aangour_at_gmail.com> wrote:

> Hi,
>
> Oracle creates copy of blocks for each consistent read.
> There is a column in v$bh (I don't remember its name) saying if the buffer
> is for a consistent read or for a current read. So it's not surprising to
> get more buffers for one table than the number of blocks it contains.
>
> Cheers,
> Ahmed
>
> Le mer. 26 févr. 2020 à 22:46, Rich J <rich242j_at_gmail.com> a écrit :
>
>> Hey all,
>>
>> In one of our 12.1.0.2 DBs, there's some discussion involving a table
>> and/or its indexes. In gathering info about the table, I see that while
>> the table size is 683264 blocks (from DBA_SEGMENTS), there appears to be
>> 1071098 blocks in the buffer cache for that table, according to V$BH.
>> That's ~8.4GB cache for a ~5.3GB table (8K block size). Seems....excessive.
>>
>> Maybe my buffer cache query is at fault:
>>
>> SELECT
>> dbo.owner||'.'||dbo.object_name object_name,
>> bh.objd,
>> COUNT(*)*8192/1024/1024 size_mb,
>> count(*) size_blocks
>> FROM
>> dba_objects DBO,
>> v$bh bh
>> WHERE
>> dbo.data_object_id = bh.objd
>> and dbo.object_name = 'MYTABLE'
>> GROUP BY
>> dbo.owner,
>> dbo.object_name,
>> bh.objd;
>>
>> If it matters, this instance is on AIX, has an SGA of ~130GB, non-ASM,
>> non-AMM, and is a primary for ADG. It's been up for about 40 days. The
>> ERP application that uses it gets bounced weekly due to middle tier Java
>> entropy (so, no lingering transactions).
>>
>> Thoughts?
>>
>> Thanks,
>> Rich
>>
>

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