Init.ora parameters affecting the library cache

• DB_FILE_MULTIBLOCK_READ_COUNT

db_file_multiblock_read_count is only applicable for tables/indexes that are full scanned. This parameter controls how much data Oracle thinks it can retrieve from the disks in a single trip (during a table/index scan). I/O chunk size for Oracle 9i is 1MB.

DB_FILE_MULTIBLOCK_READ_COUNT = I/0 chunk size / db_block_size

• OPTIMIZER_INDEX_COST_ADJ

This initialization parameter is a percentage value representing a comparison between the relative cost of physical I/O requests for indexed access and full table-scans. The default value of 100 indicates to the cost-based optimizer that indexed access is 100% as costly (i.e., equally costly) as FULL table scan access. Usually it’s around 20-50 for an OLTP system. The smaller the value, the cheaper the cost of index access. I usually start with 20. Query to suggest its value:

col c1 heading 'Average Waits for|Full Scan Read I/O'        format 9999.999
col c2 heading 'Average Waits for|Index Read I/O'            format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select a.average_wait c1,
b.average_wait c2,
   a.total_waits /(a.total_waits + b.total_waits)  c3,
   b.total_waits /(a.total_waits + b.total_waits)  c4,
   (b.average_wait / a.average_wait)*100   c5
from v$system_event  a,
   v$system_event  b
where a.event = 'db file scattered read'
and b.event = 'db file sequential read';

(avg. wait of db file sequential read / avg. wait of db file sequential read) * 100 = OPTIMIZER_INDEX_COST_ADJ

• OPTIMIZER_INDEX_CACHING

It should be set to 90. The default value of 0 indicates to the CBO that 0% of database blocks accessed using indexed access can be expected to be found in the Buffer Cache of the Oracle SGA.  This implies that all index accesses will require a physical read from the I/O subsystem for every logical read from the Buffer Cache, also known as a 0% hit ratio on the Buffer Cache.

• OPTIMIZER_MODE

It is recommended to set to “first_rows” in OLTP systems.

• SHARED_POOL_RESERVED_SIZE

We can reserve memory within the shared pool to satisfy large allocations during operations such as PL/SQL and trigger compilation. Smaller objects will not fragment this reserved area of the shared pool. This helps to ensure that the Shared Pool Reserved Area has large contiguous chunks of memory.

Set the initial value to min 10% and maksimum %30 of the SHARED_POOL_SIZE

• SESSION_CACHED_CURSORS

The CACHE_CNT (‘session cursor cache hits’) of a session should be compared to the PARSE_CNT (‘parse count (total)’), if the difference is high, consider increasing the SESSION_CACHED_CURSORS parameter.

• CURSOR_SPACE_FOR_TIME

Lets us use more space for cursors in order to save time, is currently not enabled. It affects both the shared SQL area and the client's private SQL area. When set to TRUE, then shared SQL areas are kept pinned in the shared pool. It is set to FALSE by default.

• SMALL_TABLE_THRESHOLD

This parameter controls the number of blocks from one table that will be stored at the most-recently-used end of the buffer cache before the remainder of blocks are held at the least-recently-used end of the list. If batch processing is repeatedly performing full table scans for the same table(s) over and over again and that table is of moderate size, increasing this parameter can mean that it will be retained within the Oracle buffer cache for a longer period of time.

• DATABASE BUFFER CACHE SIZE

The Cache Hit Ratio shows how many blocks were already in memory (logical reads, which include "db block gets" for blocks you are using and "consistent gets" of original blocks from rollback segments that others are updating) versus how many blocks had to be read from disk ("physical reads").   Oracle recommends that this ratio be at least 90%.

Increase DB_BUFFER_CACHE according to the following formula;

Buffer cache(bytes) X DB_BLOCK_SIZE = DB_BUFFER_CACHE

If there is room for SGA to grow (that is, if SGA_MAX_SIZE is not reached), use ALTER SYSTEM to increase the value of DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE or DB_RECYCLE_CACHE_SIZE.

• CACHE / RECYCLE SETTINGS

Identify frequently used and rarely used data blocks. Cache frequently used blocks and discard rarely used blocks.

Use RECYCLE pool to cache data blocks that are rarely used in the application. Typically, this will be a small area in the SGA. Use KEEP pool to cache data blocks that are frequently used by the application. Typically, this will be big enough to store data blocks that we want to always keep in memory. Lookup tables are very good candidates for the KEEP pool.

DB_KEEP_CACHE_SIZE = <size of KEEP pool>
DB_RECYCLE_CACHE_SIZE = < size of RECYCLE pool>

Then monitor the buffer hit ratio in regular times to adjust the buffer pool initialization parameters.

