Re: More blocks in buffer cache than object on disk

And, of course, my query is looking at V$bh, and not X$bh.

Ever have one of those days? I'm having one of those years...

On Thu, Feb 27, 2020 at 8:12 AM Rich J <rich242j_at_gmail.com> wrote:

> 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