Low Catalog Cache Hitrate

[Former Member]

Hi,

Currently, our APO production system has a low catalog cache hitrate of 80%. This hitrate rarely climbs up to a value greater than 85%.

Can anybody help me in identifying the factors that affect this? How can we improve this?

Below are current statistics and parameters in the system:

Cache Sizes

Size in KB Size in pages

I/O Buffer Cache 3,353,808 419,226

Data Cache 3,351,552 418,944

Converter 1,952 244

Other 304 38

Catalog Cache 35,328 4,416

Sequence Cache 8 1

Cache Accesses

Accesses Successful Unsuccessful Hit Rate

Entire Data Cache 210,815,372 210,739,279 76,093 99.96%

History/Undo 20,076,913 20,076,913 0 100.00%

OMS Data 158,934,232 158,861,439 72,793 99.95%

SQL Data 31,804,227 31,800,927 3,300 99.99%

Catalog Cache 20,212,459 16,282,442 3,928,363 80.56%

Sequence Cache 0 0 0 100.00%

Data Cache Usage

Usage in KB Usage in Pages Occup.

Entire Data Cache 1,386,752 173,344 41.38%

SQL Data 30,432 3,804 0.91%

OMS Data 722,328 90,291 21.55%

History/Undo 633,816 79,227 18.91%

Cache-Specific Parameter Settings

CACHE_SIZE = 419226

CAT_CACHE_SUPPLY = 4416

Let me know what your comments and suggestions.

Thanks in advance.

Best regards,

Kris Caldoza

[Former Member · Accepted solution]

Hi Kristofferson,

Check and make sure that the value of the CAT_CACHE_SUPPLY is equal to

40MAXUSERTASKS or more. If this value is less than 40MAXUSERTASKS, then try increasing this value.

This will help you quite a lot.

Thanks

Abhi

[lbreddemann · Accepted solution]

Hi Abhi,

an interesting formula - where did you get it from?

From the formula you state: for each usertask there should be 40 pages reserved for Catalog Cache (assuming that all usertasks are used).

So how is this 40 pages derived? I cannot see the general reasoning for that.

Based on your numbers Kris system would be able to fire a MAXUSERTASK value of 110 with his current setting. As we don't know how the current real setting is we cannot say right know if he already passed that amount.

Nevertheless in MaxDB there is not much you can do about a bad catalog cache hitrate except throwing in a bit more cache.

Therefore it's not so much of calculating the parameter correctely (if it would be that simple, this calculation would be already included in the software) but more about monitoring the system and adjusting it.

KR Lars

[Former Member · Accepted solution]

Thanks for the suggestion Abhi.

Current value of MAXUSERTASKS in the system is 68.

Based on your formula, 40*68 = 2720, which is lower than the current value of CAT_CACHE_SUPPLY.

So I guess, I just need to increase this parameter even more.

[Former Member · Accepted solution]

Thanks for the analysis Lars.

By how much should we try to increase the CAT_CACHE_SUPPLY value? What else can I monitor in the system that will help me analyze the statistics and probably help me come up with an appropriate value for CAT_CACHE_SUPPLY?

Below are the current parameter values in the system:

Parameter / Time New Value

-

---

|-- ALLOW_MULTIPLE_SERVERTASK_UKTS NO

|-- AUTO_RECREATE_BAD_INDEXES NO

|-- BACKUP_BLOCK_CNT 8

|-- CACHE_IN_SHARED_MEMORY NO

|-- CACHE_SIZE 419226

|-- CAT_CACHE_SUPPLY 4416

|-- CHECK_TABLE_WIDTH NO

|-- CONTROLUSERID CONTROL

|-- CONVERTER_REGIONS 8

|-- DATA_VOLUME_GROUPS 1

|-- DATA_VOLUME_NAME_0001 /sapdb/LCP/sapdata/DISKD0001

|-- DATA_VOLUME_NAME_0002 /sapdb/LCP/sapdata/DISKD0002

|-- DATA_VOLUME_SIZE_0001 1024000

|-- DATA_VOLUME_SIZE_0002 512000

|-- DATA_VOLUME_TYPE_0001 L

|-- DATA_VOLUME_TYPE_0002 L

|-- DATE_TIME_FORMAT INTERNAL

|-- DEADLOCK_DETECTION 0

|-- DEFAULT_CODE ASCII

|-- DIAG_HISTORY_NUM 2

|-- DIAG_HISTORY_PATH /sapdb/data/wrk/LCP/DIAGHISTORY

|-- EXPAND_COM_TRACE NO

|-- EXTERNAL_DUMP_REQUEST NO

|-- FBM_VOLUME_BALANCE 10

|-- FBM_VOLUME_COMPRESSION 50

|-- FORBID_LOAD_BALANCING NO

|-- FORMATTING_MODE PARALLEL

|-- FORMAT_DATAVOLUME YES

|-- HASHED_RESULTSET NO

|-- HASHED_RESULTSET_CACHESIZE 262144

|-- HEAP_CHECK_LEVEL 0

|-- HS_STORAGE_DLL libhsscopy

|-- HS_SYNC_INTERVAL 50

|-- INIT_ALLOCATORSIZE 262144

|-- INSTANCE_TYPE LVC

|-- JOIN_MAXTAB_LEVEL4 64

|-- JOIN_MAXTAB_LEVEL9 5

|-- JOIN_OPERATOR_IMPLEMENTATION NO

|-- JOIN_SEARCH_LEVEL 0

|-- JOIN_TABLEBUFFER 128

|-- KERNELDIAGSIZE 800

|-- KERNELTRACESIZE 1197

|-- KERNELVERSION KERNEL 7.5.0 BUILD 035-123-123-944

|-- LOAD_BALANCING_CHK 0

|-- LOAD_BALANCING_DIF 10

|-- LOAD_BALANCING_EQ 5

|-- LOCAL_REDO_LOG_BUFFER_SIZE 0

|-- LOG_BACKUP_TO_PIPE NO

|-- LOG_IO_QUEUE 66

|-- LOG_MIRRORED NO

|-- LOG_SEGMENT_SIZE 170666

|-- LOG_VOLUME_NAME_001 /sapdb/LCP/saplog/DISKL001

|-- LOG_VOLUME_SIZE_001 512000

|-- LOG_VOLUME_TYPE_001 F

|-- LRU_FOR_SCAN NO

|-- MAXBACKUPDEVS 2

|-- MAXCPU 16

|-- MAXDATAVOLUMES 12

|-- MAXLOCKS 300000

|-- MAXLOGVOLUMES 2

|-- MAXPAGER 64

|-- MAXRGN_REQUEST 3000

|-- MAXSERVERTASKS 22

|-- MAXUSERTASKS 68

|-- MAXVOLUMES 15

|-- MAX_HASHTABLE_MEMORY 5120

|-- MAX_RETENTION_TIME 480

|-- MAX_SERVERTASK_STACK 100

|-- MAX_SINGLE_HASHTABLE_SIZE 512

|-- MAX_SPECIALTASK_STACK 100

|-- MCOD NO

|-- MEMORY_ALLOCATION_LIMIT 0

|-- MIN_RETENTION_TIME 60

|-- MP_RGN_LOOP 100

|-- OMS_HEAP_BLOCKSIZE 10000

|-- OMS_HEAP_COUNT 16

|-- OMS_HEAP_LIMIT 4192256

|-- OMS_HEAP_THRESHOLD 100

|-- OMS_STREAM_TIMEOUT 30

|-- OMS_VERS_THRESHOLD 2097152

|-- OPMSG1 /dev/console

|-- OPMSG2 /dev/null

|-- OPTIMIZE_FETCH_REVERSE YES

|-- OPTIMIZE_JOIN_OPERATOR_SORT YES

|-- OPTIMIZE_JOIN_OUTER YES

|-- OPTIMIZE_JOIN_PARALLEL_SERVERS 0

|-- OPTIMIZE_OPERATOR_JOIN_COSTFUNC YES

|-- OPTIM_CACHE NO

|-- OPTIM_FIRST_ROWS NO

|-- OPTIM_INV_ONLY YES

|-- OPTIM_JOIN_FETCH 0

|-- OPTIM_MAX_MERGE 500

|-- PACKET_SIZE 131072

|-- PREALLOCATE_IOWORKER NO

|-- REQUEST_TIMEOUT 180

|-- RUNDIRECTORY /sapdb/data/wrk/LCP

|-- SEQUENCE_CACHE 1

|-- SESSION_TIMEOUT 900

|-- SET_VOLUME_LOCK YES

|-- SHAREDSQL NO

|-- SHAREDSQL_COMMANDCACHESIZE 32768

|-- SHAREDSQL_EXPECTEDSTATEMENTCOUNT 1500

|-- SHOW_MAX_STACK_USE NO

|-- SIMULATE_VECTORIO NEVER