If RECYCLE and KEEP pools are not allocated, rarely used statements keep allocating unnecessary buffer in the pool. While i was consulting a corporate customer on tuning, I observed too much heavy disk read sql statements, which have inactive statuses with high invalidations, allocated rather much buffer, whereas we were facing with free buffer issue in shared pool. If RECYCLE size was set, those inactive statements which have a less execution and high parse ratio with increasing invalidations would be unallocated out of buffer frequently unless they are running.

• SHARED POOL SIZE

If that ratio is less than 90%, shared pool size should be increased. 

Following statement calculates estimated shared_pool_size. Check the estimated size regularly.

select sum(a.spspv) "Packages/Views", sum(a.spssql) "SQL Statements",
     sum(a.spsusr) "SQL Users", round((sum(a.spspv) + sum(a.spssql) +
          sum(a.spsusr)) * 2.5,-6)/1024/1024 "Estimated shared_pool_size[MB]"
from (select sum(sharable_mem) spspv, 0 spssql, 0 spsusr
               from v$db_object_cache
     union all
          select 0, sum(sharable_mem), 0 from v$sqlarea
               where executions > 5
     union all
          select 0, 0, sum(250 * users_opening) from v$sqlarea) a;

• EXECUTE TO PARSE RATIO

Execute to Parse ratio = 100 - (Parse count/ Executions * 100)

If you parse once and execute more, then the ratio will be near to 100%.
If you parse every time you execute, then the ratio will be near to 1%.

When the value is too low, it means the SQL is not sharable. Set CURSOR_SHARING to SIMILAR. Set SESSION_CACHED_CURSORS init parameter. Increase OPEN_CURSORS init parameter.

• PARSE CPU TO PARSE ELAPSED RATIO

Parse CPU means amount of CPU time used for parsing.

Parse Elapsed means amount of clock time used for parsing.

Parse Elapsed = Parse CPU + Wait time.

Parse CPU to Parse Elapsed ratio = 100 * parse CPU/ parse Elapsed.

Ideally Parse Elapsed must be equal to Parse CPU, i.e., only CPU time is used for parsing. In that case the ratio is 100%. If wait time is more then the ratio will be less.

Low Value for this ratio is an indicator of latching problem.

Solution: Use bind variables often.

• GET HIT RATIO

Get Hit Ratio = GETS/GETHITS

GETS = Statements that require parsing

GETHITS = Parsed statements already in the memory.

Following statement displays all hit ratios.

SELECT cur.inst_id, 'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Value"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=1
and cur.inst_id=1
and con.inst_id=1
union all
SELECT cur.inst_id,'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Buffer Cache Hit Ratio"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=2
and cur.inst_id=2
and con.inst_id=2
union
SELECT inst_id, 'Library Cache Hit Ratio ' "Ratio", to_char(Round(sum(pins) / (sum(pins)+sum(reloads)) * 100,2)) "Library Cache Hit Ratio"
FROM gv$librarycache group by inst_id
union
SELECT inst_id,'Dictionary Cache Hit Ratio ' "Ratio", to_char(ROUND ((1 - (SUM (getmisses) / SUM (gets))) * 100, 2)) "Percentage"
FROM gv$rowcache group by inst_id
union
Select inst_id, 'Get Hit Ratio ' "Ratio",to_char(round((sum(GETHITRATIO))*100,2)) "Get Hit"--, round((sum(PINHITRATIO))*100,2)"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
Select inst_id, 'Pin Hit Ratio ' "Ratio", to_char(round((sum(PINHITRATIO))*100,2))"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
select a.inst_id,'Soft-Parse Ratio ' "Ratio", to_char(round(100 * ((a.value - b.value) / a.value ),2)) "Soft-Parse Ratio"
from (select inst_id,value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'parse count (hard)') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Execute Parse Ratio ' "Ratio", to_char(round(100 - ((a.value / b.value)* 100),2)) "Execute Parse Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'execute count') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Parse CPU to Elapsed Ratio ' "Ratio", to_char(round((a.value / b.value)* 100,2)) "Parse CPU to Elapsed Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse time cpu') a,
(select inst_id, value from gv$sysstat where name like 'parse time elapsed') b
where a.inst_id = b.inst_id
union
Select a.inst_id,'Chained Row Ratio ' "Ratio", to_char(round((a.val/b.val)*100,2)) "Chained Row Ratio"
from (SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name = 'table fetch continued row' group by inst_id) a,
(SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch by rowid') group by inst_id) b
where a.inst_id = b.inst_id
union
Select inst_id,'Latch Hit Ratio ' "Ratio", to_char(round(((sum(gets) - sum(misses))/sum(gets))*100,2)) "Latch Hit Ratio"
from gv$latch
group by inst_id
/* Available from 10g
union
select inst_id, metric_name, to_char(value)
from gv$sysmetric
where metric_name in ( 'Database Wait Time Ratio', 'Database CPU Time Ratio')
and intsize_csec = (select max(intsize_csec) from gv$sysmetric)
order by inst_id;