cancel
Showing results for 
Search instead for 
Did you mean: 

MaxDB cache pinning

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

thorsten_zielke
Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Thorsten,

Thanks for the great information.  In light of your post, I've been reviewing our situation and I have two more questions.  I hope you can help.

1. If we use pinning on a regular MaxDB (i.e. without Live Cache) will it cause any problems?  We have one instance that has been using pinning for a few years and I can see the hit rates are good in the DBAN_CACHES.csv file, but are there any negative consequences?

2. Is there a way to tell which tables exist in regular cache?

Regards,

Steve

thorsten_zielke
Contributor
0 Kudos

Hi Steve,

Ad 2)
Short answer: no

Ad 1)
It is not a problem, but a 'trade-off'. The more space you reserver for table pinning, the less Data Cache space you have available for all other database objects, which means that the 'non-pinned' tables would get flushed out earlier.
Lets say you have a performance issue related to a certain database table. Before using table cache pinning, I would advise to first identify the responsible SQL statements via MaxDB 'Command Monitor' and 'Resource Monitor'. Once you have the SQL, you could try to find out, if the statement can be optimized/rewritten to select less data for the desired result (to achive better 'selectivity') or if a database index might help to achieve less table reads. If the problematic statement is a join across tables or views, maybe the optimizer statistics are outdated. Or, maybe you can even delete some data of that table (and archive it somewhere).
And then, yes, you can use 'table pinning', just beware not to pin too big tables or for the reason of poorly optimized SQL statements 🙂

Thorsten

wei_henning
Explorer
0 Kudos

Hi Thorsten,

we also use the pinning of tables in some systems. After some time it's hard to remind which tables have the KEEP flag and are present in the pinning area. Is there a way to find out which of all tables have the KEEP attribute? Is there a system table which hold these entries?

When you know a table name it's easy to find out which setting is set (DBACOCKPIT, DB Studio). But I don't know the other way around (table unknown).

I think it would be usefull for all who want to use this feature.

Kind regards

Henning

Former Member
0 Kudos

Hi Henning,

We had the same problem.  I wrote a small query to retrieve this information.  Try running this query from the DBACOCKPIT--> Diagnostics--> SELECT Editor:

SELECT TABLENAME, CACHINGMODE, TABLEID, FILEID

  FROM DOMAIN.TABLES            

  INNER JOIN SYSINFO.FILES

    ON TABLEID = FILEID

   WHERE CACHINGMODE = 'KEEP'

Regards,

Steve

wei_henning
Explorer
0 Kudos

Thanks! Works very well.

Answers (0)