|-- SUPPRESS_CORE YES

|-- SYMBOL_DEMANGLING NO

|-- SYMBOL_RESOLUTION YES

|-- TRACE_PAGES_BUP 0

|-- TRACE_PAGES_EV 2

|-- TRACE_PAGES_GC 20

|-- TRACE_PAGES_LW 5

|-- TRACE_PAGES_PG 3

|-- TRACE_PAGES_SV 5

|-- TRACE_PAGES_TI 2

|-- TRACE_PAGES_US 10

|-- TRACE_PAGES_UT 5

|-- UPDATESTAT_SAMPLE_ALGO 0

|-- USE_COROUTINES NO

|-- USE_OPEN_DIRECT NO

|-- USE_STACK_ON_STACK NO

|-- USE_SYSTEM_PAGE_CACHE YES

|-- USE_UCONTEXT YES

|-- UTILITY_PROTSIZE 100

|-- VOLUMENO_BIT_COUNT 8

|-- XP_CONVERTER_REGIONS 0

|-- XP_DATA_CACHE_RGNS 0

|-- XP_MAXPAGER 0

|-- XP_MP_RGN_LOOP 0

|-- AKDUMP_ALLOWED YES

|-- BACKUPHISTFILE dbm.knl

|-- BACKUPMED_DEF dbm.mdf

|-- CATCACHE_MINSIZE 262144

|-- DATACACHE_RGNS 64

|-- DELAYCOMMIT NO

|-- DELAYLOGWRITER 0

|-- DIAGSEM 0

|-- DWIO_AREA_FLUSH 50

|-- DWIO_AREA_SIZE 50

|-- DWLRU_TAIL_FLUSH 25

|-- DYNTASK_STACK NO

|-- _EVENTFILE knldiag.evt

|-- _EVENTSIZE 0

|-- EVENTALIVE_CYCLE 0

|-- FBMLOW_IO_RATE 10

|-- IDXFILELIST_SIZE 2048

|-- IOPROCSFOR_PRIO 0

|-- IOPROCSFOR_READER 0

|-- IOPROCSPER_DEV 2

|-- IOPROCSSWITCH 2

|-- _KERNELDIAGFILE knldiag

|-- _KERNELDUMPFILE knldump

|-- _KERNELTRACEFILE knltrace

|-- LOCKSUPPLY_BLOCK 100

|-- _MAXEVENTS 100

|-- _MAXEVENTTASKS 1

|-- MAXGARBAGECOLL 10

|-- MAXTASKSTACK 1500

|-- _MAXTRANS 368

|-- MAXMESSAGE_FILES 69

|-- MBLOCKDATA_SIZE 32768

|-- MBLOCKQUAL_SIZE 16384

|-- MBLOCKSTACK_SIZE 32768

|-- MBLOCKSTRAT_SIZE 8192

|-- MINREPLYSIZE 4096

|-- MINSERVER_DESC 17

|-- MPDISP_LOOPS 2

|-- MPDISP_PRIO YES

|-- MPRGN_BUSY_WAIT YES

|-- MPRGN_DIRTY_READ YES

|-- MPRGN_PRIO YES

|-- MPRGN_QUEUE YES

|-- MULTIO_BLOCK_CNT 8

|-- OMSREGIONS 8

|-- OMSRGNS 33

|-- PAGESIZE 8192

|-- PRIOBASE_COM 10

|-- PRIOBASE_IOC 80

|-- PRIOBASE_RAV 80

|-- PRIOBASE_REX 40

|-- PRIOBASE_U2U 100

|-- PRIOFACTOR 80

|-- READAHEADBLOBS 32

|-- RESTARTTIME 600

|-- ROWRGNS 8

|-- _RTEDUMPFILE rtedump

|-- SERVERDBFOR_SAP YES

|-- SERVERCMD_CACHE 23

|-- SERVERDESC_CACHE 93

|-- _SHAREDDYNDATA 419575

|-- _SHAREDDYNPOOL 155849

|-- _SHMKERNEL 3522556

|-- SVP1_CONV_FLUSH NO

|-- TABRGNS 8

|-- TASKCLUSTER01 tw;al;ut;2000*sv,100*bup;10*ev,10*gc;

|-- TASKCLUSTER02 ti,100*dw;5*us;

|-- TASKCLUSTER03 equalize

|-- TRANSRGNS 8

|-- _UNICODE YES

|-- USEIOPROCS_ONLY NO

|-- UTILITYPROTFILE dbm.utl

|-- WORKDATASIZE 8192

