Buffer Pool size estimate with advisory.

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.