cancel
Showing results for 
Search instead for 
Did you mean: 

Estimate cache displacement - optimum size of CACHE_SIZE

Former Member
0 Kudos

Is there a way to determine out of the statistics data in the dbanalyzer what would be the optimum cache size for the database?

I want to know that because we plan to buy a new box and I want to size it so the actual "daily used data" is in the cache -> no (or few) User-UKTs doing reading I/O. So basically building something like an "in memory database".

Markus

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

the optimum size for the data cache depends on the used Perm size of the database and on the application.

For SAP Systems we please check the formula in SDN using the following link:

[Memory Sizing|https://wiki.sdn.sap.com/wiki/x/RWY]

Regards, Christiane

markus_doehr2
Active Contributor
0 Kudos

Hi Christiane,

> the optimum size for the data cache depends on the used Perm size of the database and on the application.

Yes - I know that - that's why I ask thist question how to find out.

> For SAP Systems we please check the formula in SDN using the following link:

> [Memory Sizing|https://wiki.sdn.sap.com/wiki/x/RWY]

Our database size is 1.8 TB - the given rule-of-thumb would mean we should have 36 GB CACHE_SIZE. We currently have 32 GB but there's still a lot of I/O done (logical I/O: 48.751.253.597 vs. physical I/O 386.955.885).

Since the dbanalyzer gives many statistical values I'm curious if there is a way to find out, which size of the cache would eleminate (at least theoretically) UKTs doing I/O

Markus

Former Member
0 Kudos

Hi Markus,

interesting are the physical I/O. This tells us that not all pages which have to be processed can be found in the data Cache.

The parameter LRU_FOR_SCAN restricts the size of the data cache which is used for Scans. More Info about the Data Cache configuration you will find in the MaxDb Internals slides: [Parameters|http://maxdb.sap.com/training/internals_7.6/kernel_parameters_EN_76.pdf]

Could it be that we have a lot of scans in the system on large tables?

Did you get any more detailrd information with the Command Monitor which commands (Reports) have to read from disk?

May be we should analyze this directly on your system via OSS call to get more ideas.

Regards, Christiane

markus_doehr2
Active Contributor
0 Kudos

> interesting are the physical I/O. This tells us that not all pages which have to be processed can be found in the data Cache.

Yes - and there's a lot of it

> The parameter LRU_FOR_SCAN restricts the size of the data cache which is used for Scans. More Info about the Data Cache configuration you will find in the MaxDb Internals slides: [Parameters|http://maxdb.sap.com/training/internals_7.6/kernel_parameters_EN_76.pdf]

LRU_FOR_SCAN is set to NO. If we would set that to YES and one person would start a big job (MRP-runs for PP, CO precaluations etc.) this would "trash" the full cache and all other UKTs would need to do I/O to get their current data again.

> Could it be that we have a lot of scans in the system on large tables?

> Did you get any more detailrd information with the Command Monitor which commands (Reports) have to read from disk?

No full table scans but "just" longrunning jobs (tablename, runtime, Cache I/O, Disk I/O)


Table	Runtime		P accesses	P Cache I/O	# Disk I/O
===================================================================
COVP	61.866,92	6.289.191	196.602		6.092.589
ACCTIT	35.678,21	5.215.440	896.540		4.318.900
COSS	12.321,35	1.872.759	606.268		1.266.491
BALHDR	12.238,66	1.134.360	65.534		1.068.826
COSS	12.109,92	1.764.038	601.264		1.162.774
VBRP	11.909,13	1.850.768	660.289		1.190.479
COSS	11.188,26	1.872.726	601.976		1.270.750
BKPF	8.734,46	916.325		65.534		850.791

There's not a single table scan done, all are done via IX_RG or PK_RG or a combination of them. Those are just the "top 10", there are much more of those.

What I now want to archive is the Disk-I/O becoming 0 due to a big CACHE_SIZE.

>

> May be we should analyze this directly on your system via OSS call to get more ideas.

340157/2008 - same question there too

Markus

Former Member
0 Kudos

Hi Markus,

I took the call. Let's start analysis via OSS call now.

I will contact you soon.

Regards, Christiane