Home > Oracle > Oracle – tuning your buffer cache

Oracle – tuning your buffer cache

In Oracle 11g, you have the option of using Automatic Memory Management by setting only the memory_target, memory_max_target parameters. However, there are times when you want to set a certain minimum size for the buffer cache when the database is restarted. Use the query below to find the current buffer cache usage and what the optimal amount may be.

column c1 heading ‘Cache Size (m)’ format 999,999,999,999
column c2 heading ‘Buffers’ format 999,999,999
column c3 heading ‘Estd Phys|Read Factor’ format 999.90
column c4 heading ‘Estd Phys| Reads’ format 999,999,999

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’;

Estd Phys Estd Phys
Cache Size (m) Buffers Read Factor Reads
—————- ———— ———– ————
1,488 174,840 1.69 51,500,844
2,976 349,680 1.51 46,049,280
4,464 524,520 1.40 42,564,190
5,952 699,360 1.31 40,032,442
7,440 874,200 1.25 38,053,847
8,928 1,049,040 1.19 36,248,974
10,416 1,223,880 1.14 34,784,904
11,904 1,398,720 1.10 33,456,226
13,392 1,573,560 1.04 31,863,882
14,880 1,748,400 1.00 30,515,295
14,884 1,748,870 1.00 30,511,825
16,368 1,923,240 .96 29,230,516
17,856 2,098,080 .92 27,997,396
19,344 2,272,920 .88 26,923,627
20,832 2,447,760 .85 25,968,141
22,320 2,622,600 .82 25,038,758
23,808 2,797,440 .79 24,145,979
25,296 2,972,280 .76 23,232,426
26,784 3,147,120 .73 22,166,922
28,272 3,321,960 .70 21,294,246
29,760 3,496,800 .66 20,193,450

21 rows selected.

In reading the output, the 1.0 read factor is currently where your cache has been tuned to. If you want to increase it, the “Estd Phys Reads” will drop, but you’ll have to have plenty of physical memory to accommodate this. To do this, set the db_cache_size = 14G.

Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: