Determining DATA_CACHE usage (%) via OS commands (e.g. dbmcli)

[Former Member]

I had a quick question on finding out the DATA_CACHE usage in % via an OS level command or SQL statement. I am trying to write a UNIX script that queries the DATA_CACHE usage and sends alerts if it exceeds a threshold say 95%.

Thanks in advance.

[Former Member · Accepted solution]

Markus, I forgot to mention this is for Livecache where a 100% DATA_CACHE leads to performance issues.

Anyways I figured out how to calculate this info:

First get the CACHE_SIZE parameter:

sql_execute select * from sysdba.configuration where description like '%Datacache%'

Then convert to KB:

value / 8 = DataCacheSize

Then get the OMS data page size:

sql_execute select value from monitor where DESCRIPTION='Data cache no of OMS data pages'

Then get the SQL Data page size:

sql_execute select value from monitor where DESCRIPTION='Data cache no of SQL data pages'

Then get the History Page size:

sql_execute select value from monitor where DESCRIPTION='Data cache no of history/undo pages'

Then calculate the % used for Data_Cache

then % used = ( SQL_Pages + OMS_Pages + Hist_Pages ) / DataCacheSize * 100

[thorsten_zielke · Accepted solution]

Sheldon,

I have to concur with Markus. Please note that you want to monitor the Data Cache hit rate, but not the Data Cache filling level. The Data Cache acts like a ram disk meaning that all requests that cannot be satisfied by the Data Cache have to be routed to the I/O system, which of course is especially bad for liveCache instances. What you want to aim for is a Data Cache hitrate of 100% - that would be the optimum, because then every request could be handled in the Data Cache already, so no need for accessing the disks.

Typically after liveCache reboot or if the Data Cache is configured too small, the hitrate will be much smaller, but in the long run you really want to have a good data cache hitrate.

The point is that we do not deliberately empty the Data Cache and so the filling level will reach 100% on any system. All data read stays in Data Cache, unless we need to make space for new data, then we have to decide on what gets deleted, but the Cache will be kept full at all times. It is really all about keeping the cache hitrate high.

If you want to check some liveCache stats via script, it might help to take a look at our Database Analyzer tool, which does a lot of health checks via SQL/internal system tables.

Thorsten Zielke

[thorsten_zielke · Accepted solution]

Sheldon,

one more thing:

looking at your dbmcli commands posted above I think what you intend to do is to monitor the data growth in your liveCache and the distribution via history and normal data. This is a good idea, because removing history too late (when it is not in the Data Cache any more) can cause trouble in liveCache environments.

Thorsten Zielke

[Former Member]

Guys thanks fo the responses. I forgot to mention this is for Livecache and not the standard Maxdb hence DATA_CACHE occupancy is very important to monitor to prevent performance issues.

I figured out the sql commands posted in my previous email by looking at the db analyser logs.

Edited by: Sheldon Baracho on Dec 17, 2008 4:59 PM

[markus_doehr2]

A full DATA_CACHE usage is not an alert. It´s normal that you cache is used to 100 % - which is a good thing. The more data is in the cache the less physical I/Os are necessary --> faster system.

Markus