SAP BW 701 and Oracle 11.2.0.2.0

[christoph_greiner2]

Hello,

i upgraded my BW-System from Oracle 10 to Oracle 11.2.0.2.0.

Know I want to optimize the Oracle Parameters.

First Step using

Note 1171650 - Automated Oracle DB parameter check

Now I want to set db_cache_size which is actually set to 3488M. RAMSIZE is 32GB - on a 64-bit X86_64 RedHat 5.6.

SAP Note 1431798 - Oracle 11.2.0: Database Parameter

Settings

says

DB_CACHE_SIZE Size depends on the available memory

(Notes 789011, 617416)

but here SAP uses

db_cache_size = db_block_buffers * db_block_size

In my case db_block_buffer = 0

So does anybody know a better formula for db_cache_size?

Best Regards

[volker_borowski2]

Hi,

did you made the required OLAP changes in the note 1171650 attached script?

The default is for OLTP not OLAP.

Check the "Advisors" in ST04. BW often benefit from a bigger PGA

for doing bigger internal sorts. So may be you should give db_cache_size

and pga_aggregate_target 1500M each ?

Volker

[christoph_greiner2]

Hello Everybody,

first thanx for your help.

I will check the automatic parameter check again - using OLAP=yes.

The DATA BUFFER QUALITY in ST04 is 99,8%

So far it looks good.

[Former Member]

Hello Christoph,

I don't want to sound discouraging, but having a DB Buffer Cache Hitratio of 99.8% on BW means one of the following:

- you have one or more SQL statements with way too many logical reads (which means wasting CPUs)

- you don't have any real workload on your system, it is idling around with some small standard SQL code

- your DB is tiny and you use a really huge buffer cache (which means wasting memory), but this seems to be unlikely with a buffer cache of 3.5 GB

So don't worry about db_cache_size any more, but of course run the parameter check script with OLAP=yes to check your Oracle parameters.

Regards,

Mark

[christoph_greiner2]

Your right 99.8% ist from my testsystem

96% is my prod-system

[Former Member]

Hello Christoph,

first of all, what is your Buffer Cache Quality? Check it via transaction ST04.

If the Buffer Cache Quality is above 94%, then there is no need of increasing

the Oracle Buffer Cache at all.

db_block_buffer is an deprecated parameter and shouldn't be used any more.

Use db_cache_size instead, it is required e.g. if you want to use memory

self-tuning features. db_cache_size = 3488M is all you need to set.

Especially on BW/Oracle systems the database performance seems to be

always slow, but as soon as the basic Oracle KPIs are ok there is little

you can do against it.

Regards,

Mark

[Former Member]

Basically it is pretty simple, there is nothing to calculate. If you have some free physical memory, then you can increase the db_cache_size by that amount.

For example your server has 32gb in total, and 6gb are still free. Then i would for example increase db_cache_size by 5gb. Just remember to not over allocate memory, this will lead to swapping. And do not mesure the memory usage on a freshly started system, do monitor the usage over a longer period first.

Cheers Michael

Edit: what was meant in that formula is just the conversion from the former used db_block_buffers to the now used db_cache_size. On Oracle 8i you had db_block_buffers, so if you had db_block_buffers = 128000, then you set db_cache_size = 1gb on 9i.