What is th optimal Database Buffer size?

[Former Member]

Good evening SAP/Oracle experts,

I am interested to know how many of you have chosen to size your Oracle data buffer (db_cache_size). There seems to be two trains of thought about this

- one that says size the data buffer as large as you can because buffer accesses are 1000s of times quicker than disk accesses

- another that says that "excessive buffer visits are the morbid obesity of the database. Extra logical I/O can degrade the perfromance of virtually every subsystem in an Oracle application" (quote Cary Millsap)

As I tend to prefer the second argument, I originally sized the data buffer on our ECC5 Production system (with CI/DB running on a server with 8Gb RAM) at 500Mb. A few months after go-live, I increased the buffer to 750Mb and recently I increased it again to 1Gb. I found a slight performance improvement with each increase, but database time remained at around 50% of total response time (from ST03N) throughout. Our average response time is currently around 700-750ms, with database time around 300-400ms. Our database is currently around 450Gb.

I therefore ask you, the SDN community, to tell me how large you tend to size your data buffer in comparison to the total amount of RAM on your database server, and what % of your average response time is spent down at the database.

Thanks in advance for any help you can provide.

Arwel Owen

SAP Infrastructure Manager,

Princes Ltd.

[Former Member]

At the OS level, it probably makes sense to size your SHM* parameters

to allow 75% to (maybe) 90% of RAM for shared memory.

As for sizing the actual SGA, make it as big as you need it to be, and

no bigger. In the meantime, there is no benefit to adding mode memory to the SGA than you actually need; in fact it could conceivably hurt performance.

you need to calculate the amount of memory your database(s) are going to use, for example:

The database SGA size(s) +

Your automatic PGA management (PGA_AGGREGATE_TARGET) +

Your user processes (keeping in mind their connection method: shared vs dedicated connections) +

Your O/S +

Any other services that are running on your server such as Enterprise Manager database control etc +

future growth predictions !

Main target is not db_cacahe_size it is SGA and <b>fit the entire SGA into physical memory. </b>

[Former Member]

Hi Vinod,

Thanks for the posts. I'm relatively happy with performance of our systems and feel that we've got got things configured fairly well.

What I'm really interested in is obtaining a comparison of how large other SAP/Oracle houses have configured their data buffer in comparison to how much RAM their database server has.

i.e. Our ECC5 DB/CI server has 8Gb RAM and a DB Buffer size of 1Gb. How much RAM does your DB server(s) have and how large have you sized your DB Buffer?

Best wishes,

Arwel.

[Former Member]

i am confused i can give these details or not

Central Server => HP Integrity , 2 processors / 4 cores / 8 threads, Dual-Core Intel Itanium 2 9050, 1.6 GHz, 32 KB(I) + 32 KB(D) L1 cache, 2 MB(I) + 512 KB(D) L2 cache, 24 MB L3 cache and 48 GB main memory. The server was running the HP-UX 11iV3 operating system, Oracle Database 10g, and mySAP ERP 2005.

configured with buffer cache, java pool, and shared pool set to 1953MB, 150MB, and 1200MB respectively for one DB.

[Former Member]

very valuable info u need to award me great points

for 64-bit Linux system with 2 CPU 16GB RAM

configured for Oracle9iR2

64-bit SLES9

db_block_buffers 250000

db_block_size 8192

java_pool_size 157286400

shared_pool_size 1258291200

Vinod

[Former Member]

my friend send their config which they have implemented

PHYSICAL_MEMORY_BYTES 33,621,540,864

db_block_size 8192

db_cache_advice off

db_cache_size 1509949440

shared_pool_reserved_size 157286400

shared_pool_size 1577058304

[Former Member]

Hi Vinod,

Thanks for the information.

Having worked in BASIS for the same company for nearly 10 years, it's always interesting to see what other companies do and how they work. Your servers have a lot more RAM than ours, yet your buffer caches are not much larger.

Did you use V$DB_CACHE_ADVICE to set the size of your caches?

Cheers,

Arwel.

[Former Member]

Arwel,

worked in BASIS for the same company for nearly 10 years???

Great Friend...I wish we will meet if god creates any opportunity!!!

To be frank No I didnt set those values.but I know very well that V$DB_CACHE_ADVICE is used most of the cases and it is recmnded also.

These values were fin'zd by System And Database Arch'ts.My friend confirmed that V$DB_CACHE_ADVICE is very good tool.

Lot more factors will decide size of db cache.To know those things we need to have in depth knowledge of OS and DB and Search in Concepts Doc by Oracle for this we shd have more time which I dont have at this stage.......

Great Arwel GREAT.....

Vinod

[Former Member]

Dear Arwel.

To specify an optimal cache value, you can use the dynamic

DB_CACHE_ADVICE parameter with statistics gathering enabled to predict

behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view.

When configuring a new instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured.

For more details about Sizing the Buffer Cache and Increasing Memory Allocated to the Buffer Cachelook at oracle documentation.

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#30301

In General Dont waste RAM use it completely but minimum 20% to OS and the rest to allocate.

Regards

Vinod