Re: More blocks in buffer cache than object on disk

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