Low hit rate alerts in Data Cache

[Former Member]

Hello!

We are getting low hit rate alerts for data cache mostly over the night.

During the normal operatin the data cache ratio is ok (98-99 %).

As consequence we get an alert regarding low data cache ration within weekly EarlyWatch Alert.

Are there a procedure to optimize the data cache ration over the night in order to improve the overall data cache ratio?

Our maxDB version is 7.8.02.034.

Thank you!

[Former Member · Accepted solution]

Hi Alex,

Also view the following note on cache recommendations:

1308217 - SAP MaxDB Version 7.8: Parameter recommendations

Also could be helpful to review 819641 - FAQ: SAP MaxDB Performance.

If you also refer to this link training link with slideshow they have specifics on this to my understanding.

http://maxdb.sap.com/training/expert_sessions/Perf_Optimization_SAP_MaxDB.pdf

Kind Regards,

Johan

[Former Member · Accepted solution]

Hello!

Can some one help to get the DBANALYZER running?

If I try to run the command, I get the following error:

E:\sapdb\ARS\db\bin>dbanalyzer.exe -d ARS -u superdba,password -f dbanalyzer_InstanceParameterCheck.cfg -o c:\param_check -i -c 1 -t 1,1 -n SAPTEST

MaxDB Database Analyzer, The Performance Analysis Tool, Version 7.8.02.34

Copyright 2000-2013 by SAP AG

ERROR 2: Invalid/incomplete program arguments.

Unknown option!

I checked the password of superdba via DMBCLI, it should be right.

[Former Member · Accepted solution]

Hello Alex,

It looks like you did the parameter check correctly. Are you sure that the files are located in your current directory and that you can write into the -o directory.

But notice the parameter check does not give any recommendation related to the Parameter Cache_Size.

If you get worse cache hitrate during batch processing in the night, it really sounds that there are missing indexes for the batch jobs. So the system has to read much more data to get the qualified records. I would first check the ressource monitor - Do you get information about long running SQL.

Additionally you can use transaction ST06/st03 to get the most expensive batch jobs.

And of course you can start Command monitor during night - but please don't forget to stop it in the morning, Otherwise it overwrites you logged commands.

Please check as well the Expert Sesion about the performance monitoring tools:

http://maxdb.sap.com/training/

• Session 4: Performance Optimization with SAP MaxDB
• Session 16: SAP MaxDB SQL Query Optimization (Part 1)
• Session 16: SAP MaxDB SQL Query Optimization (Part 2)
• Session 22: SAP MaxDB Database Analyzer

Regards, Christiane

[Former Member · Accepted solution]

Hello!

many thanks!

If I have 4 CPU on my Windows Server and I only have DB and SAP instance (no dialog instances)  can I set the parameter MaxCPUs to 4 to use all CPU?

[Former Member · Accepted solution]

HI,

Not sure of the sap standard but look at link.

http://www.datadisk.co.uk/html_docs/maxdb/overview.htm

They have basic formula on page if you are running on sap.

Refer also to

http://maxdb.sap.com/training/internals_7.6/kernel_parameters_EN_76.pdf

Another thing I tend to notice if you set value to 1 you have collisions and problems also refer to link.

http://scn.sap.com/thread/1813598

Regards,

Johan

[Former Member · Accepted solution]

Ok,

my situation is that MaxDB and SAP application server are running on the same host.

So it seems that the following formula can be used:

MAXCPU = # of CPU's / 2

[Former Member · Accepted solution]

Hi Alex,

it depends on the database version you are using. Please check if you have the following parameters:

MaxCPUs and UseableCPUs

With MaxCPU yopu can configure the maximum value of CPU cores your server has.

This parameter is not online changeable - you must restart the database to get the change active.

With UseableCPUs you configure the value of CPU cores your database should currently use for UKTs which ahve User Tasks in it. This parameter can be changed in online mode and is getting active immediately.

If you do not have a stand alone database I would set UseableCPUs < MaxCPUs.

In your case UseableCPUs 2.

But notice low datacache hitrate does not point to a CPU bottleneck. So the Change of the CPU configuration won't solve your problem.

Low Cache Hitrate is always caused by statements which do not have the right indexes to reduce the number of rows to be read to get the qualified records.

Using Command Monitor would be the best way to find the reason for the less cache Hitrate.

Regards, Christiane

[former_member188883]

Hi Alex,

It could be happening that during night time many jobs are running which require a better cache size value. Request you to monitor the requirement on cache and update cache_size parameter to fine tune the same.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Many thanks!

The cache ratio via EWA report is 96 % and catalog cache is 81 %

CacheMemorySize is setting to 1000000 and CAT_CACHE_SUPPLY is setting to 14000

Are there some rule of thumb how to set these parameters , e.g. based on RAM-size of server?

Additional question:

If I have 4 CPU on my Windows Server and I only have DB and SAP instance (no dialog instances) how can I set the parameter MaxCPUs?

[former_member188883]

Hi Alex,

You may use database analyzer as described in below SAP note

Note 1111426 - Parameter check for liveCache/MaxDB instances

This will help you to set recommendations as per SAP.

Regards,

Deepak Kori