Skip to Content

How can i monitor the internal usage of system tables in SAP Adaptive Server Enterprise?


is there any way to monitor the access/usage of system tables?

For other tables it is possible with monOpenObjectActivity for example, but all these monitoring tables doesn't contain the usage or access to system tables.

The reason i ask is, that we have a lot of system tables cache usage resulting in much logical reads - to which all system tables are bind - in SAP Adaptive Server Enterprise 15.7 SP132.

Many thanks in advance and beste regards,


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 03, 2018 at 03:35 AM

    You could try turning on traceflag 3650 to enable the capture of system table stats by the MDA tables. [I recommend you test this for awhile in development before enabling it in a production system.] [NOTE: I scrounged this tidbit from Rob's purple book.]

    Keep in mind that unlike user tables, you likely won't see much in the way of SQL text querying the system tables (unless you happen to have a largish volume of monitoring/maintenance scripts hitting the system tables).

    As Bret's pointed out, there may not be a lot you can do to reduce the volume of system table hits; though if you happen to be running with statement cache disabled there may be some room for improvement (ie, reduction in some system tables hits) if you were to enable statement cache, ymmv.

    On the other hand, the performance metrics (eg, physical/logical IOs) may help track down which system tables might be better suited in their own cache.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02, 2018 at 05:24 PM

    Could you expand on exactly what information you would hope to have on each table?

    What would you hope to be able to do with this sort of information if it were available?
    My initial thought is that there isn't much - a select from a table is going to have to reference syscolumns to check column names and datatypes, for instance, you can't really do anything about that.

    You could create a small named cache for each system table and then be able to monitor table use by usage on the corresponding cache.

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks to you Bret.

      Bind every system table or a set of system tables to seperate caches is not a good solution in every production environment and means a lot of changes. But in our case, to identify the usage of sysdatabases and sysusers (see below), that's something what we can do. Nevertheless we will use traceflag 3650 (see mark's answer) as the starting point to solve the "problem".

  • Feb 06, 2018 at 05:26 AM

    Great support! Thanks.

    Traceflag 3650 will help us to identify the hot system tables.
    Especially we want to know if we hit the bug (CR 761333) out of release SP134 ("In some workloads with lot of dynamic prepare statements executing on the system, we would see buffer cache of system table 'sysusers' and 'sysdatabases' to be hot.") or if there is another problem, maybe an application problem. We see the high frequency without changing the release and that's why we want to clarify the problem or identify the reason.

    We know that we are not really able to do anything if we know that system tables are accessed frequently. That's how ASE works, and he is working fine ;)

    Best regards and again thanks to you and your support,


    Add comment
    10|10000 characters needed characters exceeded