Sybase monProcessActivity

Hello All,

I've noticed in 12.5 monProcessActivity (MDA Table), there is no timestamp column.  Anybody know how offen monProcessActivity retain its data? Does it get refresh?  I'm trying to collect total number of committed transaction in a 24 hours period without using sp_sysmon.

Thank you,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
The monProcess% tables are only a snapshot of the process activity at the time they are queried - exactly like querying sysprocesses (and in fact hooking into many of the same areas of the ASE kernel).

Thus monProcessActivity retains no data at all, under any circumstances. Each time you query it you get a new snapshot of the process activity at that moment.

Good practice with any MDA table is to copy it into a "real" table and work with that data. You might think you could therefore insert to some kind of transaction history table the current contents of monProcessActicity, possibly formatted/manipulated/aggregated, but this is not actually a good idea. The MDA tables are not real tables, they are stored procedures whose result set is exposed as a table, and you can get poor performance issues if you hold that result set open for any kind of join, aggregation or other processing. Also anything that requires accessing the MDA table more than once (like a subquery being run once for each qualifying outer row) will behave strangely as the MDA table will return different results each time it is run.

So best practice with MDA is to dump it, as is, with as little processing as possible (preferably no processing at all, not even a WHERE clause), straight into a temp table. Then you can go to work on it - filter it, aggregate it, index it, format it - and send those results to your more permanent archive table.

To answer your specific question, all monProcess% tables are refreshed every time they are accessed.
tranicusAuthor Commented:
Hello Joe,

Thank you for the info but it still bug me. How does sp_sysmon able to pull those 'transaction summary, committed Xacts'?  One would think it have to come from somewhere.  I can't imagine an automated process run on hourly, dumping data from monProcessActiviry into a historical table.  
Joe WoodhousePrincipal ConsultantCommented:
sp_sysmon doesn't use MDA tables, but it is using some of the same underlying infrastructure.

You may find what you want in the history tables rather than the session tables, ie the monSys% MDA tables.

Like the monProcess% tables, they are refreshed each time they are accessed... but they contain a cumulative history of everything that's happened since the last time it was accessed or since ASE was booted if they haven't been accessed yet.

sp_sysmon is doing something similar. It inspects a bunch of monitor counters at the start of its sample interval, and again at the end, and calculates a number of deltas or changes between the start and end numbers.

monProcessActivity can't give you what you say you want, because it only knows about what's running right then. It doesn't aggregate over time, and has no history, it only shows a snapshot in time. This is useless for what you want.

In fact I've just gone and reread all my MDA docs and presentations from Techwave and I don't think MDA can give you this number (total committed xacts per day).

Is there a reason you don't want to use sp_sysmon? It would do the job - run it every 20 minutes, grep for the committed xacts, sum them.

If you really don't want to or can't use sp_sysmon, the Monitor Server and Historical Server can do this for you. They still exist and still work, but there is a bit of a learning curve to using them, and they interfere with using sp_sysmon (and to some extent the MDA tables too).

Take a step backwards. Why do you want to know the number of committed xacts per day? What will knowing that allow you to do? We may be able to get at it from another direction.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tranicusAuthor Commented:
Much appreciated Joe... You gave me alot to think about the MDA tables, I wish Sybase were better in it explaination.
Joe WoodhousePrincipal ConsultantCommented:
Glad that helped!

You and others might find the resources at helpful. Good luck!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.