Re: More blocks in buffer cache than object on disk

Thanks all for the responses. I was a bit hurried in my post, so I didn't elaborate more. This particular object, table F41021 for you JDE fans, gets hit by a job twice daily that I think recalculates numeric "bucket" columns and updates them whether the value changes or not. To add insult to injury, each of the millions of the 1-row updates is committed.

I understand that there will be CR blocks in the cache, but the number of them is what surprised me. In EM13, I have a custom metric that collects buffer cache contents roughly similar to the one I posted. I use this metric to chart out the buffer cache contents over time. It's interesting to visualize the effects that application changes (and database changes) have on the cache. It's also interesting to see how the contents change drastically between the business day and the nightly batch jobs (including the two that update the F41021 table).

At the time I checked this yesterday, the last update job had completed ~9 hours before. With other pressures on the buffer cache over the business day, I would have expected the blocks in the buffer cache for the F41021 table to start being flushed out, but they weren't, as blocks from other objects were taken instead. The reason I had mentioned "no lingering transactions" and the every-row-is-committed comment above is that there is very rarely long-running transactions that I thought would be the requirement for read consistency, and thus may be the cause of the seemingly high number of cached blocks for this particular table. The number of cached blocks for this table generally remains static. Whether those blocks are the same blocks at the same state over time, I do not know.

It feels like I'm missing some nugget of cache knowledge that I have forgotten since v7.3. That, and it looks like X$BH has changed (a lot?) since I looked at it last. It appears that last touch time and touch count have moved on to bigger and better things...assuming that algorithm is still used to manage the buffer cache in 12.1 (and up)?

Thanks,
Rich

On Wed, Feb 26, 2020 at 5:42 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> You might be interested to read an old note I wrote about caching and
> consistent read blocks:
> https://jonathanlewis.wordpress.com/2011/03/14/buffer-states/
>
> There's a hidden parameter _db_block_max_cr_dba default value 6, which is
> tries to limit the number of CR copies of a block that you will see in the
> buffer cache. You probably won't see many blocks getting that many CR
> copies (and you may see a few hot blocks getting far more) - but you will
> probably see quite a lot of blocks with more than one copy - typically one
> CU (current) and one or two CR (consistent read).
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Rich J <rich242j_at_gmail.com>
> Sent: 26 February 2020 21:45
> To: oracle-l_at_freelists.org
> Subject: More blocks in buffer cache than object on disk
>
> 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
>
>
>

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