Cache optimization Index and summary of buffer cache experiment 10:buffer

The title blew a bit big, buffer cache in the different situation in fact, in front of each small piece have, did not repeat the paste.

1.buffer Cache Size:

such as 16cpu,32g memory:

Initial allocation:

SGA_TARGET=16G--Half of memory

Buffer cache=12g---SGA 40%, can be set to 70%-80%--conservative point can also be set to 10G, the latter can be based on system operation combined with buffer pool advisory modified.

shared_pool=3g

When the parameter: Statistics_level is typical or all, or the Db_cache_advice parameter is set to On,oracle, the optimal buffer cache size for the current instance is automatically estimated based on the system load-through V$db_ Cache_advice or ASR report: Buffer pool Advisory column. You can then set a reasonable value based on the recommended size combined with the current system memory usage.

The following is my virtual machine environment data:--size_for_estimate Unit is M, the system is currently buffer_cache size 32M.

sys@ Bys3>select size_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_reads from V$db _cache_advice;

Size_for_estimate buffers_for_estimate Estd_physical_read_factor estd_physical_reads

----------------- -------------------- ------------------------- -------------------

4 496 2.4576 527548

8 992 2.3653 507728

12 1488 2.2055 473442

16 1984 1.887 405072

20 2480 1.3526 290349

24 2976 1.1332 243258

28 3472 1.0613 227818

32 3968 1 214661

36 4464.9342 200530

40 4960.8871 190428

44 5456.7987 171447

48 5952.7203 154614

52 6448.66 141675

56 6944.6248 134123

60 7440.5859 125766

64 7936.5529 118684

As you can see from this view: The current buffer_cache size of the system is 32m,estd_physical_reads field is the number of physically read blocks, the Estd_physical_read_factor field is the ratio of physical read/logical reading, the smaller the better.

As you can see, the larger the Buffer_cache, the less physical reading block, the smaller the ratio of physical read/logical reading, so my experimental environment is to increase buffer_cache. You can add to 64M first, then run for a period of time, and then continue to combine the V$db_cache_advice view, finally set an optimal value.