Skip to Content
0

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

Feb 02 at 01:49 PM

57

avatar image

Hello,

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,

Patrick.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Mark A Parsons Feb 03 at 03:35 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks to you Mark.

Thats really something what we can do. I studied the purple book already but it seems to me that i overlooked the traceflag :).

0
Bret Halford
Feb 02 at 05:24 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left 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".

0
Patrick Scheibe Feb 06 at 05:26 AM
0

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,

Patrick.

Share
10 |10000 characters needed characters left characters exceeded