Buffer pool size with oracle advisory views
With the following query, you can analyse the size of Buffer pool in oracle instance memory. you can get all info about its usage, size, keep objects. Following view help to analyze the buffer pool size needed for the database.
Checked the Buffer Pool size:
SELECT SUBSTR(NAME, 0, 22) NAME, SUBSTR(display_value, 0,10) VALUE
FROM v$parameter WHERE NAME IN ('db_keep_cache_size', 'db_recycle_cache_size', 'db_cache_size');
Checked the advisory for Buffer Pool Size:
select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3,estd_physical_reads c4
from v$db_cache_advice
where name = 'DEFAULT'
and block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
and advice_status = 'ON';
Output :- C1 C2 C3 C4 --- ------ -------- ---------- ---------- 992 122698 1,5039 5,0992E+10 1984 245396 1,1733 3,9781E+10 2976 368094 1,0719 3,6342E+10 3968 490792 1,0333 3,5033E+10 4960 613490 1,0153 3,4424E+10 5952 736188 1,0056 3,4096E+10 6944 858886 1,0019 3,3970E+10 7936 981584 1,0007 3,3930E+10 8928 1104282 1,0003 3,3916E+10 9920 1226980 1 3,3906E+10 ---it show the current size of buffer cache 10000 1236875 1 3,3906E+10 10912 1349678 ,9998 3,3900E+10 11904 1472376 ,9997 3,3894E+10 12896 1595074 ,9995 3,3890E+10 13888 1717772 ,9994 3,3885E+10 14880 1840470 ,999 3,3871E+10 15872 1963168 ,9986 3,3859E+10 16864 2085866 ,9981 3,3840E+10 17856 2208564 ,9969 3,3801E+10 18848 2331262 ,9941 3,3705E+10 19840 2453960 ,9634 3,2664E+10 --it show the double.
Checked the usage of Buffer Pool:
Show parameter block_size; ---use the block size parameter in below query.
SELECT DECODE(kcbwbpd.bp_name, 'DEFAULT', 'db_cache_size', 'RECYCLE',
'db_recycle_cache_size', 'KEEP', 'db_keep_cache_size')
buffer_pool_name, ROUND((COUNT(*)*:block_size)/(1024*1024),2) USED_MB
FROM x$kcbwds kcbwds, x$kcbwbpd kcbwbpd , x$bh bh
WHERE kcbwds.set_id >= kcbwbpd.bp_lo_sid
AND kcbwds.set_id <= kcbwbpd.bp_hi_sid
AND kcbwbpd.bp_size != 0
AND kcbwds.addr = bh.set_ds
AND bh.state !=0
GROUP BY kcbwbpd.bp_name;
Note: If you defined keep pool buffer cache (db_keep_cache_size) and it’s not used then you can also give that space to db_cache_size;
Checked the buffer cache loaded Object:
You can find the content of each pool in the Buffer Cache with the following query (connect as sys):
SELECT BUFF_POOL.NAME POOL, D.OWNER, O.NAME OBJECT, D.OBJECT_TYPE, SUM(CT) BLOCKS FROM (SELECT SET_DS, OBJ, COUNT(*) CT FROM X$BH GROUP BY SET_DS, OBJ) BH, OBJ$ O,X$KCBWDS KCBW,V$BUFFER_POOL BUFF_POOL, DBA_OBJECTS D
WHERE O.DATAOBJ# = BH.OBJ AND D.OBJECT_ID = O.DATAOBJ#
AND O.OWNER# > 0 AND BH.SET_DS = KCBW.ADDR
AND KCBW.SET_ID BETWEEN BUFF_POOL.LO_SETID AND
BUFF_POOL.HI_SETID AND BUFF_POOL.BUFFERS != 0
AND D.OWNER 'SYSTEM'
GROUP BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME
ORDER BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME;
OUTPUT:
POOL OWNER OBJECT OBJECT_TYPE BLOCKS ------- ----------- ------------------------ ----------- ------- DEFAULT MONITOR_LLU GROWTH_REPORT_DETAILS_U01 INDEX 2 DEFAULT MONITOR_LLU GROWTH_REPORT_USER_ASSOC TABLE 6 DEFAULT MONITOR_LLU MAILED_MAILS TABLE 1 DEFAULT MONITOR_LLU STATUS_CONFLICT_U01 INDEX 2 DEFAULT MONITOR_LLU STATUS_CONFLICT_USER_ASSOC TABLE 6 DEFAULT MONITOR_LLU STATUS_LLU_DETAILS_U01 INDEX 6 DEFAULT MONITOR_LLU SYS_C001736 INDEX 2 DEFAULT MONITOR_LLU TOAD_PLAN_TABLE TABLE 13 DEFAULT MONITOR_LLU TRANSITION TABLE 2
As you can see from the output the table MONITOR_LLU.TOAD_PLAN_TABLE has 13 blocks in the Buffer Cache. Querying the table extents following:
SQL> SELECT extent_id, file_id, block_id, bytes, blocks
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'TOAD_PLAN_TABLE'
ORDER BY EXTENT_ID ASC;
OUTPUT:
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS --------- ------- -------- ----- ---------- 0 1 26505 65536 8 1 1 24489 65536 8
But the table has 16 blocks. Executing a full table scan to MONITOR_LLU.TOAD_PLAN_TABLE maybe more blocks will be cached.
SELECT * FROM MONITOR_LLU.TOAD_PLAN_TABLE;
SELECT BUFF_POOL.NAME POOL, D.OWNER, O.NAME OBJECT, D.OBJECT_TYPE, SUM(CT) BLOCKS
FROM (SELECT SET_DS, OBJ, COUNT(*) CT FROM X$BH GROUP BY SET_DS, OBJ) BH, OBJ$ O,X$KCBWDS
KCBW,V$BUFFER_POOL BUFF_POOL, DBA_OBJECTS D
WHERE O.DATAOBJ# = BH.OBJ AND D.OBJECT_ID = O.DATAOBJ#
AND O.OWNER# > 0 AND BH.SET_DS = KCBW.ADDR
AND KCBW.SET_ID BETWEEN BUFF_POOL.LO_SETID AND
BUFF_POOL.HI_SETID AND BUFF_POOL.BUFFERS != 0
AND O.NAME = 'TOAD_PLAN_TABLE'
GROUP BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME
ORDER BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME;
OUTPUT:-
POOL OWNER OBJECT OBJECT_TYPE BLOCKS ------- ----------- --------------- ----------- ------ DEFAULT MONITOR_LLU TOAD_PLAN_TABLE TABLE 15
