promon R&D, monitoring (SQL) servers

Posted by Stefan Marquardt on 16-Nov-2015 07:58

How do you identify and monitor running server types?

I know to run R&D, 1 Status, 3 Servers but would like to see whether the server is in use for SQL or ABL.
My knowledge is that one time used for SQL it's unusable for ABL That's not like the offered configuration where I can select ABL/SQL mix.
Why space is wasted with the column Protocol when it's all the time TCP?

A workaround is to open "Background process" and then join both information but this should be possible in an easier way.

And Progress, please add a simple "A" to the main menu of promon to get rid of this terrible "R&D".
It was hidden such a long time and still looks like a foreign body not really included in the real main structure.

Can I see how long users were waiting for a server because it was busy by a single threaded long running query?

All Replies

Posted by George Potemkin on 16-Nov-2015 08:58

There is a large list of the screens/fields that are obsolete in promon. Promon does need an essential upgrade.

Posted by Richard Banville on 16-Nov-2015 09:28

It would be helpful if you could give a few details of obsolete things in promon.  You seem to indcate that a lists exists (maybe just a translation thing).

Posted by Richard Shulman on 16-Nov-2015 09:33

In promon, option 1, option 1 you can see what is a server and what is a SQL server by the Type designation.

SQSV for SQL server and SERV for ABL server.

If you use this import file with a command like this promon dbname < inputfile > outputfile:










Then grep for SQRV or SERV you would see which are which.

As an extra you could also see which are part of which broker with this input file:









You would have to file an enhancement request to have any of the features you've mentioned that are not part of promon added in future.

To my knowledge we don't make visible wait time by users (at least not directly).

If you were scraping multiple iteractions of the blocked clients screen in promon you might calculate the wait time by how many iterations you recorded for a user before it disappears from the blocked client screen.

This could also likely be programmatically performed through VSTs.

Posted by George Potemkin on 16-Nov-2015 12:12

An example of totally obsolete screen: Activity: Space Allocation

It was introduced when db had only one area and the area had only one RM chain.

rm blocks examined

Remove from rm

Add to rm, back

Move rm front to back

Remove locked rm entry

The values above are in blocks and are total per all chains.

The unit of "Add to rm, front" is a cluster size - some "average cluster size" per database.

I guess all these values are counted based on the types of recovery notes. One note is used to format/add a whole cluster to the head of RM chain.

Posted by Richard Banville on 16-Nov-2015 12:41

So would you say they are obsolete or just have different meaning now with Type II and you'd like to see more granularity on a per area per object basis as opposed to a per database wide basis?

Posted by George Potemkin on 16-Nov-2015 13:53

Sorry for petulant tone of my previous posts: it was caused by a long battle with a customer and the subject was "how safely disconnect a self-service clients?".

> you'd like to see more granularity on a per area per object basis as opposed to a per database wide basis?

More granularity. Probably it could be moved to the _TableStat and/or to the correspondent screen in promon: Activity: I/O Operations by User by Table
The activity statistics about the individual RM chains could be useful to identify the reasons why RM chain are growing even if the toss limits is optimized. I found a workaround how to get such statistics at least for long RM chains: by comparing the full list of the blocks reported by chanalys running, for example, with one day interval. We can get all fields reported by Activity: Space Allocation and more. But the workaround could require the gigabytes of data to be processed. The length of RM chain (and the toss limit) as well as the create limit have the huge effect on the speed of table scan - even for the non-indexed table scans (speed degradation can be a few tens times!). IMHO, it would be useful to monitor what is going on with RM chains.

There is a similar issue with prostrct statistics
Why prostrct statistics data does not match free block size with dbanalys report after deleting many records?
prostruct statistics is area based and is never enhanced to take into consideration the free list (and other block information) of individual objects in the area, only the free list for the area itself. Since the free space in the object has not been given back to the area (it is still reserved by the object) it is not being reported. In other words it is reporting area free space, not object free space.

For example it reports the number of free blocks but only the ones that owned by area control object. This value is defined almost by a cluster size (in the most cases: CS = ACO's free blocks + 6). Hence prostrct statistics (as well as _AreaStastus VST) does NOT report how much free space we have in database. In other words, we don't have a fast utility to get the real statistics per database. Dbanalys does but it's slow.

Best regards,

This thread is closed