More blocks in buffer cache than object on disk
From: Rich J <rich242j_at_gmail.com>
Date: Wed, 26 Feb 2020 15:45:05 -0600
Message-ID: <CAANsBX2HXT6C+GCa8pHUA3D54j86jY4DgxJMNv8Kf-5UAA3JKA_at_mail.gmail.com>
Hey all,
Date: Wed, 26 Feb 2020 15:45:05 -0600
Message-ID: <CAANsBX2HXT6C+GCa8pHUA3D54j86jY4DgxJMNv8Kf-5UAA3JKA_at_mail.gmail.com>
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,
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 26 2020 - 22:45:05 CET