--- WORKSTACKSIZE 8192

Thanks!

[former_member229109 · Accepted solution]

Hello All,

-> The formula came up from the "Setting initial parameters for liveCache", for example,

SAP Note No. 719652. Where the value of the liveCache instance parameter

CAT_CACHE_SUPPLY was recommended.

-> The parameters values for the database instance, which is not LVC type, may be

need the different settings. Pay attention for that. And apply the correct note

-> Please review the information in the SAP note:: 819641 FAQ: MaxDB Performance

-> Cris reported issue on the APO system. As far I know the customer is running the

DB2 APO database, therefore the question about settings of the CAT_CACHE_SUPPLY

parameter could be checked in the SAP Note No. 719652. Because the issue with

'a low catalog cache hitrate of 80%' is in liveCache instance.

Please correct me, Cris, if I was wrong.

-> For SAP liveCache documentation in English < See the SAP note 767598 >:

http://help.sap.com/saphelp_nw04/helpdata/en/f2/0271f49770f0498d32844fc0283645/frameset.htm

TA /nLC10, Memory Areas, Caches:

Calculates the overall hitrate, that is right from the start of the

liveCache until now and gives you the ratio in percent.

And as I could see on your system in the transaction /nLC10

->liveCache:Monitoring -> Current Status -> Memory Areas ->

Caches : for the catalog cache

liveCache Alert Monitor ::

The Alert Monitor collects data every 15 minutes and calculates the

resulting percentage hitrate only from the data gathered within these

15 minutes < by default settings>.

So the hitrate determined by the Alert Monitor will give you a better

impression of the 'real' catalog hitrate as it is not an average over

the whole uptime, but just specific 15 minutes intervals.

-> The low catalog hitrate doesn't implicate some specific database or

application problems. It doesn't by itself indicate or explain bad system performance.

Using the Database Analyzer you could find, if you have the bottlenecks

in the liveCache. For more information about the database analyzer please see SAP Note 530394.

One possible reason for bad cataloge cache could be very frequently rollbacks which were send

from the application side and cleared the cache every time. Another possible reason is that catalog

cache is not yet filled up to significant level.

"Database Analyzer" SAP documentation available at

http://help.sap.com/saphelp_nw04s/helpdata/en/2b/361e3bd129be04e10000000a114084/content.htm

< Messages -> Catalog cache hitrate >

-> Cris, if you still didn't find the reason of the 'a low catalog cache hitrate of 80%' in liveCache instance

question, I recommend to create the OSS message to the 'BC-DB-LVC' component, therefore we

could logon to your system to monitor the liveCache & check the liveCache logs.

Thank you and best regards, Natalia Khlopina

[former_member229109 · Accepted solution]

Hello Cris,

I saw that you listed the current liveCache parameters values.

I could see that you have the LVC type < liveCache > database & the version:

KERNELVERSION KERNEL 7.5.0 BUILD 035-123-123-944

INSTANCE_TYPE LVC

==>

Please see the SAP notes for the recommended values of the liveCache

parameters CAT_CACHE_SUPPLY, LOAD_BALANCING_CHK & XP_MP_RGN_LOOP :

719652 Setting initial parameters for liveCache 7.5 or 7.6

817884 Parameter values as of liveCache Version 7.5.00

Thank you and best regards, Natalia Khlopina

[Former Member · Accepted solution]

Hi Natalia,

Thank you so much for all the informatin you provided.

I will be reviewing them all (including the OSS notes you mentioned).

For now, I will just continue monitoring the system.

Thanks,

Kris Caldoza

[former_member229109 · Accepted solution]

Hello Kris,

From the information given above, please follow the SAP notes to set the liveCache

parameters :

CAT_CACHE_SUPPLY & XP_MP_RGN_LOOP :

< SAP note 719652 Setting initial parameters for liveCache 7.5 or 7.6 >

LOAD_BALANCING_CHK :

< SAP note 817884 Parameter values as of liveCache Version 7.5.00 >

-> I recommend you to create the OSS message to the component 'BC-DB-LVC',

if you need further help with the configuration of the liveCache parameters.

Thank you and best regards, Natalia Khlopina

[lbreddemann]

Hi Kris,

a low catalog cache hitrate may be the result of many drops/creates in the database. Anyhow, since you may not be able to do much about this, you may just increase the cat_cache_suplly parameter and check if the hitrate got better.

SAP databases tend to have MANY database catalog entries, so the default setting may not be enough for every installation.

KR Lars