Skip to Content
avatar image
Former Member

MaxDB cache pinning

I've done a bit of research on MaxDB cache pinning but I am still not clear on a few items.  I'm hoping the community can offer some advice.

1. What is the best way to determine which tables to pin?  Some of the documentation leads me to believe you should use tables with high reads and low writes, but other documentation mentions high access tables.  Is the term "access" referring to a combination of read and writes?

2. Is there a way to determine if a table is currently in the regular cache area versus the pin cache area?  If it is already in the regular cache most of the time, it might not be valuable to pin it.

3. How do you determine if pinning was successful?  Can you do some before and after testing to make sure the pinning is helpful?

If you can offer any other advice or tips and tricks that would be very helpful.

Regards,

Steve

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 05, 2014 at 12:50 PM

    Hi Steve,

    first let me say that 'cache pinning' is not used in the usual SAP MaxDB installations. This feature has primarily been introduced for MaxDB running as liveCache installation where we wanted to ensure that some vital SQL tables would always stay in the cache.

    This said, there is not much guidance I can offer for normal MaxDB installations as to which tables to pin and what size to reserve -it really depends on the individual scenario.

    If you want to use table pinning, I would strongly recommend to identify tables you think would most benefit from being permanently in the data cache. This would likely be small tables (so that the pinning area can be kept small) which are frequently accessed or which you have experienced long run times you could not optimize with a index or by rewriting the SQL.

    For enabling table pinning you need to first reserve space in the data cache by setting the parameter 'DataCachePinAreaThreshold'. All tables to be pinned must have the 'CACHE' attribute set (create/alter table ... cache/nocache).

    If you enable the MaxDB Database Analyzer (always strongly recommended!), you can look at the data in the DBAN_CACHES.csv file to determine the table pinning effectiveness - the last 4 columns are of interest here:
    1. PinPag_Acc (accesses to pinned pages)
    2. PinPage_Succ (successful accesses to pinned pages)
    3. PinPage_Fails (failed accesses to pinned pages)
    4. PinPage_Hit (hitrate to pinned pages).

    Important is that your pinned table should completely fit into the pin area.
    However, tracking the effectivness becomes complicated if you pin several tables as there is no way to record the hitrate for each table individually.

    Hope this helps,
    Thorsten

    Add comment
    10|10000 characters needed characters exceeded