Database Buffer Cache
Hi, in this post, i´m going to explain how to tune Oracle database buffer cache.
It is a SGA memory portion that holds some data blocks, is usually the largest structure within the SGA. All SGA are shared with all database users, so if any user needs any data holds in buffer cache he can read them directly instead of reading disk.
The goal is to minimize the number of reads that an instance needs to perform from disks.. Blocks in database buffer cache works with LRU (Least Recently Used) algorithm which means, that blocks most used will be in memory, and least used will get out.
Of course, if you are using automatic memory management (AMM) you don´t need to set it because this Oracle feature does it for you.
When a oracle server process need some block, it first searches in the Buffer cache, if this block was not find in buffer cache server process get it from disk and load it into buffer cache as MRU (Most Recently Used)... Every time any server process fails to find a block in buffer cache is called a "cache miss" and bring down buffer cache hint ratio. Definitely, we want to maintain this cache above 90%.
The LRU Algorithm and Full Table Scans
When a user process is doing a full table scan, it reads the blocks of the table into buffers and puts them into end of the queue to be the first block to leave (instead of the MRU end). This is because a fully scanned table is probably used only this time and it will full the cache, so the blocks should be moved out quickly to leave more frequently used blocks in the cache. (Unless you do a ALTER TABLE
Obviously full table scans will decrease buffer cache hit ratio....
Size of the database buffer cache
A standard block size is defined in DB_BLOCK_SIZE initialization parameter. DB_CACHE_SIZE parameter defines database buffer cache size for all Tablespaces whom block setup is as default, but if you have a tablespace defined with a block size different you should to define a specific parameter for this tablespace (for exemple for a tablespace with a 8k block you should define DB_8K_CACHE_SIZE parameter)
To see tablespace blocks size:
SQL> select TABLESPACE_NAME, BLOCK_SIZE from dba_tablespaces; TABLESPACE_NAME BLOCK_SIZE ------------------------------ ---------- SYSTEM 8192 SYSAUX 8192 UNDOTBS1 8192 TEMP 8192 USERS 8192 WCPVM_IAS_OPSS 8192 WCPVM_IAS_PORTLET 8192 WCPVM_IAS_WEBCENTER 8192 WCPVM_IAS_DISCUSS 8192 WCPVM_STB 8192 WCPVM_IAS_TEMP 8192 WCPVM_IAU 8192 WCPVM_MDS 8192 WCPVM_OCS 8192 WCPVM_OCS_TEMP 8192 WCPVM_IAS_ACTIVITY 8192
SQL> show parameter DB_BLOCK_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
With this query we can see Cache hist Ratio, as we say if it is below 90% it means a bad database performace...
SET PAGESIZE 60 SET LINESIZE 300 SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads' /
SQL> SET PAGESIZE 60
SET LINESIZE 300
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur,
v$sysstat con,
v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/SQL> SQL> SQL> 2 3 4 5 6 7 8
Cache Hit Ratio
---------------
95.64
To start using V$DB_CACHE_ADVICE view, we had to check first if db_cache_advice parameter is set to on, this parameter control statistics gathering about database buffer cache used for predicting behavior with different cache sizes...
so:
SQL> set linesize 190 SQL> show parameter db_cache_advice NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ db_cache_advice string ONIt is on (by default), so we can query V$DB_CACHE_ADVICE..
Below is a graphical representation of this view, as you can see, the more buffer assigned to database buffer cache, the fewer physical reads database needs to do. But keep in mind this is a predicting behavior, not a fact.
SQL> COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 99,999,999,999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads
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';SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- -----------------------
96 11,814 1.07 273,396,597,945
192 23,628 1.06 271,095,709,267
288 35,442 1.05 269,215,692,032
384 47,256 1.05 267,280,304,623
480 59,070 1.04 266,514,403,094
576 70,884 1.04 265,790,696,842
672 82,698 1.04 264,978,550,976
768 94,512 1.03 263,640,085,415
864 106,326 1.02 261,485,459,270
960 118,140 1.01 258,608,405,584
1,056 129,954 1.00 255,216,224,847 Current Size
1,152 141,768 .99 251,538,683,240
1,248 153,582 .97 247,825,490,772
1,344 165,396 .96 243,908,715,196
1,440 177,210 .94 240,467,179,931
1,536 189,024 .93 237,233,733,279
1,632 200,838 .92 234,643,037,809
1,728 212,652 .91 232,594,822,480
1,824 224,466 .90 230,871,812,987
1,920 236,280 .90 228,872,915,219
When your database hit cache ratio is low, it now always means that you need to increase database buffer cache because some operations launched against database like full table scans (as explained above) or in some big OLTP databases many rows are accessed only once, it has no sense to resize this buffer...
To query how many blocks from witch objects are allocated just now in our database buffer cache
set linesize 190 COLUMN object_name FORMAT A40 COLUMN number_of_blocks FORMAT 999,999,999,999 SELECT o.object_name, o.OBJECT_TYPE, COUNT(*) number_of_blocks FROM DBA_OBJECTS o, V$BH bh WHERE o.data_object_id = bh.OBJD AND o.owner != 'SYS' GROUP BY o.object_Name, o.OBJECT_TYPE ORDER BY COUNT(*);
This are top 6 objects allocated in my buffer cache:
SQL> set linesize 190 COLUMN object_name FORMAT A40 COLUMN number_of_blocks FORMAT 999,999,999,999 SELECT o.object_name, o.OBJECT_TYPE, COUNT(*) number_of_blocks FROM DBA_OBJECTS o, V$BH bh WHERE o.data_object_id = bh.OBJD AND o.owner != 'SYS' GROUP BY o.object_Name, o.OBJECT_TYPE ORDER BY COUNT(*); ..................................................................................... ..................................................................................... OBJECT_NAME OBJECT_TYPE NUMBER_OF_BLOCKS ---------------------------------------- ----------------------- ---------------- IDX_ATTR_NAME INDEX 1,147 JPS_ATTRS TABLE 1,428 MDS_ATTRIBUTES_U2 INDEX 1,942 MDS_ATTRIBUTES TABLE 2,039
so these are queries selecting last table in the list (MDS_ATTRIBUTES)
SQL> select SQL_ID, SQL_TEXT, COMMAND_TYPE from dba_hist_sqltext where SQL_TEXT like '%MDS_ATTRIBUTES%'; SQL_ID SQL_TEXT COMMAND_TYPE ------------- -------------------------------------------------------------------------------- ------------ fy1zcagmykq67 select PATH.PATH_FULLNAME, PATH.PATH_TYPE, PATH.PATH_GUID, PATH.PATH_DOC_ELEM_NS 3 fb438drcnt9k5 select PATH_FULLNAME, PATH_DOC_ELEM_NSURI, PATH_DOC_ELEM_NAME, PATH_GUID, NS_URI 3 auafcz7927n4v SELECT COMP_SEQ, COMP_LEVEL, COMP_PREFIX, COMP_LOCALNAME, COMP_VALUE, ATT_PREFIX 3 2wn4tjx3g9qfz insert into MDS_ATTRIBUTES(ATT_CONTENTID,ATT_COMP_SEQ,ATT_SEQ,ATT_NSID,ATT_PREFI 2 g0av44hq62ny8 DELETE /*+ LEADING(MDS_PATHS) */ FROM MDS_ATTRIBUTES WHERE EXISTS (SELECT 7 9pbfu1dn18r0y select PATH_FULLNAME, PATH_DOC_ELEM_NSURI, PATH_DOC_ELEM_NAME, PATH_GUID, NS_URI 3 b6czxm1v4g8v4 select PATH_FULLNAME, PATH_DOC_ELEM_NSURI, PATH_DOC_ELEM_NAME, PATH_GUID, NS_URI 3
So, at this point you can try to optimize them by Active session query performance or to resize the buffer cache by:
alter system set db_cache_size = [cache_size]M scope=both sid='*';or if any of your tablespaces has a different BLOCK_SIZE check hit ratio for avery buffer this way:
SELECT name, physical_reads, db_block_gets, consistent_gets,
1 - (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;
alter system set db_cache_size = [cache_size]M scope=both sid='*';
Comments
Post a Comment